By using the feature Schedule refresh & emailing in Supermetrics for Google Sheets, you can set up triggers that automatically update your queries and email your report to you on a schedule you set.
Scheduled refresh & emailing is available in Supermetrics for Google Sheets and Excel. Options are hourly, daily, weekly and monthly. If your license doesn’t currently support the option that you’d wish to use, you can modify your license with these instructions: Modifying existing accounts (It is not possible to modify licenses that were bought before 28th or March 2021)
Quick links to the sections below:
The Schedule refresh & emailing feature supports both the option to refresh all queries in the Sheets file the trigger is attached to, as well as allow you to send emails that contain an attachment replicating part of your file to other users. The following functions are available:
- Refresh-only triggers
- Refresh & email triggers
- Email alerts if queries fail a refresh
- Conditional emailing - only send if a certain cell is not empty
- Process all triggers now option to force run them
- A variety of scheduling cycles:
- Hourly refresh
- Daily refresh and daily email - can set time
- Weekly refresh and weekly email - can set day of week and time
- Monthly refresh and monthly email - can set day of month and time
How to set up a trigger
These steps will show you how to set up a trigger that will update all queries in the file, and if selected send the file as an email.
- Open the Sheets file you wish to set the automated trigger on, and open the Supermetrics sidebar.
- At the top of the Supermetrics sidebar, go to the Schedule tab.
- Click on the Add trigger button.
- In the Action drop-down, select an option based on how often you want the trigger to run, and whether you want to only refresh the queries, or also send an email of the report automatically. You will see options depending on your license level.
- Set the time you want your trigger to start the refresh process.
- If using a weekly or monthly refresh, you will also have the option to set the day of the week or month.
- Check the time zone to make sure it is what you want. See Changing the timezone of a trigger if you need to adjust this.
- Be aware that the time is when the refresh process will start. Depending on the amount of queries and their size in your file, it may take the system some time to complete all the refresh requests.
- You can also select to receive an email alert if your queries would fail to be refreshed.
- If you selected a refresh-only option, skip to step 10.
If you selected the email option, in Email report to: add one or more email addresses, comma separated, as recipients.
- In the Sheet: drop-down, select the specific tab of the sheet you want used for the email.
- In the Type: drop-down, select the kind of file you want attached to the email. This file will contain the refreshed data from the Sheet.
- The Advanced email settings are optional but give you additional control over the details of the email itself.
- Note: It's best to keep the Sender email: field set to firstname.lastname@example.org to avoid delivery issues.
- Click Save changes to confirm the configuration.
When the configured date/time arrives, the trigger will start processing queries. If you find the trigger is not working as expected, check the Best practices and Common issues sections below for some guidance.
As every user's scenario may be slightly different with the size of queries to refresh, number of queries in a file, number of files that needs triggers, etc., these guidelines won't cover every case exactly. But generally, following these best practices when setting your triggers up should help keep them running in a reliable and performant manner.
- Keep the number of triggers per file low - ideally 1 or 2 per file, max.
- Avoid stacking too many triggers to run at the same time, and spread non-key triggers to off-hours (like after work or at night). The default trigger system can only handle about 6 minutes per account per hour (Google AppScript limitation) so if it cannot complete the triggers assigned to that time within the 6 minutes, they will get pushed and cause reliability issues.
- Related to the above for hourly triggers, also try to keep the number of these low as they put a lot of pressure on the trigger system. As they have to run every hour, having more of them generally leads to them becoming increasingly unreliable.
- There is a technical limit of about 10-13 triggers on a file when it will just stop loading them or letting you save them. We now also have a hard limit of 5 per file to prevent this. Break the data up into separate files if you need more triggers.
- Keep the total number of triggers per account less than 50, if possible. More than that and users tend to run into performance issues. 100+ can be very problematic and our technical support will generally ask you to start making cuts at that point.
- Triggers are not removed from files moved to the trash, so they can keep running from there. Either completely delete the file or use the Managing triggers for Google Sheets feature to find and delete the trigger.
- If you transfer your license/account, make sure you transfer the triggers to the new user or delete unwanted triggers under the old user (else they get orphaned but will keep running). See also How can I transfer queries from one user to another?.
The most common reasons for trigger failures can be found in this general troubleshooting guide, so we ask you to start there to see if that helps: The Triggers I Added Don't Work.
Hourly triggers don't work at all
Triggers are delayed or unreliable
Check the Best Practices above to see if you have gone beyond the safe limits of the feature. Also see My triggers are delayed for hours or they do not fire at all.
Queries refresh but the email isn't sent or the attachment is empty
Check that you have valid settings for your email trigger and the destination is the right email. The email creation system does have a size limitation, so check if you are trying to send too much in the attachment and reduce down to one sheet specifically.
Why are certain types of triggers not available for me?
You may need to upgrade your license as the trigger frequency available is governed in your license. See Modifying existing accounts