Creating a Reminder Service with Google Apps Script

Google Calendar is great for reminding you of appointments. But if you have actions that must be done, you need something more than a one time reminder. I wanted to keep a list of actions with due dates where each day I would receive one email listing all the actions which were coming due, or overdue.


Google Apps Script is a server side scripting language that interacts with Google Docs and other Google products. Among other things it can be used to add custom functionality to a Google Spreadsheet. I have used it to write a simple reminder service.

This could be made more sophisticated with each action having a different owner that should receive a reminder. But it should serve as an example for someone just getting started with Google Apps Scripts to build on. Or for someone that wants to build their own reminder service.

The steps we're going to follow are

1. Create a spreadsheet to contain our list of actions
2. Add a Close Action function
3. Add Close Action to the spreadsheet menu
4. Add a Send Reminders function
5. Add Send Reminders to the spreadsheet menu
6. Schedule the Send Reminders function to run each night

Creating the Spreadsheet

Create and name the spreadsheeet (I called mine "Actions"). You'll need two tabs. Call one "Actions" and the second "Closed". These will contain the list of open and closed actions respectively. Add a header row to each tab naming the columns: "Created", "Closed", "Due" and "Summary". The first three columns will be dates for when each action was created, when it was closed, and when it is due. The summary is the text that will be sent in the reminder email.

You may want to add a few examples on each sheet and make sure that the dates are formatted correctly.

Add a Close Action function

The closeAction function is simpler than the sendReminders, so let's start with it. Click Tools / Script Editor. A tab should open with the following function

function myFunction() {

  

}

Highlight the function and replace it with the following

function closeAction() {

  // get the active spreadsheet and sheet

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = spreadsheet.getActiveSheet();

  // check that the user is on the sheet named "Actions"

  var name = sheet.getName();

  Logger.log("name is " + name);

  if (name != "Actions") return;

    

  // get the row the user is currently on

  var row = sheet.getActiveRange().getRow();

  var data = sheet.getRange(row,1,1,5).getValues();

  Logger.log(data);

  

  // add it to the end of the Closed sheet

  var closedSheet = spreadsheet.getSheetByName("Closed");

  var newRow = closedSheet.getLastRow();

  var newRange = closedSheet.getRange(newRow+1, 1, 1, 5);

  // add todays date into the closed date column

  var today = new Date();

  data[0][1] = Utilities.formatDate(today,"EST","MM/dd/yy");

  newRange.setValues(data);

  

  // delete the row from the actions sheet

  sheet.deleteRow(row);

}

When you save, you'll be prompted to name the project. Let's call it "Reminder Scripts". Now let's add the closeAction function to the spreadsheet menu to make testing it easier.

Add Close Action to the spreadsheet menu

We want the Close Actions entry to appear in the spreadsheet menu every time we open the document. So let's add a function which will run every time the document is opened. Paste the following in below the closeAction function:

function onOpen() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var menuEntries = [ {name: "Close Action", functionName: "closeAction"} ];

  ss.addMenu("Actions", menuEntries);

}

Select this new onOpen function in the pull down list in the toolbar and click the run button. When you go back to the spreadsheet now you should see an "Actions" menu item. Click this and there should be a "Close Action" sub-menu.

Let's test the closeAction function. Add a few example actions on the actions sheet, leaving the closed date blank. Click anywhere on the row of one of these actions and then click Actions / Close Action. The action should be deleted automatically. When you switch to the closed sheet you should see this action has been added with today's date.

Add a Send Reminder function

So we have sheets with our open and closed actions, and we have a menu item to mark an open action as closed. Now let's get to sending reminders. Add the following function below the onOpen function in the Script Editor. Make sure to change the email address at the top of the function.

function sendReminders() {

  var emailAddress = "youremailaddress@gmail.com"; 

  var daysBefore = 5;             // days before due date to send reminder

  var today = new Date();

  var message = "due\tsummary\n";

  // get url and name of spreadsheet  

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var url = spreadsheet.getUrl();

  var name = spreadsheet.getName();

  var subject = "Reminders from " + name;

  // get the number of actions

  var sheet = spreadsheet.getSheetByName("Actions");

  var numRows = sheet.getLastRow() - 1;

  

  // if there are no actions then return

  if (numRows == 0) return;

  

  // read all the actions into an array  

  var data = sheet.getRange(2, 1, numRows, 5).getValues();

  // Sort by ascending due date

  data.sort( function (a, b) { return a[2]-b[2] });

    

  for (i in data) {

    var row = data[i];

    

    // skip actions that are closed

    var closed = row[1];

    if (closed != "") continue;

      

    // skip actions that are not due

    var due = row[2];

    var days = daysLeft(due, today);

    if (days > daysBefore) continue;

    

    // add due reminders to the email message

    var summary = row[3];

    message += days + "d" + "\t" + summary + "\n";

  }

  // add the url of the spreadsheet to the end of the email

  message += "\n" + url;

  MailApp.sendEmail(emailAddress, subject, message);

  Logger.log(message);

}

You'll also need to add this function which is used above. This calculates the number of days between two dates.

function daysLeft(date1, date2) {

    var ONE_DAY = 1000 * 60 * 60 * 24

    var date1_ms = date1.getTime()

    var date2_ms = date2.getTime()

    return Math.round((date1_ms-date2_ms)/ONE_DAY)

}

Add Send Reminders to the spreadsheet menu

Find the onOpen function that you created earlier and change it as follows

function onOpen() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var menuEntries = [ {name: "Close Action", functionName: "closeAction"},

                      {name: "Send Reminders", functionName: "sendReminders"} ];

  ss.addMenu("Actions", menuEntries);

}

Now make sure there are some actions that are overdue or within 5 days of due. Click Actions / Send Reminders. The first time you do this, Google will present a popup asking that you authorize the function. This is because of the use of MailApp. Go ahead and authorize. Now you'll need to click Actions / Send Reminders. This time you should get an email with all the due reminders.

Notice that anytime you change the sendReminders function, Google will prompt you to re-authorize. If all you want is a reminder service where you have to remember to go in and send yourself reminders, then you are done. Assuming you want the reminders to be automatic then we need to...

Schedule the Send Reminders function to run each night

From the Script Editor click Triggers / Current Script's Triggers... and click Add a new trigger. Select sendReminders, Time Driven, Day timer, 5am to 6am. This will cause sendReminders to run once each night between 5am and 6am.

Summary

That's it. You should now have a working reminder service built using Google Apps Script. You can enter in all those actions and receive a simple email reminder of all the due ones every day. Remember, it will keep reminding you about an action until you open the spreadsheet and mark the action as closed. That's what the link in the email is to help with, and the Close Action item in the Actions menu.

Here are some of the capabilities of Google Apps Script that we've used here 

1. Creating a function that runs whenever the Google Spreadsheet is opened
2. Creating a custom menu in Google Spreadsheet which runs a function
3. Using MailApp to send an email
4. Creating a trigger to run a Google Apps Script function nightly

Even if you're not looking to build a reminder service, hopefully this post will serve as a useful example of some of these capabilities of Google Apps Script. Post a comment to let me know if this is useful. If you have trouble with it, post a comment and I'll try to help.