I have a Google sheet where I have set a row to become protected when I check a tickbox in Col U. But when I perform this action with the account I setup the code with, it makes two entries of protect ranges.
When I tick the box from another account, I get only 1 entry and it seems to work correctly.
Double entry of range protection with main account
Correct single entry when using a different account
My code is the following. I made it to only protect rows with a warning.
function onEdit() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getActiveSheet();
let cell = sheet.getActiveCell()
let col = cell.getColumn();//gets column numnber
let rownum = cell.getRow();//gets row number
if(col==21 && cell.getValue() == true){//if changed to true protects row to end of data
let protRange = sheet.getRange(rownum,1,1,22).protect().setWarningOnly(true).setDescription(rownum);
}
else if(col == 21 && cell.getValue() ==false){//if changed to false cycles through all the protected ranges on the sheet and unprotects if the row number is the same
let protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE)
for (let i = 0;i<protections.length;i++){
let proDesc = protections[i].getDescription();
if(parseInt(proDesc)==rownum){
protections[i].remove()
}
}
}
}
Skanda Narayanan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I changed this part of your code:
if (col == 21 && cell.getValue() == true) {//if changed to true protects row to end of data
let protRange = sheet.getRange(rownum, 1, 1, 22).protect().setWarningOnly(true).setDescription(rownum);
}
Your code does not check for existing protections. As a result, every time the checkbox is checked, it creates a new protection, which causes the main problem. To handle duplicate protections for the same row, I use the some() method before creating a new one to avoid duplicate entries.
I also converted rownum to a string using toString(), to ensure compatibility with setDescription().
Complete Code:
function onEdit() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getActiveSheet();
let cell = sheet.getActiveCell();
let col = cell.getColumn();
let rownum = cell.getRow();
if (col == 21 && cell.getValue() === true) {
let protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
let isAlreadyProtected = protections.some(protection => {
return protection.getRange().getRow() === rownum && protection.getRange().getNumRows() === 1;
});
if (!isAlreadyProtected) {
sheet.getRange(rownum, 1, 1, 22).protect()
.setWarningOnly(true)
.setDescription(rownum.toString());
}
} else if (col == 21 && cell.getValue() === false) {
let protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (let i = protections.length - 1; i >= 0; i--) {
if (protections[i].getDescription() === rownum.toString()) {
protections[i].remove();
}
}
}
}
Sample Output:
Reference:
Class Protection
some()
0