更新时间:2023-12-06 14:35:28
在很多帮助下,我可以按需运行它,这是主要代码:
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:
注释:
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,
});
}