1

I have two Google Sheet script functions that both work independently: 1) duplicates a template sheet, populating each duplicate sheet from a separate row of data in a source range; 2) makes a single duplicate of the template sheet and preserves its editing permissions - it's a protected sheet with unprotected ranges (using this answer). I am trying to combine the two so that each copy of the template using the source data has the same permissions as the template sheet. I cannot work out how to nest one function inside the other. The two script functions are:

1)

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

// Get the range of cells that store the data.
  var teamDataRange = ss.getRangeByName("SheetName");
  var teamObjects = teamDataRange.getValues();

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

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

    // Put the sheet you want to create in a variable
    var duplicate = ss.getSheetByName(teamObjects[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 (duplicate) {
           Logger.log("Sheet " + teamObjects[i] + "already exists");
        } else {

          if (teamObjects[i] == '') {
           break;
        } else {
           template.copyTo(ss).setName(teamObjects[i]);
           }
        }
  }

  return;
} 

and 2)

function duplicateProtectedSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  template = ss.getSheetByName("TemplateTeamSheet");
  duplicate = template.copyTo(ss).setName("My Copy");

  var p = template.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
  var p2 = duplicate.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(duplicate.getRange(ranges[i].getA1Notation()));
  } 
  p2.setUnprotectedRanges(newRanges);

}  

How do I use variables p and p2 from the second function in the first function so that each instance of template.copyTo(ss).setName(teamObjects[i]) preserves the template sheet's permissions?

0

Figured it out

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

// Get the range of cells that store team data.
  var teamDataRange = ss.getRangeByName("SheetName");
  var teamObjects = teamDataRange.getValues();

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

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

    // Put the sheet you want to create in a variable
    var duplicate = ss.getSheetByName(teamObjects[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 (duplicate) {
           Logger.log("Sheet " + teamObjects[i] + "already exists");
        } else {

          if (teamObjects[i] == '') {
           break;
        } else {
           template.copyTo(ss).setName(teamObjects[i]).showSheet();


var duplicatesheet = ss.getSheetByName(teamObjects[i]);


var p = template.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];          
var p2 = duplicatesheet.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(duplicatesheet.getRange(ranges[i].getA1Notation()));
  } 
  p2.setUnprotectedRanges(newRanges);


        }
        }          
}  
return;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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