Google Spreadsheet Form, populate form options bas

问题:

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?

回答1:

The functionality you are requesting doesn't currently exist. A feature request around linking Google's forms to spreadsheets would be your best bet to keep the two in sync.

There is always the option to create a form using URL parameters as outlined here: https://docs.google.com/support/bin/answer.py?hl=en&answer=160000

回答2:

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.