I have two separate spreadsheets, one for admin and one for user. The admin can lock or unlock certain ranges for either Fri, Sat, Sun or Holiday via onEdit trigger (ie fridaycheck(). When checkmark is removed those same ranges are removed. The problem is when admin checkmarks all the boxes at once and before any of the separate scripts can complete. When they finally do complete multiple protection instances exist for each of the days. When admin checks one day at a time and script is allowed to finish, everything works fine. Looking to try and prevent multiple protection instances from being inserted OR remove all instances when the check mark is removed. Here are the scripts for Fri onEdit check, lock and unlock. Any suggestions would be greatly appreciated! Thanks, Paul
function fridaycheck() {
let admincheck = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Admin")
let activeCell = admincheck.getRange("AE9")
let reference = activeCell.getA1Notation()
let sheetName = activeCell.getSheet().getName()
let activeValue = activeCell.getValue()
if (reference == "AE9" && sheetName == "Admin" && activeValue == true)
LockFriday();
else
UnlockFriday();
}
function LockFriday() {
var protectRange = 'C8:D60';
var description = 'AA Lock Friday Cells';
var spreadsheet =
SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxx');
spreadsheet.getRange(protectRange).activate();
spreadsheet.setCurrentCell(spreadsheet.getRange('D60'));
var p = spreadsheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).find(e =>
e.getRange().getA1Notation() == protectRange && e.getDescription() == description);
if (p) return;
var protection = spreadsheet.getRange(protectRange).protect();
var all = protection.getEditors();
protection.setDescription(description).removeEditors(all);
protection.setDescription(description).addEditor('[email protected]');
protection.setDescription(description).addEditor('[email protected]');
var ss = SpreadsheetApp.getActive();
var checkbox = ss.getRange("AE9").getValue();
if(checkbox==false){
ss.getRange("AE9").setValue("true");
}
}
function UnlockFriday() {
var spreadsheet = SpreadsheetApp.openById('XXXXXXXXXXXXX');
spreadsheet.getRange('C8:D60').activate();
var allProtections =
spreadsheet.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
var matchingProtection1 = allProtections.find(existingProtection => existingProtection.getRange().getA1Notation() == 'C8:D60');
if (!matchingProtection1) return;
matchingProtection1.remove();
var ss = SpreadsheetApp.getActive();
var checkbox = ss.getRange("AE9").getValue();
if(checkbox==true){
ss.getRange("AE9").setValue("false");
}
}