How to set up auto updates and email notifications in Google Sheets
Since 2014, Supermetrics for Google Sheets has been helping marketers and analysts automate tedious and repetitive tasks with the scheduled refreshing and emailing features.
Fast forward six years, and we’ve moved the scheduler to the Google Sheets sidebar, making it even easier for you to stay on top of all the marketing metrics that matter to you. We’ve also updated the trigger management workflow, allowing you to quickly check the status of all your triggers across different files.
Whether you’re a long time Supermetrics user or new to the product, in this post, I’ll show you how to use these updated features to:
- Set up auto updates in Google Sheets
- Send recurring emails from Google Sheets
- Trigger emails from Google Sheets based on cell value
- Manage all your triggers in one place
Ready? Let’s get started!
1. How to set up auto updates in Google Sheets
Instead of manually running the same query over and over again, you can use the scheduling feature in the Supermetrics sidebar to set your spreadsheet to automatically pull fresh data every hour, day, week, or month.
Step 1: Launch the Supermetrics sidebar in Google Sheets
Psst! If you’re new to Supermetrics, you can install the sidebar and start your free 14-day trial here.
Step 2: Set up your query
When the sidebar opens, we’re simply going to pull our month to date Facebook Ads spend data per campaign from Sweden into the spreadsheet.
When we’re done setting up the query, we’ll click on “Get Data to Table”.
When the query has run, the sheet will be populated with daily Facebook Ads spend data from Sweden.
Step 3: Set up auto updates
Instead of manually refreshing the query every morning, let’s set the Google Sheet to update it automatically.
We’ll start by opening the new “Schedule” tab in the sidebar.
After pressing the “Add trigger” button, the following view will open.
In this case, we’ll get the trigger to refresh the sheet daily. You can set the refreshing frequency from the “Action” drop-down.
When you’re happy with all the settings, click on “Save changes” and you’re done. ?
From now on, every morning when you open up this particular sheet, you’ll have fresh ad spend data in front of you. Cool, right?
And remember that you can apply the same steps to set up weekly or monthly data refreshes for your other reports.
2. Send daily/weekly/monthly emails from Google Sheets
Getting the sheet to automatically add fresh data is cool and everything. But what if you could also send yourself/a coworker/your boss/a client email updates about ad spend, conversions, organic traffic performance and/or any other marketing metric they’re closely monitoring?
Or what if you simply want to automatically send a weekly report to a client?
With Supermetrics, you can do either — or both.
Step 1: Choose refresh and email daily/weekly/monthly
Once you’ve set up the queries you want in your spreadsheet, navigate to “Schedule” again.
This time, under “Action”, instead of “Refresh” hourly/daily/weekly/monthly you’ll want to choose “Refresh & email” daily/weekly/monthly.
The sidebar will then show a list of email settings.
Step 2: Play around with your email settings
In addition to the basic settings, you can also play around with the advanced settings to change the sender name, email address, subject line, and/or email content.
When you’re done, simply click on “Save changes” — and tada! Your weekly email has been scheduled.
It’ll look a little something like this in your inbox.
3. Trigger emails from Google Sheets based on cell value
While the recurring emails can free up a lot of your time on their own, you might also want to set up dynamic email alerts that’ll let you know if some of your paid campaigns are overspending, if your organic traffic drops suddenly, or if certain products in your Shopify store are about to run out of stock.
There are basically unlimited ways to use the conditional emailing feature in Supermetrics for Google Sheets. But since I’m sure you can come up with a thousand and one ways to use it on your own, let’s simply look at how to set it up.
Step 1: Choose conditional emailing
Once you’ve set all the queries you want to pull into your spreadsheet, go to the “Schedule” tab and choose “Refresh & email” daily/weekly/monthly again.
At the very bottom of the sidebar, you should see a checkbox for “Conditional emailing”.
By checking the box, the following settings will appear.
Here, you’ll want to define the sheet (aka spreadsheet tab) your trigger is about and set the cell address (COLUMNROW) that triggers the alert. If this cell is empty when the data refreshes, the conditional email won’t be sent.
This is a handy feature if you only want to be notified when there’s something exceptional going on with your numbers.
Good examples include:
- Budget pacing and/or ad spend tracking for performance marketing
- Conversion tracking for performance marketing, ecommerce, or web analytics
- Organic traffic and/or keyword rank tracking for SEO
- Inventory management for ecommerce
- Sales tracking for ecommerce or self-serve SaaS
Step 2: Make the email notifications easy to digest
Under the advanced settings tab, you can optimize the email subject line and the email content so that the receiver doesn’t even have to open the email to see what’s going on.
You can create dynamic values like this by using the placeholders listed in the Supermetrics sidebar under advanced settings.
For example, for emails sent out on December 9th, 2020 these placeholders would show up as:
- #DAY# = 09
- #MONTH# = 12
- #YEAR# = 2020
- #DATE# = 2020-12-09
- #MONTHNAME# = December
- #DAYOFWEEK# = Wednesday
- #WEEK# = 50
- #LASTMONTH# = 11
- #LASTMONTHNAME# = November
- #YESTERDAY# = 2020-12-08
In this case, I’ve set my email subject line as: Your weekly FB ad spend in Finland was #CELLVALUE_Finland_B11#€ (Where my sheet is called Finland and the cell value I want to monitor is B11)
You can also send yourself a test email (or dozen) to see what the final email will look like with the values you’ve set.
4. Manage all your triggers without ever leaving Google Sheets
Last but not least, you can now also manage all your Supermetrics triggers without leaving Google Sheets. (But don’t worry, if you prefer using the dedicated trigger management page on our website, that’s not going anywhere for the time being either.)
Now let’s take a look at how the new, built-in trigger management feature works.
Step 1: Open the trigger management popup
From the bottom of the sidebar, under the “Schedule” tab, click on “Manage triggers in all your files”
This will open a popup window where you can see all your active triggers across all your files.
Step 2: Edit your triggers
From here, you can review all your triggers by clicking on the accordion icons next to the file names.
To edit the trigger in question, click on “Open file” and launch the Supermetrics sidebar.
From there, navigate to the “Schedule” tab to edit all the triggers associated with that file.
Step 3: Delete the triggers you no longer need
Instead of automatically refreshing old reports or letting unnecessary emails flood your inbox, you can also delete the triggers that no longer serve you.
Simply click on the trash icon on the right side of a trigger to permanently delete it.
Over to you ?
As you’ve probably gathered by now, there are literally millions of different ways you can use the scheduler and triggers in Supermetrics for Google Sheets.
And if you happen to stumble upon a useful setup you’d like to share with the rest of the class, please let me know! We love featuring the cool things our customers build with Supermetrics here on the blog.
Psst! If you’re not yet a customer but would like to play around with Supermetrics for Google Sheets, you can start your free 14-day trial today. Full features. No credit card required.
Turn your marketing data into opportunity
We streamline your marketing data so you can focus on the insights.