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();
}
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With