Google Spreadsheet Form, populate form options based on a spreadsheet

Question

I need to find a way to have the multiple choice options in a Google form change based on a set of cells in a Google Spreadsheet that is constantly changing. Any ideas?

monchai monchai · Accepted Answer · 2013-07-08T07:05:36

It's possible. Write your owns script in the spreadsheet to update your form. If you're not acquainted with writing script you may find someone's in Google script gallery. Let's see the sample of my script. The script is to update two list box.

function updateForm(){
  // a way to get the items from the form
  var form = FormApp.openById("<your form id>");
  var agentList = form.getItemById(<your item id>).asListItem();
  var hotelList = form.getItemById(<your item id>).asListItem();


  var ss = SpreadsheetApp.getActive();
  var agents = ss.getSheetByName("Agents");
  var hotels = ss.getSheetByName("Hotels");

  // get the values in the first column accept header row 1
  var agentValues = agents.getRange(2, 1, agents.getMaxRows() - 1).getValues();
  var hotelValues = hotels.getRange(2, 1, hotels.getMaxRows() - 1).getValues();

  var agentNames = [];
  var hotelNames = [];

  // convert 2D to 1D array and ignore empty cells
  for(var i = 0; i < agentValues.length; i++) {  
    if(agentValues[i][0] != "") {
      agentNames[i] = agentValues[i][0];
    }
  }

  for(var i = 0; i < hotelValues.length; i++) {
    if(hotelValues[i][0] != "") {
      hotelNames[i] = hotelValues[i][0];
    }
  }

  // populate the list
  agentList.setChoiceValues(agentNames);
  hotelList.setChoiceValues(hotelNames);
}

You can also link this function to the spreadsheet edit/change events to make the list update automatically.

Google Spreadsheet Form, populate form options based on a spreadsheet

2 Answers