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!

3 thoughts on “Creating your own reminder service

  1. Pingback: Update to yesterdays post | blog of greg

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