0

I'm trying to use a function to get the hexadecimal code of the cell background and concatenate it with the text contained in the same cell.

i.e. in sht1.cell(A1) I type "Hello" with red background, I would have in sht2.cell(A1) the following text: "[#FF0000]Hello"

I was trying this:

function getHexValue(range) {
return SpreadsheetApp.getActiveSheet().getRange(range).getBackground();
 }

but it doesn't always work, especially if I share the file. I tried to setup some triggers using edit, change or open events but id keeps not updating this is what i tried to do.

Any suggestion?

If I type: =gethexvalue("A5") I get the color code, if I type the formula to another cell or another sheet and I update the values or reload the sheet it doesn't work.

Rubén
  • 34,714
  • 9
  • 70
  • 166
corky81
  • 55
  • 1
  • 6
  • 1
    Possible duplicate of [Custom Functions and Recalculation](https://stackoverflow.com/questions/25163243/custom-functions-and-recalculation) – Rubén Dec 02 '18 at 21:32

1 Answers1

0

In your formula =gethexvalue("A5"), "A5" is text, not a dynamic range.

Read How to pass a range into a custom function in Google Spreadsheets? for a full discussion and 11 different answers. This is itself a possible duplicate of passing cell references to spreadsheet functions.

This =getHexValue(ADDRESS(ROW(A1), COLUMN(A1)))&A1 is dynamic. It will return:

"#ff0000Hello"

. If you want the hex in Uppercase, then use UPPER with getHexvalue.

Tedinoz
  • 5,911
  • 3
  • 25
  • 35