1

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.

Community
  • 1
  • 1
user3304107
  • 13
  • 1
  • 3

1 Answers1

0

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);
   }
}
Cyrus Loree
  • 837
  • 6
  • 7
  • That's awesome! Thanks Cyrus. Two more questions: 1. if I add more users in the email list, does my script automatically pick up on that? & 2. will the script run automatically once a day, or do I have to do something to get it to do that? – user3304107 Feb 13 '14 at 18:48
  • Okay, so I answered my first question and partially my second. However, I setup a time-based trigger, but it isn't working. I've tried specifying the specific date and time and a minute timer, but neither work for me. – user3304107 Feb 13 '14 at 19:42
  • I would test your sendEmail function to make sure it is first actually sending emails when it's suppose to; ensure you've authorized the apps script completely. I would put a break in the debugger at --if (Sdate == SsheetDate) -- and make sure your dates are coming in as expected. If it functions as expected, than just setup a daily trigger to run in the hour of the day you want the email to be sent. – Cyrus Loree Feb 14 '14 at 00:46