1

I have a Google Spreadsheet that uses a number of import() functions, including the third-party importJSON() function. It all works great, except that I keep apparently exceeding the limit on how many times per day I can perform the urlfetch() function (used in importJSON()). I'm not manually updating the sheet with any frequency at all, but I can only make maybe 30 or so manual sheet changes before I begin getting these errors.

I believe that Google Sheets itself may be updating all these calls every minute, which at I believe 7 instances of the function, times every minute, may be causing the limit to be hit. If my theory doesn't hold water, someone please correct/inform me as to what may actually be happening.

I'd like to find some way to reduce the frequency of updates. In a perfect world I'd love it to only update when I manually ask it to, but Google Sheets doesn't appear to provide such an option.

Is there any way to reduce or slow down the frequency of function calls?

Improve this question
  • 1
    OH FFS! Somehow it got set to "On Change and every minute" when I believed it to just be set to "on change". This will be far better and likely solve my problem. Thanks for forcing me to double check! – JVC Aug 11 '17 at 22:24
1

The specific issue was the Recalculation setting being not only "on change" but also at time intervals.

If the issue persists, consider the nuclear option: designate some cell of the spreadsheet, say A1, for the status (update / freeze), and put the following on top of importJSON function: 

function importJSON(...) {
  var currentStatus = SpreadsheetApp.getActiveSheet().getRange("A1").getValue();
  if (currentStatus != "Update") {
    return; 
  }
  // the rest of function

This will guarantee that the custom function does nothing unless the sheet is set in the "Update" mode. The cell used for this purpose can have data validation with dropdown, so it's easy to trigger it between two states.

But in general, a custom function like importJSON are supposed to be recalculated only when its parameter(s) change. If the content of the URL from which it imports the data changes, one may force a manual refresh by calling the function like =importJSON(url, 1) putting some extra parameter in. The custom function will ignore extra parameters, but the Sheets software does not know that.

Improve this answer
  • Unfortunately for the moment I've removed this as the accepted answer, because the problem has not gone away. I've only been able to make a few dozen changes to my sheet today - presumably updating ALL formulas that used urlfetch() each time) and I'm again over the limit. This makes no sense as I don't believe the limit could possibly be that low (only a couple hundred total queries??) Seems as though something else must be going on. – JVC Aug 12 '17 at 20:16
  • Interesting solution... I'll give it a shot. Am I correct in assuming that the logic line should actually read currentStatus and not current Status? – JVC Aug 12 '17 at 20:52
  • By the way, the quota for URL fetch is 20,000 per day – user135384 Aug 12 '17 at 20:56
  • K thought so. That does indeed prevent the import function from running constantly, but unfortunately I'll have to wait until Google resets my urlfetch limit to see if it works beyond that. Will post back probably tomorrow... thanks! – JVC Aug 12 '17 at 21:01
  • Didn't see your last comment until after I posrted mine. That's what has me so confused... there is absolutely no way I'm getting anywhere even remotely close to a fraction of 20k requests per day. I've also tried accessing this sheet from 3 different Google accounts, and all 3 are still instantly over the quota. Feels like something else must be going on, but other functions that rely on urlFetch (CRYPTOFINANCE in particular) are also failing with the same error. So what the heck could be going on here?? – JVC Aug 12 '17 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.