I want to set a checkbox to true if the value in another cell is greater than 21-CodePudding

I am working with a sheet in which where a value in Column L is greater than 21, I need to set the checkbox in N to true. However the checkbox in column N might also be ticked manually even if the value is less than 21 so I need it so that the script doesn't override that.

This is for a room booking purpose and so if the room is requested more than 21 days in advance, we want to auto decline it (I have a script to send an automated email), but we want to check the box automatically.

It might be that someone books it 7 days in advance but the room is already booked, which is why we may need to manually tick the box for other requests.

I'm afraid I can't share the sheet as it's a work one and security permissions do not allow it but I have attached a picture.

An automated email will send based on the check boxes.

I found a script that will check the box if there is a value in another cell but I'm not sure how to tweak it to make it so that the value is greater than 21 and so that it only looks at the last row

function myFunction() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Room Booking");
var hasValue = sheet.getRange("L:L").getValues();

var value = "";


for(var i = 0; i < hasValue.length; i  ){



if(hasValue[i][0].length > 0) {

sheet.getRange("N"   (i   1)).setValue("TRUE");
}
else {
sheet.getRange("N"   (i   1)).setValue("");
}

}

}

Thank you for any help!!!

enter image description here

CodePudding user response：

if L > 21 && N is FALSE

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Room Booking");
  var vs = sheet.getRange(1,1,sh.getLastRow(),3).getValues();
  vs.forEach((r,i) => {
    if(r[11] > 21 && r[13] == "FALSE") {
      sheet.getRange(i   1,14).setValues("TRUE");
    }
  })
}