Question

14

Google Apps Script - Send email based on data in cell

rated 0 times [ 21] [ 7] / answers: 1 / hits: 8182 / 10 Years ago, thu, february 13, 2014, 12:00:00

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.

Answers

Only authorized users can answer the question. Please sign in first, or register a free account.

hakeemabramh

Add To Favorites

Follow

Total Points: 234

Total Questions: 109

Total Answers: 109

Location: Romania

Member since Mon, Jun 6, 2022

2 Years ago

answered 10 Years ago quentinaveryb · Accepted Answer

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

   }

}