Parse Json - Google Apps Script

Question

I am trying to collect the JSON response from a webhook in google scripts and output the result to google sheets.

Ideally i want to parse and output the JSON data to columns in the spreadsheet, (each time the webhook is fired this data is entered on the next row in the spreadsheet)

Here is the example data that is collected from the webhook:

 {parameter={environment=prod, payload={"TEST":"WARNING: THIS IS A TEST PAYLOAD ONLY. THESE IDs ARE NOT VALID FOR THIS RETAILER","id":"b53744ce-2d21-11e2-8057-080027706aa2","retailer_id":"9a5521c3-2d20-11e2-8057-080027706aa2","customer_code":"JOEB","balance":"0.000","points":0,"note":"<p>This is a <em>really<\/em> nice customer<\/p>","year_to_date":"6750.00000","sex":"M","date_of_birth":"1986-10-12","custom_field_1":"foo","custom_field_2":"bar","custom_field_3":"baz","custom_field_4":"","updated_at":"2012-11-14 17:47:57","created_at":"2012-11-13 12:35:57","contact":{"first_name":"Joe","last_name":"Bloggs","company_name":"Acme Limited","phone":"021 555 55555","mobile":"","fax":"","email":"[email protected]","twitter":"@joebloggs","website":"http:\/\/www.example.com\/","physical_address1":"12 Jimmy Street","physical_address2":"","physical_suburb":"Johnsonville","physical_city":"Jamestown","physical_postcode":"65225","physical_state":"WA","physical_country_id":"AU","postal_address1":"12 Jimmy Street","postal_address2":"","postal_suburb":"Johnsonville","postal_city":"Jamestown","postal_postcode":"65225","postal_state":"WA","postal_country_id":"AU"},"contact_first_name":"Joe","contact_last_name":"Bloggs"}, retailer_id=b8ca3a6d-ea18-11e4-ee41-1ca4a74ff9da, type=customer.update, domain_prefix=123}, contextPath=, contentLength=1859, queryString=null, parameters={environment=[Ljava.lang.Object;@53432cae, payload=[Ljava.lang.Object;@2af01d0d, retailer_id=[Ljava.lang.Object;@4282d52a, type=[Ljava.lang.Object;@5a178fb1, domain_prefix=[Ljava.lang.Object;@107bfe01}, postData=FileUpload}

Here is my existing google script code that simply dumps the json data into the next row

    function doPost(e) {
    return handleResponse(e);
    }

    function handleResponse(e) {
     var ss =SpreadsheetApp.openById('XYZ');
     var lastRow = sheet.getLastRow() + 1;
     SpreadsheetApp.getActiveSheet().getRange('a' + lastRow).setValue(e);  
    }

I have experimented with JSON.parse but seem to be getting nowhere fast.

Many Thanks

Cooper Cooper · Accepted Answer · 2017-07-14T23:09:39

Not all of it is in JSON format. You can check it here. Below I've separated what I believe to be the JSON formatted data.

{
  "TEST": "WARNING: THIS IS A TEST PAYLOAD ONLY. THESE IDs ARE NOT VALID FOR THIS RETAILER",
  "id": "b53744ce-2d21-11e2-8057-080027706aa2",
  "retailer_id": "9a5521c3-2d20-11e2-8057-080027706aa2",
  "customer_code": "JOEB",
  "balance": "0.000",
  "points": 0,
  "note": "<p>This is a <em>really</em> nice customer</p>",
  "year_to_date": "6750.00000",
  "sex": "M",
  "date_of_birth": "1986-10-12",
  "custom_field_1": "foo",
  "custom_field_2": "bar",
  "custom_field_3": "baz",
  "custom_field_4": "",
  "updated_at": "2012-11-14 17:47:57",
  "created_at": "2012-11-13 12:35:57",
  "contact": {
    "first_name": "Joe",
    "last_name": "Bloggs",
    "company_name": "Acme Limited",
    "phone": "021 555 55555",
    "mobile": "",
    "fax": "",
    "email": "[email protected]",
    "twitter": "@joebloggs",
    "website": "http://www.example.com/",
    "physical_address1": "12 Jimmy Street",
    "physical_address2": "",
    "physical_suburb": "Johnsonville",
    "physical_city": "Jamestown",
    "physical_postcode": "65225",
    "physical_state": "WA",
    "physical_country_id": "AU",
    "postal_address1": "12 Jimmy Street",
    "postal_address2": "",
    "postal_suburb": "Johnsonville",
    "postal_city": "Jamestown",
    "postal_postcode": "65225",
    "postal_state": "WA",
    "postal_country_id": "AU"
  },
  "contact_first_name": "Joe",
  "contact_last_name": "Bloggs"
}

I ran this little script just to see if I could get the data out properly.

function messingWithJson()
{
  var html='';
  var br='<br />';
  var text=myUtilities.loadFile('jsonquestion.json');//This just loads into text from a file
  var data=JSON.parse(text);
  html+=br + Utilities.formatString('%s<br/>%s %s<br />%s', data.created_at,data.contact.first_name,data.contact.last_name,data.contact.company_name)
  html+='<br /><input type="button" value="Close" onClick="google.script.host.close();" />';
  var userInterface=HtmlService.createHtmlOutput(html).setWidth(800);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'JSON Question');
}

And this was my output.

2012-11-13 12:35:57
Joe Bloggs
Acme Limited

And I was able to recover what I wanted. If you want some help with JSON you might like to check out these videos. Look towards the middle of the playlist for JSON.

Parse Json - Google Apps Script

1 Answers