I have setup many...many Google Forms and editing them or setting up response scripts are a pain when there are multiple required fields. This became extremely tiresome and I created a script to either turn on all "Required" responses or turn them all off. It has been such a time saver!
Please access the free code here: https://payhip.com/b/KUF6G. The Google Doc containing the code will be emailed soon after it is "purchased"...again, it is free.
How does it all work?
- Setup your Google Form and create a Response Spreadsheet
- Open the Response Spreadsheet
- Go to "Extensions - Apps Script"
- If this is your only script, delete out the default script if not, please modify. If you already have a custom menu, please combine the two.
- Copy and paste in this script: https://payhip.com/b/KUF6G
- Click "Run" on the top menu
- This will ask security questions, accept everything...the script is running on your account and only your account will see the data
- Once the security is verified, it will not run the script
- Go back to your response spreadsheet and check out your new custom menu
- Feel free to run it from the custom menu from now on
Limitations: This assumes you only have one Google Form attached to the spreadsheet. If you have more forms, please edit the script to reflect the Form Response sheet's name that is linked to the form (i.e. modifying the form attached to the sheet "Form Responses 3", replace all the "Form Responses" to "Form Responses 3").
What does it all do?
The first function sets up the custom menu and makes sure it appears when the spreadsheet is opened.
The next function, "SetAllAsRequired" turns on the "required" toggle forcing all the people filling out the form to answer all the questions except for the ones with "(optional)" in the title!
This part sets up the function, tells the script that it is using the current spreadsheet and to collect all the sheets.
This starts with an abbreviated version of the for/next loop and it goes through all the sheets looking for the "Form Responses" sheet and then activates it. It finds the URL of the form attached to to the active sheet and opens it in the FormApp so it can then access items within the form.
- if the spreadsheet has more than one Form Response/Form attached, the "Form Responses" needs to be modified to match the exact response sheet (i.e. Form Responses 3).
Finally, it collects up all the Form's items and then loops through them. It checks each one if it has "(optional)" in the title and skips it (continue). Each version of the items must be checked (i.e. Text Item, Paragraph Item, etc.) and if it is the wrong one, the script will error. Using try/catch avoids this problem since it will complete the task and move on to the next item (continue) or it will just ignore the error and try a different version.
note: the above is abbreviated, for the full code head to: https://payhip.com/b/KUF6G
The last function is the almost exactly the same as above except that ".setRequired()" is set to false.
Hopefully, this saves a bit of time.
Comments ()