Separate and stack values from comma separated list

Question

I have a google sheet that is looking at another sheet in the same workbook with cell values that are a string list populated by a pandas dataframe generated from a REST API json response.

I need to visualize this data so I'm thinking need to parse the comma separated values from the list, then append/stack them on top of one another so that I can reference the column in a pie chart in Google Data Studio.

I parsed the values using the SPLIT function (column C) but now stuck on how to append them into one column stacked on top of each other.

There could also be a totally different approach here that I'm not thinking of. Any tips on how to build this out so it's in an array method (updates as the reference sheet is updated daily) that would be awesome - thanks all.

Alternatively if there's a way to create a field at the Google Data Studio report level that would work well too.

player0 player0 · Accepted Answer · 2021-02-08T23:32:42

use:

=ARRAYFORMULA(QUERY(FLATTEN(IFERROR(SPLIT(REGEXREPLACE(Sheet2!A2:A, 
 "\[""|""\]|"",""|\['|'\]|','", "♦"), "♦"))), 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  label count(Col1)''"))

Separate and stack values from comma separated list

1 Answers