I work in manufacturing. I have created a Google form for our leadership safety audits which requires unique information (name(s), area being audited, and date) and also includes 25 multiple choice safety-related questions where if yes is answered, conditions were met, no if they were not met, and N/A if they were not observed.
When that form is submitted, it grabs every question that was answered and creates a new form (with the questions as the page title) that requires the auditor to classify the type of condition they saw as well as give some detail about the observation so it can be followed up on by the area owner. It is this secondary, form that I want to use for data collection to measure safety performance by area/area owner.
The problem I am having is I am not able to link multiple forms to the same sheet ID, only spreadsheet ID, thus making my data not easily accessible due to it having to be tied to a different sheet for every form submission. I want to somehow get every 'secondary form' submission to go to the same response sheet so I can manipulate the data in a way that it can be useful.
I have spent many hours trying to find a backdoor to Google's logic that you can only link forms to the same spreadsheet ID and not sheet ID. Does anyone know how I can make this happen?
Best Answer
As you already found, it's not possible to link several forms to one sheet and the same sheet.
Usually, Google Apps Script documentation says what it's possible to do, not what doesn't, and this is the case, the related method doesn't mention any limitation on this, so we should recur to the experience.
If each form shares the same structure we could use the literal array notation t to join each sheet, example
Note that the range on the second FILTER starts on row 2 in order to avoid the repetition of column headers.
Related Q&A