AppScript to send reminders from a Google Sheet based on a timeframe.

reminderSheet.js

/* @Maintainer Shivansh Thapliyal 
** AppScript to send reminders from a Google Sheet based on a timeframe.
**/

// The event handler triggered when opening the spreadsheet. Adds a new Reminders menu to menu bar.
function onOpen(e) {
  // Add a custom menu to the spreadsheet.
  SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp, or FormApp.
      .createMenu('Sheet1')
      .addItem('Send Mail','reminder')
      .addToUi();
}

// This is our main function that is being called by the  
function reminder() {
  
  var timezone = "Asia/Kolkata"
  var hour = 14 // 2 PM
  if(!checkIfTriggerExists("reminder")){ 
    console.log("Creating new trigger as trigger that calls reminder function does not exist.")
    createTimeDrivenTriggers(timezone,hour)
  }
  else{
    console.log("Skipping creating trigger as trigger that calls reminder function already exists.")
  }
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Reminders")
  var range = sheet.getDataRange();
  var values = range.getDisplayValues();
  var lastRow = range.getLastRow();
  var sheerUrl = ss.getUrl();
  var email = Session.getActiveUser().getEmail();

  try {
    sendMail(sheet, lastRow, values); // Function to send reminder Email
  } 
  catch (e) {
    // Logs an ERROR message.
    console.error('sendMail() yielded an error: ' + e);
  }
}

function sendMail(sheet, lastRow, values) {
  for (var i = 1; i< lastRow; i++)
  { 

    if (values[i][8] == 'Pending' && values[i][11] == 'True')
    { 
      console.log(values[i][8], values[i][7], values[i][6], values[i][3], values[i][4])
      MailApp.sendEmail(values[i][7], values[i][6], values[i][3], values[i][4]);

      var date = Utilities.formatDate(new Date(), "GMT+5:30", "MM/dd/yyyy HH:MM:SS")
      
      var loggingCell = sheet.getRange(i+1, 13);
      console.log("Reminder sent to "+values[i][7]+" from " + values[i][6] +" on "+date)
      loggingCell.setValue("Reminder sent to "+values[i][7]+" from " + values[i][6] +" on "+date);
    }
  }
}

function createTimeDrivenTriggers(timezone, hour) {
  // Trigger every day at 2 pm IST
  ScriptApp.newTrigger('reminder')
    .timeBased().everyDays(1).atHour(hour).inTimezone(timezone)
    // .forSpreadSheet(ss)
    // .onWeekDay(ScriptApp.WeekDay.MONDAY)
    // .atHour(9)
    .create();
}

function checkIfTriggerExists(handlerFunction) {
  var triggers = ScriptApp.getProjectTriggers();
  var triggerExists = false;
  triggers.forEach(function (trigger) {   
    if(trigger.getHandlerFunction() === handlerFunction)
      triggerExists = true;
  });
  return triggerExists;
}