使用Fetch()Google表格将JSON解析为多个表格 [英] Parse JSON into multiple sheets using Fetch() Google sheets

问题描述

我正在尝试使用Fetch()导入json文件来填充多个Google工作表 我已经尝试了几个小时，但我对此并不了解

I am trying to import a json file using Fetch() to populate multiple Google sheets I have been trying for a few hours but I do not know enough to do this

json是动态的，因此其数据始终在变化，但其结构保持不变 我的json看起来像

The json is dynamic so its data is always changing but its structure remains the same my json looks like

{
    "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数据解析为称为Connections的工作表

I need the elements data passed into a sheet called elements and the Connections data parsed into a sheet called Connections

元素表

连接表

我得到的

我有

//=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
}

这是一张包含我所能获得的期望结果的Google表格 https://docs.google.com/spreadsheets/d /14vxiueXsUzFtkw22RHA7qVFE--PFVSwfj4zJIU7I3nk/edit?usp = sharing

Here is a Google sheet with what I get and the desired outcome https://docs.google.com/spreadsheets/d/14vxiueXsUzFtkw22RHA7qVFE--PFVSwfj4zJIU7I3nk/edit?usp=sharing

谢谢

推荐答案

修改点:

• 不需要使用await至UrlFetchApp和响应.
• 我认为在您的脚本中，temp.push(responseJson[rows[0][i]]);会检索响应值顶部的每个对象和值.检索responseJson.connections，responseJson.elements和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]]));时，可以看到所有值.但是我认为这不是您期望的结果.
• 在您的目标中，当您要使用自定义功能时，需要将脚本分隔为元素".表格和连接"工作表.

Modification points:

• It is not required to use await to UrlFetchApp and the response.
• I think that in your script, each object and value from the top of response values are retrieved by temp.push(responseJson[rows[0][i]]);. responseJson.connections, responseJson.elements and responseJson.name are retrieved. By this, when =fetchdata("https://sum-app.net/projects/14312620200623668/download_data/kumu_json") is put to a cell, only test222 is shown. I think that this is the reason of your issue. For example, when temp.push(responseJson[rows[0][i]]); is modified to temp.push(JSON.stringify(responseJson[rows[0][i]]));, all values can be seen. But I think that this it not the result you expect.
• In your goal, when you want to use the custom function, it is required to separate the script for "elements" sheet and "Connections" sheet.

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

When above points are reflected to your script, it becomes as follows.

请复制并粘贴以下脚本.使用此功能时，请将=GETCONNECTIONS("https://sum-app.net/projects/14312620200623668/download_data/kumu_json")和=GETELEMENTS("https://sum-app.net/projects/14312620200623668/download_data/kumu_json")放在连接"表中.和元素".

Please copy and paste the folloiwng scripts. When you use this, please put =GETCONNECTIONS("https://sum-app.net/projects/14312620200623668/download_data/kumu_json") and =GETELEMENTS("https://sum-app.net/projects/14312620200623668/download_data/kumu_json") to the sheet of "connections" and "elements", respectively.

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

这篇关于使用Fetch()Google表格将JSON解析为多个表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！