title: Dynamic dependent dropdown list using google apps script is not working - Google Sheets
body: <p>I have written some code which is supposed to use a value in one column which shows the 'Branch', and based on that it changes the dropdown menu in 'Assigned to OE' (for context an OE is an employee), so an OE name can be selected from a dropdown list, based on which Branch they are in. The list will only show employees from the corresponding branch. </p>
  
  <p>Here is a copy of my spreadsheet, with the irrelevant columns cleared: <a href="https://docs.google.com/spreadsheets/d/1dzTYQL1YPX6z6qtV4_tNl4ntBMLqcMQYoPcw6cKuyAw/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1dzTYQL1YPX6z6qtV4_tNl4ntBMLqcMQYoPcw6cKuyAw/edit?usp=sharing</a></p>
  
  <p>Column A of the Sales Order sheet is where I would like the dropdown lists, in each row, and the OE names sheet is where the list of OEs (employees) will be, and the dropdown lists should be populated with these columns depending on the Branch.</p>
  
  <p>The script I have written is:</p>
  
  <pre><code>function onEdit() {
  
  var app = SpreadsheetApp;
  var ss = app.openById("1UM[MASK]0M");
  var OEsheet = ss.getSheetByName("OE names");
  var SOsheet = ss.getSheetByName("Sales Order");
  var activeCell = SOsheet.getActiveCell();
  
  if(activeCell.getColumn() == 1 &amp;&amp; activeCell.getRow() &gt; 1 &amp;&amp; ss.getSheetName() == SOsheet) {
  
    activeCell.offset(0, 1).clearContent().clearDataValidations();
  
    var OEnames = OEsheet.getRange(1, 1, 1, OEsheet.getLastColumn()).getValues();
  
    var OEnamesIndex = OEnames[0].indexOf(activeCell.getValue()) + 1;
  
    if(OEnamesIndex != 0){
   var validationRange = OEsheet.getRange(2, OEnamesIndex, OEsheet.getLastRow());
   var validationRule = app.newDataValidation().requireValueInRange(validationRange).build();
  
    activeCell.offset(0, -6).setDataValidation(validationRule);
  
  }
  
  }
  }
  </code></pre>
  
  <p>This script does not seem to do anything, but I cannot figure out why.</p>
  
  <p>Thank you</p>