Cant parse all JSON values in google apps script (trailing commas)

Question

I am requesting an API and the json i am getting back is this

{
   "items":[
      {
         "id":"231321",
         "externalId":32131",
         "status":"published",
         "network":"facebook",
         "message":"message",
         "externalChannels":[
            "231312"
         ],
         "pictures":[
            picture"
         ],
         "type":"picture",
         "facebook":{
            "dark":false
         },
         "labels":[
            "bus",
            "train",
            "car"
         ],

When i try to parse the labels part though i only get the first value and not the other too. I have done some googling and found it is trailing commas but i was not able to find any solutions. Any thoughts?

EDIT: Using the script below i can see on the logger all the values of the labels but only the first is written on the spreadsheet.

function getdata() {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('api');
  var range = sheet.getRange("A:C");
  var response = UrlFetchApp.fetch("api call");
  var dataAll = JSON.parse(response.getContentText());
  var dataSet = dataAll.items;
  var rows = [],
    data;
  for (i = 0; i < dataSet.length; i++) {
    data = dataSet[i];

    rows.push([new Date(),data.labels]); //your JSON entities here
  }
  Logger.log(rows)
  //sheet.getRange(getlastRow() + 1, 1, rows.length, 2).setValues(rows);
  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 2).setValues(rows);

ziganotschka · Accepted Answer · 2020-05-25 14:02:09Z

1

The problem is not the trailing comma, but the structure of your json - you have an object inside of an object

If your json looks look described in your question (I assume the lacking brackets and quotes are typing mistakes), it is no a string, but already a valid JSON object which does not need to be parsed.
Indeed, trying to parse a non-string will give you an error
If you want to access the individual elements of your JSON object, you can e.g. push them into an array separated by key a and value
This can be conveniently done with Object.keys

Sample:

function myFunction() {
  var myObject = {
    "items":[
      {
        "id":"231321",
        "externalId":"32131",
        "status":"published",
        "network":"facebook",
        "message":"message",
        "externalChannels":[
          "231312"
        ],
        "pictures":[
          "picture"
        ],
        "type":"picture",
        "facebook":{
          "dark":false
        },
        "labels":[
          "bus",
          "train",
          "car"
        ],
      }
    ]
  }
  var data = myObject.items;
  var array = [];
  for (var i = 0; i< data.length; i++){
    var newJson = Object.keys(data[i]).map(function (key) { 
      return [key, data[i][key]]; 
    }); 
    for(var j = 0; j< newJson.length; j++){
      array.push(newJson[j]);
    }
  }
  Logger.log(array);
  Logger.log(array[0][0] + " is " + array[0][1]);
}

answered May 25, 2020 at 14:02

ziganotschka

26.9k2 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ted Over a year ago

hey, thanks for the response. i already have a script and in the logger i can see all the label values, but only one is written on the spreadsheet. I will attach my script in the original post in case you can find out what's wrong. thanks a lot @ziganotschka

ziganotschka Over a year ago

I just had a look at your updated code: Unlike in my case with two nested for loops, you have only one. So you do not loop through all array elements separately and your outcome is: ` [[Tue May 26 10:06:07 GMT+02:00 2020, [bus, train, car]]]` (a 3D array, while setValues() expects a 2D array). If you want to covert the array elements into a string, you can use join(), so: rows.push([new Date(),data.labels.join()]);

Collectives™ on Stack Overflow

Cant parse all JSON values in google apps script (trailing commas)

1 Answer 1

The problem is not the trailing comma, but the structure of your json - you have an object inside of an object

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

The problem is not the trailing comma, but the structure of your json - you have an object inside of an object

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related