使用fetch(url)和json数据具有数组对象，将输出数组作为分隔字符串 [英] Using fetch(url) and the json data has array object, output array as a delimited string

问题描述

我有一个脚本，使用 fetch(URL)将json解析为Google表格此处

I have a script that parses a json into a Google sheet using fetch(URL) answered here

通过@Tanaike，脚本运行良好，除非json数据是数组

by @Tanaike, the scripts work well except when the json data is an array

例如

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

因此，在此示例中，问题复选框" <​​/code>是字符串值的数组

So, in this example "Issues Checkbox" is an array of string values

当数组位于json数据中时，脚本的输出将用于:

When an array is in the json data the output from the scripts becomes for:

1).空数组

 "Issues Checkbox": ""

输出为空单元格值

2).数组中的单个对象:

2). A single object in the array:

 "Issues Checkbox": [
        "Option 1"
      ]

输出是对象，因此单元格值为 Option 1

the output is the object, so the cell value is Option 1

3).数组中有多个对象:输出为空单元格值

3). Multiple objects in the array: output is empty cell values`

 "Issues Checkbox": [
        "Option 1",
        "Option 2",
        "Option 3",
        .,
        .,
        .,
        "Option n" 
      ]

输出为空单元格值

我需要单元格值是数组对象的管道定界字符串

I need the cell value to be a piped delimited string of the array objects

Option 1|Option 2|Option 3| ....|Option n

谢谢

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

推荐答案

问题复选框"中的数组；不能作为数组传递.一种将它们作为一串由逗号分隔的元素传递的选项.一种方法是使用 join .

The Arrays in "Issues Checkbox" can't be passed as an Array. One option to pass them as an string of elements separated by commas. One way to do this is by using join.

var responseJson = {
  "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"
  }]
}

var elementKeys = Object.keys(responseJson.elements[0]);
var data = responseJson.elements.map(e => elementKeys.map(f => {
  return e[f] instanceof Array ? e[f].join(',') : e[f];
}));

data.unshift(elementKeys);
console.log(data)

这篇关于使用fetch(url)和json数据具有数组对象，将输出数组作为分隔字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！