How to add multiple drops downs to Google Apps Form created dynamically

Question

I am a new to Google Apps Script programming. I am trying to use a Google sheet to dynamically populate a Google Form. I have one drop down working, but when I try to add a second it just duplicates the first.

Here is my code:

//bonus, gets values from spreadsheet and returns them
function getValues() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(1, 1, lastRow);

  var range_team = sheet.getRange(1, 4, lastRow);
  var values = range.getValues();
  var values_team = range_team.getValues();

  return values;
  return values_team;
}

//what you'll need to call initially to create the form, and store id in project properties
function createForm() {

// create Form 
 var form = FormApp.create('Test Form 11_6_14_3');

// first drop down list
  var values_team = getValues(1,4,3,4);
  var list_team = form.addListItem();
  var listItems_team = list_team.setChoiceValues(values_team);

// second drop down list  

  var values = getValues(1,1,1,18);
  var list = form.addListItem();
  var listItems = list.setChoiceValues(values);

  var formId = form.getId();

  PropertiesService.getScriptProperties().setProperty('Test Form 11_6_14_3', formId);
}

Thanks and be gentle...remember I'm a noob...

rpm rpm · Accepted Answer · 2014-11-14T18:15:50

You should check your code at return values; return values_team; Do you think second return statement ever going to execute? It will never execute.
Why are you passing values in getValues() while you haven't defined any parameters in getValues() function. There is no use of these values in your getValues() function.

Why don't you use getRange() in following way to make it work. This will give you the result as you want.

function getValues(x,y) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(x, y, lastRow);
  var values = range.getValues();

  return values;
  //return values_team;                  // remove this as there it won't be called ever
}

function createForm() {

  // create Form 
  var form = FormApp.create('Test Form 11_6_14_3');

  // first drop down list
  var values_team = getValues(1,1);   // add your range values here as a parameter
  var list_team = form.addListItem();
  var listItems_team = list_team.setChoiceValues(values_team);

  // second drop down list  

  var values = getValues(1,4);        // add your range values here as a parameter
  var list = form.addListItem();
  var listItems = list.setChoiceValues(values);

  var formId = form.getId();

  PropertiesService.getScriptProperties().setProperty('Test Form 11_6_14_3', formId);
}

How to add multiple drops downs to Google Apps Form created dynamically

1 Answers