I'm trying to write a script that would untick the boxes on a row based on a cell value. for example, if the tick box in cell Q2 is true, i want the values in J2,K2,L2,and M2 to be set to false.. but i want it to do the same thing for each row, step 1: check if the cell in column Q is true, if it is, set the values of J,K,L,M on the corresponding row to false. If it's false, then do nothing. Image

So far I've tried this:

function logDataInEveryCell() {
 var range = SpreadsheetApp.getActive().getRangeByName("CheckBox");
 var values = range.getValues();

 // tickboxes
 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tracker');
 var firstBox = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('sentEmail');
 var secondBox = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('secondEmail');
 var thirdBox = SpreadsheetApp.getActiveSpreadsheet().getRangeByName ('reminder');
 var fourthBox = SpreadsheetApp.getActiveSpreadsheet().getRangeByName ('booked');

 //loop to check the value of another i checkbox
 values.forEach(function(row) {
 var i = row.forEach(function(col) {
   if ( i === true ){
     //if i checkbox is ticked, set the value of these tickboxes to false, I cannot set a static coordinates for the cell as it wont read the rows bellow...
      ss.getRange(firstBox).setValue(false);
      ss.getRange(secondBox).setValue(false);
      ss.getRange(thirdBox).setValue(false);
      ss.getRange(fourthBox).setValue(false);
     }
     Logger.log(col);
     
   });
 });
}

But it isn't working for me, any ideas how i can fix it or what i'm missing? (I've used named ranges)

CodePudding user response：

Uncheck if Q is truthy

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Tracker');
  const vs = sh.getRange(2,1,sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  vs.forEach((r,i) => {
    if(r[16]) {
      sh.getRange(i   2, 10 , 1 ,4).uncheck();
    }
  })
}