Google Forms is a cloud-based survey solution that comes with real-time collaboration and powerful tools to customize form questions. The information and data that you can collect from Google Forms are astounding! It has become quite versatile that its use in education keeps growing.
Let’s understand how to extend Google Form questions with the example of an international school that built a form where students can register their details. It had a drop-down list of countries, the name of teachers as multiple-choice questions, and a checkbox-style question where students can tick one or more boxes to mark their favorite subjects.
How to add bulk questions in Google Forms?
Creating such a form inside Google Forms is easy and simple but there are two issues that you might face:
- There’s too much data to enter and type in the form. There will be a lot of work for the form editor to manually type each choice in the question. Like in the above case, the country drop-down menu alone has roughly 200 countries to include.
- The question choices in the form may change with time. Considering the example above, some teachers may move out and some new teachers may join and the list of teachers’ names has to be updated by the form editor manually.
Integrate Questions in Google Forms with Google Sheets
In the Google-dominated world, we can easily automate adding bulk questions in Google Forms with the help of Google Scripts.
The design is simple and easy to understand. A Google Sheet, with all the answer choices for various questions in the Google Form, will act as the data source. The app will read the data from the data source (Google Sheet) to auto-populate the choices in the form with one click. There’s a way to dynamically update the form by creating a time trigger that runs every hour, day, or month and uses the most current data available in your spreadsheet.
How to Add options in Dropdown Lists and Multiple Choice Questions?
Create a Google Spreadsheet by adding the question titles in the first row, one column each. Then, write all the options or choices available for each question. This is how your spreadsheet would look like:
Note that the column headings in the spreadsheet should exactly match the form fields in the Google Form. The Google Script can add answers in bulk as multiple-choice questions with a single answer, drop-down lists, and checkbox with multiple options.
How to Bulk Add Question Choices in Google Forms?
First, open the sheet that consists of all the question choices. Then, go to the Tools menu and choose Script Editor. Replace the default code in the script editor with the Google Script below.
| /** * Auto-populate Question options in Google Forms * from values in Google Spreadsheet * * Written by Amit Agarwal (MIT License) * **/ const populateGoogleForms = () => { const GOOGLE_SHEET_NAME = “<>”; const GOOGLE_FORM_ID = “<>”; const ss = SpreadsheetApp.getActiveSpreadsheet(); const [header, …data] = ss .getSheetByName(GOOGLE_SHEET_NAME) .getDataRange() .getDisplayValues(); const choices = {}; header.forEach((title, i) => { choices[title] = data.map((d) => d[i]).filter((e) => e); }); FormApp.openById(GOOGLE_FORM_ID) .getItems() .map((item) => ({ item, values: choices[item.getTitle()], })) .filter(({ values }) => values) .forEach(({ item, values }) => { switch (item.getType()) { case FormApp.ItemType.CHECKBOX: item.asCheckboxItem().setChoiceValues(values); break; case FormApp.ItemType.LIST: item.asListItem().setChoiceValues(values); break; case FormApp.ItemType.MULTIPLE_CHOICE: item.asMultipleChoiceItem().setChoiceValues(values); break; default: // ignore item } }); ss.toast(“Google Form Updated !!”); }; |
Note to replace the GOOGLE_SHEET_NAME and the GOOGLE_FORM_ID with your own values. Now go to the Run menu inside the Script editor, then choose the ‘populateGoogleForms’ function and it will instantly add the choices for all the specified questions in the Google Form if run correctly.
Dynamically Update Answers in Google Forms
You can easily update the answer choices in the Google Form, whenever needed, by simply updating the value inside the spreadsheets and then running the same auto-populate function from the Script Editor.
In order to make it even simpler, you can also add a button on the spreadsheet that will automatically update the answers in Google Forms when clicked.
Follow the steps to add the button:
- Go to the Insert menu in the Google Sheets.
- Choose the Drawing submenu and then pick any shape of your choice.
- Add overlay text to the shape. Add something that will suggest you the use of the button.
- Once the shape is placed in the sheets, choose Assign Script from the menu (click on the top right corner of the button) and type ‘populateGoogleForms’.
And you are all done! You can now click on the button to Update your Google Forms right within Google Sheets. Remember, the Script will not append the choices but will replace all existing choices with the ones available in your Google Sheet.
