CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscustom-function
John Slegers
John Slegers

Reputation: 13

IMPORTJSON in Google Sheet sometimes not getting data

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 ?

Upvotes: 1

Views: 6428

Answers (2)

ajimix
ajimix

Reputation: 992

As Jakub said, the main issue is that all requests are counted as coming from the same Google server.

One solution which I consider easier is to put a proxy server in the middle, this can be done by either purchasing a server and setting it up (which is quite complex) or using a service like Proxycrawl which includes some free requests and after that, unless you run thousands of queries per month, it should cost you less than 1 USD per month.

To do that you just need to edit one line of the script:

var res = UrlFetchApp.fetch(url);

This line, becomes this:

var res = UrlFetchApp.fetch(`https://api.proxycrawl.com/?token=YOUR_TOKEN&url=${encodeURIComponent(url)}`);

Make sure to replace YOUR_TOKEN with your actual service token

Just this simple change will make the requests never fail as each request will be sent from a different IP instead of all coming from Google.

Upvotes: 0

Jakub Bouček
Jakub Bouček

Reputation: 1405

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:

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.

Upvotes: 1

Related Questions