Append rows from other tabs without losing formatt

2019-08-18 18:59发布

问题:

In an effort to organize a set of bank account and credit card statements, I download them. Then I open them in Excel or Google Sheets, reorganize them in matching columns, put all in one tab (TOTAL), and then sort.

The question is now how I do the copy from the individual sheets. I think I'm pretty close after pasting and repeating (the manual way) some function I found by searching. The problem is that this function removes all formatting from the source rows

Example sheet

The last tab example_TOTAL is put there only to show how I want tab TOTAL to look after the process is done.

Here's my function so far which does the most, but erases formatting (colors).

function Copy() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var ss = spreadsheet.getSheetByName('TabA'); //replace with source Sheet tab name
  var range = ss.getRange('A1:L'); //assign the range you want to copy
  var data = range.getValues();
  var ts = spreadsheet.getSheetByName('TOTAL'); //replace with destination Sheet tab name
  ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

  var ss = spreadsheet.getSheetByName('TabB'); //replace with source Sheet tab name
  var range = ss.getRange('A1:L'); //assign the range you want to copy
  var data = range.getValues();
  ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

  var ss = spreadsheet.getSheetByName('TabC'); //replace with source Sheet tab name
  var range = ss.getRange('A1:L'); //assign the range you want to copy
  var data = range.getValues();
  ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);

  var ss = spreadsheet.getSheetByName('TabD'); //replace with source Sheet tab name
  var range = ss.getRange('A1:L'); //assign the range you want to copy
  var data = range.getValues();
  ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);
}

function copyFromTo() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var ss = spreadsheet.getSheetByName('TabA');
  var copy = ss.getRange('A1:L');

  var target = spreadsheet.getSheetByName('TOTAL');
  var dest = target.getRange('A1:L');

  copy.copyTo(dest);
  var ss = spreadsheet.getSheetByName('TabB');
  var copy = ss.getRange('A1:L');
  copy.copyTo(dest);
  var ss = spreadsheet.getSheetByName('TabC');
  var copy = ss.getRange('A1:L');
  copy.copyTo(dest);
  var ss = spreadsheet.getSheetByName('TabD');
  var copy = ss.getRange('A1:L');
  copy.copyTo(dest);

}

How can I append from these sheets and still keep the formatting?