Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 340
user2956591 Avatar asked Nov 05 '13 14:11

user2956591


3 Answers

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);
like image 63
tracon6 Avatar answered Nov 16 '22 03:11

tracon6


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 :)

like image 35
Vishwesh Rathi Avatar answered Nov 16 '22 03:11

Vishwesh Rathi


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

     }
     
     
     
     }
like image 32
Jakub Mitura Avatar answered Nov 16 '22 03:11

Jakub Mitura