Get cell object in custom function

Question

I have this function to get the font color of a cell. The test function works fine (as a result I get hex code #ff0000), but when I call the get_color() function from a Google Spreadsheet, it returns #ERROR. This appears to be because I get just a plain string value from the function argument, instead of a Range object. How I could achieve it?

Maybe there is a little bit easier way to get font color of text?

function test_get_color() {
  var targetId = 'xxx'
  var cell = SpreadsheetApp.openById(targetId).getSheetByName('Sheet1').getRange('B7');

  Logger.log(get_color(cell)); 
}

function get_color(cell){
  return cell.getFontColor();
}

Glottochronology

Answer 1

When you invoke a custom function by providing a range as a parameter, the function actually receives the values from that range. This is documented in Custom Functions in Google Sheets / Arguments.

A hack for functions that require an actual range reference, like yours, is to pass the range as a string.

=get_color( "B7" )

Where the function would be something like this:

/**
 * Get the color of text in the given cell.
 *
 * @param  {"B32"}  cell  Cell reference, enclosed in quotes.
 * @returns               The text color from the given cell.
 * @customfunction
 */
function get_color( cell ) {
  if (typeof cell !== "string")
    throw new Error( "Cell reference must be enclosed in quotes." );

  var range = SpreadsheetApp.getActiveSheet().getRange( cell );
  return range.getFontColor();
}

Note: The comment block seeds the Google Sheets auto-completion help for this function. See Did you know? (Custom Functions in Google Apps Script) for more about custom functions.

Claudineclaudio

It worked, but only once :) If I change color, get_color function stuck on first value. Anyway, return range.getFontColor() :) Workaround to paste down get_color function was send parameter as =get_color("B" & ROW()) – Glottochronology

Answer 2

The best way is to call your custom function with additional params filled with outcomes of ROW() and COLUMN() fucntions.

When calling =MYFUNCTION(ROW(), COLUMN()), MyFunction obtains cell position within the sheet corresponding with the position of the cell the function is called from.

Concentration

Thanks, not bad idea! Although, this is quite old question, but still relevant :) – Glottochronology

Or you could do function MYFUNCTION() { return SpreadsheetApp.getActiveSheet().getCurrentCell().getA1Notation(); } However getCurrentCell is not an actual this equivalent, and using it like this might be subverting its documented purpose: "The current cell is the cell that has focus in the Google Sheets UI, and is highlighted by a dark border. ... When a user selects one or more cell ranges, one of the cells in the selection is the current cell." developers.google.com/apps-script/reference/spreadsheet/… But somehow it works...? – Sp

@Magne: in custom functions, getCurrentCell() returns the cell that called the function, not the cell that has focus. – Sherwoodsherwynd

@DanDascalescu Thanks Dan, that makes sense. I'm surprised the docs were wrong here. I've submitted a feedback report so they correct it. – Sp

Answer 3

You'd have to authorize access to the ID first because you open the spreadsheet by id instead of using the already open active spreadsheet.
If you are using it in and for the current spreadsheet change cell to

var cell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange('B7');

Catalpa

Yeah, but I want to use =get_color(B7) on Spreadsheet, and not only B7, but many cells. Use it like any other function – Glottochronology

Sorry, I wasn't clear about that, I was focusing on the Error itself. In that case Mogsdad's excellent answer should help you. – Catalpa

Answer 4

The new API is this:

function currentCell() {
  return SpreadsheetApp.getActiveSheet().getCurrentCell().getFontColorObject().asRgbColor().asHexString();
}

Remember: in custom functions, getCurrentCell() returns the cell that called the function, not the cell that has focus. Contrary to what the documentation says. Thanks to Dan Dascalescu for pointing this out.

Sp