Google sheets Keep formatting when coping from one sheet to another

1
votes

I have been searching for days on how to do this. I have tried 12 different scripts so far. I can copy over but not the formatting, it is killing me. I have two sheets. Sheet A and Sheet B.

I want to copy over columns A through I onto sheet B And keep the formatting. I have 2 problems the first is It copies everything instead of just the columns I want. The second is it does not keep the formatting. I have tried different scripts just for formatting and different lines of code. but so far none have worked for me.

Current Script:

function CopyDataToNewFile() {
  var sss = SpreadsheetApp.openById('16QcYsC3mbdcYiPsCp7tPFPX-pCePkaITzfF-bK4ceu0'); // sss = source spreadsheet
  var ss = sss.getSheetByName('Sheet1'); // ss = source sheet
  var SRange = ss.getDataRange();
  var A1Range = SRange.getA1Notation();
  var SData = SRange.getValues();
  var tss = SpreadsheetApp.openById('16OBwPrYmKnZZkgovDDUYByJhssU2nbRkBD0hCrgvYK4'); // tss = target spreadsheet
  var ts = tss.getSheetByName('Sheet1'); // ts = target sheet
  ts.clear({contentsOnly: true});
  ts.getRange(A1Range).setValues(SData);
}
1
google-apps-scriptgoogle-sheets
Keep which formatting? the format of the values from sheet A? Or the format in the cells whose values are altered, on sheet B? - tehhowch
Relevant documentation: getDataRange(), getRange(), copyFormatToRange(), copyTo() - tehhowch
I need to keep the same formatting of sheet A. I need an exact copy just from column A thru Column I. So On sheet A some things are in Red and some in Green. I need to see that on Sheet B - Connie

1 Answers

3
votes

I understood what you want to do as follows.

  1. You want to copy the values of from column A to column I.
  2. You want to also copy the values with the format of source range.

If my understanding is correct, how about this modification? I think that there are several answers for your situation. So please think of this as one of them. In this modification, copyTo() of Class Range and copyTo() of Class Sheet are used for this situation. The flow of this is as follows.

Flow :

  1. Retrieve the source range.
  2. Retrieve the destination range.
  3. Copy the sheet of source range to a temporal sheet. copyTo() of Class Range is used.
  4. Copy the source range to the destination range. copyTo() of Class Range is used.
  5. Delete the temporal sheet.

Modified script :

function CopyDataToNewFile() {
  var range = "A:K"; // Range you want to copy

  // Source
  var srcss = SpreadsheetApp.openById("16QcYsC3mbdcYiPsCp7tPFPX-pCePkaITzfF-bK4ceu0");
  var srcsheet = srcss.getSheetByName("Sheet1");
  var srsrange = srcsheet.getRange(range);

  // Destination
  var dstss = SpreadsheetApp.openById("16OBwPrYmKnZZkgovDDUYByJhssU2nbRkBD0hCrgvYK4");
  var dstrange = dstss.getSheetByName("Sheet1").getRange(range);

  // Copy values
  var copiedsheet = srcsheet.copyTo(dstss);
  copiedsheet.getRange(range).copyTo(dstrange);
  dstss.deleteSheet(copiedsheet);
}

References :

If I misunderstand your question, please tell me. I would like to modify it.