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!!!

CodePudding user response：

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));

CodePudding user response：

I believe your goal is as follows.

• You want to output the object of the 1st element in an array which is your sample value from doPost.

If my understanding is correct, how about the following modification?

Modified script:

function doPost(e){
  var data2 = JSON.parse(e.postData.contents);
  return ContentService.createTextOutput(JSON.stringify(data2[0])).setMimeType(ContentService.MimeType.JSON);
}

• In this modification, it supposes that e.postData.contents is your sample value. Please be careful about this.
• When the above-modified script is used, the 1st element of the array is returned.

Note:

• When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
• You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

Reference:

• createTextOutput()