Scheduling

Scheduling

Last Updated On November 10, 2019

Automated scheduling for the API Connector add-on for Google Sheets allows you to refresh your reports automatically at defined intervals. This is a pro feature; please upgrade to access.

Available Intervals

Currently reports can be updated at the following intervals:

  • hourly (Company / Enterprise plans only)
  • daily
  • weekly
  • monthly

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 Creation

To create an automatic trigger, click Add-ons > API Connector > Schedule Triggers > Add New, or click the Schedule tab in the top navigation bar.

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.

Once you’ve chosen which request(s) you’d like to schedule, choose your desired refresh frequency.

api-connector-triggers

Naming

As you can have multiple triggers in a sheet, give them clear names to differentiate (e.g. VWO – daily).

Quotas & Limits

  • Google doesn’t allow add-ons to run triggers more than once an hour. This is their policy for all add-ons, as detailed in the documentation.
    api-connector-triggers2
  • Google Apps Script services imposes additional quotas and limits. For example there is a limit of 20,000 URL fetch calls (this includes API 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.


Troubleshooting Triggers

If your triggers are slow or delayed, you are likely running into Google’s quotas and limits. These limits are per user, not per sheet, so if you have many triggers or files refreshing simultaneously, some of your triggers may be skipped or pushed to the following hour.

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

  • 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.
  • 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.
  • Limit usage of functions like IMPORTJSON() and GOOGLEFINANCE() as they can run every minute and expend your quota.
  • Triggers in the Trash bin may 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.
  • In some cases, Google deletes or disables triggers without apparent reason or warning. If your triggers have stopped running entirely, open the sheet with trigger refresh issues, and delete your existing triggers. Click Add-ons > API Connector > Refresh All Now to re-initiate your requests. Following this, re-create your triggers. This should resolve the issue.

If you’re still having trouble, please send an email to info@mixedanalytics.com. In this email, please include your Google account email, describe when the issue began, and include screenshots of your request setup and your trigger setup.

Comments:2

    1. Hi Mathias, the hourly update limit is because Google doesn’t let add-ons use triggers more than once an hour (documentation). If that ever changes, I’ll add in triggers for more frequent updates.

Leave a Reply

Your email address will not be published.