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

}
}