Add a delay in google script after parsing json

Question

I have this google script to import api calls into google sheet cells.

/**
* Imports JSON data to your spreadsheet
* @param url URL of your JSON data as string
* @param xpath simplified xpath as string
* @customfunction
*/
function IMPORTJSON(url,xpath){
try{
var res = UrlFetchApp.fetch(url);
var content = res.getContentText();
var json = JSON.parse(content);
var patharray = xpath.split(".");
//Logger.log(patharray);
for(var i=0;i<patharray.length;i++){
json = json[patharray[i]];
Utilities.sleep(1000);
}
//Logger.log(typeof(json));
if(typeof(json) === "undefined"){
return "Node Not Available";
} else if(typeof(json) === "object"){
var tempArr = [];
for(var obj in json){
tempArr.push([obj,json[obj]]);
}
return tempArr;
} else if(typeof(json) !== "object") {
return json;
}
}
catch(err){
return "Error getting data"; 
}
}

And I have various cells into google sheet that calls this function. To update the cache and get updated values from the same api calls, i added a random number parameter at the end of each string that chance simultaneously for all the calls

=IF(C6>0;IMPORTJSON(concatenate("https://axieinfinity.com/graphql-server-v2/graphql?operationName=GetAxieBriefList&query=query%20GetAxieBriefList%20%7B%20axies(auctionType:All,owner:%22";H6;"%22,%20from:%200,%20sort:%20PriceAsc,%20size:%20100)%20%7B%20total%20%7D%20%7D");"data.axies.total";doNotDelete!$A$1);0)

My problem is that I want to put a delay into the script to avoid updating all the api calls in the same time.

How can I achieve this result? Is it possible just adding a utilities.sleep(500) into the script? Because I didn't have any success by doing that

Answer 1

It's very likely that using a custom function is not a good idea because they have several limitations, instead consider to use other means to update the cells like using Range.setValue, Range.setValues or the Advanced Sheet Service (more specifically batchUpdate). Google Apps Script functions having these methods could be called from a custom menu, a simple or installable trigger, from client-side code by using google.script.run, etc.

Some of the relevant limitations of custom functions are

• 30 seconds maximum execution time
• all the formulas (including those having custom functions) are recalculated when the spreadsheet is opened.