Looking for a script to save protections on sheets when copied

Question

I am creating workbooks for teachers to monitor students progress on a specific skill. We have a workbook with various templates they may need to copy the sheets/graphs they need for each child. These sheets are protected without the exception of the cells the teachers need to access in order to input student data. Right now when we use the COPY TO a new spreadsheet, we lose those protections. (Most of our K-2 teachers are not tech-savvy enough to have full access to a sheet with a graph.) There has to be a way to create some type of protection that carries over to copied files that is foolproof. Thanks to anyone who knows how to do this.

I tried using this code and it does copy my sheet and keep it protected, BUT how can I get it to stay protected when the COPY TO is used to another existing workbook?

function duplicateProtectedSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  sheet = ss.getSheetByName("B - General Info");
  sheet2 = sheet.copyTo(ss).setName("My Copy1"); 
  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);
}

Here is an example of my current workbook with the templates teachers will be copied to their own workbooks. https://docs.google.com/spreadsheets/d/1JB95bdu4Qatx7uMZrQFP7GkiKBuuB47PCV8ZjyQWmHo/edit?usp=sharing

Possible duplicate of How to duplicate a sheet with protected ranges? — AMolina
Aside from the flagged question, this answer provides an in depth approach to this issue. — AMolina
I have used that script, but it only copies to to the same spreadsheet (workbook). (Unless, I am doing it wrong!) I need it to go to a different existing spreadsheet (workbook) that the user chooses. — Michelle Beitzel
you mean the same sheets (pages) in the same Spreadsheet (file)? I just want to make sure. And what you want it to do the copying into a new Spreadsheet with the same protections included, correct? — AMolina
No. I want the same sheet (page) to go copy to an other existing Spreadsheet (file) with the same protections. There is a set of graphs (sheets for teachers to input student data that creates a graph). The teachers need to be able to add the graphs (sheets) to individual student Spreadsheets (files). The teachers will select which graphs (sheets) they want to add to which file. Basically, I want teachers to select the graph they want, click the COPY TO button and add to an existing spreadsheet, but wanted to maintain the protection. Not sure if this is even possible. — Michelle Beitzel

AMolina AMolina · Accepted Answer · 2019-09-11T11:32:58

try this:

function test(){
  var ss = SpreadsheetApp.getActiveSheet();
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  var newSheet = ss.copyTo(SpreadsheetApp.getActive());
  var prot;
  var p;
  var editors;

  for (var i = 0; i < protections.length; i++){
    prot  = protections[i];
    p = newSheet.getRange(prot.getRange().getA1Notation()).protect();
    p.setDescription(prot.getDescription());
    editors = prot.getEditors();
    for (var j = 0; j < editors.length; j++){
      p.addEditor(editors[i]);
    }
  }
}

This code copies the sheet and stores the original's protections, then it set the same protections in the newly-created sheet. for you it would be a matter of setting ss and newSheet to the ones you intend to use.

Looking for a script to save protections on sheets when copied

1 Answers