3

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.

Sheldon
  • 175
  • 9
  • Its an add-on which when installed gives you access to a task pane which can be used to manipulate the spreadsheet along with a custom function which you can use by typing into a cell `=CustomFunction(param,param)` – Sheldon Mar 06 '22 at 00:34
  • If your add-on has a custom menu to open the "task pane", it's an editor add-on, not a Workspace Add-on, by the other hand if your add-on uses the Card Service it's a Workspace Add-on. – Rubén Mar 06 '22 at 00:36

1 Answers1

3

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

Resources

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Thanks for the answer. We currently have a Google Workspace Account and I also thought this was the case with quotas as well, but what I'm observing is that a brand new user can come and attempt to use the add on but have this error almost immediately which leads me to believe this limit is not per user. Is there any way I can verify if this limit is per user or per add on? – Sheldon Mar 06 '22 at 00:32
  • So regular gmail users have only 20,000 requests/day available to them? – Sheldon Mar 06 '22 at 00:39
  • even though the docs do indicate that the limit should be per user, if I go try to use the custom function on my account which hasn't reach the 20,000 requests/day limit it results in the same quota error. Is there an add on configuration or something else that needs to be done to make this quota per user? – Sheldon Mar 06 '22 at 03:41
  • Please post a new question including a [mcve] – Rubén Mar 06 '22 at 04:02