Google sheet: Error on copy sheet to other spreadsheet

Question

I am trying to copy a sheet into other spreadsheet by using this code. However, there is error: [Exception: Specified sheet must be part of the spreadsheet]

function copy_to_A() {
  
 var source = SpreadsheetApp.getActiveSpreadsheet();
 var destination = SpreadsheetApp.openById(Bestwise_id);
 var tempsheet = source.getSheetByName('Bestwise-tocopy');

 destination.insertSheet('New Sheet Name', 0, {template: tempsheet});
}

On what line is the exception occuring? There is a lot of context missing here. — Johannes Mols, Oct 23 '21 at 10:10

score 1 · Answer 1 · answered Oct 23 '21 at 04:43

Issue and workaround:

Unfortunately, it seems that when the Spreadsheet of the source sheet is different from the destination Spreadsheet, insertSheet cannot be used for other Spreadsheet. It seems that this is the current specification. So when you want to use destination.insertSheet('New Sheet Name', 0, {template: tempsheet});, how about the following workaround? The flow of this workaround is as follows.

Copy tempsheet sheet from source Spreadsheet to destination Spreadsheet.
Using insertSheet('New Sheet Name', 0, {template: tempsheet}), the copied sheet is used.
Delete the copied sheet.

When this flow is reflected to your script, it becomes as follows.

Modified script:

function copy_to_A() {
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var destination = SpreadsheetApp.openById(Bestwise_id);
  var tempsheet = source.getSheetByName('Bestwise-tocopy');
  
  // 1. Copy `tempsheet` sheet from source Spreadsheet to destination Spreadsheet.
  var copiedSheet = tempsheet.copyTo(destination);
  
  // 2. Using `insertSheet('New Sheet Name', 0, {template: tempsheet})`, the copied sheet is used.
  destination.insertSheet('New Sheet Name', 0, { template: copiedSheet });
  
  // 3. Delete the copied sheet.
  destination.deleteSheet(copiedSheet);
}

Google sheet: Error on copy sheet to other spreadsheet

1 Answers1

Issue and workaround:

Modified script:

References: