Currently, I have a Google Script where I have to protect multiple ranges for multiple people across multiple sheets in one worksheet.
Here is the code I have right now:
function setPermissions () {
for (var number = 1; number < 30; number++) {
var n = number.toString();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(n);
var protectionDescription = 'Initial Sheet Protection - Script'
var protectedRangesArray = [
'A1:F5',
'G1:G4',
'H1:K5',
'L1:L2',
'M1:N5',
'A6:P8',
'A7:B61',
'A62:P62',
'O9:O61',
'F9:F11',
'A1:P2'
];
for (var i = 0; i < protectedRangesArray.length; i++) {
var range = sheet.getRange(protectedRangesArray[i]);
var protection = range.protect().setDescription(protectionDescription);
protection.removeEditors(protection.getEditors());
};
// = Everything else is repeated from here so you really only need to look at the above code.
var protectedRangesArray = [
'C9:E61',
'F12:F61',
'G9:H61',
'P9:P61',
'O3:P5'
];
for (var i = 0; i < protectedRangesArray.length; i++) {
var range = sheet.getRange(protectedRangesArray[i]);
var protection = range.protect().setDescription(protectionDescription);
protection.addEditors([
saEmail,
amEmail,
bmEmail,
meEmail
]);
protection.removeEditors([
brEmail
]);
};
// =====
var protectedRangesArray = [
'K9:N61'
];
for (var i = 0; i < protectedRangesArray.length; i++) {
var range = sheet.getRange(protectedRangesArray[i]);
var protection = range.protect().setDescription(protectionDescription);
protection.addEditors([
bmEmail,
brEmail
]);
protection.removeEditors([
saEmail,
amEmail,
meEmail
]);
};
// =====
var protectedRangesArray = [
'G5:G5'
];
for (var i = 0; i < protectedRangesArray.length; i++) {
var range = sheet.getRange(protectedRangesArray[i]);
var protection = range.protect().setDescription(protectionDescription);
protection.addEditors([
amEmail,
bmEmail,
meEmail
]);
protection.removeEditors([
saEmail,
brEmail
]);
};
// =====
var protectedRangesArray = [
'L3:L3'
];
for (var i = 0; i < protectedRangesArray.length; i++) {
var range = sheet.getRange(protectedRangesArray[i]);
var protection = range.protect().setDescription(protectionDescription);
protection.addEditors([
amEmail,
bmEmail,
meEmail
]);
protection.removeEditors([
saEmail,
brEmail
]);
};
// =====
var protectedRangesArray = [
'L4:L4'
];
for (var i = 0; i < protectedRangesArray.length; i++) {
var range = sheet.getRange(protectedRangesArray[i]);
var protection = range.protect().setDescription(protectionDescription);
protection.addEditors([
bmEmail,
meEmail
]);
protection.removeEditors([
saEmail,
amEmail,
brEmail
]);
};
// =====
var protectedRangesArray = [
'L5:L5',
'I9:J61'
];
for (var i = 0; i < protectedRangesArray.length; i++) {
var range = sheet.getRange(protectedRangesArray[i]);
var protection = range.protect().setDescription(protectionDescription);
protection.addEditors([
meEmail
]);
protection.removeEditors([
saEmail,
amEmail,
bmEmail,
brEmail
]);
};
};
};
Understandably, the code takes a very long time.
What I'm trying to figure out is how to reduce the number of getRange() calls I make throughout the script. From what I understand, that slows down a script tremendously.
I tried var protection = range[0][0].protect().setDescription(protectionDescription);, after defining var range as sheet.getRange(1,1,62,16) but it gives the error Cannot read property "0" from undefined.
Is there anyway to speed up this function? Right now, I'm doing one sheet at a time (each sheet takes about 5 minutes).
EDIT: Here is the updated (and much faster code) for anyone that cares (thanks BMcV):
function setPermissions() {
var worksheet = SpreadsheetApp.getActiveSpreadsheet();
var protectionDescription = 'Initial Sheet Protection - Script';
var protectedRangesArray = [];
var addEditorsArray = [];
var removeEditorsArray = [];
for (var number = 0; number < 30; number++) {
var sheet = worksheet.getSheetByName(number.toString());
protectedRangesArray = [
[//0
'A1:F5',
'G1:G4',
'H1:K5',
'L1:L2',
'M1:N5',
'A6:P8',
'A7:B61',
'A62:P62',
'O9:O61',
'F9:F11',
'A1:P2'],
[//1
'C9:E61',
'F12:F61',
'G9:H61',
'P9:P61',
'O3:P5'],
[//2
'K9:N61'],
[//3
'G5:G5'],
[//4
'L3:L3'],
[//5
'L4:L4'],
[//6
'L5:L5',
'I9:J61']
];
addEditorsArray = [
[], //0
[saEmail, amEmail, bmEmail, meEmail], //1
[bmEmail, brEmail], //2
[amEmail, bmEmail, meEmail], //3
[amEmail, bmEmail, meEmail], //4
[bmEmail, meEmail], //5
[meEmail] //6
];
removeEditorsArray = [
[saEmail, amEmail, bmEmail, brEmail, meEmail], //0
[brEmail], //1
[saEmail, amEmail, meEmail], //2
[saEmail, brEmail], //3
[saEmail, brEmail], //4
[saEmail, amEmail, brEmail], //5
[saEmail, amEmail, bmEmail, brEmail] //6
];
protectRanges(sheet, protectionDescription, protectedRangesArray, addEditorsArray, removeEditorsArray)
};
};
function protectRanges(sheet, protectionDescription, protectedRangesArray, addEditorsArray, removeEditorsArray) {
var i = 0, n,
len = protectedRangesArray.length,
range, protection;
for (i; i < len; i++) {
n = 0
for (n; n < protectedRangesArray[i].length; n++) {
range = sheet.getRange(protectedRangesArray[i][n]);
protection = range.protect().setDescription(protectionDescription);
protection.addEditors(addEditorsArray[i]);
protection.removeEditors(removeEditorsArray[i]);
}
}
}
There are a few things which may help make the script more efficient and most likely, faster.
Take advantage of hoisting. In many areas of this script, variables are defined multiple times. It would be better to do this:
var number = 1; // Some code here involving number
number = 10; // notice no var at the start!
This function should also be split up into a few helper functions. This will help make your code more readable and easier to maintain.
function protectRanges(sheet, protectionDescription, protectedRangesArray) {
var i = 0,
len = protectedRangesArray.length,
range, protection;
for (i; i < len; i++) {
range = sheet.getRange(protectedRangesArray[i]);
protection = range.protect().setDescription(protectionDescription);
protection.removeEditors(protection.getEditors());
}
}
This way, at least you don't have to write the code again and again. This concept is called DRY (Don't Repeat Yourself). Whenever possible, move repetitive code to a separate function. Now, it will be easier to find ways to improve the performance.
One thing that may help would be to move the spreadsheet definition outside of the loop. Currently, SpreadsheetApp.getActiveSpreadsheet() is called 30 times and only needs to be called 1 time.
The main thing would be to simplify the function generally.