testing for background color in google script

Question

Just a note: I am not very versed in coding and brand new to google script.

I am trying test for background color within a script. Specifically, I will have an array of names stored into a named range and want to count how many cells are set to green.
So far I have the following but receive an error: TypeError: Cannot set property "0.0" of undefined to "#00ff00"

function testCount(range) {

  var ranges = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("testrange");
  var names = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("testrange").getValues();

  var NumColumns = ranges.getNumColumns();
  var NumRows = ranges.getNumRows();
  var c = 0;

  for (var i = 0; i<NumColumns; i++){
    for (var j = 0; j<NumRows; j++){
      if (ranges.getBackgrounds()[i][j] ="#00ff00"){ 
        c++;
      }else{
        c=c;
      }
    }
  }
  return c;

I grabbed the value for green when I tried the following for a cell that was colored

return ranges.getBackgrounds()[0][1];

Phil Bozak Phil Bozak · Accepted Answer · 2013-03-03T01:07:40

Just looks like your code needs a little cleaning. I'll explain the edits.

function testCount() {

  var ranges = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("testrange");

No need to have the var names line because it seems that you don't use it.

  var NumColumns = ranges.getNumColumns();
  var NumRows = ranges.getNumRows();

Grab the backgrounds of all the cells at once and store that in a variable.

  var backgrounds = ranges.getBackgrounds();
  var c = 0;

  for (var i = 0; i<NumColumns; i++){
    for (var j = 0; j<NumRows; j++){

Reference the backgrounds variable that we created above. Also, the first number is the row number, and the second number is the column number. So you'll want to swap i and j from what you had originally. Also, a = 10 assigns the value of 10 to the variable a. To check for equality, you use ==. This checks if the two values are the same.

      if (backgrounds[j][i] == "#00ff00"){ 
        c++;
      }

No need to have an else statement that doesn't do anything. You can leave the else part out.

    }
  }
  return c;
}

testing for background color in google script

1 Answers