Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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();
}
like image 490
Rozkalns Avatar asked Oct 21 '15 12:10

Rozkalns


People also ask

How do I value a cell in Google Sheets?

In Google Sheets, the formula INDEX() allows you to return the value of a cell by specifying which row and column to look at in the specified array. =INDEX(A:A,1,1) for example will always return the first cell in column A.


2 Answers

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.

like image 120
ošky Avatar answered Oct 03 '22 08:10

ošky


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.

like image 34
Mogsdad Avatar answered Oct 03 '22 08:10

Mogsdad