Modifying the structure of a JSON response

How to modify JSON objects so that they match exactly what you need.

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.

kb-JSON-substitute-newKVP

New Key-Value pair example

Let’s say you used GET_COMPANY_DETAIL_CRUNCHBASE() and want to change all the keys from foundedOn to founded on:

=SUBSTITUTE(GET_COMPANY_DETAIL_CRUNCHBASE("Apple"),"foundedOn","founded on")

kb-new-kvp

Inspecting modified JSON

You’ll need to copy and paste the value of the cell into another using Ctrl + Shift + V or Cmd + Shift + V to see the actual change in the JSON.

Adding new key-value pairs

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

kb-JSON-new-KVP

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.

Adding new Key-Value pairs example

If you have a JSON object with the company domain.

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

And you want to add the company name so that it would look like this.

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

Then we just have to write: =SUBSTITUTE(cell-with-JSON,"domain",'companyName":"rows","domain')

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:

kb-JSON-array-to-object

Why not use SUBSTITUTE?

Why don’t we use SUBSTITUTE()? Well, you might have arrays inside your JSON - and remember, SUBSTITUTE() will replace all instances of a string in the entire JSON object.