About #
Streamlines the task of managing and processing Google Forms responses by efficiently updating form items based on reference lists in Google Sheets, validating submitted forms, and sending email notifications for invalid submissions.
While Google Forms allows validation of individual items, it lacks the ability to validate across multiple items. For instance, it's currently impossible to hide certain dropdown options based on a user's previous responses. This tool addresses this limitation by checking responses immediately after submission. If a user selects an invalid combination of options across multiple items, a notification email is automatically sent to them, requesting resubmission.
- A Google account with access to Google Sheets.
- A foundational understanding of Google Sheets, Google Form and Google Apps Script.
You can access the sample Google Form and Google Sheet, complete with response submissions and embedded scripts, via the links provided below:
- Sample Google Form: Access here
- Sample Google Sheet: Access here
Usage #
Tool 1: Updating Google Form Items based on Data on Google Sheet:
- Pre-define Information in Sheets: Input necessary information in advance in the following sheets:
course-lineupsheet: Enter course lineup for each program.instructor-lineupsheet: Enter instructor lineup for each program.
Figure: Input Course Line-up to Be Reflected on Google Form - Execute Functions and Update Google Form: Use the
Update Course LineupandUpdate Instructor Lineupfrom the Custom Menu to update item options in Google Form. Verify if the target items in the Google Form are updated successfully.- Update Course Lineup: Synchronize each program's course lineup from the Spreadsheet to the Google Form.
- Update Instructor Lineup: Synchronize each program's instructor lineup from the Spreadsheet to the Google Form.
Figure: Custom Menu 
Figure: Updated Google Form Items
Tool 2: Automatic Notification Email along with Invalid Choices in Form Submission:
- Set Trigger: Navigate to Triggers in Apps Script and set a trigger for the
onFormSubmitfunction.
Figure: Navigate to Apps Script 
Figure: Trigger Setup - Test Form Submission: Ensure that the notification email is sent to respondents when a respondent chooses the same instructor twice.
Figure: Email Notification Example 
Figure: Email Notification Example
Please note that your use of this website is subject to the Terms and Conditions.
You can find the source code for this tool on Github. Click HERE .
Last updated: May 5, 2024
Form Manage Simplifier
