How do you send reminder date based on cell date in Google Sheets?

Question

I have been researching how to send e-mails based on a specific date from a cell in Google Sheets. I have found this article, which does almost exactly what I would like to do as time does not matter to me.

My problem is that when I use the code, I get the error TypeError: Cannot find function toLocaleDateString in object . (line 19, file "Code").

Here is a copy of the sheet setup I have: https://docs.google.com/spreadsheets/d/1FlrpvLMRMuq8t6pI4inlKI2acrZJ68pyGHQ7Xtqi5AU/edit?usp=sharing

Here is my code, formatted for my columns etc

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[3];  // POC Email column D
    var subject = row[5];     // Subject column F
    var message = row[6];    // Message column G
    var emailSent = row[7];   // Output the message is sent in column H
    var reminderDate = row[2].toLocaleDateString();  // date specified in cell C

    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();
    }
  }
}

How can I fix this error? In addition to the error is there a way I can e-mail both points of contact in my sheet?

Thank you for all the help you can provide.

Answer 1

The most likely reason you are getting the error is you are trying to access an empty cell and trying to convert it into a date string.

Modify this line:

var numRows = 999;

to the following

var numRows = sheet.getLastRow()-1 // Get last row, -1 because your startrow is 2

so you only access rows that have data in it. Also to send emails to multiple people just include all the emails in a comma separated string like this:

var emailAddress = row[3] + "," + row[4];  // POC Email column D and E (Just create a comma seprated email list to send email to multiple people)

Find the modified code below:

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 = sheet.getLastRow() - 1;   // Number of rows to process, -1 since you are starting from row 2
  // Fetch the range of cells A2:G(LastRow -1) 
  var dataRange = sheet.getRange(startRow, 1, numRows, 8)
  // 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[3] + "," + row[4];  // POC Email column D and E (Just create a comma seprated email list to send email to multiple people)
    var subject = row[5];     // Subject column F
    var message = row[6];    // Message column G
    var emailSent = row[7];   // Output the message is sent in column H
    var reminderDate = row[2].toLocaleDateString();  // date specified in cell C

    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, 8).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Hope that helps!

Answer 2

This line:

var reminderDate = row[2].toLocaleDateString();  // date specified in cell C

You should break up into multiple operations:

var cellValue,reminderDate;//Define variables at top of function

cellValue = row[2];// date specified in cell C

if (!cellValue) {continue;}//If there is no cell value continue looping

if (typeof cellValue === 'object') {
  reminderDate = cellValue.toLocaleDateString();
} else {
  cellValue = new Date(cellValue);//Convert date as string to object
  reminderDate = cellValue.toLocaleDateString();
}