How to make a flat table from google forms responses?

Question

With a standard response to a form with a checkbox field, I get this. (screenshots below):

how to get a flat form (without commas)?

Timestamp	name	col	var
8/5/2023	some name	col1	var1
8/5/2023	some name	col1	var2
8/5/2023	some name	col1	var3
8/5/2023	some name	col2	var2
8/5/2023	some name	col2	var3
8/5/2023	some name	col3	var1
8/5/2023	some name	col3	var3

I tried the "flatten" function but didn't get the desired result

link to the example table https://docs.google.com/spreadsheets/d/1Wse3lvmM_lUcvh2X_e9h8xBPQwbuCzuyk1OBJQLL_ss/edit?usp=sharing

In order for someone to help you with code, please provide the data and code you have tried. How did you use `flatten` for that matter? Do not expect anyone to make an effort if you don't make yours first. — user776686, Aug 05 '23 at 20:55
Welcome to [Stack Overflow](https://stackoverflow.com/tour). Please [edit](https://stackoverflow.com/posts/76843484/edit) your question and insert a [table](https://stackoverflow.com/editing-help#tables) of sample data together with another table that shows your _manually entered_ desired results. Also consider sharing a publicly editable sample spreadsheet. There is a [blank sheet maker](https://docs.google.com/forms/d/e/1FAIpQLSeprZS3Al0n7JiVQIEiCi_Ad9FRXbpgB7x1-Wq6iAfdmVbWiA/viewform) that lets you share safely. — doubleunary, Aug 05 '23 at 22:19
link to the example table https://docs.google.com/spreadsheets/d/1Wse3lvmM_lUcvh2X_e9h8xBPQwbuCzuyk1OBJQLL_ss/edit?usp=sharing — SoursXond, Aug 11 '23 at 14:06

doubleunary · Accepted Answer · 2023-08-11T20:27:21.893

Use the unpivot pattern, like this:

=let( 
  unpivot_,  lambda(d,f,g,let(r_,lambda(t,o,h,chooserows(t,sequence(h,1,o))),c_,lambda(t,o,w,choosecols(t,sequence(1,w,o))),s_,lambda(r,c,h,w,r_(c_(d,c,w),r,h)),l_,lambda(c,s_(1,c,1,1)),hr,hstack(s_(1,1,1,f),"Label","Q1"),i,sequence(1,(columns(d)-f)/g,f+1,g),n,tocol(æ,2),reduce(hr,sequence(rows(d)-1,1,2),lambda(z,r,let(e,s_(r,1,1,f),s,reduce(n,i,lambda(t,c,let(x,s_(r,c,1,g),if(single(x)="",t,vstack(t,hstack(e,l_(c),x)))))),vstack(z,s)))))), 

  split_, lambda(label, col, vstack(iferror(bycol(split(col, ", "), lambda(col, vstack(iferror(regexextract(label, "\[(.+)\]")), col)))))), 

  data, filter( 
    hstack( 
      'Form Responses 1'!A1:A, 
      'Form Responses 1'!E1:E, 
      split_('Form Responses 1'!B1, 'Form Responses 1'!B2:B), 
      split_('Form Responses 1'!C1, 'Form Responses 1'!C2:C), 
      split_('Form Responses 1'!D1, 'Form Responses 1'!D2:D) 
    ), 
    len('Form Responses 1'!A1:A) 
  ), 

  unpivot_(data, 2, 1) 
)

With form response data like this:

Timestamp	Q1 [col1]	Q1 [col2]	Q1 [col3]	Name
8/6/2023 9:34:20	b1, b2, b3	c1, c2	d1, d2	Alice
8/7/2023 11:02:49	b1, b4		d2, d3, d4	Bob

...the results look like this:

Timestamp	Name	Label	Q1
8/6/2023 9:34:20	Alice	col1	b1
8/6/2023 9:34:20	Alice	col1	b2
8/6/2023 9:34:20	Alice	col1	b3
8/6/2023 9:34:20	Alice	col2	c1
8/6/2023 9:34:20	Alice	col2	c2
8/6/2023 9:34:20	Alice	col3	d1
8/6/2023 9:34:20	Alice	col3	d2
8/7/2023 11:02:49	Bob	col1	b1
8/7/2023 11:02:49	Bob	col1	b4
8/7/2023 11:02:49	Bob	col3	d2
8/7/2023 11:02:49	Bob	col3	d3
8/7/2023 11:02:49	Bob	col3	d4

See the sample spreadsheet.

thanks for the solution, this is really almost what I need. However, in your example, the column names are lost. Look at the example response — SoursXond, Aug 11 '23 at 13:57

How to make a flat table from google forms responses?

1 Answers1