Get Dropdown list depending of checkboxes checked

August 4, 2022

I develop a script with Apps Script where i can create a dropdown list in a specific cell. Values of this dropdown must be depending about checkboxes which re checked.

With the onEdit function, I have succeeded to create dropdown list when one checkbox is checked.

But when I check an other one, my dropdown list have only the value of the new chekbox. The last is not save.

I think it’s because my array doesn’t save the last value checked because I the onEdit function is launched after each modification.
I don’t know how can I do what I would like.

This is my code :

 function onEdit(e) {
   var ss = e.source;
   var activeSheet = ss.getActiveSheet();
   var cell = e.range;
   var tDropdown = [];

   if (activeSheet.getName() == "Sheet 1" && cell.getColumn() == 1 && cell.getRow() > 1) {
     var choice1 = cell.getValue();
     if (choice1 === false){
       cell.offset(0,3).clearDataValidations()
       cell.offset(0,3).setValue("");
     }else{
       tDropdown.push("Value A");
       var rule = SpreadsheetApp.newDataValidation().requireValueInList(tDropdown, true).setAllowInvalid(false).build();  
       cell.offset(0,3).setValue("");
       cell.offset(0,3).setDataValidation(rule);

     }
   }
   if (activeSheet.getName() == "Sheet 1" && cell.getColumn() == 2 && cell.getRow() > 1) {
     var choice1 = cell.getValue();
     if (choice1 === false){
       cell.offset(0,2).clearDataValidations()
       cell.offset(0,2).setValue("");
     }else{
       tDropdown.push("Value B");
       var rule = SpreadsheetApp.newDataValidation().requireValueInList(tDropdown, true).setAllowInvalid(false).build();  
       cell.offset(0,2).setValue("");
       cell.offset(0,2).setDataValidation(rule);
     }
   }
   if (activeSheet.getName() == "Sheet 1" && cell.getColumn() == 3 && cell.getRow() > 1) {
     var choice1 = cell.getValue();
     if (choice1 === false){
       cell.offset(0,1).clearDataValidations()
       cell.offset(0,1).setValue("");
     }else{
       tDropdown.push("Value C");
       var rule = SpreadsheetApp.newDataValidation().requireValueInList(tDropdown, true).setAllowInvalid(false).build();  
       cell.offset(0,1).setValue("");
       cell.offset(0,1).setDataValidation(rule);
     }
   }
 }

And this is the link of my Sheets.

Thank you for your help.

>Solution :

Try this:

function onEdit(e) {
  var ss = e.source;
  var activeSheet = ss.getActiveSheet();
  var cell = e.range;
  if (activeSheet.getName() != "Sheet 1") return;
  if (cell.getColumn() > 3) return;
  var row = cell.getRow();
  if (row == 1) return;

  // get all three choices from current row
  var choices = activeSheet.getRange(row,1,1,3).getValues().flat();

  // make the array considering the choices
  var tDropdown = ['Value A','Value B','Value C'].map((x,i) => choices[i] ? x : '');

  // etc
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(tDropdown, true).setAllowInvalid(false).build();  
  activeSheet.getRange(row,4).setDataValidation(rule).setValue('');
}