I have a script in Sheets that is supposed to flatten a JSON post but am having some difficulty getting around the format of the incoming JSON.
Below, I have an example POST which I am trying to parse. Underneath that is my doPost function, with some of the ways I have tried getting the content. doPost uses another function to actually parse the content, but I can't seem to get around what looks like an array formatted JSON?
Here is an example of the POST object:
[
{
"id": "xyz123",
"payload": {
"reference_id": "id_6",
"unit_id": "000111222",
"origin": {
"name": "T-shirt Supply",
"city": "Jiujiang",
"state": "Jiangxi",
"country": "China",
},
"destination": {
"name": "Main Office",
"city": "Surabaya",
"state": "East Java",
"country": "Indonesia",
},
},
"status": "data_received",
"created_at": "2020-01-29T07:41:33.918Z",
"updated_at": "2020-01-29T07:41:33.918Z"
}
]
I have tried in several ways to access the contents, such as payload.reference_id, but for some reason can't find my way into the curled brackets of the JSON object. Here are some of the ways I have tried:
function doPost(e){
var data = e.postData.contents;
// returns JSON formatted [{ "id": "xyz", "payload" : {"reference" : "1", "updated" : true}}]
var data2 = JSON.parse(e.postData.contents);
// returns [object Object]
var data3 = data[0];
// returns [
var data4 = ContentService.createTextOutput(e.postData.contents).setMimeType(ContentService.MimeType.JSON);
// same result as 'data'
return dataX;
}
I have also attempted various workarounds, such as parsing twice, stringify, and more. Any help is greatly appreciated!!!
I think you don't need to parse e.postData.contents, probably it is already an object, not string.
Or if it is string and there is only one element, you can try this:
var data2 = JSON.parse(e.postData.contents.slice(1,-1));
I believe your goal is as follows.
doPost.If my understanding is correct, how about the following modification?
function doPost(e){
var data2 = JSON.parse(e.postData.contents);
return ContentService.createTextOutput(JSON.stringify(data2[0])).setMimeType(ContentService.MimeType.JSON);
}
e.postData.contents is your sample value. Please be careful about this.