CODEWITHSUNDEEP
google-sheetsgoogle-forms
user23414048
user23414048

Reputation: 3

Repeat column headers in google sheets formula / query

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

Upvotes: 0

Views: 96

Answers (1)

rockinfreakshow
rockinfreakshow

Reputation: 30122

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"))

enter image description here

Upvotes: 0

Related Questions