Google Apps Script, copy one spreadsheet to another spreadsheet with formatting

Question

Hopefully this question has not already been answered. I have spent a considerable amount of time searching, and although I have found similar posts, none have done exactly what I am trying to do.

I would like to use Google Apps Script to copy a single sheet from a Google spreadsheet into a different Google spreadsheet, and I want to retain formatting (including merged cells). Is there any way to do this? I have tried the following functions:

copyTo() copyFormatToRange()

but these methods only work within the same spreadsheet and do not allow data to be copied between different spreadsheets. Does anyone have any suggestions? Thanks!

Answer 1

Have you looked here:

https://developers.google.com/apps-script/reference/spreadsheet/sheet#copyTo(Spreadsheet)

copyTo(spreadsheet)

Copies the sheet to another spreadsheet. The destination spreadsheet can be the source. The new spreadsheet will have the name "Copy of [original spreadsheet name]".

 var source = SpreadsheetApp.getActiveSpreadsheet();

 var sheet = source.getSheets()[0];

 var destination = SpreadsheetApp.openById("ID_GOES HERE");

 sheet.copyTo(destination);

Answer 2

If you want to duplicate a sheet of a particular spreadsheet, you can use:

SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();

This will create a copy of your current sheet and make that copy as active.

duplicateActiveSheet()

Keep scripting :)

Answer 3

In case Anybody would like to just copy formats of particular range in particular sheet

   /**
     copying full formatting including sizez and merging from one range to new location
https://stackoverflow.com/questions/25106580/copy-value-and-format-from-a-sheet-to-a-new-google-spreadsheet-document
below first coordinates of original range we want to copy and then cooridinaes of the begining of the place ino which we want to copy our range
    @param startColumnOfOriginal {Number} 
     @param startRowOfOriginal {Number} 
     @param numberOfRows {Number} 
     @param numberOfColumns {Number} 
     
     @param startColumnOfTarget {Number} 
     @param startRowOfTarget {Number} 
   
     @param sheetOfOrigin {Sheet} sheet object of where our source is
     @param sheetOfTarget {Sheet} sheet object where we want to copy it
     */
     
     function copyFullFormatting(startRowOfOriginal,startColumnOfOriginal,numberOfRows
     ,numberOfColumns, startRowOfTarget, startColumnOfTarget,  sheetOfOrigin, sheetOfTarget
     ){
     const sourceRange = sheetOfOrigin.getRange(
     startRowOfOriginal, startColumnOfOriginal, numberOfRows, numberOfColumns)
     
     const targetRange = sheetOfTarget.getRange(
     startRowOfTarget, startColumnOfTarget, numberOfRows, numberOfColumns)
     sourceRange.copyFormatToRange(sheetOfTarget,startColumnOfOriginal, startColumnOfTarget+ numberOfColumns, startRowOfTarget, startRowOfTarget+numberOfRows )
     //iterating over rows of source range
     for(var rowNumb=startRowOfOriginal;rowNumb<startRowOfOriginal+numberOfRows;rowNumb++  ){
     const targetRowNumb = rowNumb-startRowOfOriginal+startRowOfTarget
     sheetOfTarget.setRowHeight(targetRowNumb, sheetOfOrigin.getRowHeight(rowNumb))  
     }
     
     // iterating over columns in target range
     for (var colNumb=startColumnOfOriginal;colNumb<startColumnOfOriginal+numberOfColumns;colNumb++  ){
     const targetColNumb = colNumb-startColumnOfOriginal+startColumnOfTarget
     sheetOfTarget.setColumnWidth(targetColNumb, sheetOfOrigin.getColumnWidth(colNumb))

     }
     
     
     
     }