I made a code with @OMila aid to restrict some ranges for certain users while protecting the whole remaining ranges in sheet from their editing.. I want to check on the protection status of a sheet/range per for loop iteration, if it is protected ==> iteration++ (check next sheet), if not protected, run the script and protect ranges. The purpose is, when certain people make new sheets, I want the script to run automatically via a trigger, but when the number of sheets increase the execution time will increase per spreadsheet and will probably hit google quotations limit, so i need to make an optimized way to execute the script by putting an if condition to check the sheet protection status and do as described before. this is the code:
function Sheet_Ranges_Protection() {
var Veranda_Test = SpreadsheetApp.openById("Sheet ID");
var Veranda_Sheets = Veranda_Test.getSheets();
for(var SheetNumb = 0; SheetNumb < Veranda_Sheets.length; SheetNumb++) {
var me = Session.getEffectiveUser();
// Define ranges that will be protected for everyone
var range1 = Veranda_Sheets[SheetNumb].getRange(6, 1,
Veranda_Sheets[SheetNumb].getMaxRows(),
Veranda_Sheets[SheetNumb].getMaxColumns());
var range2 = Veranda_Sheets[SheetNumb].getRange(1, 8, 5,
Veranda_Sheets[SheetNumb].getMaxColumns());
var range3 = Veranda_Sheets[SheetNumb].getRange(1, 4, 5);
var ranges = [range1, range2, range3];
// Set protection for all the sheet minus QC/PLN ranges
for(var i = 0; i < ranges.length; i++) {
var rangeProtection = ranges[i].protect().setDescription('Range protection');
rangeProtection.addEditor(me);
rangeProtection.removeEditors(rangeProtection.getEditors());
if (rangeProtection.canDomainEdit()) {
rangeProtection.setDomainEdit(false);
}
}
var QC_Range = Veranda_Sheets[SheetNumb].getRange("E1:G5");
var PLN_Range = Veranda_Sheets[SheetNumb].getRange("A1:C5");
// Set protection for QC range
var QC_protection = QC_Range.protect().setDescription('QC protection');
QC_protection.removeEditors(QC_protection.getEditors());
QC_protection.addEditor('Editor1@gmail.com');
if (QC_protection.canDomainEdit()) {
QC_protection.setDomainEdit(false);
}
// Set protection for PLN range
var PLN_protection = PLN_Range.protect().setDescription('PLN protection');
PLN_protection.removeEditors(PLN_protection.getEditors());
PLN_protection.addEditor('Editor2@gmail.com');
if (PLN_protection.canDomainEdit()) {
PLN_protection.setDomainEdit(false);
}
}
}
Aucun commentaire:
Enregistrer un commentaire