Google-sheets – Google Sheets ARRAYFORMULA not copying form responses

google sheets

So I have a form setup which sends data into a sheet labeled 'Form Responses'.

I have another sheet that mimics the column layout of 'Form Responses' but has formatting applied to it, (called 'Feedback') – Both in the same spreadsheet file.

In 'Feedback', I have the following formula to copy the form responses into the feedback sheet:

=ARRAYFORMULA('Form Responses'!$A2:$G2) // 7 columns

Upon keying in said formula, and filling in all the 1000 available rows, existing data is copied over and formatted neatly. Yay.

However, when new data is submitted via the form into 'Form Responses', the 'Feedback' sheet does not update. Instead, it appears that the rows are just… missing. 

 Cell | Formula
 A9   | =ARRAYFORMULA('Form Responses'!$A9:$G9)
 A10  | =ARRAYFORMULA('Form Responses'!$A10:$G10) 
 A11  | =ARRAYFORMULA('Form Responses'!$A11:$G11) // Last row with data
 A12  | =ARRAYFORMULA('Form Responses'!$A13:$G13) // A12 formula missing

The formula for A12:G12 seems to have disappeared. This happens with all new form responses – If I have 3 new responses, it'll go from A11 to A15. And the 'Feedback' sheet won't update.

What's happening? Where (and why) is my formula usage wrong? Is there a different way altogether to dynamically copy form responses into a different sheet, and not just one-off?

Best Answer

I would suggest this if you are simply copying to a new sheet.

=query('Form Responses'!A:G, "SELECT * ")

Syntax QUERY(data, query, [headers])

The 'Form Responses'!A:G, is the data range of the source sheet that you would like to draw data from.

The "Select * ", is the query and will select everything within the range defined previously in the same order.

I did not know this when I first started using Query so I will share. If you want to select specific columns but reorder them plus leave other columns out, you can do this by getting specific with your SELECT

ie. You have Data in Columns A:F but you want to leave D and F out and put E before C in your new sheet, You can use this "SELECT A,B,E,C"

You can read more about it here its quite powerful.

You can format the new sheet to your hearts delight and it will always update when you enter new data. As long as you have data in the range you've defined in the query.

Related Topic