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 "Column Task") it auto-complete automatically.

This is an example: https://docs.google.com/spreadsheets/d/13fIO3OgAALDVx5PmX6mx0RMdES4PUdsSBU9aBOQhwuk/edit?usp=sharing

Code i use:

var mainWsName = "Page 1"; 
var optionsWsName = "Data";
var firstLevelColumn = 1; 
var secondLevelColumn = 2;
var thirdLevelColumn = 5;

var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
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 && c === firstLevelColumn && r > 3){
    applyFirstLevelValidation(val,r)
  }else if(wsName === mainWsName && c === secondLevelColumn && r > 3){
    applySecondLevelValidation(val,r)
  }
  
  if(c === 1 && r > 3 && wsName === mainWsName && val === "intern"){    
    e.source.getActiveSheet().getRange(row,6).setValue("No");
  }else if(c === 1 && r > 3 && wsName === mainWsName && val !== ""){
    e.source.getActiveSheet().getRange(row,6).setValue("Yes");
  }else if(c === 1 && r > 3 && wsName === mainWsName && val === ""){
    e.source.getActiveSheet().getRange(row,6).clearContent();
    ws.getRange(r,secondLevelColumn).clearDataValidations();
  }
}//end onEdit

function applyFirstLevelValidation(val,r){

  if(val === ""){
  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 === ""){
  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 && 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);


}

CodePudding user response：

In your situation, how about the following modification?

Modified script:

In this modification, applySecondLevelValidation is modified as follows.

function applySecondLevelValidation(val, r) {
  if (val === "") {
    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 && o[1] === val });
    var listToApply = filteredOptions.map(function (o) { return o[2] });
    var cell = ws.getRange(r, thirdLevelColumn);
    
    // I modified below script.
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(listToApply).build();
    cell.setDataValidation(rule).setValue(listToApply[0] || null);
  }
}

• In this modification, after the data validation was put, the 1st element of listToApply is set.