In this sheet i have multiple dependent dropdown lists. These dropdowns lists evaluate if they have multiple or only one option available. If i only have one option, it will autocomplete with the single possible value.

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

The problem is that when I chose a client that only has one project, this project is completed automatically but the task isn't.

This is an example of what i want when i will select Client 3 in the column a

image

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("Not");
  }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();
  }
}

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);

  
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(listToApply).build();//creo el dropdown
  if(listToApply[1] || null){
    applyValidationToCell(listToApply,cell);
  }else{
    cell.setDataValidation(rule).setValue(listToApply[0] || null);
  }
  }
}

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);

  
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(listToApply).build();
  cell.setDataValidation(rule).setValue(listToApply[0] || null);
  }
}

function applyValidationToCell(list,cell) {

  var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build();

  cell.setDataValidation(rule);




}

CodePudding user response：

Although I'm not sure whether I could correctly understand your question, for example, is the following modification the result you expect? In this modification, please modify the function of applyFirstLevelValidation as follows.

From:

if(listToApply[1] || null){
  applyValidationToCell(listToApply,cell);
}else{
  cell.setDataValidation(rule).setValue(listToApply[0] || null);
}

To:

if (listToApply[1] || null) {
  applyValidationToCell(listToApply, cell);
} else {
  cell.setDataValidation(rule).setValue(listToApply[0] || null);
  applySecondLevelValidation(listToApply[0], r); // <--- Added
}