Intelligently Fill Down Custom Function With Range Parameter in Google Sheets

Question

I have a range of cells that come color coded (background color) from another system (that exports its data into excel format, which I have imported into Google Spreadsheets/Sheets) and I need to make simple calculations based on the background color of each cell.

Unfortunately, the only way I've found in Sheets to determine the background color of a cell is through the use of a script, which I have no experience with.

I have been using the following script:

function getBGColor(range) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  return sheet.getRange(range).getBackgroundColor();
}

My issue is that the range must be entered in quotes. My understanding is that the value of the cell is passed to the function if the range is entered without quotes. Correct?

=getBGColor("E2")

Sheets doesn't intelligently fill down values in quotes. When I fill down, the formula does not increment as I would need it to (there are hundred of cells I need to get the color of).

I get

=getBGColor("E2")
=getBGColor("E2")

instead of

=getBGColor("E3")
=getBGColor("E4")

I feel like I must be searching using the wrong terminology because I can't find any solutions to this. Do I need to create a custom function that replicates the intelligent fill-down functionality or is there some other solution that I've missed somewhere? Am I attacking the problem incorrectly?

I try to make sheets that I can re-use when new data is available and that can be manipulated on the fly (lots of cell references) to demonstrate the effects of changes in data but it is looking like Sheets may not be the best tool for this? Excel has worked well but my superiors are pushing to move all of this kind of data into Sheets.

Thank you.

Patrick Patrick · Accepted Answer · 2015-02-05T16:18:46

Just found something that seems to work. While the

CELL("color",reference)

doesn't work, it did lead me to the address info_type. I was able to use the following workaround:

=getBGColor(CELL("address",E2))

which will intelligently fill down.

If there are better ways to do this please let me know!

Intelligently Fill Down Custom Function With Range Parameter in Google Sheets

1 Answers