Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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);
share|improve this answer
    
Apologies if this post is in the wrong place, but the code doesn't work for me. It gives a "ReferenceError: "values" is not defined." error. This is fixed by changing: "values.push(options2DArr[i][0]);" to "options.push(options2DArr[i][0]);" But then the ".setChoices(options)" line gives the following error: Cannot convert Array to Choice[]. It should say .setChoiceValues(options) –  user56366 Jan 5 at 16:39
    
Ah, thanks! This is definitely the right place to post that. I have corrected values to options. However, I'm not sure about the rest of your comment. The code already does setChoiceValues. Moreover, I seem to have lost the form I used to experiment with, so I cannot reproduce the error. –  Vidar S. Ramdal Jan 6 at 8:36

There is a script for that, Called formranger http://youpd.org/formranger

You can find instructions in this document: https://docs.google.com/document/d/1YvgrxeZJNb2CBpSra1j59Mh1bVpk4vBQYLOoyVjby7A/edit?hl=en&forcehl=1

The formRanger allows users to automatically populate the options in any multiple-choice, checkbox, or listbox style question in a Google Form from any column in the attached spreadsheet.

share|improve this answer
    
Hoi Willem, this is considered a one-liner. Try adding a bit more info, like what does it do, how to install, prepare an example? –  Jacob Jan Tuinstra Feb 25 at 19:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.