Setup automated reminders in Google Sheets

Spreadsheets are the backbone of organizing information for teams across the world, but organization alone isn’t enough to ensure that work gets done. A well made spreadsheet can tell you how many tasks you have outstanding, but only if you remember to open it!

In this article I’ll show you how to setup reminder emails that will get sent automatically each day if there are any pending tasks in your spreadsheet. Stay tuned until the end, where I’ll show you an easier way to accomplish this using Coda.

The situation

For the purpose of this example, let’s pretend you bake birthday cakes as a side gig. Potential customers are directed to a Google Form where they fill out some basic details of their request.

A standard Google Form.

These responses get sent to a spreadsheet, and you use the provided information to determine a cost and timeline. You’ve added an additional column named Responded which you check off once you’ve followed up with the customer via email.

The spreadsheet of responses.

Since cake orders aren’t pouring in at the moment, you sometimes forget to check the spreadsheet and have missed out on some opportunities. What you’d love is an email notification when there are some new requests you need to follow up on.

Create a simple notification

Google Sheets has some built-in notification options, which you can access using Tools > Notification settings > Edit notifications.

The dialog for creating simple notifications.

Here you can select what triggers the notification and how often to send it. The notification email will look something like this:

What a simple notification email looks like.

While easy to set up, the conditions for the notification and the content of the email are rather fixed, and don’t allow for much customization.

Create a custom notification

Using Google Apps Script you can write code to generate a completely custom notification, using the criteria and wording of your choice. To get started you’ll need to create a new script by clicking Extensions > Apps Script. Then enter the following code into the script editor:

function notify() {
  let sheet = SpreadsheetApp.getActive().getSheetByName("Requests");
  let requests = sheet.getDataRange().getValues();
  let openRequests = requests.filter(row => {
    let complete = row[5];  // The 6th column.
    return !complete;
  });
  if (openRequests.length > 0) {
    GmailApp.sendEmail(Session.getActiveUser().getEmail(), 
      "Open Cake Requests", 
      `There are ${openRequests.length} cake requests that need a response.`);
  }
}

The code above contains a single function, notify() that will be run daily. It reads the rows from the Requests sheet of the spreadsheet, filtering for only the ones that are not marked as complete. If there are any, it uses the GmailApp service to send you an email with a custom subject and body that lists the number of open requests.

Save the script and give it a name.

The finished script.

You can test it manually using the Run button in the menu bar. You’ll be prompted to authorize the script, and be shown a scary dialog like this:

Google warning you about the code that you just wrote.

Don’t worry, this is (unfortunately) normal. Click Advanced > Continue to <Title> (unsafe) to get past it. Once all the permissions have been granted, you should get an email sent to your inbox.

The custom notification email, snazzy.

To get it to run automatically every day you’ll need to setup a Trigger. Triggers allow scripts to be run automatically when certain events take place or on a regular schedule. To add one, select the Triggers tab in the Apps Script editor (has a clock icon) and then click the + Add Trigger button.

The options for the trigger.

Configure the trigger to run the notify() function, from Head (the latest code) using a Time-driven source, every day at the time of your choice. The exact time isn’t guaranteed, but is instead of range. Click the Save button to finish creating the trigger.

Now you should get a daily notification of any pending requests in the spreadsheet, send right to your inbox.

Try it the Coda way

While Apps Script is a powerful platform, there is a high learning curve in order to get started if you’ve never programmed before. Additionally, the triggers themselves are hard to find from the spreadsheet, which can make it confusing for other collaborators to figure out why the spreadsheet is spamming them daily.

At Coda we made automations a core feature of our document, making them easier to create and manage. Let’s take the same example as above, but show you how to do it in a Coda doc. Like Sheets, Coda supports building forms and sending the responses to a table.

A Coda doc and form, looking sharp.

(Follow along using the Coda doc here).

To make the rest of the setup simpler, let’s first create a “view” of the Requests table that shows only the pending requests. Type /view in the doc and select the Requests table. Rename the new view to “Pending Requests” and then click Options > Filter > + Add filter. Select the Responded column and then click Unchecked. You should now have a table that only includes the requests that haven’t yet been responded to.

Using a view to show a subset of the rows.

To setup the notification create an automation by clicking the gear icon in the upper right of the doc and selecting Automations > + Add Rule. In the sidebar you can then configure when the automation is run and what it does.

Create the automation right from the side panel, no scripting!

1) Set the When option to Time-based, selecting the Day option and the time.

2) Click Add a condition and enter the following formula in the If section:

Count([Pending Requests]) > 0

This formula is written using the Coda Formula Language, which should feel familiar to long time users of spreadsheets. It ensures that the notification is only sent when there is at least one pending request.

3) In the Then section select the option Notify user, selecting to notify Specific users and then select yourself from the Users dropdown. In the Message field press equals and enter the following formula:

Format("There are {1} cake requests that need a response.", 
  Count([Pending Requests]))

This formula generates the message from a template, inserting the number of pending requests in place of the placeholder value {1} .

Click the Test rule button at the bottom to test out the configuration. You should get a notification in your user menu, which will also be sent to email and Slack depending on your notification settings.

The notification as shown in the Coda UI. It will also be sent to your email.

Once you are happy with the results, enable the automation using the toggle at the top of the panel. That’s it!

In addition to being easier to setup, automations in Coda can do so much more:

• Run when a form is submitted or row is edited.
• Use the formula language to define sophisticated conditions.
• Use one of dozens of built-in actions to update the document, or use actions from Packs to make changes in other applications.

To learn more about how to use automations in Coda, check out the video below.