Copy Sheet from one Spreadsheet to another using "copyTo" with index and new name?-CodePudding

When I run code with copyTo, like this:

var destination = SpreadsheetApp.openById('ID_GOES HERE');
sheet.copyTo(destination);

The sheet appears in the destination spreadsheet just fine, but the tab name adds "Copy of" and appears as the last tab in the file. Is there a way to use "copyTo" so that I can also make it index 0 and give it a new name? Or must I add additional code that re-indexes and renames?

Thanks!

CodePudding user response：

Here is the full implementation:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var destination = SpreadsheetApp.openById('###'); // <-- destination sheet ID
  var copied_sheet = sheet.copyTo(destination);
  copied_sheet.setName(sheet.getName()); // get a name from original sheet
  copied_sheet.activate();
  destination.moveActiveSheet(1); // move the copied sheet in first position
}

But you need to make sure that destination spreadsheet has no sheet with the same name already. This is why SpreadsheetApp adds 'Copy' to an original name.

References:

CodePudding user response：

To rename your new sheet at will, you can just set a new name. As an example, I made this function which allows you to rename your active sheet through a dialog box.

You can call this function from inside your copyTo function or adapt it to go along with it.

function sheetRename() {
  var spreadsheet = SpreadsheetApp.getActive(); //you can set to get spreadsheet by URL or ID
  var sheet = spreadsheet.getActiveSheet(); //same as above
  var newName = Browser.inputBox("New Sheet Name","Input new sheet name",Browser.Buttons.OK_CANCEL); //creates a dialog box asking for a new name input
  sheet.setName(newName); //renames your sheet
};

As for the index, I'm not sure about how to do it, but I believe it is doable.