Exception: Service invoked too many times for one day: urlfetch.

[Flopicek]

Hello, I am using this script ImportJSON to import data market into my spreadsheet and I am getting this kind of Error.

I know that google sheets has a limit of 20K for urlfetch calls for a day. So I went in Google Apps Script and set My Trigger for once a day. I am calling this ImportJSON just twice in my spreadsheet. So I thought it counts just twice a day ... Still I was getting a error .. so I checked My Executions in Apps Script and I've seen thousands of execution (Instead of two) ... hundrests in second ... Does anybody know where the problem could be?

You can see in the picture ... the same time I am using a script a checking out my executions it is working (two executions on top of the list). As soon as I am no checking my executions the script is running almost continually (all the other executions excluded the first two).

It should be possible to update it every 5 minutes a day and I still should be in a limit of 20000 calls a day. But because of this error I am not able to update it even once a day ... Its running continually And I am getting urlfetch error ...

Does anybody have this kind of problem? If so please share your solution. Thanks

I

[Suhib91]

I have the same issue, I was trying to find out if we can use cache service, but unfortunately without any luck

[andreahansen1019]

I am also having this issue, and it's very frustrating. It seems like it might be an issue with the script being used as an "inline" function rather than something that references data from the spreadsheet and spits out answers upon execution. Given this, I don't think that setting a trigger would do anything to stop it from fetching URLs constantly because it's functioning like any other Google Sheets function (which is to say constantly updating).

I'm out of my depth in terms of knowing how to set it up without this... but if anyone knows the right way to get around this issue I'd love to know!

[Zannick]

I moved the function into a UI menu so it is only triggered manually. This requires adding code like:

function onOpen() {
  // you can only have one onOpen, so you'll have to add these lines to an existing one if you have it elsewhere in your project
  ui.createMenu('JSON')
    .addItem('Fetch JSON description', 'WriteJSON')
    .addToUi();
}

function WriteJSON() {
  var spreadsheet = SpreadsheetApp.getActive();
  var rawSheet = spreadsheet.getSheetByName("Raw JSON"); // your sheet name here

  var array = ImportJSON(...); // your ImportJSON[etc] call here.
  // write the array starting at (1,1), assumes array has results.
  rawSheet.getRange(1, 1, array.length, array[0].length).setValues(array);
}

To make it periodic, I would probably make a WriteJSONCached to be used inline in the sheet, and in the code for it, consult the current time against a cached value of the last time it ran (or inspect a timestamp written out in the json response). But the menu is sufficient for my needs at the moment.

[shanness]

I don't have any triggers, just a checkbox trigger ($A$1). Is there any way to see what is causing updates to happen (when the sheets are closed)?