google-sheetsgoogle-apps-scriptbackground-color

testing for background color in google script


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];

Solution

  • 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;
    }