I'm trying to implement a performance evaluation system using google sheets + forms. Overall, what I need to do is generate a form for each user, according to a template. After the user has submitted an answer, I need to edit the form, in order to remove one of the options, avoiding duplicate submissions by my coworkers.
In order to achieve that, I intended to create the forms and link them to the same Spreadsheet sheet - and, as far as I could understand, that is not possible. Therefore, having my forms submitting data to the same place is not feasible using the standard flow, since I'd need to create about 100 tabs in my spreadsheet, each one containing around 7, 8 rows (which would not be entered at once, but gradually, in each spreadsheet).
Anyway, my last idea was not to link any of the forms to my spreadsheet, but rather write a simple script in the template form to write that data in my Spreadsheet. That script is bound to the forms, and, therefore, when I copy the template and create a new form, the script gets copied as well. That worked finely, but, for every new copy, I need to manually grant permissions for the script (it needs to write on my responses Spreadsheet).
The most simple solution for me would be able to write the results of all forms in the same spreadsheet (being able to act on each of the forms submissions separately), but I'm afraid that won't be possible. Therefore, is it at all possible to copy a bound to file project and its permissions altogether, so that it won't require manual granting?
Have you considered using a Google web app, and making your own interface instead of a Google Form? https://developers.google.com/apps-script/guides/web