I use a script that allows me to lock a row if in the “BG” column I put an “X”.
I would like that if I put a “0” then the protection is removed.
Also add a function so that the script runs automatically when I change the value in the “BG” cell.
My Script:
function Lock_Cells() { var sheet = SpreadsheetApp.getActive(); for (var i = 5; i <= 1000; i++) { var Check_Cell = "BG" + i; var Temp = sheet.getRange(Check_Cell).getValue(); if (Temp == "X") { var Lock_Range = "B" + i + ":BG" + i; var protection = sheet.getRange(Lock_Range).protect(); var description = "Ligne " + i; protection.setDescription(description); var eds = protection.getEditors(); protection.removeEditors(eds); } } }
Advertisement
Answer
Instead of looping through all the rows, just use onEdit Trigger. onEdit Trigger will execute a function whenever a user edited or inserted value to the sheet. It has an Event Object which has property of range which you can use to determine the row and column of the edited cell. Using those properties you can easily lock a specific row.
Try this code:
function onEdit(e) { let range = e.range; //get the range of edited cell let row = range.getRow(); //get the row let col = range.getColumn(); //get the column let value = e.value; //get the new value of edited cell let sheet = range.getSheet(); //get the sheet where edit is made if(col == 59 && row >= 5 && value == "X"){ //check if the edited cell is BG and row is equal or greater than 5 and value is X let lock_range = `B${row}:BG${row}`; //set lock range using row let protection = sheet.getRange(lock_range).protect() //set protection .setDescription(`Ligne ${row}`) //add description protection.removeEditors(protection.getEditors()); //remove editors }else if(col == 59 && row >= 5 && value == "O"){ //check if the edited cell is BG and row is equal or greater than 5 and value is O var protectedRange = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); //get all protection with type range for (var i = 0; i < protectedRange.length; i++) { //loop if (protectedRange[i].getDescription() == `Ligne ${row}`) { //row matching protectedRange[i].remove(); //remove protection } } } }
Demo:
Test Sheet:
Adding “X”:
Replacing “X” with “O”: