CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mr. Jarsand
Mr. Jarsand

Reputation: 21

Problem with script not unchecking boxes when checking another one in google sheet

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?

Upvotes: 0

Views: 133

Answers (2)

Cooper
Cooper

Reputation: 64082

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;

Upvotes: 0

Mr. Jarsand
Mr. Jarsand

Reputation: 21

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");
     }
   }
 }

Upvotes: 2

Related Questions