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
  • Reduce data and spread out triggers
  • Address authorization-related errors
  • Correct cell references
  • Delete excess Google triggers
  • 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, please try the following tips to resolve them.

• Reduce data and spread out triggers
• Address authorization-related errors
• Correct cell references
• Delete excess Google triggers
• 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:

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.

2. Address authorization-related errors

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).

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 +++Sheet1!A1+++.

4. Delete excess Google triggers

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.

Diagnosis: Check if new API Connector scheduled requests don't run, while earlier triggers run without issue. In addition, see if you have 300+ Sheets in which you've opened API Connector.

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.

5. 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.