CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
WL Tam
WL Tam

Reputation: 11

Google sheet: Error on copy sheet to other spreadsheet

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

Upvotes: 1

Views: 1102

Answers (1)

Tanaike
Tanaike

Reputation: 201388

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.

  1. Copy tempsheet sheet from source Spreadsheet to destination Spreadsheet.
  2. Using insertSheet('New Sheet Name', 0, {template: tempsheet}), the copied sheet is used.
  3. 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);
}

References:

Upvotes: 1

Related Questions