Copying Cell Color from Conditional Formatting

Question

I have a spreadsheet that currently conditionally formats some cells, changing the background color. I am trying to copy/paste these cells and preserve the color of the background without having to copy in all the data required for the conditional formatting.

More specifically: I am conditionally highlight the cells in column A based on the data in column B. I want to be able to copy column A to another sheet and preserve which items in column A have been highlighted. I do not want to copy the data from column B into the new sheet.

Is there a way to do this?

Answer 1

There does not seem to be one from the Sheets UI.

The script editor has the ability to get a cell or cells color irrespective of whether it is conditional formatting or manual, with getBackground() or getBackgrounds() https://developers.google.com/apps-script/reference/spreadsheet/range#getBackground()

There isn't an elegant way to copy to and from arbitrary locations this way. However you could create a script to copy the current selection to an intermediate "scratch" sheet and then from there you could copy paste like normal.