I currently have a multitude of docs each containing 30-60+ sheets. Each sheet is formatted identically with the same headers/column labels. In doing research I found its not possible to mass protect across multiple sheet within GUI. Bulk Sheet Manager addon can mass protect the entire sheets but not specific ranges. Can anyone help with the required script to protect all columns excluding K:M?
I think this script gets me close but without more indepth script knowledge it might as well be arabic for me.
var app = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActive();
function onOpen() {
app.createMenu("Ranges")
.addItem("Set ranges", "setRanges")
.addItem("Delete ranges", "deleteRanges")
.addToUi();
}
function setRanges() {
var text = app.prompt("Give string", "Like A1:A10, Sheet2!A4:B5, This is a test!A:A", app.ButtonSet.OK_CANCEL);
if(text.getSelectedButton() == app.Button.OK) {
var ranges = text.getResponseText().split(",");
for(var i = 0, iLen = ranges.length; i < iLen; i++) {
var value, range, index, sname, split, sh;
value = ranges[i].trim(), split = value.split("!");
switch(split.length) {
case 1:
range = value;
sh = ss.getActiveSheet();
break;
case 2:
range = value;
sh = ss.getSheetByName(String(split[0].trim()));
break;
default:
index = value.lastIndexOf("!");
range = "'" + value.slice(0, index) + "'!" + split[split.length - 1].trim();
sh = ss.getSheetByName(String(value.slice(0, index)));
break;
}
try {
sh.getRange(String(range))
.protect()
.setDescription("Range" + i);
} catch(e) {
throw "Range: " + (i) + " doesn't exist";
}
}
}
}
function deleteRanges() {
var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var j = 0, jLen = protections.length; j < jLen; j++) {
protections[j].remove();
}
}
setRanges
anddeleteRanges
. Did you write these or were they sourced elsewhere? You said that they get you "close"; are you able to describe what is required in order to complete the functions? AAMOI, do you have a plan for applying these functions to your other spreadsheets?