-1

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","you@gmail.com"]);

  //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
  });
}
dracomagmus
  • 3
  • 3
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand your question. What do you want to lock in your Google Spreadsheet from the users? – Tanaike Dec 24 '21 at 01:58
  • I would like to protect all cells except these ranges A5:V19,B23:V26,B29:V35,B39:V45 from the 8 users. The other 2 users have access to everything. My google script did just that, but it also prevent the 8 users to change the tab name and the color. How do I protect the sheet (except some cells) but still give the 8 users access to change the tab name or change the tab color. – dracomagmus Dec 24 '21 at 02:56
  • Thank you for replying. I have to apologize for my poor English skill again. I would like to confirm my understanding for your replying. In your goal, you want to lock the cells `A5:V19,B23:V26,B29:V35,B39:V45` from the users, and also you want to lock to change the tab name and the tab color from the users. Is my understanding correct? – Tanaike Dec 24 '21 at 03:03
  • Lock: all cells. Unlock: A5:V19,B23:V26,B29:V35,B39:V45 and allow all users to edit tab name and color. – dracomagmus Dec 24 '21 at 03:23
  • Thank you for replying. From your replying, I proposed a modified script as an answer. Could you please confirm it? If I misunderstood your question and that was not useful, I apologize. – Tanaike Dec 24 '21 at 03:35

1 Answers1

0

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","you@gmail.com"]);
  })
}
  • 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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165