I have a Spreadsheets that collects data from external JSON API to form a database, I know this JSON API updates 1 time a day, this my spreadsheets make about 500 "URL Fetch calls" to fill the whole database, because it is 1 call per line and the database has about 500 lines, in addition, I also do the "File > Share > Public on web" mode and I know that there is an audience of 50~100 people who access this page.

According to Google's rules, external data is limited to 20,000 per day, as stated on this page, in the "URL Fetch calls" line: https://developers.google.com/apps-script/guides/services/quotas

If I check the option "automatically republish when changes are made" I notice that at the bottom of the page it shows the information "Updated automatically every 5 minutes", but with this option checked, the limit of the "URL Fetch calls" happens in the middle of the day exceeds.

So I have some questions:

• spreadsheets make 500 calls every 5min. even if the data is not changed? if yes then i have 144,000 calls per day so is there any way to change this time to 5min. for 12hour?
• by changing ImportJSON.gs is there any way to make this refresh rate be reduced to just 1 or 2 times a day?

here is the function I use to collect the JSON data: https://github.com/bradjasper/ImportJSON/blob/master/ImportJSON.gs

At the moment I haven't tried anything to solve it, as I don't have much knowledge about this ImportJSON. I expect some code change in ImportJSON function or GoogleSheets config or something like that.

CodePudding user response：

Use the Cache Service to make your custom function call external services less often. See Apps Script Best Practices for sample code. I am not affiliated with any product, website or book related with the Cache Service or the sample code, and I am not employed by any company that is so affiliated.

If all data rows can be retrieved in one go, get them all with one call instead of duplicating your spreadsheet formula 500 times.