I am trying to output a JSON response to a Google Spreadsheet. The JSON response looks like this:
[{
"city": "Mumbai",
"cityC": "MUM",
"cat": {
"Subs": null,
"respC": 200,
"respM": "OK",
"resp": [{
"CATID": "ABC53249",
"CATDSCR": "FRIED ITEMS",
"SEG": [{
"SEGID": "DFG00985",
"SEGDSCR": "FRIED FISH"
},
{
"SEGID": "FKK24091",
"SEGDSCR": "FRIED VEGGIES"
},
{
"SEGID": "LGH65255",
"SEGDSCR": "FRIED EGGS"
}
]
},
{
"CATID": "GIY35321",
"CATDSCR": "BOILED ITEMS",
"SEG": [{
"SEGID": "QWW35442",
"SEGDSCR": "BOILED FISH"
},
{
"SEGID": "FKG35443",
"SEGDSCR": "BOILED EGGS"
}
]
}
],
"totRec": 0
}
},
{
"city": "Pune",
"cityC": "PUN",
"cat": {
"Subs": null,
"respC": 200,
"respM": "OK",
"resp": [{
"CATID": "EFD53249",
"CATDSCR": "FRUIT FLAVOURS",
"SEG": [{
"SEGID": "SLD00985",
"SEGDSCR": "FRUIT JUICE"
},
{
"SEGID": "DLS24091",
"SEGDSCR": "FRUIT SALAD"
},
{
"SEGID": "DDD65255",
"SEGDSCR": "FRUIT MIX"
}
]
},
{
"CATID": "OLS35321",
"CATDSCR": "MILK ITEMS",
"SEG": [{
"SEGID": "REW35442",
"SEGDSCR": "MILK CHOCOS"
},
{
"SEGID": "DES35443",
"SEGDSCR": "MILK JUICE"
}
]
}
],
"totRec": 0
}
}
]
The following code parses the JSON but needs headings to be present in the google spreadsheet. It also uses dot (or undescore) notation which i dont want.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
var oJSON = JSON.parse(response.getContentText());
// flatten the json
var rows = oJSON.map(function(d){
return flatten(d);
});
// insert new rows
// sheet.insertRowsAfter(1, rows.length);
// getting our headers
var heads = sheet.getDataRange().offset(0, 0, 1).getValues()[0];
// convert object data into a 2d array
var tr = rows.map (function (row) {
return heads.map(function(cell){
return row[cell] || "";
});
});
// write result
sheet.getRange(2, 1, tr.length, tr[0].length).setValues(tr);
}
/**
* Flatten an object.
* https://stackoverflow.com/a/19101235/1027723
*
* @param {Object} data object to flatten
* @return {Object} the flattened result
*/
function flatten(data) {
var result = {};
function recurse (cur, prop) {
if (Object(cur) !== cur) {
result[prop] = cur;
} else if (Array.isArray(cur)) {
for(var i=0, l=cur.length; i<l; i++)
// recurse(cur[i], prop ? prop+"."+i : ""+i);
recurse(cur[i], prop ? ""+i : ""+i);
if (l == 0)
result[prop] = [];
} else {
var isEmpty = true;
for (var p in cur) {
isEmpty = false;
// recurse(cur[p], prop ? prop+"."+p : p);
recurse(cur[p], prop ? p : p);
}
if (isEmpty)
result[prop] = {};
}
}
recurse(data, "");
return result;
}
My question is how do i modify the code to pull the column headings as well as row data to be outputted to the Google spreadsheet?
My spreadsheet headers should be the JSON key : value pairs that should be outputted, where the Key should be the header and its corresponding value should be each row's cell data.