CODEWITHSUNDEEP
javascriptjsongoogle-apps-script
Xys
Xys

Reputation: 10881

Indent pretty JSON in Google Sheet Apps Script

I would like to show an alert with a pretty JSON, but it doesn't get indented well.

Here's the JS code I'm using :

var jsonText = JSON.stringify(jsonObject, null, 2)
SpreadsheetApp.getUi().alert(jsonText);

And here is the indentation I get as a result :

...
"xxx": "",
"xxx": ""
},
{
"xxx": "yyy",
"xxx": "yyy",
"xxx": "",
"xxx": ""
}
]

It's missing the tab indentation, any idea how I could fix this?

Upvotes: 2

Views: 3586

Answers (3)

Cooper
Cooper

Reputation: 64100

You can see the indentations in a text area:

function onMyEdit(e) {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(`<textarea cols="30" rows="12">${JSON.stringify(e,['range','columnStart','rowStart','value','source','rowEnd','rowStart','oldValue'],10)}</textarea>`),'Simple Dialog');
}

enter image description here

Upvotes: 5

TheMaster
TheMaster

Reputation: 50697

It seems normal space \u{0020} characters are removed from SpreadsheetApp Ui.alert() function. You may use any of the other space characters mentioned in this answer as a space character. \u{2000} works:

  SpreadsheetApp.getUi().alert(
    JSON.stringify(
      { x: 1, y: 2, z: [1, 2, { a: { b: 1 } }] },
      null,
      '\u{2000}'.repeat(2)
    )
  );

Upvotes: 4

andrewJames
andrewJames

Reputation: 22022

The only way I know to pretty-print JSON in your alert window is to force JSON.stringify() to use hard-coded non-breaking space characters for the space parameter.

Example:

function popup() {
  var jsonObject = [{"x":1,"y":2},{"x":1,"y":2}];
  // warning - the two spaces used below need to be non-breaking spaces:
  var jsonText = JSON.stringify(jsonObject, null, "  ");
  SpreadsheetApp.getUi().alert(jsonText);
}

This generates the following result in the alert window:

enter image description here

Note - You probably cannot copy/paste those non-breaking spaces from my answer - I think you will only end up copying regular spaces, which will not give you the required indentation (they will be collapsed as per standard HTML rules regarding consecutive whitespace).

So, instead, you can manually enter the NBSP characters using your keyboard.

For me on Windows this was by me holding down the ALT key and typing the number sequence 0160.

On a Mac it is opt + space.

For more systems, see nbsp keyboard entry methods.

Personally, I find this a bit unpleasant - you cannot easily tell that these are hard-coded NBSPs - they look like regular whitespaces in your source code. That is why I added a comment in the code. If there is a cleaner solution, that would get my vote.

(And using the &nbsp; sequence did not work for me - it was treated as a literal string).

Upvotes: 2

Related Questions