3

I have created a function to get the text color of a cell when I insert the formula in another cell. My aim is to filter rows by color.

Steps:

1. Create the code:

function getHex(input) {
  return SpreadsheetApp.getActiveSpreadsheet().getRange(input).getFontColor();
};

2. Type the function in cell M2, for example:

=getHex("C2:C10")

3. Range M2:M10 will return the font color from C2:C10.

This function worked with .getBackgrounds() (https://www.techjunkie.com/filter-by-color-google-sheets/), but it is not working with .getFontColor(). Can someone help me, please?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Bernardo Giarola
  • 303
  • 1
  • 2
  • 9
  • 5
    Hi Bernardo, are you trying to just write the string of the hex color of the cells, (in the M2:M10 range in this example). If you call getFontColor on a range it will only return the color for the top-left cell, you should use getFontColors to get all the colors for the range in an array – AMolina Jun 17 '19 at 13:08
  • Have you already looked at https://developers.google.com/apps-script/reference/spreadsheet/range for a method that returns a array of values instead of a single value? Also look at https://developers.google.com/apps-script/guides/sheets/functions. – Rubén Jun 17 '19 at 15:39
  • Hi Alberto! It worked! Thank you! – Bernardo Giarola Jun 17 '19 at 17:07

1 Answers1

1

Posting this to leave the answer documented.

If you call getFontColor() on a range it will only return the color for the top-left cell of the range (this method is what you would normally use for evaluating a single cell), you should use getFontColors() which returns all the colors for the range in an array.

As far as I can see in the documentation available at the moment, many of the Range Class methods follow this pattern, there’s a method to get a single value and another to get an array of the same values, same happens with getBackground() & getBackgrounds(), for example, this has to do with how ranges can be used for both aggregations of cells as well as single ones. Below is a link to the documentation, it should help you with any other doubts.

Documentation URL: https://developers.google.com/apps-script/reference/spreadsheet/range#getfontcolors

AMolina
  • 1,355
  • 1
  • 7
  • 17