Use a Google Forms to update existing Google Sheets

When submitted the form would find the correct sheet based off (1), then find the correct row based off (2), then find the correct column based off (3), then copy (4) and (5) into the corresponding cells.

Is it possible to do this? Or would I need to create a separate form for each division?

I don't have any familiarity with Google scripting, although I am experienced with C++ and have a basic working knowledge of scripting in Python.

1 Answer 1

This will be difficult to implement (but not impossible) with stock Google Forms. You can't use Google Apps Script to manipulate the display of a form in response to user actions like you can with Javascript on a form you've coded from scratch.

Google Apps Script can generate and manipulate a form in the same way you can in the Form Editor, and it can be triggered when the Form is submitted to take action with the response, but it can not interact with the user directly.

To achieve what you want you have a few options.

You could set up a multi-page Google Form with logic branches to get the forms behaviour you want, then use an Apps Script triggered onFormSubmit() to take care of moving data to the appropriate locations.

Or you can skip using stock Google Forms altogether and instead present the form using a Google Apps Script published as a web-app, which gives you full control over the form (HTML, CSS & Javascript) displayed to the user, but you do not get to leverage any of the Google Form features. You could render the form with doGet() and process the submission with doPost().

A third approach would be a hybrid of the two, set up several individual Google Forms, and use an Apps Script published as a Web App to display the first drop down and direct the user to the appropriate form. You could then have scripts triggered onFormSubmit() of each of the Google Forms to handle moving your data around.