Google App Script Copy a single sheet and create a new spreadsheet in specific folder

Question

Is it possible to copy a single sheet and create a new spreadsheet based on that in a specific folder?

I have a "Spreadsheet A" which has "Sheet A1", "Sheet A2" and "Sheet A3".

I want to Clone "Sheet A3" and create a new "Spreadsheet B" in Folder "All Data"

Please advise!

What have you tried to do so far? Do you have a minimum, reproducible example (https://stackoverflow.com/help/minimal-reproducible-example) to share? Some related questions that might be helpful: https://stackoverflow.com/questions/19791132/google-apps-script-copy-one-spreadsheet-to-another-spreadsheet-with-formatting and https://stackoverflow.com/questions/17099497/how-can-copy-specifics-sheet-to-another-spreadsheet-using-google-script — mshcruz, Jun 22 '21 at 11:41
I tried doing this, but it creates a new sheet within current spreadsheet only. SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet(); I am not able to figure out, how current sheet can bind to a new spreadsheet or is it even possible? — 404, Jun 22 '21 at 11:44

score 1 · Accepted Answer · answered Jun 22 '21 at 12:05

One way to do it is by creating a new spreadsheet, moving it to the desired folder and then using copyTo() to copy the sheet to the newly created spreadsheet.

function extractSheetToFolder() {
  // Create new spreadsheet
  const destinationSpreadsheet = SpreadsheetApp.create('Spreadsheet B');

  // Get destination folder based on its ID
  const destinationFolder = DriveApp.getFolderById('ABC123');

  // Move new spreadsheet to destination folder
  DriveApp.getFileById(destinationSpreadsheet.getId()).moveTo(destinationFolder);

  // Get the source sheet
  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet A3');
  
  // Copy source sheet to destination spreadsheet
  // The copied sheet will be named "Copy of Sheet A3", 
  // so rename copied sheet to use the same name as the original sheet
  sourceSheet.copyTo(destinationSpreadsheet).setName(sourceSheet.getName());

  // Remove default first sheet (called "Sheet1" in English) from new spreadsheet
  destinationSpreadsheet.deleteSheet(destinationSpreadsheet.getSheets()[0]);  
}

Google App Script Copy a single sheet and create a new spreadsheet in specific folder

1 Answers1