PROBLEM
Since Google Sheets doesn't natively support JSON data, which I'm importing, I'm trying to split the data into a matrix of four columns and an unfixed number of rows using only Google Sheets's native functions.
DETAILS
I've used the IMPORTDATA
function nested in a TRANSPOSE
function to import a JSON file found here into a single column starting in cell B4 as shown below.
In cell D4 is this formula:
=ARRAYFORMULA(transpose(split(regexreplace(query(array_constrain(if(mod(row(B$4:B), 14) = 4, "^", "|") & B$4:B,max(row(B$4:B) * (B$4:B <> "")),1),,9^99), "^\^",), "^", true, TRUE)))
This results in an error: "Text result of REGEXREPLACE is longer than the limit of 50000 characters." Other attempts result in a single cell returned.
QUESTION
Is there a way to parse JSON data into a matrix utilizing Google Sheets native functions solely and without using scripts, such as IMPORTJson?