How to Slow Down Frequent Execution of Import Functions in Google Sheets

google sheetsimport

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?

Best Answer

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.

Related Topic