Using fetch(url) and the json data has array object, output array as a delimited string

Question

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

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

e.g

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

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

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

1). An empty array

 "Issues Checkbox": ""

the output is an empty cell value

2). A single object in the array:

 "Issues Checkbox": [
        "Option 1"
      ]

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

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

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

the output is an empty cell value

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

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

Thank you

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

score 1 · Answer 1 · answered Oct 18 '20 at 20:16

Hi i think with is as a example you can move forward. You want to do this before you write it to the sheet.

let api = 
    {
      "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"
    };

const checkboxes = api["Issues Checkbox"];
const arr = [];
const array = checkboxes.forEach(box => {
    arr.push(box);
})

api["Issues Checkbox"] = arr.join(" | ");

console.log(api);

score 1 · Accepted Answer · answered Oct 18 '20 at 20:20

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)

Using fetch(url) and the json data has array object, output array as a delimited string

2 Answers2

Linked