CODEWITHSUNDEEP
functiongoogle-apps-scriptgoogle-sheetsscripting
finaslonchas
finaslonchas

Reputation: 85

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

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?

Upvotes: 4

Views: 8404

Answers (3)

Piotr Kocybik
Piotr Kocybik

Reputation: 121

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.

Upvotes: 4

roma
roma

Reputation: 1580

You can use =address to resolve name

=GETBACKGROUNDCOLOR(address(row(A1),column(A1)))

for relative cells or

=GETBACKGROUNDCOLOR(address(row(),column()))

for cell with formula

However, the big problem would be that then you'll change cell backround color, formula won't recalculate(function re-eval won't trigger), so it might not be exactly a way.

Example sheet: https://docs.google.com/spreadsheets/d/1lfFRLVqhns0AJCbZd6ikgcDtvktcgpNWfNomMASWemE/edit#gid=0

Upvotes: 1

JPV
JPV

Reputation: 27262

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).

Upvotes: 3

Related Questions