Run function to populate dropdown list in Form on change in sheet cell range

Question

I am looking for a script to run as soon as a range of cells (example: C2:C) change. When the script runs I want it to call a Form and update the Form drop-down list with the new values in the cell range C2:C.

I have tried this with an installable trigger, however the trigger fires even when other tabs in the spreadsheet are changed, i.e sheet 2 etc.

function updatetestForm(){
// call your form and connect to the drop-down item
var form = FormApp.openById("formID");
var namesList = form.getItemById("12345678").asListItem();

// identify the sheet where the data resides needed to populate the drop-down
var ss = SpreadsheetApp.openById("sheetID");
var names = ss.getSheetByName("sheet1");

// grab the values in the first column of the sheet - use 2 to skip the first 1 row (limit of 1000) 
var namesValues = names.getRange(2, 1, names.getLastRow() - 0).getValues();
var test = [];

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

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

}

I tried using an installable trigger but the trigger fired when other sheet tabs changed.

Answer 1

You could check the event that launched the trigger. For example, by changing the title to add the e to grab the event, you can check the sheet involved. You can add this first line and wrap the rest of the function with that opening curly bracket (close at the end, obviously)

`function updatetestForm(e){

  if(e.range.getSheet().getName() == "sheet1") {

You can add more conditions to check the column inside the if statement:

if(e.range.getSheet().getName() == "sheet1" && e.range.getColumn() == 3) {

And so on