Google Forms defaults to putting all selected tickbox responses for a question into one cell. What I would like, for each tickbox question, is to have the google sheet contain a set of columns for all tickbox options, for a given quesrtion, in which that answer could be stored. I want this because I think data would be easier to summarise if stored this way.
Is it possible to change how Google Forms stores responses in Google Sheets?
If not, can anyone recommend a good approach to going from a column of cells each possibly containing multiple tickbox responses, to an expansion of that, such that each tickbox option lives in a column and it is therefore easy to summarise how many responses are given for each tickbox option?
I am looking for a general solution not an specific one, as there are multiple questions I would want to apply this process to. I am thinking I might need to use an AppScript query to extract the possible response options, then perhaps iterate over it, but I'm not sure how to start out approaching this.
But maybe there's an even simpler way. I can think of a general database type approach here but not sure how to map this to JavaScript. Or is there some kind of R style operator in Sheets that does this automagically?
What would be good way of approaching this? Thanks.
P.s. I asked this same Q yesterday on Google Docs Editors Help but nobody answered, so am trying here.
easy to summarise how many responses are given for each tickbox option
This formula gives you the number of times each checkbox was ticked, for a given question. In this case, it is for the first question, the answers to which are stored in column B, on a sheet called Form Responses 1:
=QUERY( FLATTEN( ARRAYFORMULA( SPLIT( FILTER('Form Responses 1'!B2:B, 'Form Responses 1'!B2:B<>""), ", ", TRUE, TRUE) ) ), "SELECT Col1, COUNT(Col1) WHERE Col1<>'' GROUP BY Col1 LABEL(Count(Col1)) ''", -1)