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
• Trigger Limits
• Troubleshooting Triggers

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:
  
  
  • a) has Edit access to the sheet,
  • b) has an API Connector account that enables scheduling, and
  • c) 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.

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 set up 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:

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.

Example

In the screenshot below, Daily will always run first. Even if Hourly runs every hour while Daily runs only once a day, Daily will 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.

Trigger Limits

• Google doesn’t allow Workspace add-ons to run triggers more than once an hour. This is their policy for all add-ons using their time-driven triggers, as detailed in their documentation.
  
  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 the same as scheduling and is generally only used when you are active in your sheet.
  
• Google Apps Script services imposes additional quotas and limits (info). For example there is a limit of 20,000 URL fetch 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.
  
• Google allows their scheduler to run in up to 300 sheets per script. This means that if you have 300 sheets, your API Connector triggers won't run in the 301st sheet.

Troubleshooting Triggers

The most common causes of trigger issues are the 6 minutes/hour and 90 minutes/day runtime limits on triggers shown above. 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 triggers time out, you will see a "Request started" message without a subsequent "Request completed" message in the logs.

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

• Reduce data and spread out triggers
• Address authorization-related errors
• Other tips

Reduce data and spread out triggers

1. Reduce the size of your request. Most APIs provide some way to limit the response through parameters in the URL. Filtering fields through the visual field editor or JMESPath 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. Spread out your requests, i.e. instead of scheduling them all to run at midnight, set some at midnight, some at 1am, some at 2am, and so on.
4. 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.

Address authorization-related errors

By default, triggers will automatically run under any user who meets these conditions:

• a) has Edit access to the sheet,
• b) has an API Connector account that enables scheduling, and
• c) has opened the sidebar in the sheet at least once

This can lead to two authorization-related issues:

1. For OAuth requests, if multiple users meet the above conditions, the "wrong" (i.e. unauthenticated) user may execute the trigger. Therefore, make sure you've either selected an authenticated user under the Run as setting, or that all users have authenticated themselves to any OAuth connections.
2. When using the Run as setting, make sure that user meets the conditions listed above. For example, triggers will fail if they are assigned to a user who has never opened the API Connector sidebar in the sheet.

Other tips

1. 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.
2. Limit usage of functions like IMPORTAPI(), IMPORTJSON(), and GOOGLEFINANCE() as they can run urlfetches every minute or two and expend your quota.
3. 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.
4. 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 and 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).
5. 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.
6. Google generally provides higher quotas for users with a paid Google Workspace business account. If you are using a standard (free) consumer account, upgrading to Google Workspace may help.
7. Turn on request logging to better identify trigger issues.