Autofill Options in Google Forms using Google Apps Script

5 min readSep 7, 2021

Originally published on FormPublisher.

Recently the College I study at had a Technical Fest that had multiple technical events. At the end of the Technical fest, we had to send out a feedback form to record their experience and suggestions on the various events that were conducted. During event registrations, we had collected the data on attendees, the events and the speakers which were stored in Google Sheets. In order to not ask the same questions twice, and reuse the data that had already been collected, I decided to create a Feedback form using Google Apps Script to autofill the options in the Google Form using the Google Sheet containing the student data.

Step1: Sample Google Form

The Google Form Contains the following questions:

Name of the Student
The ID of the student
The event that he/she attended
Feedback on the event

Step2: Add the questions to the Google Sheet

Now that we have added the questions to the Google Form, it’s time to add the questions to the Google Sheet. Here you need to make sure that you copy the same question.

If you prefer working with the Google Sheet click here. After adding the questions you need to add the options under each question as shown below.

Step3: Write the Automation Script

While you are in the Sheet, launch the Apps Script Editor.

To do that follow these steps:

(1) Click on the Tools button and click on the Script Editor.

(2) This brings up the Script Editor.

We have reached the Script Editor. Let’s Code.

const sheet_ID = "1zNrOZZbHIrO6L15ntStoBkBHfkAe01lpP3rGXwRFBp0";
const form_ID = "1avhXYR_BprUM3tg-Jy5IEVzQZfjBar-Jqezbxkc4dh8";
const data = SpreadsheetApp.openById(sheet_ID).getSheetByName("Sheet1");
const form = FormApp.openById(form_ID);function main(){
              const startRowIndex = 1 
              const startColumnIndex = 1
              const amountOfRowsToUse = 1
             const lastColumn = data.getLastColumn()
       const labels = const labels = data.getRange(startRowIndex, startColumnIndex, amountOfRowsToUse, lastColumn).getValues()[0];
      
const startColumnIndex = 2
const lastRowIndex = data.getLastRow()-1
const lastColumnIndex = 1labels.forEach((label, i) => {
  const startRowIndex = i +1
  const options = data.getRange(startColumnIndex, startRowIndex, lastRowIndex, lastColumnIndex).getValues()
  updateDropdownTitle(label,options)
})

Here we have first declared the Google sheet and Google Form ID, followed by opening them using the openById function. Next, we are going to create our first function where we are going to get the questions followed by the options from the Google Sheet. To get the questions from the sheet, we will use the getRange function in which we will be passing starting from the first column and first row, using the getRange function, followed by using the getValues function and store it in a variable label.

const options = data.getRange(startColumnIndex, startRowIndex, lastRowIndex, lastColumnIndex).getValues()

Since there will be multiple options for each question, we will have to go through each of them. Using the forEach function, we are going to go through each of the options for each question. In the forEach function, we are going to pass the labels variable and the variable i to iterate through all the options in the sheet. Next, we are going to use the getRange function to extract the options from the data variable that contains the questions and options from the Google Sheet. There can be N number of options in the sheet, so we are going to use the getLastRow function to get all the options from the Google Sheet.

updateDropdownTitle(label,options)

In the end, we have called the function updateDropdownTitle and passed the label and options in the function.

function updateDropdownTitle(title,values){
      const items = form.getItems();
      const titles = items.map(item =>  item.getTitle())
      const pos = titles.indexOf(title);
      const item = items[pos]; 
      const itemID = item.getId();
      update_dropdown(itemID,values);
   }

To add the options for each question we need to get the questions from the Google Form. To do that we are going to use the getItems function, which will get all the data from the Form and store it in an array. To get each question by the id we are going to map through each of them and get each of the question titles.

To put in the options, we need to get the exact position for each question. To do that we are going to create a new variable pos that gets the position of the question using the indexOf function and passing the titles that we previously got.

const pos = titles.indexOf(title);
const item = items[pos];
const itemID = item.getId();
updateDropdown(itemID,values);

Now that we have got the positions for the questions, we are going to get the individual question. To do that we are creating a new variable item and pass in the items variable that we created and pass the pos variable. Finally, to set the values we need to get the exact id of each question. To get the id for each question, we are going to use the getId function and pass the item variable that contains each question.

updateDropdown(itemID,values);

In the end, we have called the function update_dropdown and passed the id for each question followed by the options.

function updateDropdown(id,values){
     const item = form.getItemById(id);
     item.asMultipleChoiceItem().setChoiceValues(values);
}

This is the most important function in the code. This function is now going to put in the options for each question in the Google Form. In the getItembyId, we have passed the id of the question as a parameter and then for each question, we are going to set the choice values and pass in the values variable that contains the options for each question.