且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

如何为特定选项卡的整个工作表保护添加编辑器?

更新时间: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:

注释:

  • 编辑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,
  });
}