Apps Script, Count BG Color function on conditionally formatted cells

Question

I'm trying to use a function in sheets that will count the number of cells that correspond to the parameter colorref. The issue I'm having is that I need to run this function on a range that has a conditional color background set. Which is returning every cell as white.

Any help is super helpful! Thanks.

For example:

countonBG("E:E", "V2")

V2 = '#FFFF' // white

conditional: if E:E = U:U then E:E background color = 'yellow'

return remaining white cells

function countonBG(range,colorref) {
    var sheet = SpreadsheetApp.getActiveSheet();
    var color = sheet.getRange(colorref).getBackground();
    var range = sheet.getRange(range);
    var rangeVal = range.getValues();
    var count = 0;
    var allColors = range.getBackgrounds();
    for (var i = 0; i < rangeVal.length; i++) {
        for (var j = 0; j < allColors[0].length; j++) {
            if (allColors[i][j] == color) count += 1;
        };
    };
    return count
}

KRR KRR · Accepted Answer · 2015-06-09T17:47:19

The color reference for white is #ffffff.

Tried this code and it is giving me all the background colors:

function countonBG(range,colorref) {
    var sheet = SpreadsheetApp.getActiveSheet();
    var color = sheet.getRange(9, 1).getBackground();
    var range = sheet.getRange(1, 1, 7, 6);
    var rangeVal = range.getValues();
    var count = 0;
    var allColors = range.getBackgrounds();
  Logger.log(allColors);
    for (var i = 0; i < rangeVal.length; i++) {
        for (var j = 0; j < allColors[0].length; j++) {
            if (allColors[i][j] == color) count += 1;
        }
    }
    Logger.log(count);
}

and the logger returns this result:

[15-06-09 10:43:58:043 PDT] [[#cccccc, #cccccc, #cccccc, #ffffff, #ffffff, #ffffff], [#f1c232, #f1c232, #cc0000, #cc0000, #cc0000, #ffffff], [#f1c232, #f1c232, #cc0000, #cc0000, #cc0000, #ffffff], [#f1c232, #f1c232, #cc0000, #cc0000, #cc0000, #ffffff], [#f1c232, #f1c232, #ffffff, #ffffff, #ffffff, #ffffff], [#f1c232, #f1c232, #ffd966, #ffd966, #ffd966, #ffffff], [#ffffff, #ffffff, #ffffff, #ffffff, #ffffff, #ffffff]]
[15-06-09 10:43:58:044 PDT] 3.0

Also find the spreadsheet below:

enter image description here

Apps Script, Count BG Color function on conditionally formatted cells

1 Answers