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

google sheetsimportdatajson

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.

Imported JSON Data

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?

Best Answer

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().

Related Topic