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?

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?

• javascript
• json
• google-apps-script

• 3 Using, alert(JSON.stringify([{"x":1,"y":2},{"x":1,"y":2}], null, 2)) I can see the indentation with a native Chrome browser alert dialog. Google's app-scripts dialog seems to render the text in HTML. So it will honor the new-lines in your formatted JSON, but it will trim the leading white-space on each line. This is normal when setting HTML text for an element. You would have to specify white-space as pre somehow, or add literal space escape characters to your JSON text. Something along the lines of str.replace(/ /g, ' '). – Mr. Polywhirl Commented Nov 12, 2021 at 16:24
• @Mr.Polywhirl Tyour ment can be a good legitimate answer for this question. You may want to post this an answer. – SputnikDrunk2 Commented Nov 12, 2021 at 17:46

3 Answers 3

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

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

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:

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 ment in the code. If there is a cleaner solution, that would get my vote.

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