Autofill Options in Google Forms using Google Apps Script

September 06, 2021
Autofill Options in Google Forms using Google Apps Script
Table of Contents
Table of Contents

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:

  1. Name of the Student
  2. The ID of the student
  3. The event that he/she attended
  4. 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 = data.getRange(startRowIndex, startColumnIndex, amountOfRowsToUse, lastColumn).getValues()[0];
    const startColIndex = 2
    const lastRowIndex = data.getLastRow()-1
    const lastColumnIndex = 1
    labels.forEach((label, i) => {
        const startRowIndex = i +1
        const options = data.getRange(startColIndex, startRowIndex, lastRowIndex, lastColumnIndex).getValues()
        updateDropdownTitle(label,options)
})


}


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();
    updateDropdown(itemID,values);
}

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

}


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 = data.getRange(startRowIndex,startColumnIndex,      amountOfRowsToUse, lastColumn).getValues()[0];
     
    const startColumnIndex = 2
    const lastRowIndex = data.getLastRow()-1
    const lastColumnIndex = 1
    labels.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();
      updateDropdown(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 updateDropdown 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.

Step4: Check the Output

Our code is complete. Select the correct function (main) as shown below and run the program.

On successful execution, the script will take the options from the Google Sheet and put them in the Google Form.

Summary

We saw how you can Autofill Options in Google Forms using Google Apps Script.

If you prefer to watch a video instead of reading, we've got you covered:

I hope you have understood how to Autofill options in Google Forms using Google Apps Script. You can send your feedback to aryanirani123@gmail.com.





Great! Next, complete checkout for full access to Form Publisher Blog
Welcome back! You've successfully signed in
You've successfully subscribed to Form Publisher Blog
Success! Your account is fully activated, you now have access to all content
Success! Your billing info has been updated
Your billing was not updated