I have a google form setup to receive submissions into a google sheet. The data that comes through is in wide form, I am attempting to get it in long form.
Incoming Sample:
Timestamp | Name | Question 1a | Response 1r | Score
2/15/2024 | Joe | (Null) | 1r value |1s value
Desired Result:
Timestamp | Name | Question | Response | Score
2/15/2024 | Joe | Q1 | 1r value | 1s value
2/15/2024 Joe Q2 2r value 2s value
However, I can’t repeat the column headers as these only contain the questions without associated values.
Here is the link to the workbook: https://docs.google.com/spreadsheets/d/1GQjLN1JKN16pm8GWm7WXalkYiWHf9KrDT-CWV8fFqn8/edit#gid=1257586209
I’m 90 % there with this formula but I’m not sure how to repeat the column headers as associated questions:
=SORT(QUERY({‘Form Responses’!A:E,’Form Responses’!F:H;’Form Responses’!A:E,’Form Responses’!I:K;’Form Responses’!A:E,’Form Responses’!L:N;’Form Responses’!A:E,’Form Responses’!O:Q}, "select * ",0))
>Solution :
Slightly adjusting your current formula to:
=let(Σ,rows('Form Responses'!A2:A),
query(vstack({'Form Responses'!A2:E,wrapcols('Form Responses'!F1,Σ,'Form Responses'!F1),'Form Responses'!G2:H},{'Form Responses'!A2:E,wrapcols('Form Responses'!I1,Σ,'Form Responses'!I1),'Form Responses'!J2:K},{'Form Responses'!A2:E,wrapcols('Form Responses'!L1,Σ,'Form Responses'!L1),'Form Responses'!M2:N},{'Form Responses'!A2:E,wrapcols('Form Responses'!O1,Σ,'Form Responses'!O1),'Form Responses'!P2:Q}),
"where Col1 is not null order by Col1"))
