Is it possible to populate a google form from a google spreadsheet?

Question

I'd like to create a form that uses the data from the spreadsheet, so that it's dynamic. Is it possible to do this? I haven't been able to find anywhere that describes how, or any examples.

All that seems possible is to populate a spreadsheet from a form, which I'll also use but its not the primary concern here.

Solution 1

Google Apps Scripts.. finally a well documented way of generating Google forms programmatically. https://developers.google.com/apps-script/reference/forms/

Solution 2

Yes it is. Use a Form script and update the information from the spreadsheet using a trigger on the FORM OPEN. Here is an example that gets data from two different sheets and insert data in a combo box and into a multiple choice control.

function getNewNames(){
  var form = FormApp.getActiveForm();
  var items = form.getItems();

  var ss = SpreadsheetApp.openByUrl(
     'https://docs.google.com/spreadsheets/d/YOURDOCSNAMEHERE/edit');
    var assSheet1 = ss.getSheetByName('Sheet1');
    var assValues = assSheet1.getDataRange().getValues();
    var values = assValues.slice(1);
    var valSort = values.sort();

  var ss2 = SpreadsheetApp.openByUrl(
     'https://docs.google.com/spreadsheets/d/DOCSNAMEHERE/edit');
     var playerSheet1 = ss2.getSheets()[0];
     var playerValues = playerSheet1.getDataRange().getValues(); 
     var player = playerValues.slice(0);
     var plySort = player.sort();

  var names = [];

  for(var p = 0; p < plySort.length; p++){
    names.push(plySort[p][0])
  }

  var pList = items[0].asListItem();
  pList.setChoiceValues(names).setRequired(true).setHelpText('Please Select Player Name')

  var areas = [];
  for (var i = 0; i < valSort.length; i++) {
      areas.push(valSort[i][1])
    }

  var aList = items[1].asMultipleChoiceItem();
      aList.setChoiceValues(areas).setRequired(true).setHelpText('Select Area of Assessment')

}

Solution 3

You may want to have a look at the FormRanger plugin. It does not populate the questions of your form, but dynamically populates the possible answers in a multiple choice questions, list or grid questions. It can even pull data out of the previous responses of the form. It can set to automatically update after each submit or every hour. Beware: if you use filter questions to redirect to different sections based on the answer to a questions, you can not use Formranger for that questions as it would break the re-directs.