How to fix the "Service invoked too many times for one day: urlfetch" error?

Question

I am getting the following error in my Google sheet:

Service invoked too many times for one day: urlfetch

I know for a fact I am not making 100k calls, but I do have quite a few custom functions in my sheet. I tried to make a new sheet and copy/paste the script into that one, but I still get the same error. I then switched my account, made a new sheet, added the code, and I still got the error.

Is this just because I am on the same computer? Is Google smart enough to realize I am the same person trying to do it? I highly doubt that, so I am wondering why it would be throwing this error, even after switching accounts and making a new sheet.

In addition to that, is there any way to make sure I don't go over the limit in the future? This error sets me back at least a day with what I was working on. I do plan to write a script to just copy/paste the imported HTML as values into another sheet, but until I get that working, I need a temporary fix.

---

Sample code:

function tbaTeamsAtEvent(eventcode){
  return ImportJSON("https://www.thebluealliance.com/api/v3/event/" + eventcode + "/teams?X-TBA-Auth-Key=" + auth_key);
}

function ImportJSONForTeamEvents(url, query, options){
  var includeFunc = includeXPath_;
  var transformFunc = defaultTransform_;
  var jsondata = UrlFetchApp.fetch(url);
  var object   = JSON.parse(jsondata.getContentText());
  var newObject = [];
  for(var i = 0; i < object.length; i++){
    var teamObject = {};
    teamObject.playoff = object[i].alliances
    
    newObject.push(teamObject);
  }
  
  return parseJSONObject_(object, query, "", includeFunc, transformFunc);
}

That is one "set" of code that is used for a specific function. I am pulling two different functions multiple times. I have about 600 of one function, and 4 of another. That would only be just over a thousand calls if all were run simultaneously.

I should note that I also have another sheet in my drive that automatically updates every hour with a UrlFetch. I do no believe this should affect this though, due to the very low pull rate.

Corrinecorrinne

That number is just a total for a day. But if you exceed the rate of 100000/86400 operations per second during any time during the day then you can still trigger the error. I don't actually know this but I've avoided such errors for a long time by using this approach. So I'm guessing that it might work for you. But you may have to wait for a while before trying it again. – Mcmullen

@Mcmullen what counts as an operation? I have a function that reads and formats an API, and is about 200 lines of code. I have many (about 600) of these functions in my sheet, would each function count as an operation, or would each process the script goes through count as an operation? Based on what i have, it seems each individual operation in the script? – Corrinecorrinne

You mean you have 600 UrlFetches in your script? If so, yeh I think that could be the problem. If you can control slow them down to about 2 per second without timing out that would probably work. Again, I have no inside information. I don't really know. This is the approach I've used to avoid errors. I guess two per second will make you time out so perhaps you'll need to rethink your function. – Mcmullen

Are you able to provide some code that reproduces your problem? As Cooper said, this rate can be exceeded based on the # of calls per second. However, this is VERY unlikely. – Duodecimal

@Mcmullen I do not have 600 UrlFetches, sorry for the confusion. I have 2 UrlFetches per function. I will edit the OP to reflect some of the code – Corrinecorrinne

@Duodecimal I edited the OP to reflect some of the code I have – Corrinecorrinne

Could you explain what you are trying to achieve? It seems your code has multiple inter-dependencies so it's hard to troubleshoot without the whole thing. Try to follow some of the advice on How to Ask. – Duodecimal

Answer 1

I had a similar issue even though I was only calling two fetch calls in my functions and each function per data row. It exponentially grew, and with my data changing, every recalculate call also called those functioned, which VERY quickly hit the max.
My solution? I started using the Cache Service to temporarily store the results of the fetch calls, even if only for a few seconds, to allow for all the cells triggered by the same recalculation event to propagate using only the single call. This simple addition saved me thousands of fetch calls each time I accessed my sheets.

For reference: https://developers.google.com/apps-script/reference/cache?hl=en

Ustulation