Duplicating Template Sheet Many Times At Once While Keeping The Range Protections

I'm using a code to duplicate a template sheet with multiple range protections within it, Currently, I'm running the script to create one by one the new tabs. Can anyone help me wi

Solution 1:

How about this modification? I think that you can achieve what you want using Sheets API. The flow of this sample script is as follows.

When you use this script, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

Flow:

1. Set copied sheet names. For example, those are ["A", "B", "C",,]. This is from your question.
2. Copy template sheet using the sheet names.
3. Retrieve protected ranges from Template sheet using Sheets API.
4. Create request body.
5. Set protected ranges to copied sheets using Sheets API.

By this flow, the protected ranges of Template sheet can be copied to the all copied sheets by only one API call.

Modified script:

functionduplicateSheetWithProtections() {
  var sheetNames = ["A", "B"]; // Please set copied sheet names here.var ss = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = ss.getId();
  var sheet = ss.getSheetByName('Template');

  // Copy template sheet.var copiedSheetIds = sheetNames.map(function(e) {return sheet.copyTo(ss).setName(e).getSheetId()});

  // Retrieve protected ranges from Template sheet.var sheets = Sheets.Spreadsheets.get(spreadsheetId).sheets;
  var templateSheet = sheets.filter(function(e) {return e.properties.title == "Template"});
  var protectedRanges = templateSheet[0].protectedRanges;

  // Create request body.var resources = copiedSheetIds.map(function(e) {
    return protectedRanges.map(function(f) {
      var obj = JSON.parse(JSON.stringify(f));
      delete obj.protectedRangeId;
      if (obj.warningOnly) delete obj.editors;
      obj.range.sheetId = e;
      return {"addProtectedRange": {"protectedRange": obj}};
    });
  });
  resources = Array.prototype.concat.apply([], resources);

  // Set protected ranges to copied sheetsSheets.Spreadsheets.batchUpdate({"requests": resources}, spreadsheetId);
}

References:

• spreadsheets.get
• spreadsheets.batchUpdate

If I misunderstand your question, please tell me. I would like to modify it.