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.

23 comments:

  1. Fantastic...This is what I dreamed to have such a script.

    Reminder script works fantastic.

    Closed script runs without error but items in the Action sheet is not moving to the Closed sheet.

    Days left function ; Please tell what is this for. On run this gives an error as follows
    TypeError: Cannot call method "getTime" of undefined. (السطر 84)Dismiss

    Please solve the problem of "close function. ( not moving from Action sheet to Closed sheet)

    Thanks really ..May the Almighty God Bless you. Peace be upon you.
    Please mail me at sacosana@gmail.com

    ReplyDelete
  2. Thanks.. It worked for me the Closed function after i change the name of the sheet from Actions to actions.

    Only the "daysleft function" gives the following error eventhough it did not affect anything
    TypeError: Cannot call method "getTime" of undefined. (السطر 84)Dismiss

    Thanks again...

    ReplyDelete
  3. sacosana - thanks for the feedback. I've corrected the case on the "actions" and "closed" sheets in the functions above (now "Actions" and "Closed" to match the instructions).

    I've also added a comment to explain that DaysLeft() is a function that calculates the number of whole days between two dates.

    I'm not sure why you're seeing a TypeError. Perhaps there is a row in your actions sheet that has a corrupted date?

    ReplyDelete
  4. Thanks for the Script.It works fine. but It gives an Error as follows when there is no date available in "Due"
    TypeError: Cannot find function getTime in object . (line 84)

    if "closed column" is filled with data then It skips without error and also no reminder mail. Logic is okay once it is finished we dont require a reminder. but

    My case, the "due column" will be filled only later depends on other criteria. But without date on due column it gives the above error. Just because of one row where the due dates is not filled I will not get reminder even though "due dates" are filled for other rows for different cases.

    Please help in resolving this.

    ReplyDelete
  5. I think if you want to skip actions which do not have a due date you can make the following modification (add the if statement below):

    var due = row[2];
    if (due == "") continue;
    var days = daysLeft(due, today);

    I haven't tested this. Let me know if it works.

    ReplyDelete
  6. Thanks Mr. Alex for the "If statement."
    It works only if I remove the following script.
    // skip actions that are not due
    var due = row[2];
    var days = daysLeft(due, today);
    if (days > daysBefore) continue;

    How to have both running.

    The second problem is that We should get reminder mails only if there is something to be reminded. But for an incident even if everything is closed we get a blank reminder mail with just Title, Summary and Due.

    How to stop this.

    Wish you a and all a very Happy new year 2012...

    ReplyDelete
  7. Hello Mr. Alex. Good day. It works perfectly for me after adding the script for the blank due column.( skiping if the due columnn is blank).I have added after the skip action that closed. Perfect.

    Yes. I too need a script to be added to avoid getting blank reminder if actions are closed or if the date is not yet due. Reminder mail shoud come only if there is something to be reminded. Please help.

    ReplyDelete
  8. Please help in getting the script to avoid getting blank reminder if actions are closed and also if the date is not yet due. Reminder mail shoud come only if there is something to be reminded. Please help.

    ReplyDelete
  9. Thanks for this script! How would I tweak the script to send reminders to multiple email recipients?

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. simply fantastic! thank you for making it so simple!

    ReplyDelete
  12. @steve, simple add ur email ids with a comma separating then. ex: test@test.com,test1@test1.com

    ReplyDelete
  13. Getting the following message (TypeError: Cannot find function getTime in object ) after changing the onOpen code. Also, "Send Reminders" does not appear on the drop-down menu under "Actions."

    Any suggestions most appreciated.

    Many thanks.

    ReplyDelete
  14. Sounds like a typo somewhere. Perhaps the name of the date variable (date1 or date2) is spelled differently between the function signature and where getTime is called on it?

    ReplyDelete
  15. Thanks—I'll take a look, but I did a straight cut and paste, so I'm not sure what could have happened.....

    ReplyDelete
  16. Here's the full script if that helps:

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

    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [ {name: "Close Action", functionName: "closeAction"},
    {name: "Send Reminders", functionName: "sendReminders"} ];
    ss.addMenu("Actions", menuEntries);
    }

    function sendReminders() {
    var emailAddress = "benzacar@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);
    }
    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)
    }

    ReplyDelete
  17. I can't see any errors. A few questions. When you close and open the spreadsheet, do neither the close action nor send reminders items appear in the menu bar?

    Can you check that there are no invalid dates in the spreadsheet? It occurs to me that the typeerror you are seeing would happen if the function read a cell that didn't have a date in it and then tried to call getTime. Even a blank line in the spreadsheet could cause that problem.

    ReplyDelete

  18. Hi, Alex—Close Action and Send Reminders both appear, but when I click on a date cell in any of the active columns and try to send a reminder, I get the same message as before: TypeError: Cannot find function getTime in object.

    This is all that's in the Actions tab of the spreadsheet.

    Created Closed Due Summary
    Get on it.
    Now.
    2/2/2013 2/14/2013 Let's go.
    2/2/2013 2/18/2013 Come on.

    ________________

    Sorry for the bother. I'm sure I must be overlooking something obvious. Many thanks!

    ReplyDelete
  19. I think the problem is those two rows with "Get on it." and "Now.". It looks like those are in rows 2 and 3 and that they have no dates. So either you have left the date fields blank or you have put the text in the date column. Either way it's going to cause the sendReminders function to fail, because it expects to find valid dates in all cells of column A except the header. If you delete rows 2 and 3 it should work.

    ReplyDelete
  20. Thanks. This is awesome. I know this is years too late, but may I ask one question? Is there any way to have one reminder (row) go to one email address and another reminder (a different row) go to another email address?

    ReplyDelete
    Replies
    1. Let me see if I understand what you're looking to do. Are you thinking of adding a new column to the Actions sheet, so that you have the headings: Created, Closed, Due, Summary, Email. And then you want one reminder email per row in the Actions sheet, with it being sent to the address in the Email column for that row?

      If that's what you're looking for then at a high level you'll need to change the sendReminder function in the following ways:

      - add a line in the for loop which sets emailAddress = row[4]
      - move the closing brace } after the Logger.log to bring the sending into the loop
      - adjust the lines that set the message value so they just contain the current reminder

      If I misunderstood what you're trying to do, let me know.

      Delete
  21. Hey I need some help with an error about the following - TypeError: Cannot call method "getTime" of undefined. (line 83, file "Code") I am stuck and dont know what to do to make it work?

    ReplyDelete
    Replies
    1. Typically this happens if there is a row in the spreadsheet without a valid date in the due column, can you try removing rows in the spreadsheet until the problem goes away to isolate which row might be causing it?

      Delete