How to add Editors to whole sheet protection for specific tabs?

Question

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.

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).

Spreadsheet Demo

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) ?

ADDED THE FOLLOWING SCRIPT

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(['[email protected]']); 
  }

Thanks a lot

Answer 1

With a lot of help, I got it work as wanted, here is the main code:

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:

Notes:

• The script fires as soon you edit the Setup_Protection sheet
• If you do not mention some of the tabs in the Setup Sheet, by default it will be protected them
• if you list them but you leave the cell next to it without email, the script will start to run and will stop at the tab where no email es mentioned.

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);
  }

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