使用 Fetch() Google Sheets 将 JSON 解析为多个工作表

我正在尝试使用 Fetch() 导入 json 文件来填充多个 Google 工作表 我已经尝试了几个小时，但我不知道如何做到这一点

json 是动态的，所以它的数据总是在变化，但它的结构保持不变 我的 json 看起来像

{
    "connections": [
    {
      "Id": 131527,
      "From": 49647,
      "To": 49644,
      "Name From": "Horus Max",
      "Name To": "Bob allison",
      "Initial Date": "9/21/2020 15:20",
      "Last Date": "9/21/2020 15:20",
      "Type": "",
      "Weight": 0,
      "Checkbox ZZZ": "",
      "Text Area": "",
      "Radio AAA": "value one AAA",
      "Select bbb": "value one sss"
    },
    { 

    },
    ],
   "elements": [
    {
      "Id": 49645,
      "Type": "Person",
      "Label": "Sally Yager",
      "First Name": "Sally",
      "Last Name": "Yager",
      "Description": "",
      "Segment": "555",
      "Image": null,
      "Project Name": "test222",
      "Initial Date": "09/29/2020 17:44",
      "Last Date": "09/29/2020 17:47",
      "Issues Checkbox": [
        "Option 1",
        "Option 6"
      ],
      "IssuesRadio": "Option 3",
      "Notes": "222"
    },
    {
    }
    ],
    "name": "My project name"
}

我需要将元素数据传递到名为 elements 的工作表中，并将连接数据解析到名为 Connections 的工作表中

Elements sheet

Connections sheet

我得到什么

I have

//=fetchdata("https://sum-app.net/projects/14312620200623668/download_data/kumu_json")
async function fetchdata(url) {
  var response = await UrlFetchApp.fetch(url);
  var responseText = await response.getContentText();
  var responseJson = JSON.parse(responseText);
  
  // Return something to display in the Google Sheet.  
  var rows = [Object.keys(responseJson)]; // Retrieve headers.
  var temp = [];
  for (var i = 0; i < rows[0].length; i++) {
    temp.push(responseJson[rows[0][i]]); // Retrieve values.
  }
  rows.push(temp);
  return temp
}

这是一张谷歌表格，其中包含我得到的内容和期望的结果https://docs.google.com/spreadsheets/d/14vxiueXsUzFtkw22RHA7qVFE--PFVSwfj4zJIU7I3nk/edit?usp=sharing

Thanks

修改要点：

• 不需要使用await to UrlFetchApp和回应。
• 我认为在您的脚本中，响应值顶部的每个对象和值都是通过temp.push(responseJson[rows[0][i]]);. responseJson.connections, responseJson.elements and responseJson.name被检索到。至此，当=fetchdata("https://sum-app.net/projects/14312620200623668/download_data/kumu_json")被放入一个单元格中，仅test222显示。我认为这就是你的问题的原因。例如，当temp.push(responseJson[rows[0][i]]);修改为temp.push(JSON.stringify(responseJson[rows[0][i]]));，所有值都可以看到。但我认为这不是你所期望的结果。
• 在您的目标中，当您想要使用自定义功能时，需要将“元素”表和“连接”表的脚本分开。

当以上几点反映到您的脚本中时，它会变成如下所示。

示例脚本：

请复制并粘贴以下脚本。当您使用此功能时，请放入=GETCONNECTIONS("https://sum-app.net/projects/14312620200623668/download_data/kumu_json") and =GETELEMENTS("https://sum-app.net/projects/14312620200623668/download_data/kumu_json")分别到“连接”和“元素”表。

// =GETCONNECTIONS("https://sum-app.net/projects/14312620200623668/download_data/kumu_json")
function GETCONNECTIONS(url) {
  var response = UrlFetchApp.fetch(url);
  var responseText = response.getContentText();
  var responseJson = JSON.parse(responseText);
  var connectionKeys = Object.keys(responseJson.connections[0]);
  
  // At JSON object, the order of keys are not guaranteed. So the following array including the keys might be suitable.
//  var connectionKeys = ["Id","From","To","Name From","Name To","Initial Date","Last Date","Type","Weight","Checkbox ZZZ","Text Area","Radio AAA","Select bbb"];
  var data = responseJson.connections.map(e => connectionKeys.map(f => e[f]));
  data.unshift(connectionKeys);
  return data;
}

// =GETELEMENTS("https://sum-app.net/projects/14312620200623668/download_data/kumu_json")
function GETELEMENTS(url) {
  var response = UrlFetchApp.fetch(url);
  var responseText = response.getContentText();
  var responseJson = JSON.parse(responseText);
  var elementKeys = Object.keys(responseJson.elements[0]);

  // At JSON object, the order of keys are not guaranteed. So the following array including the keys might be suitable.
//  var elementKeys = ["Id","Type","Label","First Name","Last Name","Description","Segment","Image","Project Name","Initial Date","Last Date","Issues Checkbox","IssuesRadio","Notes"];
  var data = responseJson.elements.map(e => elementKeys.map(f => e[f]));
  data.unshift(elementKeys);
  return data;
}