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 below!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s