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!
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);
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()
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))
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With