Copy values and formatting to another Google spreadsheet with Google Apps Script

Question

I have a sheet built that's pulling in data from an XML feed. I have a script written that will then break out that one sheet into several other sheets based on the contents of a particular column. This all works fine.

My problem is that after I pull in the data from XML and before I split it into new sheets, I add some columns, do some formatting, etc. And then when I copy a row (using appendRow()) to the new sheet, I lose the formatting and formulas.

The code I'm using currently Is:

var datarange = template_sheet.getDataRange();

for (var i in dataRange) {
   workbook.getSheetByName(dataRange[i][6]).appendRow(dataRange[i]);

}

Is there a way to do what I'm looking for? Or do I need to breakout the original data into separate sheets first, then loop through each sheet and do the formatting/formulas?

Answer 1

You should use the method copyTo(range, optAdvancedArgs) of the Range object.

var datarange = template_sheet.getDataRange();
for (var i in datarange)
  datarange[i].copyTo(
    workbook.getSheetByName(dataRange[i][6]).getRange(
      workbook.getSheetByName(dataRange[i][6]).getMaxRows()+1, 1));

should do the trick

in more legible:

var datarange = template_sheet.getDataRange();
for (var i in datarange) {
  var sheet = workbook.getSheetByName(dataRange[i][6].getValue());
  datarange[i].copyTo(sheet.getRange(sheet.getMaxRows()+1, 1));
}

Answer 2

Copy a tab with values and formatting from one tab to a new tab:

function copyTab() {
  var ss, sourceSheet, sourceData, sourceDataRange, newSheetTab;

  ss = SpreadsheetApp.getActiveSpreadsheet();//Get active spreadsheet

  sourceSheet = ss.getSheetByName("Sheet1");//Get the source sheet tab
  newSheetTab = ss.insertSheet("New Sheet");//Create a new sheet tab

  sourceDataRange = sourceSheet.getDataRange();

  sourceDataRange.copyTo(newSheetTab.getRange(1, 1));//Copies the data from a range of 
  //cells to another range of cells. By default both the values and formatting are copied

}