i have a google sheet that is connected to a google form, there are multiple columns that i would like to combine to create a master data. could you help me create a script for that? thank you.
so the google sheet looks like this...
and i'm trying to make it to be like this. expected result
Thank you in advance.
i tried searching the net for scripts that could do that but the closest one i found keeps getting an error.
so i basically i want the script to run everytime theres a new answer from the google form. and just add the new data in that format into the existing sheet. Thank you in advance.
add detail: made a mistake earlier about the kind of output i wanted. heres the default answer sheet once someone answered the google form. original sheet and then heres the output i was trying to come up with. expected result
Use choosecols(), hstack(), byrow(), torow() and filter(). Choose Insert > Sheet and put this formula in cell A1 of the new sheet:
=let(
data, 'Form Responses 1'!A1:Q,
head, choosecols(data, 1),
body, choosecols(data, sequence(1, columns(B:P), 2)),
tail, choosecols(data, -2, -1),
table, hstack(
head,
byrow(body, lambda(row, single(torow(row, 1)))),
tail
),
filter(table, len(head))
)
See let(), choosecols(), sequence(), hstack(), byrow(), torow() and filter().