如何为特定选项卡的整个工作表保护添加编辑器? [英] How to add Editors to whole sheet protection for specific tabs?
问题描述
我正在尝试寻找一种替代我现在正在手动执行的操作的方法，这非常累人(109个电子表格，每个电子表格包含许多选项卡)，这是允许编辑特定选项卡的特定编辑器(与之一起使用使用脚本共享了整个电子表格.
I'm trying to find an alternative to what I'm doing manually right now, it's quite exhausting (109 spreadsheets each containing many tabs), and that is to give permission to edit specific tabs for specific Editors (with whom the whole spreadsheet has been shared with) using a script.
我有一个带有选项卡(A，B，C，D，....)的电子表格，这些选项卡包含许多受保护的范围(例如A！1:2)，除了我自己以外，所有其他人都应该保持受保护的状态.但是，每个选项卡都有(整个)工作表保护，我可以在其中(手动)选择允许在该选项卡中进行编辑的用户(这是我在这里发现的一项技巧，目的是防止同一电子表格的许多编辑者能够编辑受保护的标签.
I have a spreadsheet with the tabs (A,B,C,D,....), these tabs contains many protected ranges (e.g. A!1:2) and should remain protected for all except myself. However, each tab has also a (whole) sheet protection where I would select (manually) the user who is allow to edit within that tab (which was a trick that I found here to prevent many Editors of the same spreadsheet to be able to edit tabs that are protected).
使用脚本: 是否可以有一个工作表设置，在该设置中我可以按一定顺序输入当前编辑者的电子邮件(例如，包含选项卡名称的列，在其旁边是允许编辑它们的电子邮件)?
Using a script: Is it possible to have a Sheet setup where I would input the emails of the current editors in a certain order (for example a column containing the names of the tabs and next to them the emails of the ones who are allowed to edit them) ?
添加了以下脚本
function SetProtection() {
var ss = SpreadsheetApp.getActive();
//removes sheet protection
var protections = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET);
for (var i = 0; i < protections.length; i++) {
var protection = protections[i];
if (protection.canEdit()) {
protection.remove();
}
}
var sh7 = ss.getSheetByName("Sheet1");
var protection = sh7.protect().setDescription('Whole Sheet Protected');
//protects whole sheet
protection.addEditors(['test@gmail.com']);
}
非常感谢
推荐答案
在很多帮助下，我可以按需运行它，这是主要代码:
With a lot of help, I got it work as wanted, here is the main code:
特别感谢RENO BLAIR的巨大帮助，以及其他也想提供帮助的人(@Tanaike ++)
Special thanks to RENO BLAIR for his tremendous help, and anyone else who tried to help as well (@Tanaike ++)
我共享代码，也许对某些人可能有益:
I share the code, maybe it can be beneficial for some people:
注释:
- 编辑Setup_Protection工作表后，脚本便会触发
- 如果您在设置表中未提及某些标签，则默认情况下它将受到保护
- 如果您列出了它们，但是您将其旁边的单元格保留了下来而没有电子邮件，则该脚本将开始运行，并将停止在未提及电子邮件的选项卡上.
CODE.gs
var environment = {
protectionConfigSheetName: "Setup_Protection",
};
// Script fires when the Setup_Protection SHEET is edited
function onEdit(e) {
if (e.range.getSheet().getName() === environment.protectionConfigSheetName) resetSpreadsheetProtections();
}
function removeSpreadsheetProtections(spreadsheet) {
[
SpreadsheetApp.ProtectionType.SHEET,
//SpreadsheetApp.ProtectionType.RANGE, // I don't want to remove the Range Protections that I will set up in each tab
].forEach(function (type) {
return spreadsheet.getProtections(type).forEach(function (protection) { return protection.remove(); });
});
}
function getProtectionConfig(spreadsheet) {
var protectionConfigSheetName = "Setup_Protection";
var sheet = spreadsheet.getSheetByName(environment.protectionConfigSheetName);
var values = sheet.getDataRange().getValues();
var protectionConfig = values
.slice(1)
.reduce(function (protectionConfig, _a) {
var targetSheetName = _a[0], emailAddress = _a[1];
var config = protectionConfig.find(function (_a) {
var sheetName = _a.sheetName;
return sheetName === targetSheetName;
});
var editors = emailAddress.split(",");
if (config)
config.editors = config.editors.concat(editors);
else
protectionConfig.push({
sheetName: targetSheetName,
editors: editors.slice()
});
return protectionConfig;
}, []);
return protectionConfig;
}
function setSpreadsheetProtections(spreadsheet, protectionConfig) {
spreadsheet.getSheets().forEach(function (sheet) {
var protection = sheet.protect();
protection.removeEditors(protection.getEditors().map(function(editor) {
return editor.getEmail();
}));
var currentSheetName = sheet.getName();
var config = protectionConfig.find(function (_a) {
var sheetName = _a.sheetName;
return sheetName === currentSheetName;
});
if (config)
protection.addEditors(config.editors);
});
}
function resetSpreadsheetProtections() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var protectionConfig = getProtectionConfig(spreadsheet);
removeSpreadsheetProtections(spreadsheet);
setSpreadsheetProtections(spreadsheet, protectionConfig);
}
还有另一个名为Polyfill的文件(也是必需的):
There is another file as well called Polyfill (and is also needed):
Polyfill.gs
// https://tc39.github.io/ecma262/#sec-array.prototype.find
if (!Array.prototype.find) {
Object.defineProperty(Array.prototype, "find", {
value: function(predicate) {
// 1. Let O be ? ToObject(this value).
if (this == null) {
throw new TypeError('"this" is null or not defined');
}
var o = Object(this);
// 2. Let len be ? ToLength(? Get(O, "length")).
var len = o.length >>> 0;
// 3. If IsCallable(predicate) is false, throw a TypeError exception.
if (typeof predicate !== "function") {
throw new TypeError("predicate must be a function");
}
// 4. If thisArg was supplied, let T be thisArg; else let T be undefined.
var thisArg = arguments[1];
// 5. Let k be 0.
var k = 0;
// 6. Repeat, while k < len
while (k < len) {
// a. Let Pk be ! ToString(k).
// b. Let kValue be ? Get(O, Pk).
// c. Let testResult be ToBoolean(? Call(predicate, T, « kValue, k, O »)).
// d. If testResult is true, return kValue.
var kValue = o[k];
if (predicate.call(thisArg, kValue, k, o)) {
return kValue;
}
// e. Increase k by 1.
k++;
}
// 7. Return undefined.
return undefined;
},
configurable: true,
writable: true,
});
}
这篇关于如何为特定选项卡的整个工作表保护添加编辑器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
