Does copyTo work across different spreadsheet files?

Yes. Pass a different Spreadsheet object to copyTo instead of the active one: source.copyTo(SpreadsheetApp.openById('TARGET_ID')). The returned Sheet object belongs to the target file, so call setName on that reference. Note that cross-file copies do not carry over named ranges that reference the source file.

Why does getSheetByName return null instead of throwing when the sheet is missing?

That is intentional API design. getSheetByName is a lookup; a miss is not an error, it is just an absent result. setName, by contrast, enforces a constraint on an object that already exists, so a violation throws. The two methods have different failure contracts, which is why you need both null-checks in the script above.

Can I rename a sheet to a name that differs only in letter case?

No. Google Sheets treats tab names as case-insensitive for uniqueness. Renaming 'Summary' to 'summary' on the same spreadsheet throws the same collision error as renaming it to an exact duplicate.

The copied tab always lands at the end. How do I move it right after the source?

After setName, call ss.moveActiveSheet with the target index. Get the source's current index first: var targetIndex = source.getIndex() + 1; ss.setActiveSheet(copy); ss.moveActiveSheet(targetIndex). getIndex() is one-based, so adding 1 places the copy immediately to the right of the source.

// Sheets · Apps Script

Duplicate a sheet tab and rename it in Google Sheets.

How to copy a Google Sheets tab and rename it with Apps Script, including the getSheetByName guard that prevents setName from throwing on a name collision.

“I want to copy an existing sheet tab and give the copy a specific name without the script crashing or leaving a stray "Copy of" tab behind.”

The script

copy · paste · trigger

duplicateAndRename.gs

Apps Script

// Duplicate a named sheet tab and rename the copy.
// Throws if the source sheet does not exist.
function duplicateAndRename(sourceName, newName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Guard: bail if the target name is already taken
  if (ss.getSheetByName(newName) !== null) {
    throw new Error('A sheet named "' + newName + '" already exists.');
  }

  var source = ss.getSheetByName(sourceName);
  if (source === null) {
    throw new Error('Source sheet "' + sourceName + '" not found.');
  }

  var copy = source.copyTo(ss);
  copy.setName(newName);
  return copy;
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why setName silently kills your script

Apps Script's setName throws a generic "Service Spreadsheets failed while accessing document" error when the name you pass already belongs to another tab. Google Sheets does not allow two tabs to share a name, and the API enforces that constraint at call time, not at validation time. The script dies, the quota-copied sheet sits in your spreadsheet as "Copy of [source]" at the far right, and nothing cleans it up.

The first time I hit this it was in a weekly reporting script that duplicated a template tab and named it after the current week. It worked fine for months, then someone manually created a tab with the same name and the next Monday run left three orphan copies before anyone noticed. The fix is one getSheetByName null-check before you ever call setName.

What copyTo actually produces

source.copyTo(ss) appends a full clone of the source sheet to the same spreadsheet and returns the new Sheet object. The clone is named "Copy of [sourceName]" by default. All cell values, formulas, formatting, conditional formatting rules, and named ranges scoped to that sheet are copied. The copy lands at the last tab position regardless of where the source sits.

If you need the copy at a specific position, call ss.moveActiveSheet(index) or ss.getSheets().length on the returned Sheet object to determine the current position, then use ss.moveActiveSheet. There is no position argument on copyTo itself.

Calling the function and handling the collision case

Call duplicateAndRename('Template', 'Week 24') from any other function, a custom menu handler, or a time-based trigger. If a sheet named 'Week 24' already exists, the guard throws before copyTo runs, so no orphan tab is created.

If your use case wants overwrite semantics instead of a hard stop, swap the guard: delete the existing sheet first with ss.deleteSheet(ss.getSheetByName(newName)) before calling copyTo. Be deliberate about that choice — deleteSheet is permanent and there is no undo in script context.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Does copyTo work across different spreadsheet files?: Yes. Pass a different Spreadsheet object to copyTo instead of the active one: source.copyTo(SpreadsheetApp.openById('TARGET_ID')). The returned Sheet object belongs to the target file, so call setName on that reference. Note that cross-file copies do not carry over named ranges that reference the source file.
Why does getSheetByName return null instead of throwing when the sheet is missing?: That is intentional API design. getSheetByName is a lookup; a miss is not an error, it is just an absent result. setName, by contrast, enforces a constraint on an object that already exists, so a violation throws. The two methods have different failure contracts, which is why you need both null-checks in the script above.
Can I rename a sheet to a name that differs only in letter case?: No. Google Sheets treats tab names as case-insensitive for uniqueness. Renaming 'Summary' to 'summary' on the same spreadsheet throws the same collision error as renaming it to an exact duplicate.
The copied tab always lands at the end. How do I move it right after the source?: After setName, call ss.moveActiveSheet with the target index. Get the source's current index first: var targetIndex = source.getIndex() + 1; ss.setActiveSheet(copy); ss.moveActiveSheet(targetIndex). getIndex() is one-based, so adding 1 places the copy immediately to the right of the source.

// one good script a week