CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Rozkalns
Rozkalns

Reputation: 512

Get cell object in custom function

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?

get_color() returns #ERROR

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

Upvotes: 7

Views: 11840

Answers (4)

Magne
Magne

Reputation: 17223

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.

Upvotes: 0

ošky
ošky

Reputation: 336

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.

Upvotes: 11

Mogsdad
Mogsdad

Reputation: 45710

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.

Upvotes: 8

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

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

Upvotes: 0

Related Questions