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!

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.

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.

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.

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

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

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.

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.

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:

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.

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.

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.

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.

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.

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.

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:

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:

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.

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:

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

Related posts:

Data visualization is a powerful tool for conveying complex information in a concise manner. As data becomes increasingly abundant and complex, effective visualization techniques become even more…

When you buy a drone, you normally need to choose between providing aerial HD footage, and the performance to race other fliers. While you could certainly set up something to do both — especially if…

Sound of this label knock me out. Great mix of happiness and mystic. You can find exactly what you want at that right moment for your soul. The origins of London based DJ & producer Tristan Hallis…