Did you ever wonder if there was a way to autofill Google Forms from Google Sheets? Sometimes, you already have some respondent data in a Google Sheet and want to make it easier for them to fill out your forms. This is where knowing how to prefill Google Forms from Google Sheets can come in handy.
For example, 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. Here’s how I went about auto populating my Google Form with data in Google Sheets.
Step 1: Sample Google Form to autofill
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
Step 2: 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. When trying to use Google Spreadsheet to autofill Google Forms, you have 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.
More tips on Google Apps Script here
Step 3: Write the Automation Script to Prefill Google Forms
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 Google Sheets forEach function, we are going to go through each of the options for each question. In the Google Sheets forEach function, we are going to pass the labels variable and the variable 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.
Learn more about Google Apps Script here
Step 4: Check if Google Forms Prefill Worked
Our code to prefill a Google Form is complete. Select the correct function (main) as shown below and run the program to prefill your Google Form.
On successful execution, the script will take the options from the Google Sheet and fill your forms automatically.
Organizing an event? Learn how to organize RSVP's using Google Forms
Summary: How to Pre-Populate Google Form from Spreadsheet
We saw how you can populate a Google Form from Google spreadsheet using Google Apps Script.
If you prefer to watch a video on how to make Google Forms pull data from Google spreadsheets to autofill certain field, we've got you covered there too:
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.