Automating Google Spreadsheets - Email Reminders

I use Google services almost constantly, but the one that I find myself truly intrigued by is their Drive service. It has completely replaced every other program out there for any word processing and spreadsheet needs I have. What's more, the "always on" cloud functionality it brings makes it far more powerful than any desktop office suite.

One of the more interesting things I did with it recently was to create a spreadsheet with a set of reminders that automatically send me an email when they are due. It was simple and easy, and works great! In this article, I'll be sharing my spreadsheet and scripting tricks with you.

Part 1: Spreadsheet Setup

First, you'll need a Spreadsheet on Google Drive that you plan to use to hold your data and your scripts. Since this is going to be a reminder spreadsheet, we're going to be working primarily with dates. For instance, my spreadsheet looks like:

Here I've listed some chores, but you could use this for whatever - prescription reminders, recurring meetings, birthdays, anything you might want a reminder for.

Date math is relatively easy in spreadsheet terms. If you add or subtract integers from dates, you can calculate new dates automagically. The today() macro does what you'd expect - it just returns today's date, ready to be used alongside other dates for more date mathemagic.

I've shown the formulas here, although when you enter them into the spreadsheet they'll automatically populate with the computed values. Also remember that you can click and drag the little "dot" in the lower right hand corner of the active cell to automatically set up formulas in an entire range of cells. Once you've filled out row 2, you can just drag down and have rows 3 and 4 (and so on...) filled in with minimal effort.

Remember that in order for the reminders to work, you're going to want to keep the Last Day Completed column updated. Whenever you do the task, put today's date into that column. Do note that you can't "cheat" and use today() there, because if you do it will update to the current date every time you open the spreadsheet or the script runs, which would be a bad thing!

Part 2: Scripting

Now you've got your data entered, it's time for the automation. Google Drive uses a Javascript-like scripting language, so if you can write Javascript or jQuery style scripts, you'll be fine.

If you're new to coding, you might want to run through an interactive tutorial like [Codeacademy](http://www.codecademy.com/# !/exercises/0).

However, if you've written code and feel comfortable in any other language, you should be fine. Javascript is very C-like in its syntax, except that variables are weakly typed (everything is just "var") and there are a number of shortcuts like associative arrays and "for each" style loops. We won't be doing any funky lambda functions or anything advanced like that.

To get started, click the Tools menu, and then click Script Editor... to bring up the script editing window. You can choose from some templates here, any is fine since we'll be writing our own code here in a second. You might as well click Spreadsheet if you're overwhelmed by the options. :)

Here's the script we're going to use:

[javascript] function checkReminder() { // get the spreadsheet object var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // set the first sheet as active SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]); // fetch this sheet var sheet = spreadsheet.getActiveSheet();

// figure out what the last row is var lastRow = sheet.getLastRow();

// the rows are indexed starting at 1, and the first row // is the headers, so start with row 2 var startRow = 2;

// grab column 5 (the 'days left' column) var range = sheet.getRange(2,5,lastRow-startRow+1,1 ); var numRows = range.getNumRows(); var days_left_values = range.getValues();

// Now, grab the reminder name column range = sheet.getRange(2, 1, lastRow-startRow+1, 1); var reminder_info_values = range.getValues();

var warning_count = 0; var msg = "";

// Loop over the days left values for (var i = 0; i <= numRows - 1; i++) { var days_left = days_left_values[i][0]; if(days_left == 7) { // if it's exactly 7, do something with the data. var reminder_name = reminder_info_values[i][0];

  msg = msg + "Reminder: "+reminder_name+" is due in "+days_left+" days.\n";
  warning_count++;
}

}

if(warning_count) { MailApp.sendEmail("[email protected],[email protected]", "Reminder Spreadsheet Message", msg); }

}; [/javascript]

This is a lot to process at once, so let's break it down.

Getting the Spreadsheet

[javascript] // get the spreadsheet object var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // set the first sheet as active SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]); // fetch this sheet var sheet = spreadsheet.getActiveSheet(); [/javascript]

We start by grabbing the "active spreadsheet" which is the file on the Google Drive that this script is living in. Then, we need to get one individual sheet (or page) from the script. In this example, I'm setting the active sheet to be the first one in the spreadsheet document, just in case we've gone and added another at some point. Then I'm storing the active sheet in the "sheet" variable. This will let us grab regions to work on later in the script.

Getting the Cells of Interest

[javascript] // figure out what the last row is var lastRow = sheet.getLastRow();

// the rows are indexed starting at 1, and the first row // is the headers, so start with row 2 var startRow = 2;

// grab column 5 (the 'days left' column) var range = sheet.getRange(2,5,lastRow-startRow+1,1 ); var numRows = range.getNumRows(); var days_left_values = range.getValues();

// Now, grab the reminder name column range = sheet.getRange(1, 1, lastRow-startRow+1, 1); var reminder_info_values = range.getValues(); [/javascript]

Now we've got to get the part of the sheet that we're actually interested in. In this case, all we need is the "days left" column, and the "reminder name" column. However, we want this column for every valid row in the spreadsheet.

So, we start by figuring out which is the last valid row in the spreadsheet. Luckily, there's an easy function for that. We already know the first row we want to consider, so that can be a constant for now.

From here, we need to use sheet.getRange to get the actual spreadsheet data. It's function signature looks like:

[javascript]getRange(row, column, numRows, numColumns)[/javascript]

This function is a bit confusing, since it takes a 1-indexed column and row, but will return a 2-D array that is 0-indexed. That means, if we want cell A1, we would need to call:

[javascript] var range_A1 = getRange(1, 1, 1, 1); var values_A1 = range_A1.getValues(); [/javascript]

After this, the actual value of cell A1 would be in values_A1[0][0].

Processing the Cell Data

Now that we've got our values, we can loop over them and decide if we need to take action:

[javascript]

var warning_count = 0; var msg = "";

// Loop over the days left values for (var i = 0; i <= numRows - 1; i++) { var days_left = days_left_values[i][0]; if(days_left == 7) { // if it's exactly 7, do something with the data. var reminder_name = reminder_info_values[i][0];

  msg = msg + "Reminder: "+reminder_name+" is due in "+days_left+" days.\n";
  warning_count++;
}

} [/javascript]

Here, the msg variable is going to hold the contents of our email, if we choose to send one. We'll choose to send one based on whether or not we have any reminders, which we're going to count with the warning_count variable. I'm comparing days_left to 7, which means that we'll get a reminder exactly one week before the task is due. You could change this to whatever you wish - even negative numbers, if you want to be reminded if something comes past due!

You could also add a couple of tests here. Perhaps you'd like a 1-week left reminder and a day-of reminder. Just use 2 if statements, or an if {} else {} instead of the simple if I've got here.

Sending the Reminder Email

[javascript] if(warning_count) { MailApp.sendEmail("[email protected],[email protected]", "Reminder Spreadsheet Message", msg); } [/javascript]

Finally, it's time to send the email. This is pretty straightforward and Google really makes it simple to do. The if(warning_count) check makes sure that we don't send an email if there's nothing to be reminded about.

However, for debug reasons, you may want to test your script and not send an email. If you want to do this, you can use:

[javascript] Logger.log(msg); [/javascript]

This will log a message when you run your script instead of sending the email. To see this log, you'll need to go to the View menu, and choose Logs.... This will show you what log messages were printed each time your script has run.

Finally, make sure you save your script!

Part 3: Events

Great, so now we've got a spreadsheet tracking our whatevers, and a script that can process the data and do something meaningful with it. Now we've got to automate. Again, Google makes this super easy. From the Resources menu, choose Current Project Triggers.... A menu will pop up, just click add one now to add a new trigger.

From here, you want to pick:

• Your function name (I called mine checkReminders in the sample code)
• Time Driven
• Day Timer
• Whatever time of day is convenient - I use "Midnight to 1am" so that I have a reminder first thing in the morning.

Then click Save. Now the spreadsheet will update and run the reminders script once a day, and send you an email.

Similar Problems

One thing we hear from a lot of people in the comments of this article is:

I have a spreadsheet where each row has its own email address, and I want to send a similar email to a bunch of people instead of your example of sending email to just one. How do I do this?

This is called a mail merge and is a pretty common thing to do with spreadsheets. Google has a tutorial you can read about mail merges on their Google App Script website that you can read. You'll see many of the same functions, like getRange and sendEmail, just used in slightly different ways.

If you were able to follow this tutorial, chances are pretty good that you can follow their code and get their example working as well.

While I'm on the subject of Google's tutorials, they have a whole list of them, which you can view right here. There are a bunch of interesting ideas for projects in here to maximize the way you use Google Drive/Docs.

Conclusion

Once you've got the fundamentals under your belt, it's really not that hard to do all sorts of exciting things with spreadsheet scripting. The sky's the limit! There's nothing stopping you from sending your reminders only on certain days of the week, for example. You can even embed your spreadsheets into other applications... but that's another topic for another post :)