API Connector Documentation
Scheduling
Automated scheduling for the API Connector add-on for Google Sheets allows you to refresh your reports automatically at defined intervals.
Contents
- Create a Trigger
- Trigger Settings
- Available Refresh Frequency
- Trigger Execution Time
- Trigger Ordering
- Trigger Timezones
- Troubleshooting
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.
Trigger Settings
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)
- daily
- weekly
- monthly
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.
If you'd like to know the exact time your triggers execute, toggle on logging or add a timestamp to your request (located under Output Options).
Trigger Ordering
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.
Trigger Frequencies
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.
Example
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.
Re-order Triggers
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.
Trigger Timezones
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.
Troubleshooting
See here for information on troubleshooting triggers: Troubleshooting Triggers
Is it possible to run this every minute or so?
Hi Mathias, the hourly update limit is because Google doesn't let add-ons use triggers more than once an hour (documentation). If that ever changes, I'll add in triggers for more frequent updates.
I will post my question here also.
Is it possible to control the time of the schedule the triggers at a specific time within the hour or is it determined by Google?
It's determined by Google. They slightly randomize the time within the hour, so there is no way to force it to update at a specific time other than trial and error. If you haven't already, you can add a timestamp to your request (located under Output Options) so that you know exactly when it executes. Once it runs the first time, Google will keep that time consistent day to day.
Hi Ana
While an API call every hour is fine for me, the problem is that the API may not be called on the exact precise time every hour.
Is it possible to manually add the script in Google Sheets, and if so, how would one go about it?
https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_programmatically
Hi Jaco, there's no way to manually hook into API Connector code since the extension runs in its own sandbox. Even if you could, I'm not sure how manually adding the script in Google Sheets would help with running at the exact precise time every hour, since scheduling limits and execution times are set by Google, not API Connector. We already use Google's programmatic triggers in the code.
Please let me know if that answers your question, if not, I'll be happy to explain further.
Would it be possible to set the output mode via the trigger creation/edit page?
Right now I have to create multiple requests and then put them in the order they need to run in. If the I could set up one trigger to run a request in Overwrite and then, another to run it in Append I could do the same thing but with triggers instead of Requests. This would be a lot easier for me to manage since it's just a run parameter that's changing.
Hey Fred, you are suggesting that the output mode gets set on the trigger screen rather than on the create report screen, right? That might be convenient in this specific case, but I would be concerned about splitting up where options get set, especially as a lot of people choose overwrite vs append mode without using scheduling at all. Also, it doesn't seem too time consuming to create a new version of a request with a different output mode, but maybe I'm missing something about your workflow.
Let me know what you think.
First of all, thank you very much for creating this plugin.
I use it to pull data from XML as a paid user. Everything works perfectly when I manually trigger it.
However, when I use hourly schedule triggers, I constantly have problems. I can't clearly tell when it is working or not. That's why I often have to trigger manually.
I guess you are sending these schedule triggers into a queue system, and it performs these operations in turn. In busy times, it doesn't come to us.
How can we overcome this problem?
Hi Oguz, I think we already communicated about this via email but I'll respond here as well. Thank you for the message and sorry you're having trouble with your requests. Triggers are managed by Google, and the most likely cause of scheduling issues is that you're running into Google's 6 mins/execution scheduling limit. This article contains several suggestions for avoiding Google's limitations.
Update: there’s now a request logging feature which should also help identify when requests fail.
Hello. I'm a paying Mixed Analytics customer. I understand that Google scheduling cannot allow a user to specify when within an hour a job gets called (this is not a limitation of Mixed Analytics). I'm trying to get my task to run exactly on the hour (8:00:00, 9:00:00, etc.) 24 times a day, 365 days a year by setting up a macro in google sheets, and then scheduling the macro to run at exact times.
My questions are - have you seen a way to schedule macros for an exact time? Can I call Mixed Analytics from within a google sheet macro? Is this a workaround to this problem or am I wasting my time?
Sorry, I don't know how to call our add-on from a macro, since Google only lets us access add-on functions through the menu, sidebar, custom functions, etc (the elements of the add-on itself). I tested creating a macro and it didn't recognize any of my actions in the add-on.
For something like this it might be better to create your own custom script (or have a developer write one) as Google's time restrictions don't apply to those.
Anna,
Have you implemented exponential backoff in response to errors? If not, have you considered it?
https://developers.google.com/admin-sdk/directory/v1/limits
I've setup my url YT API calls to not go over quota, but am still getting 403 errors in AC_log. YT developer console shows no indication of going over limits.
Hey John, sorry, no we haven't. I think the problem is that there are many potential errors that wouldn't be resolved by trying again (like authorization errors), and could even make it worse (like if the quota needs to reset). Scheduling only sends requests 1x max per hour, and we automatically insert 2 second delays between multi-query requests, so rate limits usually aren't a problem. If by "YT" you meant YouTube, API Connector has a quota from YouTube that gets shared between all our users, so one possibility is that you hit our limit. We can contact Google to ask for a quota increase, and/or you can get your own API key so you pull from your own personal quota. Update: we received a quota increase from YouTube.
HI,
Somehow the scheduling can only update the status daily/ weekly/ monthly. The option of "hourly" does not show up in the option. Is that true?
Hourly scheduling is a Business plan feature, so if you don't see it, you're most likely not on that plan. To check your plan, please click the Account link in the main menu or footer of API Connector, or just send us a message via support.
As a paying Mixed Analytics customer, we can schedule triggers. Over the years this has grown, some are no longer needed. How can we get a detailed overview of all the triggers? Via https://script.google.com/home/triggers I get to see 30+ daily requests but there is not much (no) detail in them, it would be nice to see through which Spreadsheet the trigger is running.
You could navigate to Google Drive > Recent to see any sheets that have recently updated, but for a more comprehensive list please message support. We can query the backend server logs to get you a list of where your triggers are running. I'll also add more accessible trigger metadata to our list for future development.
Update: You can now see all your recently active sheets on the Sheets tab in API Connector's Account screen.
How do I create a button - "Refresh data"? Which will run all requests.
You can't create a button but you can do one of the following:
1) Click Extensions > API Connector > Refresh All Now
2) Use the IMPORTAPI custom function (see the section on fast cell-based refresh for info on refreshing requests via a checkbox)
Hi,
quite disapointed with the scheduling feature that is no longer included in the free version. can't use the api anymore
Sorry you're disappointed, but scheduling has been a paid feature since the day this extension was released 4+ years ago. If you had access before and don't anymore, you were likely in a free trial that temporarily activated all paid features.
Hi,
If I have date parameters in my API link, is there a way of these to update automatically rather than staying static? I would like to run the report once a day for month to date without having to manually change the dates every time.
Thanks
Molly
Sure, you can create dynamic dates in your sheet (e.g.
=today()-1for yesterday) and then reference those cells in your request. Please see here for more info: Use Cell Values in RequestsThat's exactly what I was looking for. Thanks
I was hoping to run a request as another user but I can't select them from the Run as dropdown.
Can you please click Share in the right-hand corner of Google Sheets and make sure the user has Edit access? They should have individual access to the sheet (i.e. not through an alias or group).