Google Sheets Script – Function that outputs the background color of a cell

user_13113499 0
Viewed 4217

I would like to write my own function in Google Script that I can use in Google Sheets to get the background color of a cell. Let's call the function GETBACKGROUNDCOLOR.

I want to be able to pass a cell reference (in A1 notation) as parameter, e.g. if I call =GETBACKGROUNDCOLOR(B2), it should return the background color of cell B2. If I call the function without parameter, I want it to return the background color of the same cell in which it is called, e.g. calling =GETBACKGROUNDCOLOR() in C3 should return the background color of C3.

I have tried this:

function GETBACKGROUNDCOLOR(cell){
  return SpreadsheetApp.getActiveSheet().getRange(cell).getBackground();
}

But when I call GETBACKGROUNDCOLOR(A1), the parameter is not the cell reference of A1, but the cell content of A1.

How can I solve this?

google-apps-script google-sheets function scripting
3 Answers

You could try something like

=GETBACKGROUNDCOLOR("A"&row(A1))

or

=GETBACKGROUNDCOLOR(cell("address", A1))

and see if that works?

(Note: depening on your locale you may have to use a semi-colon instead of a comma).

user_38894230

Bulletproof script below:

1. Paste to AppsScript:

function bgHex(cellAddress) {
 var mycell = SpreadsheetApp.getActiveSheet().getRange(cellAddress);
 var bghex = mycell.getBackground();
 return bghex;
}

2. Back in sheets call it with =bgHex(CELL("address"; B4)) or =bgHex(CELL("address", B4)), depending on your locale. Look closely and note the difference: delimiters. Some countries like Poland use comma as decimal delimiter and we use semicolons for separating arguments. Others use dots as delimieters so they can use comma for separating args.

With that function you'll get the hex code for specified cell's background. Call any cell, no range errors, no mismatched data formats.

Now you can play with getting other stuff out of these cells, for example check cell values. Here's a test sheet so You can see how it works.

You will find a second function there, for extracting cell's value – the principle is the same.

function cellValue(cellAddress) {
  var mycell = SpreadsheetApp.getActiveSheet().getRange(cellAddress);
  var value = mycell.getValue();
return value;
}

Now go to https://developers.google.com/apps-script/reference/spreadsheet/range and play with other methods.

user_151987880
user_13005180
Related