Automated scheduling for the API Connector add-on for Google Sheets allows you to refresh your reports automatically at defined intervals.
- Create a Trigger
- Trigger Settings
- Available Refresh Frequency
- Trigger Execution Time
- Trigger Ordering
- Trigger Timezones
Create a Trigger
To create an automatic trigger, click the Schedule tab in the top navigation menu in the API Connector sidebar and click Create trigger. Optionally, tick the box to create a log of all requests run in your sheet.
When you add a new trigger there are several available options.
- Active/Paused. Paused triggers will not run until their status is switched to active.
- API Request name. Here you can choose specific API request names from the drop-down menu, so different requests can have different triggers. Alternately, you can choose the 'All' option to select all of them at once, but be mindful of Google's limits.
- Run request. Once you've chosen which request(s) you'd like to schedule, choose your desired refresh frequency.
- Run as. By default, this will be set to "Any user", in which case triggers may run as any user who:
- has Edit access to the sheet,
- has an API Connector account that enables scheduling, and
- has opened the sidebar in the sheet at least once
In some cases, you may wish to assign scheduled requests to a specific user, for example if only that user has authenticated an OAuth connection, or you want to split up requests between users to avoid hitting Google's limits. The Run as list will show all users who have Edit access to the sheet.
- Trigger name. Give your trigger a name.
Available Refresh Frequency
Currently reports can be updated at the following intervals:
- hourly, including every 1, 3, 6, and 12 hours (Business / Team plans only)
The exact execution time is set by Google, see here for more info.
Trigger Execution Time
Triggers are set within API Connector and then managed by Google, which will execute your requests on a recurring interval.
Google slightly randomizes the time. For example, if you set a daily trigger between 2am - 3am, Google will choose a time between 2am and 3am for your trigger. Following that, your trigger time will remain consistent each day, such that exactly 24 hours passes before the trigger fires again.
In some cases you may want one request to run before a second request, to ensure that some ID or other value is ready for a subsequent request. This can be done by ordering triggers. There are two methods of ordering triggers:
- Individual request triggers will run in the order they're set on the Trigger screen. The trigger listed at the top will run first, the trigger underneath that will run second, and so on, all the way to the bottom.
- An "All" trigger only has one trigger listed on the Trigger screen, as that trigger applies to all the requests in the sheet. In that case, the "All" triggers will run in the order of requests listed on the Requests screen. As above, requests will run from top to bottom.
If two triggers are set to run at the same frequency, the one higher up will always run prior to the other.
If triggers are set to run at different frequencies (e.g. one hourly, one daily), they continue to run only at their assigned frequency, but the one higher up takes precedence over the other one during the interval that they overlap.
In the screenshot below, Hourly runs every hour and Daily runs just once a day. Daily will always run first during the hour that they both run.
On both the Requests screen and the Saved Triggers screen, triggers can be re-ordered by grabbing their handles and dragging and dropping them to the desired order.
All triggers will use the spreadsheet timezone, which you can view by clicking File > Spreadsheet Settings. For more information on changing your sheet timezone, check https://support.google.com/docs/answer/58515.
Please note that if you change your spreadsheet timezone, you will need to delete and re-add your triggers to run off the updated timezone.
See here for information on troubleshooting triggers: Troubleshooting Triggers