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!

score 33 · Accepted Answer · edited Nov 05 '13 at 17:21

33

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

edited Nov 05 '13 at 17:21

Serge insas

45,904
7
105
131

answered Nov 05 '13 at 14:41

tracon6

496
4
2

2

Unfortunately, the method copyTo() does not copy formatting, or at least not merged cells. Also, I need to call this copy multiple times over the next few months, and I do not want a new sheet in the destination spreadsheet every time a copy is made. I want the data copied from the source spreadsheet into the same destination spreadsheet every time. Also, I do not believe copyTo() allows you to copy between spreadsheets, only between sheets within a single spreadsheet. – user2956591 Nov 05 '13 at 14:54
3

I just tested the code out and it works. (1) copyTo() does copy formatting, including merged cells and (2) it does work between spreadsheets (see line in code that starts with "var destination = "). To eliminate the multiple spreadsheet issue, you could write the script so that would delete the old sheet, copy the new sheet, and then rename the new sheet. Is there a specific reason why you need to have the data copied into the same destination spreadsheet every time? – tracon6 Nov 05 '13 at 16:16
Aha! I checked my code and I had the wrong destination Spreadsheet ID in my openById() method. Wonderful! Thank you so much! Regarding why I need to do this: I have multiple users submit forms to me. I then break up the orders into separate sheets by user, and add aditional info. The final step is to copy these individual user sheets out of the master spreadsheet into their own separate spreadsheet which only they can access. – user2956591 Nov 05 '13 at 17:04
1

It copys the reference to the new spreadsheet... is there a way to copy value only? – user3347814 Aug 03 '14 at 02:19
1

to copy values only - `range.copyValuesToRange()` – ZygD Sep 27 '15 at 01:17
Could you please give me an explanation on how to apply this with my worksheets? So lets say my first worksheet is called: WorksheetA and my second is called WorksheetB. And I want the data copied from frist worksheet to the second. How do I apply this to the variables/parameters in your script? And I will I tell it to copy all the data from lets say A4:A - L4:L ? – MSD Nov 23 '16 at 11:23
sadly, this does not allow you to set the name of the sheet on creation – Michael Apr 03 '18 at 23:40
@MSD as per https://stackoverflow.com/questions/58923439/copy-an-entire-spreadsheet-just-preserve-the-values you can accomplish what you want by 1. copying the sheet within the same worksheet into a temporary worksheet, 2. copying the values of the temporary worksheet onto themselves by using the Range class' copyTo function and passing {contentsOnly:true} as 2nd argument, 3. copying the sheet into the the other worksheet by means of DriveApp copyTo function, then 4. deleting the temporary worksheet. By the way the programming style in the mentioned answer is very elegant. – marcocassisa Nov 21 '20 at 15:13

score 3 · Answer 2 · edited Jun 20 '20 at 09:12

3

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 12 '18 at 07:39

Vishwesh Rathi

55
1

score 3 · Answer 3 · answered Oct 01 '20 at 06:46

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

     }
     
     
     
     }

Felix H · Answer 4 · 2021-11-09T06:26:12.643

Try this function.

CopyTable("sda12wrsdsdaada121", "sheetA", "sheetB", "A1:D200")

/**
 * Copy data and formatting from source to destination
 * @param {string} srcId id of source
 * @param {string} srcSheetName sheet's name of source
 * @param {string} destSheetName sheet's name of destination
 * @param {string} copyRange A1Notation of range
 */
function CopyTable(srcId, srcSheetName, destSheetName, copyRange){

  var srcSpreadSheet = SpreadsheetApp.openById(srcId);
  var srcSheet = srcSpreadSheet.getSheetByName(srcSheetName);

  var destSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var destSheet = destSpreadSheet.getSheetByName(destSheetName);
  destSheet.clear();

  var srcRange = srcSheet.getRange(copyRange); 

  var values = srcRange.getValues();
  var background = srcRange.getBackgrounds();
  var fontColor = srcRange.getFontColors();
  var fontFamily = srcRange.getFontFamilies();
  var fontLine = srcRange.getFontLines();
  var fontSize = srcRange.getFontSizes();
  var fontStyle = srcRange.getFontStyles();
  var fontWeight = srcRange.getFontWeights();
  var textStyle = srcRange.getTextStyles();
  var horAlign = srcRange.getHorizontalAlignments();
  var vertAlign = srcRange.getVerticalAlignments();
  var bandings = srcRange.getBandings();
  var mergedRanges = srcRange.getMergedRanges();

  var destRange = destSheet.getRange(copyRange);
 
  destRange.setValues(values);
  destRange.setBackgrounds(background);
  destRange.setFontColors(fontColor);
  destRange.setFontFamilies(fontFamily);
  destRange.setFontLines(fontLine);
  destRange.setFontSizes(fontSize);
  destRange.setFontStyles(fontStyle);
  destRange.setFontWeights(fontWeight);
  destRange.setTextStyles(textStyle);
  destRange.setHorizontalAlignments(horAlign);
  destRange.setVerticalAlignments(vertAlign);
  
  for (let i in bandings){
    let srcBandA1 = bandings[i].getRange().getA1Notation();
    let destBandRange = destSheet.getRange(srcBandA1);

    destBandRange.applyRowBanding()
    .setFirstRowColor(bandings[i].getFirstRowColor())
    .setSecondRowColor(bandings[i].getSecondRowColor())
    .setHeaderRowColor(bandings[i].getHeaderRowColor())
    .setFooterRowColor(bandings[i].getFooterRowColor());
  }

  for (let i = 0; i < mergedRanges.length; i++) {
    destSheet.getRange(mergedRanges[i].getA1Notation()).merge();
  }
 
  for (let i = 1; i <= srcRange.getHeight(); i++) {
    let width = srcSheet.getColumnWidth(i);
    destSheet.setColumnWidth(i, width);
  }
 
  for (let i = 1; i <= srcRange.getWidth(); i++){
    let height = srcSheet.getRowHeight(i);
    destSheet.setRowHeight(i, height);
  }
}

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Nov 09 '21 at 07:09

score 0 · Answer 5 · answered Sep 29 '22 at 09:23

This will work for copying specific values from one sheet to another sheet-

var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var source = ss.getSheets()[0];
var destination = ss.getSheets()[1]; 
destination.clear() 

var range = source.getRange(1,1,10, 5);  
range.copyValuesToRange(destination, 1, 5, 1, 10);

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

5 Answers5

Keep scripting :)

Linked

Related