How to automatically update dropdown in a Google Form based on range in Google Sheets?

Question

I have a google form with a dropdown (see below)

I have a column on a google sheet that gets updated everyday.

Is there any way that I can automatically link the names from the google sheet to the google form dropdown Question 1 such that each time the sheet gets updated with an additional name - the google form automatically gets updated with the name in the dropdown. I imagine we would need to use Google AppScript. Any guidance in pointing me in the right direction would be appreciated.

James D James D · Accepted Answer · 2020-03-22T21:52:54

A very generic script but you should be able to modify it as you see fit

function updateForm(){

  var ss = SpreadsheetApp.openById('----------'); // ID of spreadsheet with names
  var sheet = ss.getSheetByName('Names'); // Name of sheet with range of names
  var nameValues = sheet.getRange('A2:A10').getValues(); // Get name values

  var form = FormApp.openById('---------');  // ID of form
  var formItems = form.getItems();
  var question = formItems[2].asListItem(); // Get the second item on the from 

  var names = []

  for(var x = 1; x < nameValues.length; x++){

    if(nameValues[x][0] != ""){ // Ignores blank cells
     names.push(question.createChoice(nameValues[x][0])) // Create an array of choice objects
   } 
  }
  var setQuestion1 = question.setChoices(names); // Update the question

}

To update the form when the sheet is edited you can use an installed onEdit trigger. With the addition of logic you can limit the updting of the form to only occour when a particular range has been edited.

In this example the form will only update when an edit has been made to column A of the sheet 'Names'

function updateForm(e){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var sheetName = sheet.getSheetName();
  var getCol = e.range.getColumn(); 

  if(sheetName == 'Names' && 1){

  var nameValues = sheet.getRange('A2:A10').getValues(); // Get name values

  var form = FormApp.openById('---------');  // ID of form
  var formItems = form.getItems();
  var question = formItems[2].asListItem(); // Get the second item on the from 

  var names = []

  for(var x = 1; x < nameValues.length; x++){

    if(nameValues[x][0] != ""){ // Ignores blank cells
     names.push(question.createChoice(nameValues[x][0])) // Create an array of choice objects
   } 
  }
  var setQuestion1 = question.setChoices(names); // Update the question
  }
}

How to automatically update dropdown in a Google Form based on range in Google Sheets?

1 Answers