I'm looking for a way to edit a large Google spreadsheet collectively via a form for each row in the spreadsheet. (I don't want to simply give access to the spreadsheet to everyone because some fields have multiple lines and are a bit hard to edit directly inside of the spreadsheet.)
I've managed to create pre-filled forms for each row with Is it possible to 'prefill' a google form using data from a google spreadsheet? and How to prefill Google form checkboxes?. (and I then can create a view as an Awesome-table with a button for each row which opens the pre-filled form corresponding to the row).
But the responses do not update the existing rows but add a new row (at row 2 strangely enough, protecting the headers, but messing up the Awesome-table that needs filter information in row 2).
How can I make a pre-filled form that writes at a specific row of a sheet? If possible the same row that the pre-fill was created from. Maybe with some script that is triggered by the submission of the form and which replaces the standard recording? Or is there a completely other way to edit Google spreadsheets by means of forms?
Update:
This was much harder than expected and I finally got back to it. Here is what I ended up hacking together: A spreadsheet document document with three sheets:
- main: The main data
- temp: only used for the automatically added entries after submission
- links: keeping the editing links and response ids corresponding to each line of the main sheet
Then
- A function that can be triggered from the menus: creates a prefilled answer per line of main, submits, and puts the url and id in the links sheet. These links are distributed to different people who are asked to update the prefilled information of their row by means of the form.
- A onFormSubmit function attached to the form that
- searches for the line with the right response id in links
- updates the corresponding line in main
So the temp sheet is not used at all, all entries are handled manually. What I haven't managed to do is to allow a manual change in the main table and to resubmit the data under the same id. (I don't want to make a completely new response because the links are already distributed.)
