Google-sheets – Script to place duplicate Google sheet beside the sheet it copies (instead of the end)

google sheetsgoogle-apps-script

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 
   }
  }
}

Best Answer

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.

Related Topic