Modifying the structure of a JSON response

Sometimes, you might need to modify the JSON you receive or create, either so that you can send it onwards, or so that you can create a nice and simple table. When using Rows functions like INSERT(), OVERWRITE(), and UPDATE() to display JSON data in a table, you can only display key-value pairs that are on the same level.

In this article, we’ll cover how to:

• Change key name or values
• Add new key-value pairs
• Change arrays into objects

Ready? Let’s go!

Changing key names or values

If you need to change all instances of a key or value with a different one, just use the SUBSTITUTE() function.

Adding new key-value pairs

If you need to add a new key-value pair to your JSON object use the SUBSTITUTE() function:

Here, notice two things:

1. We include the pre-existing key twice. This is so that we don’t destroy the structure of the JSON.
2. We use single quotation marks '' in the third parameter (in green) as we need to reserved the use of double quotation marks for the JSON.

To add more than one new key-value-pair, just keep adding them before the pre-existing key in the third parameter.

1{
2  "domain": "rows.com"
3}

1{
2  "companyName": "rows",
3  "domain": "rows.com"
4}

Changing an array into an object

When you use RANGE2JSON() you create an array - but you might need to change it to an object instead. To do that, just use this formula: