Google表格中的IMPORTJSON有时无法获取数据 [英] IMPORTJSON in Google Sheet sometimes not getting data

问题描述

我创建了一张表格以保留我的加密货币资产.我使用了在youtube上发现的importJSON函数:(我自己更改了帮助文本)

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

}

我使用此函数来读取API: 这是我的剧本:

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

但是这给出了一个有关以下错误:TypeError:无法从null读取属性"quotes".我在做什么错了?

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

推荐答案

最大的问题是您的脚本调用API至少有4次.如果很少有用户这样做，那么Google服务器会调用API太多次.

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.

Coinmarketcap的API带宽有限.当任何客户端达到此限制时，API返回 HTTP错误429 . Google脚本位于共享的Google服务器上，这意味着许多用户看起来是Coinmarketcap API的一个客户端.

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.

当API拒绝您的请求时，脚本将失败–错误消息与假定的错误相对应(xpath在空变量中找不到 quotes 组件).

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

这是无情的行为.请不要通过大量调用破坏API.

您可以一次从API加载数据，然后针对数据中的每个发现重新使用它.

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

我有从Coinmarketcap API自动填充的类似电子表格，您可以将其复制为:

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

• 硬币电子表格
• Google脚本 .

此脚本严格要求API在整个运行时使用一次，并对所有查询重用一个响应.

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

您还可以在代码中进行一些更改以节省资源:

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

从此更改IMPORTJSON功能:

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

对此:

function IMPORTJSON(json, xpath) {
...

您可以按如下所示更改代码的

和rutime部分:

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]);
...

主要优点是:UrlFetchApp.fetch仅被调用一次.

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

是的，我知道，此代码无法像您的1:1那样工作.那是因为那只能得到欧元而不是BTC的价格.自然地，不需要获取BTC与BTC之间的比较，因为它始终为1，并且您可以从EUR响应中算出其他值-请不要在此类查询中使用api.

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.

这篇关于Google表格中的IMPORTJSON有时无法获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！