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