add data to a range from a dropdown in google sheets

Question

I have a real estate office that uses google sheets to show address, buyer name, realtor name, and seller name. For the realtor name column I created a dropdown so they can select from a list of names. What I need to do is be able to update that list range when a new name is entered into the dropdown. For example if a new realtor lists a property and their name is not in the list the person entering the info just has to type it in the dropdown and the list will automatically update for next time. Thank you

Answer 1

With the following script, if name is not listed in the dropdown list, then the user can enter their name in the dropdown and this will add the name to the list of names.

function script2(e) {
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var sht1 = s.getSheetByName('2022'); //sheet where you have the dropdown
  var sht2 = s.getSheetByName('Realtors'); //sheet where you have the list of names
  var range = sht2.getRange('A:A'); //range for the list of names
  var columnP = 16;
  var columnQ = 17;
  
  //get value of active cell if active cell is within columns P or Q
  if (e.range.getColumn() === columnP || e.range.getColumn() === columnQ){
    var cellValue = sht1.getActiveCell().getValue();
    var result = hasValue(range, cellValue);
  }

  //if value is not found then it will add name to the list
  if (result == false){
    sht2.getRange('A2').getNextDataCell(SpreadsheetApp.Direction.DOWN).offset(1,0).setValue(cellValue);
  }
    
  else{
  }
}

//this function searchs a value in a range
function hasValue(range, value) {
  return range.getValues().flat().includes(value);
}