Best way to maintain Google sheet permissions when creating a copy

Question

each month I create a copy of a google sheet file. It has quite a few sheets that have permissions - protected formula fields. The issue is, when copy is created only the owner can edit the protected fields, and if I want to add others it takes quite a while. There are two possible solutions relying on File / Copy / Share with the same people, however if I want to add additional people it will take quite a while.

1. protect entire sheet, then I cold have only one permission per sheet, as it allows multiple ranges to be excluded. it is easy to add additional ranges and people. not sure if I may have some issues protecting entire sheet?
2. protect cells only, is there a way to group them or add more than one range to one permission?

Is there a better way to maintain and add people to many permissions? Thanks

Answer 1

You need to create a script that get an array of the protected ranges, and copy the same protected ranges to the new sheet.

function duplicateSheetWithProtections() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  sheet = ss.getSheetByName('original');
  sheet2 = sheet.copyTo(ss).setName('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());
      p2.addEditors(p.getEditors());
   }
  }
} 

You can check more information on Google's documentation