Scheduling

Automated scheduling for the API Connector add-on for Google Sheets allows you to refresh your reports automatically at defined intervals. This is a pro feature; please upgrade to access.

Contents

Available Intervals

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

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.

Trigger Creation

To create an automatic trigger, click the Schedule tab in the top navigation menu in the API Connector sidebar.

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.

Once you’ve chosen which request(s) you’d like to schedule, choose your desired refresh frequency.
api-connector-triggers

Execution Time

Triggers are set within API Connector and then managed by Google, which will execute your requests on a recurring interval. According to Google, the time is slightly randomized — 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 want to know the exact time your triggers execute, you can 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:

  1. If you select an “All” trigger, it will run through every request in the sheet, in the order they’re set in the Requests screen. Requests will run from top to bottom.
  2. If you select specific requests instead of All, it will run through them in the order they’re set on the Trigger screen. Again, triggers will run from top to bottom.

This means that if two triggers are set to run at the same schedule, the one higher up will run prior to the other. Further, if two triggers are set at different intervals, the one higher up still takes precedence over the other one. For example, if you have one trigger that runs every hour, and then one trigger below that that runs every 12 hours, the hourly trigger will run first during each of the 12 hour intervals that these triggers intersect.

On both the Requests screen and the Trigger screen, triggers can be re-ordered by grabbing their handles and dragging and dropping them to the desired order.

Timestamps

In some cases, you may want to see when your request ran or create vertically-oriented time-series data. To start the time-series collection process, first make sure you check Add timestamp under Output options. Run your request one time normally to get any headers in your first row of data, then re-run in append mode.

Then, schedule your trigger to run at your desired interval. When it runs you’ll see timestamps in the left-hand column.

Note: If your API URL returns more than one row, there will be gaps in the “Refresh Time” column equal to the number of rows returned for each call.

Request Frequency

Google doesn’t allow add-ons to run triggers more than once an hour. This is their policy for all add-ons, as detailed in the documentation.

api-connector-triggers2

As an alternative, you may be able to use API Connector’s custom IMPORTAPI() function to force more frequent refreshes (check the section on Fast cell-based refresh). Note that IMPORTAPI() is not as reliable as scheduling and will run only when the sheet is open.

Troubleshooting Triggers

Google Apps Script services imposes additional quotas and limits. For example there is a limit of 20,000 URL fetch calls (this includes API calls) per day per user, processes can only run for 6 minutes each hour, and triggers have a total runtime limit of 90 mins a day. If your triggers stop running or get delayed, you are likely running into these limits.
api-connector-triggers-img3

The most common causes of trigger issues are the 6 minutes/execution and 90 minutes/day runtime limits on triggers. These runtimes are shared between all your sheets that use triggers, so if you have many sheets or queries refreshing at the same time, your triggers may instead be skipped or pushed to the next hour or day. This can happen consecutively, such that your triggers don’t run at all.

If you are experiencing issues with your triggers, please try the following tips to resolve them:

  1. Reduce the size of your request. Most APIs provide some way to limit the response through parameters in the URL. JMESPath filtering can help too as it reduces the time required to print data into the sheet.
  2. To avoid inadvertently reaching Google’s limits, limit triggers and queries where possible. Reserve hourly refreshes for your most important queries, and don’t run hourly refreshes if you only need daily. Your data may never fully refresh if you implement large numbers of hourly triggers.
  3. Similarly, be aware that paginated calls each count as one URL fetch request, i.e. if you run a request that paginates through 100 pages, Google will count 100 urlfetches. Large amounts of pagination are also more likely to run into Google’s 6 minutes per execution limits. Consider using append mode rather than re-fetching an entire data set via pagination.
  4. Remove unecessary triggers. Navigate to https://drive.google.com/drive/my-drive and click Recent to identify files that have updated recently, and check to see if these updates were automatically triggered by any script or add-on (not just API Connector). Remove any unused triggers, as they may be interfering with the triggers you want.
  5. Limit usage of functions like IMPORTAPI(), IMPORTJSON(), and GOOGLEFINANCE() as they can run urlfetches every minute or two and expend your quota.
  6. Triggers in the Trash bin will remain active until they are permanently deleted. Therefore, navigate to https://drive.google.com/drive/my-drive and click Trash > Empty Trash to remove them permanently.
  7. Occasionally, Google deletes or disables triggers without apparent reason or warning. This is a bug that has been reported and acknowledged by Google, e.g. here. (Triggers are stored on Google’s side, so you can determine if you’ve been affected by setting triggers and then looking to check if they’ve been deleted or disabled. You can see all triggers associated with your account here: https://script.google.com/home/triggers).
  8. Re-opening the add-on automatically re-initiates all triggers. So, if your triggers have stopped running, please open the sheet with trigger refresh issues, and open API Connector. This will restore any triggers that have been disabled or deleted by Google.
  9. Google generally provides higher quotas for users with a G Suite business account. If you are using a standard (free) consumer account, upgrading to G Suite may help.

11 thoughts on “Scheduling”

  1. 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?

    Reply
    • 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.

      Reply
    • Hi Jaco, sorry, there’s no way to manually hook into API Connector code. 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.

      Reply
  2. 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.

    Reply
    • Hey Fred, thank you for the suggestion. Just so I follow, you are suggesting that the output mode gets set on the trigger screen rather than on the create report screen, right? I can see how that might be convenient in this 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 click Actions > Copy and 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. I am always looking to make API Connector better and I very much appreciate your feedback!

      Reply
  3. Hi,
    I have a question regarding to the autorefresh function with import api. I am referencing the $A$1 cell to the googlefinance currency so it updates every 2 minutes as you suggested. I am referencing the importapi to the saved request that pulls data. Therefore, the importapi looks like =importapi(“saved request”, $a$1) – when i do it i see this error “Request failed: Cannot find function split in object 0.842075.”. What should I do in that case?

    Reply
    • Hi Wojtek, thank you for the message and sorry you’re having trouble with your request. I think the problem is that you’re missing the middle argument in your request. Can you please try the following instead?
      =IMPORTAPI("saved request","",$A$1)

      Please let me know if that resolves the issue.

      Reply

Leave a Comment