How to duplicate a sheet with protected ranges?

Question

I have built a template sheet that I will duplicate every week. I noticed that when I go to duplicate it, none the protected ranges are copied along with it. Is it possible to use a Google Apps script to copy the exact same protected ranges into the duplicated sheet? It would save me lots of time every week.

Answer 1

Note: A similar question was posted on Web Apps, so I am adapting a part of my answer there to the present case.

---

Using sheet.getProtections method, you can get the array of protections on a given sheet, and loop over them, creating their analogs on the target sheet. This is somewhat annoying because there seems to be no method to simply clone a protection to another range. (One can change the range of protection, but that would move it to the new range, instead of copying.)

So, in the function below I do the following:

1. Get the A1 notation of each protected range with p.getRange().getA1Notation();
2. Protect the corresponding range of the target sheet with p2 = sheet2.getRange(rangeNotation).protect();
3. Set the properties of new protection p2 according to the properties of original protection p. This includes removing/adding editors if the protection is not just of the warning type.

function duplicateSheetWithProtections() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Template');
  var sheet2 = sheet.copyTo(ss).setName('My Copy'); 
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var p = protections[i];
    var rangeNotation = p.getRange().getA1Notation();
    var p2 = sheet2.getRange(rangeNotation).protect();
    p2.setDescription(p.getDescription());
    p2.setWarningOnly(p.isWarningOnly());
    if (!p.isWarningOnly()) {
      p2.removeEditors(p2.getEditors());  // remove editors 
      p2.addEditors(p.getEditors());      // except those permitted for original
      // p2.setDomainEdit(p.canDomainEdit()); //  only if using an Apps domain 
    }
  }
} 

Answer 2

Here is adjusted code from above which will prompt you for the name of the sheet you would like to copy and what you want the new name to be:

function duplicateSheetWithProtections() {
  var ui = SpreadsheetApp.getUi();
  var oldSheetName = ui.prompt('Sheet to Copy?');
  var newSheetName = ui.prompt('New Sheet Name?');
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  sheet = ss.getSheetByName(oldSheetName.getResponseText());
  sheet2 = sheet.copyTo(ss).setName(newSheetName.getResponseText()); 
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var p = protections[i];
    var rangeNotation = p.getRange().getA1Notation();
    var p2 = sheet2.getRange(rangeNotation).protect();
    p2.setDescription(p.getDescription());
    p2.setWarningOnly(p.isWarningOnly());
    if (!p.isWarningOnly()) {
      p2.removeEditors(p2.getEditors());
      p2.addEditors(p.getEditors());
      // p2.setDomainEdit(p.canDomainEdit()); //  only if using an Apps domain 
   }
  }

Answer 3

I found a solution to duplicating a sheet with permissions here:

https://webapps.stackexchange.com/questions/86984/in-google-sheets-how-do-i-duplicate-a-sheet-along-with-its-permission/87000#87000

It looks to have solved it, although I am busy with another problem currently of hiding columns with permissions.

Regards

Answer 4

the Protection class can be used to protect various ranges in the sheet and possibly even better you can protect the whole sheet and then unprotect specific ranges where input is desired.