i need help regarding sending automated reminder to doers mail

[Oltimo MIS]

function createTriggerFromSheet() {

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Setup Sheet");

  if (!sheet) {

    Logger.log("Error: 'Setup Sheet' not found.");

    return;

  }

  let time = sheet.getRange("B2").getValue(); // Fetch time from B2

  time = Number(time); // Ensure it's a number

  if (isNaN(time) || time < 0 || time > 23) {

    Logger.log("Error: Invalid time value in B2. Enter a number between 0 and 23.");

    return;

  }

  deleteExistingTriggers(); // Prevent duplicate triggers

  ScriptApp.newTrigger('sendReminder') // Function to run

    .timeBased()

    .atHour(time)  // Run at the specified hour

    .everyDays(1)  // Run every day

    .create();

  Logger.log("✅ Reminder trigger set for " + time + ":00 hours.");

}

// Function to delete existing triggers to avoid duplicates

function deleteExistingTriggers() {

  var triggers = ScriptApp.getProjectTriggers();

  triggers.forEach(function (trigger) {

    if (trigger.getHandlerFunction() === 'sendReminder') {

      ScriptApp.deleteTrigger(trigger);

      Logger.log("🗑️ Deleted existing trigger for 'sendReminder'.");

    }

  });

}

// Example function to be triggered (Modify this as needed)

function sendReminder() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Get "Doer List" sheet

  var doerSheet = ss.getSheetByName("Doer List");

  if (!doerSheet) {

    Logger.log("Error: 'Doer List' sheet not found.");

    return;

  }

  var lastDoerRow = doerSheet.getLastRow();

  if (lastDoerRow < 2) {

    Logger.log("Error: No doers found.");

    return;

  }

  var doerData = doerSheet.getRange(2, 1, lastDoerRow - 1, 3).getValues(); // Fetch Name, Number, Email

  // Get "Master" sheet

  var masterSheet = ss.getSheetByName("Master");

  if (!masterSheet) {

    Logger.log("Error: 'Master' sheet not found.");

    return;

  }

  var columnToCheck = masterSheet.getRange("B:B").getValues();

  var lastMasterRow = getLastRowSpecial(columnToCheck) - 1;

  if (lastMasterRow < 1) {

    Logger.log("Error: No tasks found.");

    return;

  }

  var taskData = masterSheet.getRange(2, 1, lastMasterRow, 10).getValues(); // Fetch all task data

  var today = new Date();

  var tomorrow = new Date(today);

  tomorrow.setDate(today.getDate() + 1);

  tomorrow.setHours(0, 0, 0, 0); // Normalize time

  Logger.log("Processing reminders for tasks due on: " + tomorrow);

  // Process each doer

  doerData.forEach(function (row) {

    var doerName = row[0]?.toString().trim();

    var doerEmail = row[2]?.toString().trim();

    if (!doerEmail) return; // Skip if email is empty

    let reminderTasks = [];

    // Check all tasks in "Master"

    taskData.forEach(function (taskRow) {

      var assignedEmail = taskRow[9]?.toString().trim(); // Assigned Email (Column J)

      var dueDateValue = taskRow[6]; // Latest Revision Date (Column G)

      var status = taskRow[8]?.toString().trim(); // Task Status (Column I)

      var taskName = taskRow[1]?.toString().trim(); // Task Name (Column B)

      var taskDesc = taskRow[2]?.toString().trim(); // Task Description (Column C)

      if (!assignedEmail || !dueDateValue) return; // Skip invalid rows

      // Convert dueDate to Date Object

      var dueDate = new Date(dueDateValue);

      dueDate.setHours(0, 0, 0, 0); // Normalize date

      Logger.log("Checking task: " + taskName + " | Assigned: " + assignedEmail + " | Due Date: " + dueDate);

      // If task is due tomorrow, assigned to the doer, and not completed

      if (doerEmail === assignedEmail && dueDate.getTime() === tomorrow.getTime() && (status === "" || status.toLowerCase() !== "completed")) {

        reminderTasks.push("Task: " + taskName + " - " + taskDesc);

      }

    });

    // If there are pending tasks, send email

    if (reminderTasks.length > 0) {

      var taskList = reminderTasks.join("\n");

      var message =

        "Hello " +

        doerName +

        ",\n\nYou have the following pending tasks due today:\n\n" +

        taskList +

        "\n\nPlease complete them on time.\n\nThank you!";

      try {

        GmailApp.sendEmail(doerEmail, "Reminder: Pending Tasks Due Today", message);

        Logger.log("Reminder sent to: " + doerEmail);

      } catch (e) {

        Logger.log("Error sending email to " + doerEmail + ": " + e.message);

      }

    }

  });

}

// Function to get last non-empty row in a column

function getLastRowSpecial(range) {

  return range.filter(String).length;

}

// Function to manually test trigger creation

function testCreateTrigger() {

  createTriggerFromSheet();

} this is my code Please tell me why my code is not sending automated reminder to doers.

[Google Pony]

Possible Issues & Fixes

1. Trigger Not Running
   

   • Check Apps Script > Triggers to confirm sendReminder exists.

   • For testing, replace .atHour(time) with .everyMinutes(5).

2. Triggers Not Deleting Properly
   

   • Manually delete old triggers from Apps Script > Triggers and recreate them.

3. Email Sending Limit
   

   • GmailApp allows 100 emails/day. Check Apps Script > Executions for limits.

   • Use Logger.log(doerEmail); before sending emails to verify recipients.

4. Incorrect Date Comparison
   

• • Use .toDateString() instead of .getTime() for date checks:
  • if (dueDate.toDateString() === tomorrow.toDateString())

5. Email Sending Errors
   
   

Log full error details:
try { GmailApp.sendEmail(...); Logger.log("✅ Sent: " + doerEmail); }

catch (e) { Logger.log("❌ Error: " + e.toString()); }

Next Steps

1. Try running sendReminder() manually and check logs (View > Logs).

2. Ensure valid emails with Logger.log(doerData).

3. Verify triggers exist in Apps Script > Triggers.

Sincerely yours,

Sandeep Kumar Vollala

Developer - India