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);
}
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);
}
| improve this answer | |
  • 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 at 22:46
  • @diGga glad it was helpful to you :) Happy coding :) – Marios Nov 7 at 22:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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