Print

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

  • 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.
    api-connector-triggers2

  • 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.
    api-connector-triggers-img3


  • 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

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

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. 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. 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. 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.
  5. 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.
  6. Turn on request logging to better identify trigger issues.

6 thoughts on “Troubleshooting Triggers”

  1. Hi!
    Yesterday, at 8.00 pm, schedule a trigger for the execution of the API request between 1.00 am. and 2.00 am.
    Today I consulted at 9.00am and it was never executed. What time system does the API Connector use? Thank you!

    Reply
    • Hi there, all triggers use the timezone of the spreadsheet. If your request didn't complete when it should have, please turn on request logging and check the troubleshooting suggestions above. Just shoot me a message if you'd like help looking into it.

      Reply
  2. I'm having an issue where the scheduled triggers that I run don't really refresh the data, it just pastes the same data again. However, when I run the request manually by clicking 'run' on a specific request from the 'requests' tab, it updates the data properly. Any idea what might be causing this?

    Reply
    • That's strange. I wonder if it could be a time or time-zone related issue, where the scheduled request pulls data for the same day, but when you pull it manually it's now a new day such that it pulls fresh data. Could you please a) enable request logging and b) try setting your schedule to run a bit later? Also, if this request uses cell references, please double check that they've been entered using the correct syntax.

      Reply
  3. Tôi đã đặt lịch trình tự động kích hoạt chạy " run ip " khung giờ từ 2 - 3 am , nhưng tới nay là 2 ngày trôi qua nhưng " run ip " vẫn không tự động chạy theo đúng lịch , tôi phải chạy thủ công bằng thao tác bấm " run " . xin admin giải quyết vấn đề này giúp tôi . cảm ơn rất nhiều

    Reply
    • Can you please turn on request logging to see if there's any error associated with these scheduled requests? I'll also email you so we can look into this in more detail.

      Reply

Leave a Comment

Jump To