Combine multiple columns on google sheet and create a new header for that combined column

I have a google sheet that is connected to a google form. so when someone answers that form the data goes as follows.,

Original Data Sheet

I would like a script that will help me create a master data, the idea is, column A remains and then combine columns B~P into one column, and create a custom header for it and then create a new column that will provide which branch is it from and then Q and R after that. it should end up like this.

Expected Result

is there a way where everytime someone answers the google form, the script automatically update it in that format? thanks.

I tried searching the net for scripts that could do that but the closest one i found keeps getting an error.

Solution

That can be done with a plain vanilla spreadsheet formula without resorting to scripting. To unpivot the data and rearrange the columns, choose Insert > Sheet and put this formula in cell A1 of the new sheet:

=let( 
  unpivot_, lambda(data, numFixedCols, numColsPerGroup, let(k,n(numFixedCols),d,if(k,data,hstack(sequence(rows(data)),data)),f,if(k,k,1),g,numColsPerGroup,s,lambda(r,c,h,w,chooserows(choosecols(d,sequence(1,w,c)),sequence(h,1,r))),h,hstack(s(1,1,1,f),"Branch","Name"),i,sequence(1,(columns(d)-f)/g,f+1,g),a,reduce(h,sequence(rows(d)-1,1,2),lambda(a,r,let(x,s(r,1,1,f),b,reduce(tocol(æ,2),i,lambda(y,c,let(z,s(r,c,1,g),if(""=+z,y,vstack(y,hstack(x,s(1,c,1,1),z)))))),vstack(a,b)))),if(k,a,choosecols(a,sequence(1,columns(a)-1,2))))), 

  data, filter('Form Responses 1'!A1:R, len('Form Responses 1'!A1:A)), 
  head, choosecols(data, 1, -2, -1), 
  body, choosecols(data, sequence(1, columns(B:P), 2)), 
  table, unpivot_(hstack(head, body), columns(head), 1), 
  choosecols(table, 1, 5, 4, 2, 3) 
)

The results will update automatically as new form responses come in.

See let(), lambda(), filter(), choosecols(), sequence(), columns() and hstack().