Send reminder emails based on date

Question

I'm using the following script to send email reminders from a Google Sheet, but would like to modify it so that it send the email out on a date specified in cell F of each row.

var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 999;   // Number of rows to process
  // Fetch the range of cells A2:B999
  var dataRange = sheet.getRange(startRow, 1, numRows, 999)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var subject = row[1];     // Second column
    var message = row[2];    // Third column
    var emailSent = row[3];     

    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates

      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 4).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

That's what I have and any attempts to add a date in there have failed pretty badly.

I came across this earlier question: Google Apps Script - Send Email based on date in cell but was unable to combine it with my script.

Answer 1

The solution Serge provided in that previous answer sets the stage for you to have a very flexible script, able to use any portion of the date / time as a criteria for sending.

Here's a simpler and less flexible approach. Assumptions:

• The date is in the spreadsheet as a date, not a string.
• We only care that the date matches; hours, minutes and seconds are inconsequential.
• The script and the reminder dates in the spreadsheet are based on the same timezone.

The magic here is all about comparing dates. A JavaScript Date object is a numeric representation of time elapsed from the start of 1970, Universal time. Comparing equality of dates then, is difficult. However, thanks to the assumption above, we only care about the date, which is helpful. To get around timezone concerns and eliminate the effect of hours, minutes, etc., we just use the same Date method to generate date strings from the date objects we want to compare. The toLocaleDateString() method adjusts for time zones for us.

Resulting script:

var EMAIL_SENT = "EMAIL_SENT";

function sendEmails3() {
  var today = new Date().toLocaleDateString();  // Today's date, without time

  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 999;   // Number of rows to process
  // Fetch the range of cells A2:B999
  var dataRange = sheet.getRange(startRow, 1, numRows, 999)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var subject = row[1];     // Second column
    var message = row[2];    // Third column
    var emailSent = row[3];     
    var reminderDate = row[5].toLocaleDateString();  // date specified in cell F

    if (reminderDate != today)      // Skip this reminder if not for today
      continue;

    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates

      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 4).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}