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:

 .setTitle('Do you prefer cats or dogs?')

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


3 Answers 3


You should be able to do it with something like

// Change these three values to match your own spreadsheet
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

.setTitle('Do you prefer cats or dogs?')
.setChoiceValues(options) // <-- Uses the spreadsheet values as choice options
  • 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, 2014 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. Jan 6, 2014 at 8:36

There is a script for that, Called formranger http://web.archive.org/web/20140302104243/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.

It requires the following permissions:

enter image description here

  • 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
    Feb 25, 2014 at 19:38

Why don't you simply copy this list, with each choice on a separate line (in a text file) or in cells along a column and then paste (ctrl+v) in option 1 textbox? Doing this will generate the pasted items as separate choices, simple!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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