Google Form - Spreadsheet and data insertion

1
votes

I'm facing a very annoying problem with google forms/sheets. It is hard to explain so I am going to use multiple screenshots.

Context
I have created a Google Form. Participants can grade certain aspects of a project with it. All the responses are sent to a Google Spreadsheet.
In this spreadsheet, I perform various manipulations with the data.

The problem
The first thing I do is collect the responses in a different sheet called data: enter image description here

Form Responses 1 is the sheet where the responses go, directly from Google Form. Here we can clearly see that the next response will be gathered from the B15 cell.
As we can see the B15 cell is empty on the Form Responses 1: enter image description here

I will now add a new response through the form.
It is added to Form Responses 1:
enter image description here

However, when I check if the cells from this new row have been added to my data sheet this is what is displayed:
enter image description here

The cell value in the formula has switched from B15 to B16, skipping B15 entirely (the formula in B14 is ='Form Responses 1'!B14). This is because Google Forms inserts a new row for each new response in the spreadsheet, instead of populating existing ones.

So for each new response I need to manually rewrite the formula that gathers the data from Form Responses 1, like this:

enter image description here

The objective of this project is to automate reports, calculate average grades, etc, which is not being done right now.

Is there anyway I can stop this from happening? Or is there another way I could gather the data from Form Responses 1 into another sheet that I am missing?

I hope you understood my problem, it was hard to explain and I've never seen it before.

Thank you.

1
google-sheetsgoogle-forms

1 Answers

3
votes

Rather than use a hard-coded cell, use Google Sheet's expanding array functions. These array functions will not be foiled by the "row insert" behavior of the form submission.

Particularly, using ='Form Responses 1'!A:Z (change Z to suit) will ensure that whatever data is on the response sheet, in the columns A:Z, will be brought into the calling sheet.

If you prefer a little more control, or order over the responses, consider using QUERY(), e.g. data!A1: =QUERY('Form Responses 1'!A:Z, "select A, B, C, H, Z order by B asc", 1)