0

with the help of a tutorial i made a script with dependent drop downs. I tried to modify it that when a cell in Column A is cleared/modified the first level of the drop down too.

var mainWsName = "master";
var optionsWsName = "options";
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);
var options = wsOptions.getRange(2, 1,wsOptions.getLastRow()-1,2).getValues();

function onEdit(e){
  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 == 2 && r > 3){
    if(val === ""){
      ws.getRange(r, 3).clearContent(); 
      ws.getRange(r, 3).clearDataValidations();
    } else {
      ws.getRange(r, 3).clearContent(); 
      var filteredOptions = options.filter(function(o){ return o[0] === val });
      var listToApply = filteredOptions.map(function(o){ return o[1] });
      var cell = ws.getRange(r, 3); 
      applyValidationToCell(listToApply,cell);
    }
  }  
}

function applyValidationToCell(list,cell){
  var rule = SpreadsheetApp
  .newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build();
  cell.setDataValidation(rule);
}

1 Answer 1

0

Explanation:

  • The direct way to solve your issue is to add one more if condition to check whether the value of the cell in column A is empty. If it is empty, then remove both data validations and the existing values. If not, then create them:

    if(wsName == mainWsName && ws.getRange(r,1).getValue()==''){
        ws.getRange(r, 2,1,2).clearContent(); 
        ws.getRange(r, 2,1,2).clearDataValidations();
    } 
    else {
        var listToApply = options.map(o=>o[0]);
        var cellB = ws.getRange(r, 2); 
        applyValidationToCell(listToApply,cellB); 
    }
    

Complete solution:

var mainWsName = "master";
var optionsWsName = "options";
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);
var options = wsOptions.getRange(2, 1,wsOptions.getLastRow()-1,2).getValues();

function onEdit(e){
  var activeCell = e.range;
  var val = activeCell.getValue();
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var wsName = activeCell.getSheet().getName();

  
  // new code starts here
  if(wsName == mainWsName && ws.getRange(r,1).getValue()==''){
      ws.getRange(r, 2,1,2).clearContent(); 
      ws.getRange(r, 2,1,2).clearDataValidations();
  } 
  else {
      var listToApply = options.map(o=>o[0]);
      var cellB = ws.getRange(r, 2); 
      applyValidationToCell(listToApply,cellB); 
  }
  // new code ends here

  if(wsName == mainWsName && c == 2 && r > 3){


    if(val === ""){
      ws.getRange(r, 3).clearContent(); 
      ws.getRange(r, 3).clearDataValidations();
    } else {
      ws.getRange(r, 3).clearContent(); 
      var filteredOptions = options.filter(function(o){ return o[0] === val });
      var listToApply = filteredOptions.map(function(o){ return o[1] });
      var cell = ws.getRange(r, 3); 
      applyValidationToCell(listToApply,cell);
    }
  }  
}

function applyValidationToCell(list,cell){
  
  var rule = SpreadsheetApp
  .newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build();
  
  cell.setDataValidation(rule);
}
2
  • Thanks so much Marios! I tried with the IF but wrapped it arround the existing IF. Which didn´t work. Also i tried to bring it in to the IF Attribute. Thanks again i carry on training myself.
    – diGga
    Nov 7, 2020 at 22:46
  • @diGga glad it was helpful to you :) Happy coding :)
    – Marios
    Nov 7, 2020 at 22:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.