Google Sheets, Forms and Query for updating data to multiple sheets?

1
votes

I am using a Google Form to collect data into a Google Sheet, I made another sheet and want to fetch data to this new sheet when a new form is submitted.

Used =QUERY('Form responses 1'!A228) to get data from the Form responses 1 sheet and it works good. I dragged the cell and the formula get copied correctly to the lower cells like =QUERY('Form responses 1'!A229) =QUERY('Form responses 1'!A230) and so on.

But now when a form is submitted nothing gets updated in the new sheet, I noticed the cell address has somehow skipped a place and when I do a new drag and drop from top column everything works fine. The cell references are copied correctly so that it should work on new form submit, but as soon a form is submitted the next row formula gets omitted, like the row got deleted in new sheet. i.e. After form submit if the current row formula is =QUERY('Form responses 1'!A228) and net row formula WAS =QUERY('Form responses 1'!A229) it gets changed to =QUERY('Form responses 1'!A330) and no row gets updated in the new sheet.

1
google-sheetsgoogle-formsgoogle-query-language
The question includes google-apps-script and google-sheets-api but the question body doesn't mention any clue about why they are include. Please clarify or remove them. By the other hand Google Forms and Query function are mentioned but the respective tags, google-form and google-query-language aren't included - Rubén
Another unclear thing is why you are using formulas like =QUERY('Form responses 1'!A228) instead of something like ='Form responses 1'!A228 is the last is simpler than the first. - Rubén
Corrected the tags, also, I am not an Google Doc expert and followed an example provided on Youtube which showed using QUERY. - WannabeCoder
Thanks for making the correction. Which video includes such example? - Rubén
That is what I am not able to find again and the issue, I watched it on my mobile and Youtube history is switched off :( . can you pls. tell what would be the correct approach to get this done. - WannabeCoder

1 Answers

2
votes

Try just ={'Form responses 1'!A228:A} . This is a formula that will bring in every cell from A228 to the bottom of the sheet. This way you do not have to keep dragging down formulas.