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

Question

I'm trying to parse a column with JSON array data in an Excel file. I want to convert that JSON array to multiple rows.

I've tried using split text to columns, but that is giving inconsistent results.

Input:

Expected Output:

You can find my input and expected_output sheet in this Google sheet
https://docs.google.com/spreadsheets/d/1zohaZXEoppbn4GBq-BLEFHk5n4PPwYPOTnbZbq6YbNs/edit?usp=sharing

Answer 1

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)), "♦")), "♠"))

Answer 2

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)

Answer 3

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