Add Options in Google Forms Questions from Google Sheets

Add Options in Google Forms Questions from Google Sheets

by

Hey, friends today you will learn How to Add Options in Google Forms Questions from Google Sheets. Learn how to bulk add options in dropdown lists and multiple-choice questions in Google Form using values from Google Sheets.

Add Options in Google Forms Questions from Google Sheets

An international school is creating a Google Form for pupils to register their information. The form would have a drop-down selection of nations, a multiple-choice question with the names of class teachers, and a checkbox style question where students could select one or more of their preferred subjects.

Adding Bulk Questions in Google Forms

It is easy to create such a form inside Google Forms – here’s a sample form – but there are two issues:

  • There is far too much information to enter and type into the form. For example, the nation drop-down menu alone has a list of 250 countries, and the form editor would have to manually input each choice in the question.
  • The form’s question options may vary over time. In the previous example, some instructors may leave, while others may join, and the drop-down list in the form must be manually changed if there is a change in the personnel.

You might also like our trending code snippets

Auto-Populate Questions in Google Forms with Google Sheets

We can easily automate the process of adding question choices in Google Forms in bulk using, you guessed it, Google Apps Script, just like we do anything else in the world of Google Forms and Google Sheets.

Video

The concept is straightforward. We’ll have a Google Sheet as the data source, with all of the answer options for the various questions in the Google Form.

With a single click, the app will read the data from this Google Sheet and auto-populate the options in the form. You can even set a timer to run every hour, day, or month to dynamically update your form with the most recent data from the spreadsheet.

Add Options in Dropdown Lists & Multiple Choice Questions

Create a Google Spreadsheet and enter the question titles, one per column, in the first row of the document. Next, make a list of all the options or choices that should be offered for each inquiry.

Here’s how your spreadsheet structure would look like:

Google Form Answers in Google sheets

The crucial thing to remember here is that your spreadsheet column titles must perfectly match the Google Form form field labels. The Google Script can add responses in mass to multiple-choice questions with a single answer, drop-down lists, and checkboxes with numerous possibilities.

Bulk Add Question Choices in Google Forms

Open the Google Sheet with the question options, then go to the Tools menu and select Script Editor. In the script editor, replace the default code with the Google Script below. Please watch the video instruction to understand how this code works in greater detail.

/**
 * Auto-populate Question options in Google Forms
 * from values in Google Spreadsheet
 *
 * Written by Amit Agarwal (MIT License)
 *
 **/

const populateGoogleForms = () => {
  const GOOGLE_SHEET_NAME = "<<Put the name of Google sheet here>>";
  const GOOGLE_FORM_ID = "<<Put your Google Form ID here>>";

  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 !!");
};

You must replace the GOOGLE SHEET NAME and GOOGLE FORM ID variables with your own. Go to the Run menu within the Script editor, select the populateGoogleForms function, and it should automatically populate options for all of the provided questions in the Google Form.

Dynamically Update Answers in Google Forms

Whenever you need to update the answer options in Google Forms in the future, simply update the values in the spreadsheet and perform the same auto-populate script from the Script editor.

To make things even easier, add a button to the spreadsheet page that, when clicked, instantly updates the answers in Google Forms.

In Google Sheets, go to the Insert menu, select Drawing, and then select any shape. Overlay text can also be added to the form. After you’ve placed the shape on the spreadsheet canvas, go to the menu, select assign script, and type populateGoogleForms.

Bulk Import Button in Google Sheets

That’s all. You can change your Google Forms directly from Google Sheets by clicking this button. Please keep in mind that the script will not append options; rather, it will replace all current options with those present in your Google Sheet.

Leave a Comment