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();
}

Answer 1

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.

Answer 2

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.

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');