Troubleshooting Triggers

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.

Contents

• Google's Limits & Quotas
• Troubleshooting Triggers
  • Request started but didn't complete, or didn't start at all
  • OAuth access not granted or expired
  • API returns an error message when run on a schedule, but works when run manually
  • New triggers aren't recognized, while existing triggers run without issue
• General Tips

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 only allows their scheduler to run in up to 300 sheets per script (though this doesn't seem to be mentioned on https://developers.google.com/apps-script/guides/services/quotas). This means that if you have 300 sheets, scheduled requests won't run in the 301st sheet.

Troubleshooting Triggers

If you are experiencing issues with your triggers, first turn on request logging. This is an important step to identify the cause of the problem. Once you see the results of the request log, take steps as follows:

• Request started but didn't complete, or didn't start at all
• OAuth access not granted or expired
• API returns an error message when run on a schedule, but works when run manually
• New triggers aren't recognized, while existing triggers run without issue

1. Request log shows "Request started" without a subsequent "Request completed" message. Some scheduled requests may not appear at all.

Issue: This scenario is caused by 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.

Solution: Run through the following tips to address quota-related issues:

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. Don't re-fetch data that's already in your sheet. Instead use append or merge mode to add new data to the end.
3. 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.
4. 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.
5. Run your requests in a fresh new sheet without formulas. This can help if your current sheet contains a lot of formulas that update each time the API response sends new data into the sheet, since that process contributes to total execution time.
6. Google's quotas apply to all scripts and extensions used by your Google account. Navigate to https://script.google.com/home/executions to see if services other than API Connector are expending your quota.

2. Request log shows "Request failed: Access not granted or expired" and was executed by the "wrong" email address

Issue: By default, triggers can run under any user who meets these conditions:

1. has Edit access to the sheet,
2. has an API Connector account that enables scheduling, and
3. has opened the sidebar in the sheet at least once

This can lead to the following authorization-related issues:

1. For requests that require OAuth, if multiple users meet the above three conditions, the "wrong" (i.e. unauthenticated) user may execute the trigger.
2. 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).

Solution: Make sure you've selected an authenticated user under the Run as setting, and that they meet the three conditions listed above.

3. API returns an error message but works when run manually.

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.

Solution: Ensure that all cell references have been configured correctly. The correct syntax is +++Sheet1!A1+++.

4. New triggers don't run at all, while existing triggers run without issue

Issue: As mentioned above, there is an undocumented limit of 300 Google triggers per user per project (note that Google triggers are different from API Connector triggers). Since API Connector sets one Google trigger per sheet, this means that if you have 300 sheets, your API Connector triggers won't run in the 301st sheet.

Solution: If you notice that new triggers aren't running, and have more than 300 sheets, delete triggers from https://script.google.com/home/triggers. You can delete them all, and then re-open the API Connector sidebar in just the sheets in which you'd like triggers to run. Opening the sidebar will re-instate those deleted triggers.

General tips

1. 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.
2. Limit usage of functions like IMPORTAPI, IMPORTJSON, and GOOGLEFINANCE as 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.
3. 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.
4. 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.
5. Turn on request logging to better identify trigger issues.