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.
CodePudding user response:
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('');
}