1

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?

2
  • The short answer is YES, there is a way. Is this something you need to import once and then keep a static copy of it? Or is this something that needs to be updated periodically?
    – Erik Tyler
    May 24, 2022 at 2:11
  • The URL the formula points to is updated regularly.
    – Catalyx
    May 24, 2022 at 10:31

2 Answers 2

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

2
  • Brilliant. Could you please explain, or point to where to find info for, the regular expression used "(\d+) (.+?)[ /]([-A-Z]+) ?([\w]+?| )(?:]]})?$"?
    – Catalyx
    May 24, 2022 at 11:39
  • To learn the exact regular expression syntax used by Google Sheets, see RE2. May 24, 2022 at 11:43
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.