I am creating a dropdown list in Google Forms, and have approximately 300 options that my users can choose from. Is there an efficient way to add the options to the Google Form as opposed to manually typing in 300 options on the backend?
Google Forms – Create Form with Many List Options
google-forms
Related Solutions
To get Radio Buttons on a Google Docs Form, you need set the Question Type to Multiple Choice
Tom Horwood's answer works great, but only after correcting an important error in his code. All references to LIST_DATA
elements should be made using the index j
(not i
). I don't have sufficient karma to comment on Tom's post so here's the fully monty:
Below is the corrected code. I've also changed the name of the LIST_DATA
elements so they are a little more descriptive. It also shows how Tom had thought ahead (kudos!) and provided the facility to update more than one form element with the contents of more than one spreadsheet list.
var FORMID = "PUT_YOUR_FORM_ID_HERE";
var LIST_DATA = [{formFieldTitle:"Job Number", worksheetName:"JobNumbers"},
{formFieldTitle:"Task 1 Category", worksheetName:"TaskCategories"},
{formFieldTitle:"Task 2 Category", worksheetName:"TaskCategories"},
{formFieldTitle:"Task 3 Category", worksheetName:"TaskCategories"},
{formFieldTitle:"Task 4 Category", worksheetName:"TaskCategories"},
{formFieldTitle:"Task 5 Category", worksheetName:"TaskCategories"},
{formFieldTitle:"Task 6 Category", worksheetName:"TaskCategories"},
];
function onOpen(e){
var menuEntries = [];
menuEntries.push({name: "Update Lists", functionName: "updateLists"});
SpreadsheetApp.getActiveSpreadsheet().addMenu("List Updater", menuEntries)
};
function updateLists() {
var form = FormApp.openById(FORMID);
var items = form.getItems();
for (var i = 0; i < items.length; i += 1){
for (var j = 0; j < LIST_DATA.length; j += 1) {
var item = items[i]
if (item.getTitle() === LIST_DATA[j].formFieldTitle){
updateListChoices(item.asListItem(), LIST_DATA[j].worksheetName);
break;
}
}
}
}
function updateListChoices(item, sheetName){
var data = (SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName(sheetName)
.getDataRange()
.getValues());
var choices = [];
for (var i = 0; i < data.length; i+=1){
choices.push(item.createChoice(data[i][0]));
}
item.setChoices(choices);
}
To summarise how to get this to work (for those, like me, who are using Google Apps Script for the first time). I've referred to sheet names and form field names in the code snippet above to hopefully make it more understandable:
In the spreadsheet which is associated with your form:
- Create a new worksheet. This will contain the list of items you want added to a field. Give the worksheet a suitable name (for example,
TaskCategories
). Put your list of items there in the first column of that worksheet. Delete all extra columns and rows on that sheet (this may or may not be necessary - haven't tested) - From the Tools menu, choose "Script Editor". Copy and paste the code snippet above into the script editor. You will need to change the following parts of the script:
- The value of variable
FORMID
will need to be changed to the ID of your form. The ID is the long code (between forward slashes) in the URL bar in your browser for the desired form. - The
LIST_DATA
variable will need to be modified to suit your form and your needs. You'll note that each item inLIST_DATA
is a tuple offormFieldTitle
andworksheetName
. The former is the name of the form field (that must be a "choose from list" type of field) - in the form editor it's calledQuestion Title
. The latter is the name of the worksheet that you created earlier that contains the list of items you want to populate the field with. Add as many of these tuples to the list as you need. - Now save the script (Click on the save icon)
- Test the script by choosing 'Run' from the script editor menu and choosing "updateLists". The first time you do this it will ask for permission. If the script succeeds then you can observe that your form now has the specified fields populated, otherwise you'll note an error message on screen.
- The value of variable
- Create a new worksheet. This will contain the list of items you want added to a field. Give the worksheet a suitable name (for example,
If you return to the spreadsheet, you should see a new menu item entitled
List Updater
. It has one itemUpdate Lists
which you need to run every time you change any of your list(s) worksheet(s) - it will update the form accordingly.
I also note, for the benefit of readers, that FormRanger does not work with the new Google Spreadsheets. The FormRanger developers say as much on their website. Perhaps/hopefully that will change in future, but as at time of this posting the above code works for me and I'm using it in a deployed form for a client.
Related Topic
- Google Forms – Simplify Checkbox Responses into Itemized List
- Google Forms – Send Different Data from Displayed Value on Select Option
- Google Forms Script – Draw List of Variables from Table
- Google-forms – Searching dropdown list in Google Forms
- Google-sheets – Importing data into Google Form response sheet with date, list, grid
- Google-sheets – How to get data from Google Sheets to a Google Forms dropdown list
Best Answer
Use copy & paste 1.
Reference
1: Quick tip: Copy and paste a list into Google Forms, Google Drive G+ page.