2

Is there any way to modify my script to have the sheet it's copying insert the new sheet immediately to the right of the one it's copying? I have tons of tabs in the file, and it's starting to get really annoying to scroll all the way over to the end every time I create a new one.

function duplicateProtectedSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  sheet = ss.getSheetByName("Jun 24");
  sheet2 = sheet.copyTo(ss).setName("Jun 30"); 
  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);
    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 
   }
  }
}  
0

The class Sheet method copyTo() doesn't offer that. Thankfully, there's a Spreadsheet method that does; insertSheet(sheetName, sheetIndex, options)

So take your line

sheet2 = sheet.copyTo(ss).setName("Jun 30");

and instead use something resembling

sheet2 = ss.insertSheet("Jun 30", sheet.getIndex(), {template:sheet});

The middle parameter is the index of the new sheet, and they're numbered left-to-right and zero-indexed. So when creating this new sheet, we cite the existing index of the source sheet, and so the duplicate is created just adjacent, right.

  • Only took 10 months to find that. Er, better late than never? We've helped "the next guy"? – Joel Reid Apr 24 '19 at 13:33

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.