5

Our add-on for Google Sheets provides users with an extended library of functions.

The problem is, each function run does a UrlFetch. So if users drag a column down > 100 times, they will likely see the error: "Error: Service invoked too many times in a short time: urlfetch".

Apparently a common solution is to add a random bit of sleep before the UrlFetch function (eg https://productforums.google.com/forum/#!topic/docs/_lgg9hbU6k8). But is there no other way to solve this? After testing with random sleep, I maybe increase the limit to 200 functions at a time, max.

The underlying problem is I do not know know what the limitation actually is. For instance, is it when there's > 100 UrlFetch requests at once in Google's queue that the rate-limit hits? I'm trying to really understand what our options are, but don't even fully get the limitations!

Thanks so much for the help, especially if you're someone from Google :).

bumpkin
  • 3,321
  • 4
  • 25
  • 32
  • Have you checked the best practices? the url you are fetching, is it the same for every row? https://developers.google.com/apps-script/best_practices – Gerardo May 11 '15 at 22:46
  • Hi @Gerardo. I did read those. Unfortunately it changes for each row so I can't cache. – bumpkin May 12 '15 at 00:12
  • are you executing each raw using onEdit? or some events? or is it triggered by the user? – Gerardo May 12 '15 at 16:28
  • @Gerardo - we have a custom function that makes a UrlFetch request, and returns dynamic results. This custom function is triggered by the user, just like they would use any native Sheets function. When the user is dragging down our function > 100 times, we're seeing the error. – bumpkin May 12 '15 at 20:12

1 Answers1

4

The answer to your question "is it when there's > 100 UrlFetch requests at once in Google's queue that the rate-limit hits?" is basically no. The limit is not 100 calls.

You will see that error ("Error: Service invoked too many times in a short time: urlfetch") if 1 of these conditions is met:

  • 22 MB of data is sent or received via urlfetch per minute
  • 3,000 or more calls are made per minute
  • ...or if you hit the daily max. rates for calls and data..

In your case, it sounds like you get the error message before hitting the daily data max or daily call max, so it's probably data per minute condition: 22 MB is sent or received via urlfetch per minute.

You could continually check the number of bytes you're processing via urlfetch and using that, get the function to sleep for a minute if it's close to the limit. However, that's a bit annoying.

You may want to consider trying to make the function more efficient so less data is sent or fewer calls are made. How to do this depends a lot on the function, and we'd need to see the code to make specific suggestions there.

You can find Google's quotas here: https://cloud.google.com/appengine/docs/quotas#UrlFetch

JesseG
  • 238
  • 1
  • 9
  • 1
    this is really helpful. Thank you. Our functions run arbitrary code so hard to make generally more efficient. But at least we can document this for our users. – bumpkin Jun 28 '15 at 04:52
  • The quotas documentation doesn't mention the per-minute limitations; do you have a reference for those? I'm running into the same issue, so I'd like to know if those numbers are still current. – Aaron Dunigan AtLee Jul 20 '23 at 17:24