GetDisplayValues not copying formatting

[Loveday Lemon]

Hi all, I am using a script that includes "GetValues" to copy data from one sheet to another, but it's copying only the text, not the formatting. Part of the text in some cells in bold or in a different color. I tried using  "GetDisplayValues" instead, but I still get the text without any formatting in the destination sheet. Is there any way to get the formatting as well? Thanks in advance for any help! 

[Scott Bennett]

Try using this. 




copyTo(destination, options) 

Scott Bennett

Sent from my iPhone 

On Aug 25, 2022, at 5:56 AM, Loveday Lemon <bakerst...@gmail.com> wrote:

Hi all, I am using a script that includes "GetValues" to copy data from one sheet to another, but it's copying only the text, not the formatting. Part of the text in some cells in bold or in a different color. I tried using  "GetDisplayValues" instead, but I still get the text without any formatting in the destination sheet. Is there any way to get the formatting as well? Thanks in advance for any help! 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d1efa8e5-beb4-4639-82ac-29b488d2e2d4n%40googlegroups.com.

[Loveday Lemon]

Thank you very much, I'm using a script (see below) that I got from a benefactor (I want to learn but the step is high), and am not sure what to put in the brackets after  copyTo. I'd be grateful for more details. 

function copyAll() {

   const ss = SpreadsheetApp.getActive();

   const sheet = ss.getActiveSheet();

   const data = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).getDisplayValues();

   copyToSS(data);

}

[Bennett, Scott]

copyToSS is not a function.  Unless it is defined somewhere else.

I found another option. 

function copyAll() {

  const ss = SpreadsheetApp.getActive(); 

  const sheet = ss.getActiveSheet();

  var destSheet = 'you need your destination sheet the same way you defined your source sheet.'

  const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getDisplayValues();

  var destRange = destSheet.getRange(2, 1, data.length, data[0].length)

  var copyData = destRange.setValues(data)

  var copyFormat = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).copyTo(destRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT)

}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/16f8c871-6dfc-4a7b-9828-4c9ebd869ccen%40googlegroups.com.

[Loveday Lemon]

Thanks! I'm not sure how to use that because the destination sheet changes for each row of the source file.

This file here is the source file. For each row, I'm copying columns L to Y into the sheet whose link is in column Z. 

Works a charm, except for the formatting in column Y which is not copied.

I don't know how to make the destSheet variable according to the row, is this possible?

Thanks for your help!

[Bennett, Scott]

There are some variables there.  What sheet are you using in the destination?  Do the rows always line so is row 4 in the source sheet going to row 4 in the destination sheet?  

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/67044144-b0fe-46fc-9963-b13661bc0e17n%40googlegroups.com.

--

Scott Bennett

Data and Assessment Coordinator/Math Teacher

Bradley-Bourbonnais Community High School

P 815.937.3707 ext. 6010

[Loveday Lemon]

Yes that's the problem, variables. In the destination sheets I'm using Sheet1 always, but the rows don't line up. The original script was written so that the data would be copied in the first free row of each destination sheet below the header line. 

[Bennett, Scott]

I think it can be done.  The way you want it, you would have to copy each row individually. Then send over the formatting.  Your original script is getting all the data.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d6835430-331e-4c08-9c37-c6433928e50cn%40googlegroups.com.

[Loveday Lemon]

OK thanks!