Google Sheets Scripts - Copy whole sheet protection

Question

I am using a solution given here to copy sheets while maintaining the protection. I have added the Google Sheets API - V4 with name Sheets.

However, it throws error -

GoogleJsonResponseException: API call to sheets.spreadsheets.batchUpdate failed with error: Invalid requests[0].addProtectedRange: The range you're trying to exclude must be within "A".

After Some trial and error I found that because my protection is whole sheet except some ranges, it gives error. When I protect only few ranges, the code works fine.

Now the solution here does duplicate sheet with the whole sheet protection. However, it's for single sheet. I need to combine these two but I am struggling.

The sheet object returned by copyTo() and Sheets.Spreadsheets.get(id) are different, the second one does not have protect() method. So I do not have a specific error here, but need solution directed towards combining two solutions. I need to duplicate multiple sheets while maintaining whole sheet protection without some ranges.

Answer 1

Issue:

The linked code pertains to the multiple protection ranges, not the sheet protection with exceptions. As you can see, the code included addProtectedRange and it conflicts with your existing protections since the code assumed you to have Range but instead you have Sheet protections which are not compatible with each other.

Use this code instead:

Code:

function duplicateSheetWithProtections() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 

  // Template sheet
  var sheet = ss.getSheetByName("Template");
  var p = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];

  // Output sheet, and protect the sheet by default
  var sheet2 = sheet.copyTo(ss).setName("Template Copy"); 
  var p2 = sheet2.protect();
  
  // Copy protection properties
  p2.setDescription(p.getDescription());
  p2.setWarningOnly(p.isWarningOnly());  

  if (!p.isWarningOnly()) {
    // Copy editors
    p2.removeEditors(p2.getEditors());
    p2.addEditors(p.getEditors());
    // Copy domain settings if using Apps domain
    // p2.setDomainEdit(p.canDomainEdit()); 
  }

  // Get all unprotected ranges on template
  var ranges = p.getUnprotectedRanges();
  var newRanges = [];
  for (var i = 0; i < ranges.length; i++) {
    newRanges.push(sheet2.getRange(ranges[i].getA1Notation()));
  } 

  // Unprotect them in output
  p2.setUnprotectedRanges(newRanges);
}  

Output:

EDIT:

• If you want to have multiple sheets duplicated from template, then just modify the function by adding a parameter/variable as your sheetName. Code should be like this:

function duplicateSheetWithProtections(sheetName) {

and

var sheet2 = sheet.copyTo(ss).setName(sheetName);

• Then call the function inside a loop.

function main() {
  var sheetNames = ["A", "B", "C"];
  sheetNames.forEach(function (sheetName){
    duplicateSheetWithProtections(sheetName);
  });
}

Reference:

• Scenario 1: template is a protected sheet with unprotected ranges