IMPORTJSON in Google Sheet sometimes not getting d

Question

I have created a sheet to keep my crypto holdings. I use this importJSON function I found on youtube : (I have changed the help text for myself)

/**
* Imports JSON data to your spreadsheet Ex: IMPORTJSON("https://api.coinmarketcap.com/v2/ticker/1/?convert=EUR","data/quotes/EUR/price")
* @param url URL of your JSON data as string
* @param xpath simplified xpath as string
* @customfunction
*/
function IMPORTJSON(url,xpath){

  try{
    // /rates/EUR
    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]];
    }

    //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";  
  }

}

I use this function to readout an API : This is a piece of my script :

  var btc_eur = IMPORTJSON("https://api.coinmarketcap.com/v2/ticker/1/?convert=EUR","data/quotes/EUR/price");
  var btc_btc = IMPORTJSON("https://api.coinmarketcap.com/v2/ticker/1/?convert=BTC","data/quotes/BTC/price");
  ss.getRange("B2").setValue([btc_eur]);
  ss.getRange("H2").setValue([btc_btc]);

  var bhc_eur = IMPORTJSON("https://api.coinmarketcap.com/v2/ticker/1831/?convert=EUR","data/quotes/EUR/price");
  var bhc_btc = IMPORTJSON("https://api.coinmarketcap.com/v2/ticker/1831/?convert=BTC","data/quotes/BTC/price");
  ss.getRange("B3").setValue([bhc_eur]);
  ss.getRange("H3").setValue([bhc_btc]);

The last few days I get "Error getting data" errors. When I start manualy the script it works.

I than tried this code I found here :

ImportJson

function IMPORTJSON(url,xpath){
  var res = UrlFetchApp.fetch(url);
  var content = res.getContentText();
  var json = JSON.parse(content);
  var patharray = xpath.split("/");
  var res = [];
  for (var i in json[patharray[0]]) {
    res.push(json[patharray[0]][i][patharray[1]]);
  }
  return res;
}

But this gives an error about : TypeError: Cannot read property "quotes" from null. What am I doing wrong ?

Answer 1

The big problem is your script call API at least 4 times. When few users do it too, the Google server call API too much times.

The API of Coinmarketcap has limited bandwidth. When any client reach this limit, the API return HTTP error 429. Google Scripts is on shared Google servers, that means lot of users looks as one client for Coinmarketcap API.

When API decline your request, your script fails – the error message corresponds to the assumed error (xpath cant find quotes component in empty varible).

This is ruthless behavior. Please, don't ruin API via mass calls.

You can load data from API at once and re-use it angain for each finding in data.

I have similar Spreadsheet automatically filled from Coinmarketcap API, you can copy it for your:

• Coins spreadsheet
• Google Script on GitHub.

This my script is strictly ask API only once for whole runtime and reusing one response for all queries.

Change of your script

Also you can make few changes in your Code for saving resources:

Change IMPORTJSON function from this:

function IMPORTJSON(url,xpath){
    var res = UrlFetchApp.fetch(url);
    var content = res.getContentText();
    var json = JSON.parse(content);
...

to this:

function IMPORTJSON(json, xpath) {
...

and rutime section of code you can change like this:

var res = UrlFetchApp.fetch("https://api.coinmarketcap.com/v2/ticker/1/?convert=EUR");
var content = res.getContentText();
var json = JSON.parse(content);

var btc_eur = IMPORTJSON(json,"data/quotes/EUR/price");
var btc_btc = IMPORTJSON(json,"data/quotes/BTC/price");
ss.getRange("B2").setValue([btc_eur]);
ss.getRange("H2").setValue([btc_btc]);
...

Main benefit is: the UrlFetchApp.fetch is called only once.

Yes, I know, this code is not works 1:1 like your. That because that receive prices only for EUR and not for BTC. Naturally fetching comparation between BTC and BTC is unnecessary because it is always 1 and other values you can count matematically from EUR response – please don't abuse an api for such queries.