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:

1. Instead of removing the option completely, it replaces the option with "NOT_FOUND"
2. 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

Answer 1

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