Google sheet Apps script: how to do JSON formatter?

Knight
March 18, 2023
80 views
0 votes
2 Answers

I have JSON text in excel cells but it’s hard to read. I want to format the text so it’s easier to read the variable/value.

 var ss = SpreadsheetApp.getActive();
 var jVal = ss.getRange('N145').getValue();
 var jFormat = JSON.stringify(jVal,null, 3);
 SpreadsheetApp.getUi().alert(jFormat);

Text in Google Sheet:

{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"}

I am getting output as:

"{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"}"

I want to look like:

{
   "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"
}

Tags: google-apps-script json

Answers

- PatrickdC
- March 18, 2023 at 4:03 am
- 0 votes
0
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:
Login or Signup to reply.

- Tedinoz
- March 18, 2023 at 4:11 am
- 0 votes
0
The data is not complying JSON. The main problem is that double quotes have been stripped from many elements. As @PatrickdC points out the data has to be converted to complying JSON.

Let’s assume that you output is in Cell B1.
- Cell B2: =REGEXREPLACE(B1,"[{]","{""")
  - insert double quotes after curly brackets
- Cell B3: =REGEXREPLACE(B2,"[:]",""":")
  - insert double quotes before colons
- Cell B4: =REGEXREPLACE(B3,"[,]",",""")
  - insert double quotes after commas
SAMPLE

PRINT BEAUTIFIED JSON

Refer Indent pretty JSON in Google Sheet Apps Script
Login or Signup to reply.