Google Apps Script - Send email based on data in cell

Question

So I'm trying to set up a reminder email to automatically be sent based on the date in a cell. Kind of like this: Google Apps Script - Send Email based on date in cell Here's my sample workbook: https://docs.google.com/spreadsheet/ccc?key=0AiHAV8ZZ5nexdDJqODhmamhldjN1ZTRKc09iZXNBZ3c#gid=0

This is the code that I have:

function sendEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;  // First row of data to process
var numRows = sheet.getLastRow()-1;   // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
// Fetch values for each row in the Range.
var data = dataRange.getValues();
//Logger.log(data)

for (i in data) {
var row = data[i];
var date = new Date();
date.setHours(0);
date.setMinutes(0);
date.setSeconds(0);
//Logger.log(date);
var sheetDate = new Date(row[2]);
//Logger.log(sheetDate);
var Sdate = Utilities.formatDate(date,'GMT+0200','yyyy:MM:dd')
var SsheetDate = Utilities.formatDate(sheetDate,'GMT+0200', 'yyyy:MM:dd')
 Logger.log(Sdate+' =? '+SsheetDate)
    if (Sdate == SsheetDate){
      var emailAddress = row[0];  // First column
      var message = row[1];       // Second column
      var subject = "It's time to practice!" +message;
      MailApp.sendEmail(emailAddress, subject, message);
      //Logger.log('SENT :'+emailAddress+'  '+subject+'  '+message)
        }    
 }
}

But I'm not sure if it's working, and will it automatically send the email out? Obviously, I know very little script.

Cyrus Loree Cyrus Loree · Accepted Answer · 2014-02-13T03:17:59

You only have 1 error in your existing code shared here, that is keeping it from working:

var sheetDate = new Date(row[2]);

You only have 2 indexes in your array, so this should be:

var sheetDate = new Date(row[1]);

Also, because you are using Utilities.formatDate to yyyy:MM:dd format, you do not need to set the hours minute and seconds, because Utilities.formatDate is returning a string with no time component. Furthermore, you do not need to create sheetDate or date, those can both be constructed as the first parameter in the Utilities.formatDate (see below). One other thing on this topic, because your date values are formatted as a date in your spreadsheet, they are being returned to your script as a date object, so really, it isn't necessary to call new Date(row[1]) .. but it doesn't hurt anything.

function sendEmail() {
  try{
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2;  // First row of data to process
    var numRows = sheet.getLastRow()-1;   // Number of rows to process
    // Fetch the range of cells A2:B3
    var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
    // Fetch values for each row in the Range.
    var data = dataRange.getValues();

    for (i in data) {
      var row = data[i];
      //Logger.log(sheetDate);
      var Sdate = Utilities.formatDate(new Date(),'GMT-0500','yyyy:MM:dd')
      var SsheetDate = Utilities.formatDate(new Date(row[1]),'GMT+0200', 'yyyy:MM:dd')
      Logger.log(Sdate+' =? '+SsheetDate)
      if (Sdate == SsheetDate){
          var emailAddress = row[0];  // First column
          var message = row[1];       // Second column
          var subject = "It's time to practice!" +message;
          MailApp.sendEmail(emailAddress, subject, message);
          //Logger.log('SENT :'+emailAddress+'  '+subject+'  '+message)
       }    
     }
   }catch(err){
     Logger.log(err.lineNumber + ' - ' + err);
   }
}

Google Apps Script - Send email based on data in cell

1 Answers