Creating your own reminder service

Yesterday, a colleague of mine posted about a service called IFTTT (If this then that) which can create relationships between different services like Facebook, Gmail and Twitter.  Through the use of triggers, you can do some cool stuff.  Be sure to check out Dan’s writeup, it’s probably much more interesting than what you’re about to read.

For me, IFTTT is a little overkill, I usually stick with Google Calendar to remind me about stuff I’m planning on ignoring, but it got me thinking about creating my own very basic version of a crontab in the cloud for some notifications. Like submitting expense reports etc.

Enter Google Sheets and time-driven triggers.  Oh, and before you ask what the point is, the answer is just a little fun.  And if you’re wondering why my idea of fun is working on spreadsheets… ummm… data. mmmmmmm.

So first, we create a new Google Sheets document with some basic details: email address to notify, subject, date, time and message body:

Screen Shot 2014-04-17 at 11.20.31 PM

Once you have a few entries in there (notice the date is blank, I haven’t worked on that just yet – future post) click on Tools > Script Editor

Screen Shot 2014-04-17 at 10.17.57 PM

Here’s the code to add:

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", "yyyy-MM-dd");
 // 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 (time == currentTime)
 {
 // Function to send email, lets wrap it.
 MailApp.sendEmail(emailAddress, subject, message);
 }
 }
}

What does this do?  Right now nothing unless you run it manually using the play button in the toolbar.  For the automation magic, you need to add a trigger.  Click on ‘Resources’ in the toolbar, then ‘All your triggers’:

Screen Shot 2014-04-17 at 10.55.51 PM

Now click on the ‘Add a new trigger’ link and fill it out:

Screen Shot 2014-04-17 at 10.56.05 PM

You’ve now scheduled your app to run every 60 seconds, checking for upcoming events.  If the time in your spreadsheet matches the current time, emails get sent out.  I’ll probably make some changes and add date support as well.

So, is this better than a commercial service like IFTTT, Google Calendar or a plain old crontab entry set to send an email? Probably not.  Was it more fun to work on? You betcha!