Google Sheets使用ImportJSON导入嵌套JSON数据报错求助

根因分析 #

• 你拉取的JSON结构中items是数组类型，结构示例如下：

{
  "items": [
    {"name": "道具1", "rating": 90, "series": "系列A"},
    {"name": "道具2", "rating": 85, "series": "系列B"}
  ]
}

• 原有函数执行json = json["items"]之后拿到的是数组，下一步执行json = json["name"]时，数组没有name属性，返回undefined，后续操作触发异常进入catch分支，返回Error getting data
• 之前bradjasper版本的Attribute provided with no value: url报错，是因为调用函数时url参数为空或者参数顺序错误导致，优先修复自定义函数的数组适配逻辑即可满足你的需求

修复后的自定义函数代码 #

直接替换你现有的IMPORTJSON代码即可，适配了数组场景的子字段查询：

/**
* 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{
    var res = UrlFetchApp.fetch(url);
    var content = res.getContentText();
    var json = JSON.parse(content);
    var patharray = xpath.split("/").filter(p => p.length > 0); // 过滤空路径
    
    for(var i=0;i<patharray.length;i++){
      const currentKey = patharray[i];
      // 处理当前值是数组的情况
      if(Array.isArray(json)){
        // 遍历数组所有元素，提取对应key的值
        json = json.map(item => item ? item[currentKey] : undefined).filter(v => v !== undefined);
      } else {
        json = json[currentKey];
      }
      if(typeof json === "undefined") return "Node Not Available";
    }
    
    if(Array.isArray(json)){
      // 数组展开为单列返回
      return json.map(item => [item]);
    } else if(typeof(json) === "object"){
      var tempArr = [];
      for(var obj in json){
        tempArr.push([obj,json[obj]]);
      }
      return tempArr;
    } else {
      return json;
    }
  }
  catch(err){
    return "Error getting data";  
  }
}

使用说明 #

• 要拉取所有道具的name字段，直接使用公式=IMPORTJSON("你的API地址", "items/name")即可，会自动把所有道具名称按单列返回
• 要拉取其他子字段同理，比如items/rating返回所有道具总评，items/series返回所有道具所属系列
• 如果你要返回包含所有字段的完整表格，可以直接用=IMPORTJSON("你的API地址", "items")，会自动展开成多行多列的表格

内容的提问来源于stack exchange，提问作者Wess