I am trying to create a user friendly way to fill out a questionnaire using google forms but import the responses into specific cells within an existing spreadsheet.
So, each child has their own spreadsheet that compiles their data. I have a template that lists each question and has a cell for a numeric response.
- Currently, I have the spreadsheet set up where the parents select their numeric response from a scale: 0, 2, 4, 8, 10 using a dropdown per question. But there are over 100 questions, which is both time consuming on their end and would also give them access to additional data in “hidden sheets” from the clinicians, even if the sheets are protected from editing.
So, I am wanting to create a form that is connected to the client’s spreadsheet that the parents can fill out and their responses will be imported into the respective cells that match up with each question on the data sheet.
- For example, if a parent selects a rating of “2” on the google form in response to a question of the level of independence of their child for question 1, I’d want their response of “2” to be reflected in the child’s respective data sheet in cell T16 within the template I have created.
- Then the numeric response to question 2 would be reflected in cell T17.
However, the responses would not be continuous throughout the entire column (T). The responses would need to be in cells T16:T31, T38:T64, T71:T97, and continues on in blocks.
It would also need to import the child’s name and the submission date in respective cells as well.
An additional parameter: Each child has their own spreadsheet, so the link to the parent form would somehow need to be automatically connected to each child separately whenever a new copy of the data sheet was created. So that way an infinite number of children have their own dataset.
I hope that makes sense! Any help would be much appreciated! Please let me know if additional clarification is needed!