There may be times that scheduled reports don't update as expected. Triggers are run through Google's Apps Script platform, so it's important to understand the quotas and limits enforced by Google, as well as API Connector settings that may affect scheduled requests.
- Google's Limits & Quotas
- Troubleshooting Triggers
Google's Limits & Quotas
- 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.
- 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.
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
- Correct cell references
- General tips
1. Reduce data and spread out triggers
Issue: 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.
Diagnosis: If triggers time out, you will usually see a "Request started" message without a subsequent "Request completed" message in the logs. You may also have scheduled requests that don't appear at all, if the quota expired before they began.
Solution: Run through the following tips to address quota-related issues:
- 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.
- Don't re-fetch data that's already in your sheet. Instead use append or merge mode to add new data to the end.
- 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.
- 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.
2. Address authorization-related errors
Issue: By default, triggers can run under any user who meets these conditions:
- 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
This can lead to the following authorization-related issues:
- For requests that require OAuth, if multiple users meet the above three conditions, the "wrong" (i.e. unauthenticated) user may execute the trigger.
- Triggers will fail if they are assigned to a user who doesn't meet the conditions listed above (e.g. if they have never opened the API Connector sidebar in the sheet).
Diagnosis: To identify auth-related issues, turn on request logging. The request log will identify which user ran the request.
Solution: Make sure you've selected an authenticated user under the Run as setting, and that they meet the three conditions listed above.
3. Correct cell references
Issue: Scheduled and manual requests should produce exactly the same results.
However, if a cell reference has been entered without a sheet name, e.g.
+++A1+++, this can produce a different response between scheduled and manual requests. The reason is that without an explicitly specified sheet, the request will default to using the currently active sheet. This could work while a user is active in the sheet and manually runs the request, while then failing when the request runs on a schedule.
Diagnosis: Check if the request works when run manually, and only fails when run on a schedule.
Solution: Ensure that all cell references have been configured correctly. The correct syntax is
4. General tips
- Remove unecessary triggers. Click the Account icon from the footer of API Connector, and navigate to the Sheets tab to see which sheets are running requests. Open those sheets and remove any unused triggers, as they may be interfering with the triggers you want.
- Limit usage of functions like
GOOGLEFINANCEas they can run urlfetches every minute or two and expend your quota. To see which applications are executing requests under your account, check https://script.google.com/home/executions.
- 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.
- Occasionally, Google deletes or disables triggers without apparent reason. This is a bug that has been reported and acknowledged by Google, e.g. here and here. Re-opening the add-on automatically re-initiates all triggers, so re-open API Connector to restore any triggers that have been disabled or deleted by Google.
- Google 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.
- Turn on request logging to better identify trigger issues.