Is there a Google Sheets or MS Excel formula to convert a column with JSON array to multiple rows

One may adopt the code from my project.

json is a tricky format, it uses different data types and also may contain nested items:

{ key: { key1: "a", key2: "b" } }

This is the reason to use google-apps-script for the task.

Please, copy the sample file:

or see the source code here:

https://github.com/Max-Makhrov/GoogleSheets/blob/master/Json.gs

Sample usage:

=getJsonArrayAsTable(C2)

there are many ways one of which is:

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE( IF(IFERROR(SPLIT(C2:C, ","))<>"", "♦"&A2:A&"♠"&B2:B&"♠"& REGEXEXTRACT(SPLIT(SUBSTITUTE(C2:C, """", ), ","), ":(\d+)"), )) ,,999^99)),,999^99)), "♦")), "♠"))

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE( IF(LEN(A2), "♦"&A2&"♠"&B2&QUERY(TRANSPOSE("♠"&QUERY(REGEXEXTRACT(SPLIT( TRANSPOSE(SPLIT(SUBSTITUTE(C2, """", ), "{")), ","), ":(\d+)"), "offset 1", 0)) ,,999^99), )),,999^99)),,999^99)), "♦")), "♠"))

I needed to do something similar today in Google Sheets. The solution I came up with is surprisingle simple. It may be useful but has a couple of dependencies:

• The JSON data conforms to a Google Apps Script Range-like structure (examples below)**.
• A custom function defined using Google Apps Script.

The custom function

function JSONTOJS(JSONString) {  return JSON.parse(JSONString);}

Sample Data

A simple list:

["Option 1", "Option 2", "Option 3", "Option 4"]

Tabular data:

[["Title 1", "Title 2", "Title 3", "Title 4"], ["Option 1", "Option 2", "Option 3", "Option 4"], ["Option 5", "Option 6", "Option 7", "Option 8"]]

Key:value pairs:

[["key1","value 1"], ["key2","value 2"], ["key3","value 3"]]

In use:Assuming one of these JSON strings was in cell A1, in some other cell input the following formula and the data will be displayed in the cells in your Sheet.

=JSONTOJS(A1)

Example Sheet (get a copy of it)

** If you're defining the data structure, this may be useful. You're unlikely to receive data from some service in this format (it is quite Google Sheets unique), in which case your mileage will vary.

google-apps-script