Google sheet Apps script: how to do JSON formatter?

Suggestion: Check the JSON Format and then use JSON.parse within JSON.stringify

In terms of the format, you need to edit the JSON in the specified cell by making the keys into string since the getValue() function only reads values of type Number, Boolean, Date, or String. Thus, JSON objects will be automatically read as string values (which resulted in the output from your post).

As a workaround, you may change:

{payload:{client:"something",messages:[{name:"something",contentType:"something",messageType:"something",content:"something",liveChatGroupType:"",chatCondition:"something",liveChatGroup:"something"}],version:"2.0",params:{type:"dtree"}},messageType:"PAYLOAD"}

to:

{"payload":{"client":"something","messages":[{"name":"something","contentType":"something","messageType":"something","content":"something","liveChatGroupType":"","chatCondition":"something","liveChatGroup":"something"}],"version":"2.0","params":{"type":"dtree"}},"messageType":"PAYLOAD"}

Afterwards, you may use the modified script below to display the json in the alert popup:

function test() {
  var ss = SpreadsheetApp.getActive();
  var jVal = ss.getRange('A2').getValue();
  var jFormat = JSON.stringify(JSON.parse(jVal),null,3);
  console.log(jFormat)
  SpreadsheetApp.getUi().alert(jFormat);
}

In the script above, the string read by the getValue() is first converted into a JSON object using JSON.parse and is then converted back into a formatted string for display using JSON.stringify.

The console output should look like this:

While the alert popup should look like this:

References:

• getValue()
• JSON.parse()
• JSON.stringify()