Google Sheets – Intermediate, Arrays, Form Data
Picture this. You have set up your Google Sheet and attached your Google Form data to it so you might get a tab that looks a little something like this:
You probably don’t want to mess with this tab because the Form is still live.
Instead, you decide to create a new Google Sheet tab that you want to automatically transfer all the data into, including the current form response data and any new form responses you might get.
The Common Mistake
A common, though mistaken, approach to this is to do a cell-by-cell transfer of data. For example, we would grab the first data cell of our “Form responses 1” Sheet tab and in a new sheet tab cell we would write:
|
1 |
'Form responses 1'!A2 |
We would then drag that cell across to the right to cover all the columns. Then all the way down to the bottom of the page to cover the current responses and any new responses added.
That might look a little like this:
The problem
This looks like it might work, right? Let’s test it out by adding in a new form response. For me, it will be the 6th response and will appear on row 7 of the ‘Form Responses 1’ sheet tab.