Google Sheets: Transpose Form answers from the same row to rows/columns, keeping the first 2 columns fixed and the other columns variable?

Question

I have a Google Form whereby a customer can make multiple requests in one go, rather than using the "Submit another response" function, as this is preferential time wise.

Therefore the data formats across 1 row only as follows into the linked Google Sheet.

Timestamp	Email Address	Question 1a	Question 1b	Question 1c	Question 2a	Question 2b	Question 2c
01	Abc@.com	1a	1b	1c	2a	2b	2c
02	Abc@.com	1a	1b	1c
03	Xyz@.com	1a	1b	1c	2a	2b	2c

I am trying to work out a method, ideally using Google Apps Script or otherwise using formulas, to transpose the data, repeating the timestamp and email address but otherwise "chunking" out the submissions in columns 3, 4, 5 and then 6, 7, 8 into separate rows, for example.

Timestamp	Email Address	Attribute	Attribute	Attribute
01	Abc@.com	1a	1b	1c
01	Abc@.com	2a	2a	2c
02	Abc@.com	1a	1b	1c
03	Xyz@.com	1a	1b	1c
03	Xyz@.com	2a	2b	2c

It must iterate down every row so therefore any new submissions must be appended to the existing list in the Target Sheet, or the whole lot can be redone each day on a timed trigger to transpose the list out of hours.

Example sheet

https://docs.google.com/spreadsheets/d/1sMMdl26HS2Bfsb4iYLfOQt6ydMW5qVKXyy3UJ-tEdaU/edit?usp=sharing

We have an existing VBA solution but need to transfer to Cloud based using Google Sheets.

Any support or ideas would be welcome. Thanks.

Answer 1

If you have only 2 sets of answers

=sort(query({'Source Data'!A:B,'Source Data'!C:H;'Source Data'!A:B,'Source Data'!J:O},"select * where Col3 is not null",0))

for more, add 'Source Data'!A:B,'Source Data'!xxx:yyy