8

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?

6

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.)

  • I inserted your suggestion in my loop and tested in Scenario 1, I got an error message TypeError: Cannot call method "protect" of null. I'm getting this error because from this line var p2 = sheet.protect();. – Arvind Nov 26 '15 at 18:14
  • 1
    So, that was the line sheet2.protect(); ? Then it means sheet2 is null, so you should look at the line where it's defined. – user79865 Nov 26 '15 at 18:17
  • In my code sheet2 is referred as sheet. It is defined as var sheet = ss.getSheetByName(AttendanceObjects[i]); – Arvind Nov 26 '15 at 18:21
  • Anyway. Debugging your code is your job, not mine. – user79865 Nov 26 '15 at 18:23

protected by Community Mar 28 '18 at 21:15

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.