Auto populate form options from spreadsheet column

Question

I created a google form with a drop-down question and I wanted to auto-populate the drop-down list using a specific column in the attached spreadsheet.

I searched the web for solutions and I found two effective ways: An Add-on called the "Form Ranger" and a code to add to the sheet script editor

I tried the Add-on first for easier and faster installation and it worked well with one major problem is that it takes much time to update the list in my form which was unacceptable so I decided rather add the below code to the sheet script editor:

function updateForm(){
// call your form and connect to the drop-down item
var form = FormApp.openById("Your Form ID");

var namesList = form.getItemById("The Drop-Down List ID").asListItem();

// identify the sheet where the data resides needed to populate the drop-down
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("Name of Sheet in Spreadsheet");

// grab the values in the first column of the sheet - use 2 to skip header row 
var namesValues = names.getRange(2, 1, names.getMaxRows() - 1).getValues();

var studentNames = [];

// convert the array ignoring empty cells
for(var i = 0; i < namesValues.length; i++)    
if(namesValues[i][0] != "")
  studentNames[i] = namesValues[i][0];

// populate the drop-down with the array data
namesList.setChoiceValues(studentNames);

}

The above code worked much better because it updates the form right away on submission with only two problems:

Instead of removing the option completely, it replaces the option with "NOT_FOUND"
It doesn't remove the last option at all; imagine I have 7 options so it works perfectly with only six options but never removes the entire list options at all

So if anyone can please help me get the job done in a better way, I will be very much grateful

I'm having trouble following what you're trying to do, the option list is updated on the values of a sheet, but what do you mean by removing the options, when you talk about your problems. could you show screenshots of what you are experiencing/trying to achieve? — AMolina
Hello Alberto, thanks for your interest. what do I mean about removing an option: okay here's the thing, imagine I have a column in my sheet that is responsible for updating the form options. In the column I have 3 options for example (Option 1, Option 2, Option 3) and I added a formula that removes the option from the column automatically once submitted in the form. So when a user chooses option 1, then option 1 is removed from the column and we have only Option 2, Option 3 and so on. But when I go back to the form I still have 3 options as follow (Not_Found, option 2 and option 3) — Mahmoud Bayoumi
For more clarification, i just need to add a code to tell the form "if the cell is empty or blank in the referenced column, just remove the option completely without adding Not_Found instead" Thanks in advance — Mahmoud Bayoumi

AMolina AMolina · Accepted Answer · 2019-07-24T09:25:17

Thank you for the explanation in the comments, try the following code:

function onEdit() {
  var ws = SpreadsheetApp.getActiveSpreadsheet();
  var ss = ws.getSheetByName("sheet1");
  var name = ss.getRange("A6:A8").getValues();

  var rule = SpreadsheetApp
  .newDataValidation()
  .requireValueInList(name)
  .build();

  ws.getRange("B3").setDataValidation(rule);  

}

This code will be executed when your sheet gets updated, it will read the "options" from A6:A8 (in my example) and with them populate the dropdown menu in B3. The menu in B3 will remove the choices if they are deleted and create any new ones. You can couple this to your code that removes the options from where you had them.

Here you can find some documentation on the methods I used and more information on working with data validation

Auto populate form options from spreadsheet column

1 Answers