IMPORTJSON自定义函数Google表格 [英] IMPORTJSON custom function google sheets

问题描述

/**
* Imports JSON data to your spreadsheet Ex: IMPORTJSON("http://myapisite.com","city/population")
* @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";  
  }

}

我正在Google工作表中使用此自定义功能来尝试导入某些api数据.我已经做了一些事情，但是我很难从 https://politicsandwar.com/api/alliances/(位于"a1"单元格中)

I'm using this custom funcation in a google sheet to try and import certain api data. I've gotten it to do some things but im having trouble getting it to pul the "id" from https://politicsandwar.com/api/alliances/ (this is in cell "a1")

在工作表即时消息中的单元格中，我使用= importjson(A1，"alliances/id")，但它表示节点不可用.

In the cell in my sheet im using =importjson(A1, "alliances/id") but all it says is node not available.

有什么建议吗?

推荐答案

节点不可用"的原因:

从https://politicsandwar.com/api/alliances/检索的数据是JSON数据. alliances是一个数组.当for(var i=0;i<patharray.length;i++){json = json[patharray[i]];}由输入的xpath ["alliances", "id"]运行时，我认为第二个循环会发生错误，因为json.alliances中的键没有id.在您的脚本中，似乎无法分析JSON中的数组.

Reason of "Node Not Available" :

Data retrieved from https://politicsandwar.com/api/alliances/ is JSON data. alliances is an array. When for(var i=0;i<patharray.length;i++){json = json[patharray[i]];} is run by the inputted xpath ["alliances", "id"], I think that an error occurs at 2nd loop because of no id of the key in json.alliances. In your script, it seems that arrays in JSON cannot be analyzed.

如果要使用alliances/id从https://politicsandwar.com/api/alliances/检索数据，则仅用于执行此操作的示例脚本如下.

If you want to retrieve data from https://politicsandwar.com/api/alliances/ using alliances/id, the sample script only for doing it is as follows.

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

如果数据显示不是您想要的，请随时告诉我.如果我误解了您的问题，很抱歉.

If the display of data is not what you want, feel free to tell me. If I misunderstand your question, I'm sorry.

这篇关于IMPORTJSON自定义函数Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！