Splitting JSON-Formatted Single Column Into Rows and Columns in Google Sheets

Question

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?

Answer 1

Split the data before applying regexextract(), like this:

=arrayformula( 
  iferror( 
    regexextract( 
      transpose( 
        split( 
          query( 
            transpose( importdata("https://www.sec.gov/files/company_tickers_exchange.json") ), 
            "", 9^9 
          ), 
          "] [", false, true 
        ) 
      ), 
      "(\d+) (.+?)[ /]([-A-Z]+) ?([\w]+?| )(?:]]})?$" 
    ) 
  ) 
)

The formula will be do a lot of text processing. You would probably be better off by using ImportJSON().

Answer 2

Another solution:

Place your original IMPORTDATA formula in A1 of some sheet. Hide Col A if you like. Then in the top cell of some other column (say, Col B), you can place the following formula:

=ArrayFormula({"Name","Ticker","Exchange","Data";REGEXREPLACE(QUERY({A5:A,{A6:A;""},{A7:A;"";""},{A8:A;"";"";""}},"Select Col2, Col3, Col4, Col1 SKIPPING 4"),"(data)|\[|\]|:|}","")})

This will return the headers and all results; and it should produce those results quite quickly, despite the large number of data elements to be processed.

The four sub-arrays { __,{__},{__},{__} } that form the QUERY have null pseudo-rows added to the end of each except the first (i.e. adding 0, 1, 2 and 3 null rows respectively), in order to keep the four array lengths parallel despite their having different starting points.