A spreadsheet with multiple tabs have 10 editors/access. I wanted to lock some ranges in each tab from 8 of those editors.

When I ran google app script written below, it also lock the 8 editors from editing the tab name and even the tab color. Can advise on where i went wrong? I want them to still able to change the tab name and colour of the tab.

function addClassProtectionFor_Current(){ //Main function to run

  var currentclasstab = SpreadsheetApp.getActiveSheet();

  // Remove all range protections in the spreadsheet
  var protections = currentclasstab.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i  ) {
    var protection = protections[i];
    protection.remove();
  }

  var protection = currentclasstab.protect();
  //restrict editors to owner
  protection.getRange().getA1Notation();
  var eds = protection.getEditors();
  protection.removeEditors(eds);
  

  //Add Editors to give access to the protected ranges
  protection.addEditors(["me.gmail.com","[email protected]"]);

  //set unprotected ranges
  var ranges = protection.getUnprotectedRanges();

  //Ranges to leave unlocked
  var data = ["A5:V19","B23:V26","B29:V35","B39:V45"];

  data.forEach(res => { //LOOPS INTO EVERY ARRAY CONTAINING SPECIFIC RANGES
    ranges.push(currentclasstab.getRange(res));
    protection.setUnprotectedRanges(ranges); //REMOVES THE PROTECTION ON THE RANGE
  });
}

CodePudding user response：

I believe your goal is as follows.

• You want to protect all cells except for the cells of A5:V19,B23:V26,B29:V35,B39:V45 from the users.
• You want to make users change the tab name and the tab color while the cells are protected.
• You want to achieve this using Google Apps Script.

Issue and workaround:

In the current stage, unfortunately, it seems that "UnprotectedRanges" cannot be used for the protected sheet. In this case, the users cannot change the tab name and tab color. So, in your situation, as a workaround, in order to make users change the tab name and the tab color, it is required to protect all cells except for A5:V19,B23:V26,B29:V35,B39:V45. When this is reflected in your script, it becomes as follows.

Modified script:

function addClassProtectionFor_Current() {
  var currentclasstab = SpreadsheetApp.getActiveSheet();
  var protections = currentclasstab.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i  ) {
    var protection = protections[i];
    protection.remove();
  }
  var ranges = ["A1:4", "W5:19", "A20:A", "B20:22", "B27:28", "B36:38", "W23:26", "W29:35", "W39:45", "B46:"   currentclasstab.getMaxRows()];
  currentclasstab.getRangeList(ranges).getRanges().forEach(r => {
    const protection = r.protect();
    var eds = protection.getEditors();
    protection.removeEditors(eds);
    protection.addEditors(["me.gmail.com","[email protected]"]);
  })
}

• In this script, the values of ranges are all cells except for A5:V19,B23:V26,B29:V35,B39:V45. If you want to protect other cells, please add them to the array.

Reference:

• setUnprotectedRanges(ranges)