AppScript UrlFetchApp quota limitation

Question

I currently have an Apps Script Editor add-on that works with Google Sheets. It has a custom function implementation that uses the built in UrlFetchApp.fetch() to fetch external data. The add on is growing quickly and pretty much daily I'm seeing this error:

Exception: Service invoked too many times for one day: urlfetch.

I've implemented a cache to save this data for the maximum time of 6 hours, but even with this optimization it seems like it's not enough to account for the volume of requests users are making.

In essence the pseudo code is:

Check cache for data:
 if data:
  use data and return to user
 else:
  newData = UrlFetchApp.fetch(url)
  cache.put(key, newData, 21600)
  return newData to user

I've contacted Google and was told that it's not possible to increase this quota limit and was told to post here to see if anyone can help solve this.

With that being said my question: how is an add on with a custom function expected to scale when this quota only allows for 100,000 requests/day? It seems like this makes it nearly impossible to scale to 1000, 10,000, 100,000 users.

Any guidance or advice on how to tackle this would be amazing, thanks.

Answer 1

From the question

With that being said my question: how is an add on with a custom function expected to scale when this quota only allows for 100,000 requests/day? It seems like this makes it nearly impossible to scale to 1000, 10,000, 100,000 users.

You are missing that the quotas are set by user, not by add-on, in other words, if the Google Apps Script quotas are enough for the first 10 users, it's very likely that they will be enough for the next 100,000 users.

NOTE: The above doesn't include the quotas of the service to be called by UrlFetch.

If by custom function you mean a Google Apps Script function to be called from a Google Sheets formula, you should be aware that every cell having the formula will consume UrlFetchApp quotas and every time that the spreadsheet is opened and every time that the custom function parameters changes will do the same, so you should prepare yourself to manage this i.e. informing your add-on users of the Google Apps Script quotas.

P.S. Google Workspace users have a higher quota than free accounts users.

---

How to prevent UrlFetchApp quotas error caused by custom functions:

1. If the Cache Service isn't working for your add-on "freeze" the results of the formulas using your custom function by using an installable trigger i.e. make a sheet/spreadsheet to copy-paste-values-only and get the values from there. One option is to do this only when the quota was consumed, another might be to allow calling the UrlFetchApp once every 15 minutes.
2. Set a counter using the Properties Service to limit the number of formulas using your custom function by user .
3. Make your custom function able to return an array of values (as a an array formula), optimize the calls done using UrlFechApp and instruct your users to use range references instead of single cells.
4. Instruct your users to "copy-paste as value only" to prevent the custom function formulas being recalculated when that is not really needed.
5. Offer an alternative to custom functions like using time-driven trigger or a custom menu / button on your "task pane" to reduce the calls to UrlFechtApp.

Related

• How does daily Google Apps Script services quota limit apply?
• Google App Script URLFetch quota not clear
• Call custom function in google sheets only once and save value to cell
• Service invoked too many times for one day: urlfetch - Pretty sure quota not exceeded

Resources

• https://developers.google.com/apps-script/guides/services/quotas