Trying to set up an onedit script for google sheet that unchecks all tickboxes in one column when a new one in the same column is ticked in.
function onEdit(e){
if(e.range.columnStart != 1 || e.value == "FALSE") return;
for(var i=1;i<100;i ){
if(i == e.range.rowStart) continue;
// Here I am trying to check if the cell has the value TRUE and if so change it to FALSE.
if(e.source.getActiveSheet().getRange(i,1).value == "TRUE"){
e.source.getActiveSheet().getRange(i,1).setValue("FALSE");
}
}
}
I'm going through the cells with an FOR statement and try to check if they have the value TRUE and if so change it to FALSE. It seems to not detect any TRUE though. If I remove the IF statement it does keep the newly checked box checked but fills the whole column with the FALSE value.
What is it that I am missing or have misunderstood trying to use the IF statement to detect TRUE and change it to FALSE?
CodePudding user response:
Managed to solve it. Instead of checking for the value TRUE I tried to use the isChecked() function and it works.
function onEdit(e){
if(e.range.columnStart != 1 || e.value == "FALSE") return;
for(var i=1;i<100;i ){
if(i == e.range.rowStart) continue;
if(e.source.getActiveSheet().getRange(i,1).isChecked() == true){
e.source.getActiveSheet().getRange(i,1).setValue("FALSE");
}
}
}
CodePudding user response:
It should be a bit faster this way:
function onEdit(e) {
const sh = e.range.getSheet();
if (sh.getName() != "Your Sheetname" || e.range.columnStart != 1 || e.value == "FALSE") return;
let vs = sh.getRange(1, 1, sh.getLastRow(), 1).getValues()
vs.forEach((r, i) => {if (i 1 != e.range.rowStart) { vs[i][0] = "FALSE" }});
sh.getRange(1, 1, vs.length, vs[0].length).setValues(vs);
}
If you your using its in a bunch of sheets, you could use:
if(!~['Sheet1','Sheet2].indexOf(sh.getName()) || e.range.columnStart != 1 || e.value == "FALSE") return;