Google-sheets – How to create a multiple choice question in Google Forms based on spreadsheet data

google sheetsgoogle-apps-scriptgoogle-forms

I am making a Google Form for evaluations. I will have a multiple choice question in there and the available options have to come from a list of items, say on a spreadsheet or a text document. I have figured out that I can create question using:

form.addMultipleChoiceItem()
 .setTitle('Do you prefer cats or dogs?')
 .setChoiceValues(['Cats','Dogs'])
 .showOtherOption(true);

However, how do I make it so that the list of choices come from a Google Spreadsheet?

Best Answer

You should be able to do it with something like

// Change these three values to match your own spreadsheet
var OPTIONS_SPREADSHEET_ID = "0Avea1NXBTibYdEY4XzBvSk84MjB2RENiOFljSkVaY0E";
var OPTIONS_SHEET_NAME = "Sheet 1";
var OPTIONS_RANGE = "A1:A4"; // We have 4 options, listed in column A

// This returns a 2D array of the values of the range, e.g. [["A1value", "B1Value"], ["A2Value", "B2Value"], ["A3Value", "B3Value"]];
var options2DArr = SpreadsheetApp.openById(OPTIONS_SPREADSHEET_ID).getSheetByName(OPTIONS_SHEET_NAME).getRange(OPTIONS_RANGE).getValues();
var options = [];
for (var i = 0; i < options2DArr.length; i++) { // Now iterate through the 2D array and extract values from the first column only
  options.push(options2DArr[i][0]); // 0 is the index of the first column
}

form.addMultipleChoiceItem()
.setTitle('Do you prefer cats or dogs?')
.setChoiceValues(options) // <-- Uses the spreadsheet values as choice options
.showOtherOption(true);
Related Topic