Google Forms responses create new row in spreadsheet, offsetting my query

Question

I have a template form and a template spreadsheet. They are un-linked by default. I have a script that copies them both, sends them to a new clients folder in the drive and links them based on the clients name. The idea is for the client to login to a website account and make submissions to the form.

I used *=QUERY('Form Responses'!B2:H,"select "), inside the spreadsheet to collect the data from Sheet #1 (responses) to Sheet #2. I had to make a script that auto changes the name of Sheet #1 to stay static. However I am running into a few problems.

When the Responses generate they push Row 2 down and thereby my query adjusts to B3. Now I either need to get around that by somehow setting the formula in a script, so it doesn't adjust or I need a script that will make an auto post/response to the form with "0" as the value and simply set the formula to B3. Either way is fine for me, because it doesn't affect the calculations if the response that gets stored in B2 is "0", but I need it automatic.

Any suggestions?

Answer 1

Try changing your formula to =QUERY('Form Responses'!$B$2:H,"select ").

The "$" changes make the cell in the formula absolute instead of relative and should stop the changing of the cell in the formula.

Answer 2

The "usual procedure" is to submit the first response and then add the formula but in a "template scenario" this could not be convenient. An alternative is to use a formula to join the column header and the formula of the second row. I.E. Add the following formula to the cell on the first row:

={"Column Header";QUERY('Form Responses'!B2:H,"select B")}