title: Dependent drop down list auto-complete - google sheets script editor
body: <p>I have a sheet that has multiple dependent dropdown lists. I took the base code from a tutorial.
  Now I need that when I have a single option available in the dropdown list (for example in &quot;Column Task&quot;) it auto-complete automatically.</p>
  <p>This is an example:
  <a href="https://docs.google.com/spreadsheets/d/13fIO3OgAALDVx5PmX6mx0RMdES4PUdsSBU9aBOQhwuk/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/13fIO3OgAALDVx5PmX6mx0RMdES4PUdsSBU9aBOQhwuk/edit?usp=sharing</a></p>
  <p>Code i use:</p>
  <pre><code>var mainWsName = &quot;Page 1&quot;; 
  var optionsWsName = &quot;Data&quot;;
  var firstLevelColumn = 1; 
  var secondLevelColumn = 2;
  var thirdLevelColumn = 5;
  
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
  var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(&quot;Data&quot;);
  var options = wsOptions.getRange(2,1,wsOptions.getLastRow()-1,7).getValues(); 
  
  
  function onEdit(e){
    var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
    var row = e.range.getRow();
    var col = e.range.getColumn();
    
  
    var activeCell = e.range;
    var val = activeCell.getValue();                
    var r = activeCell.getRow();                   
    var c = activeCell.getColumn();                
    var wsName = activeCell.getSheet().getName()
  
    if(wsName === mainWsName &amp;&amp; c === firstLevelColumn &amp;&amp; r &gt; 3){
      applyFirstLevelValidation(val,r)
    }else if(wsName === mainWsName &amp;&amp; c === secondLevelColumn &amp;&amp; r &gt; 3){
      applySecondLevelValidation(val,r)
    }
    
    if(c === 1 &amp;&amp; r &gt; 3 &amp;&amp; wsName === mainWsName &amp;&amp; val === &quot;intern&quot;){    
      e.source.getActiveSheet().getRange(row,6).setValue(&quot;No&quot;);
    }else if(c === 1 &amp;&amp; r &gt; 3 &amp;&amp; wsName === mainWsName &amp;&amp; val !== &quot;&quot;){
      e.source.getActiveSheet().getRange(row,6).setValue(&quot;Yes&quot;);
    }else if(c === 1 &amp;&amp; r &gt; 3 &amp;&amp; wsName === mainWsName &amp;&amp; val === &quot;&quot;){
      e.source.getActiveSheet().getRange(row,6).clearContent();
      ws.getRange(r,secondLevelColumn).clearDataValidations();
    }
  }//end onEdit
  
  function applyFirstLevelValidation(val,r){
  
    if(val === &quot;&quot;){
    ws.getRange(r,secondLevelColumn).clearContent();
    ws.getRange(r,secondLevelColumn).clearDataValidations();
    ws.getRange(r,thirdLevelColumn).clearContent();
    ws.getRange(r,thirdLevelColumn).clearDataValidations();
    } else{
    ws.getRange(r,secondLevelColumn).clearContent();
    ws.getRange(r,secondLevelColumn).clearDataValidations();
    ws.getRange(r,thirdLevelColumn).clearContent();
    ws.getRange(r,thirdLevelColumn).clearDataValidations();
    var filteredOptions = options.filter(function(o){ return o[0] === val });
    var listToApply = filteredOptions.map(function(o){ return o[1] });
    var cell = ws.getRange(r,secondLevelColumn);
    applyValidationToCell(listToApply,cell);
    }
  }
  
  function applySecondLevelValidation(val,r){
  
    if(val === &quot;&quot;){
    ws.getRange(r,thirdLevelColumn).clearContent();
    ws.getRange(r,thirdLevelColumn).clearDataValidations();
    } else{
    ws.getRange(r,thirdLevelColumn).clearContent();
    var firstLevelColValue = ws.getRange(r,firstLevelColumn).getValue();
    var filteredOptions = options.filter(function(o){ return o[0] === firstLevelColValue &amp;&amp; o[1] === val });
    var listToApply = filteredOptions.map(function(o){ return o[2] }); 
    var cell = ws.getRange(r,thirdLevelColumn);
    
    
    
    applyValidationToCell(listToApply,cell);
    }
  }
  
  function applyValidationToCell(list,cell) {
  
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build();
    
  
    cell.setDataValidation(rule);
  
  
  }
  
  </code></pre>