0

Goal: AUTOMATICALLY trigger script to send an email when date == today

Problem: Script works when run manually, but when using the 'Current project's triggers' i receive:

  • NO error message
  • NO email
  • The sheet is not populated w/ 'EMAIL_SENT'

Script:

// Send email reminder to the team
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 = sheet.getLastRow()-1 // Get last row, -1 because your startrow is 2;   // 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[8];  // First column
    var subject = row[9];     // Second column
    var message = row[10];    // Third column
    var emailSent = row[11];
    //var reminderDate = row[6].toLocaleDateString();  // date specified in cell G
    var cellValue, reminderDate; //Define variables at top of function

    cellValue = row[6]; // 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();
    }

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

I referred to the following existing Q&A:

  1. Send reminder emails based on date, but this returned the error "TypeError: Cannot find function toLocaleDateString in object. " when ran using the 'Current project's triggers'. It worked perfectly when ran manually.
  2. How do you send reminder date based on cell date in Google Sheets?, which recommended to break var reminderDate = row[2].toLocaleDateString(); , but the result is as above: nothing happens when run via trigger.
tehhowch
  • 9,645
  • 4
  • 24
  • 42
marie_
  • 1
  • 1
  • The problem may be in your date comparisson. [Check out this SO question](https://stackoverflow.com/questions/38860641/google-apps-script-comparing-dates). Add some logging to check whether the if statement is actually doing what you'd expect it to. – Casper Sep 17 '18 at 14:41
  • 1
    When the function executes via trigger, the "active sheet" is the first tab, because the function is run on Google's servers (in all cases) and has no associated UI instance. Consider a more robust manner of specifying the desired sheet, i.e. `getSheetByName`. Triggered scripts may still generate error messages - consider checking your project's Stackdriver logs, via *View -> Stackdriver Logs*. Finally, `Date` comparison for equality requires you to coerce it numerically: https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript – tehhowch Sep 17 '18 at 14:41
  • @marie How did you get on with your Google Sheets code for triggered email sending? – Tedinoz Oct 12 '18 at 07:09

0 Answers0