Update to yesterdays post

Yesterday, I wrote about making your own reminder service using Google Sheets. Today I made some improvements to that code to look for a date as well, so it’s more viable.

A couple of notes:

  • The timzone function in google scripts seems to be off by an hour, not sure why. So you may have to adjust that.
  • If there’s no date in the spreadsheet, that item is repeated daily.
  • Make sure you’re the date field is set to plain text, and that the date you enter will match the format of the date inside the script: 04-18-2014
  • If you run into trouble, the Logger function is invaluable.  “View > Execution Transcript” will show you everything about the last run.

I think that’s about it. So without much ado, here’s the updated script, basic as it may be:

function sendEmails() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var startRow = 2; // Start on row 2, row 1 is the legend
 var numRows = 100; // How many rows to check? Set high.
 var dataRange = sheet.getRange(startRow, 1, numRows, 5)
 var data = dataRange.getValues();
 var todaysDate = Utilities.formatDate(new Date(), "GMT-4", "MM-dd-yyyy");
 // This works but the TZ is off by an hour, looking into this
 var currentTime = Utilities.formatDate(new Date(), "GMT-4", "HH:mm");
 for (i in data) {
 var row = data[i];
 var emailAddress = row[0];
 var subject = row[1];
 var date = row[2];
 var time = row[3];
 var message = row[4];
 if ((date == '') && (time == currentTime)) {
 MailApp.sendEmail(emailAddress, subject, message);
 }
 if ((date == todaysDate) && (time == currentTime)) {
 MailApp.sendEmail(emailAddress, subject, message);
 }
}
}

Leave a Reply