Google-sheets – In Google Sheets how to duplicate a sheet along with its permission

google sheetsgoogle-apps-scriptgoogle-drive-sharinggoogle-sheets-protectionpermissions

In a Google Spreadsheet called Attendance there is a sheet called Template. The user duplicates this sheet, renames the sheet with current date and uses this sheet to mark attendance for students.
The Template sheet contains protected cells and the attendance is marked by entering Student's ID number in the space given (unprotected cells).
I use the following script to duplicate multiple sheets and rename them everyday:

function createDailyAttendance() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var refss = ss.getSheetByName("DataPointers");

    // Get the range Row and Column information.
  var dataRangeRow = refss.getRange("K2").getValue();
  //var dataRangeCol = ss.getRangeByName(ColName).getValue();


   // Get the range of cells that store Duplicate sheet name.
  var AttendanceDataRange = refss.getRange(dataRangeRow);

  var AttendanceObjects = AttendanceDataRange.getValues();

  var template = ss.getSheetByName('Template');

  for (var i=0; i < AttendanceObjects.length; i++) {

     // Put the sheet you want to create in a variable
     var sheet = ss.getSheetByName(AttendanceObjects[i]);

      // Check if the sheet you want to create already exists. If so,
      // log this and loop back. If not, create the new sheet.
        if (sheet) {
           Logger.log("Sheet " + AttendanceObjects[i] + "already exists");
        } else {
           template.copyTo(ss).setName(AttendanceObjects[i]);
           }
        }
  return;
}

This script helps me create multiple copies of sheets from Template but the duplicate copies do not retain the Cell/Range permissions.
Is there a way to add a loop function which extracts permission from Template and applies it every time the loop template.copyTo creates a sheet?

Best Answer

Scenario 1: template is a protected sheet with unprotected ranges

In the script below, I duplicate the sheet, get its protection of Sheet type, then protect the new sheet in the same way: same description, same type. If the protection is not just a warning, then remove all editors, and add those allowed for the original sheet. Finally, loop over unprotected ranges, remapping each of them (via getA1Notation) to the new sheet, and unprotect those. 

function duplicateProtectedSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  sheet = ss.getSheetByName("Sheet1");
  sheet2 = sheet.copyTo(ss).setName("My Copy"); 
  var p = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
  var p2 = sheet2.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 
  }
  var ranges = p.getUnprotectedRanges();
  var newRanges = [];
  for (var i = 0; i < ranges.length; i++) {
    newRanges.push(sheet2.getRange(ranges[i].getA1Notation()));
  } 
  p2.setUnprotectedRanges(newRanges);
}

Scenario 2: template is a sheet with protected ranges

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. 
function duplicateSheetWithProtections() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  sheet = ss.getSheetByName('Template');
  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());
      p2.addEditors(p.getEditors());
      // p2.setDomainEdit(p.canDomainEdit()); //  only if using an Apps domain 
   }
  }
}

It's also possible to have protected ranges within a protected sheet, in which case you would need to combine the two functions (do everything that each of them does, except of course you'll be duplicating the sheet only once.)

Related Topic