Counting cells with particular background color in Google Sheets

2
votes

I am trying to produce a visual rota for my restaurant using Google Sheets. I need to count the background color of a row in order to get a figure for the number of half hour periods worked that day.

Here is a link to the google sheet:

https://docs.google.com/spreadsheets/d/19IEDGZypi3nVt55-OayvPo__pbV0sTuRQ3wCJZ1Mhck/edit?usp=sharing

And the script that I am using:

function countBG(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 < allColors.length; i++) {
      for (var j = 0; j < allColors[0].length; j++) {
          if (allColors[i][j] == color) count += 1;
      };
  };
  return count;
}

I find that the script works the first time it's run, but after that it gives an error:

Range not found (line 4, file "Code")

Would appreciate any help in getting this working, I'm new at Google Sheets and scripting so possibly missing something obvious.

Thanks,

DB.

2
google-apps-scriptgoogle-sheets
Does it work or just not throw an error? sheet.getRange(colorref).getBackground(); ought to be getBackgrounds() or it'll just return one cell's worth of data. – Tom Woodward
@TomWoodward: I assume the colorref parameter is a single cell that has the color that needs to be counted in the range. If so, .getBackground() would be correct. – JPV
@JPV question seems to indicate wanting to count a row's worth of background. Certainly can do it cell by cell but multiple values seems easier. Maybe 'ought' was the wrong word. – Tom Woodward

2 Answers

1
votes

If you wanted to run it as a custom function and just use a single row as the input, this will work.

You'd pass the row variables like so =countBG("D6:AH6")

function countBG(input) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var colors = sheet.getRange(input).getBackgrounds();
  var count = 0;
  Logger.log(colors);
  for (var i = 0; i < colors.length; i++) {
      for (var j = 0; j < colors[0].length; j++) {
          if (colors[i][j] != "#ffffff") count += 1;
      };
  };
  Logger.log(count);
  return count;
}

Example in the sheet here in column AI

1
votes

For some reason SpreadsheetApp.getActiveSheet().getRange(...) gives me only a matrix of values, not an actual range.

My solution (notice how the range is a String here):

function countColoredCellsInRange(countRange, expectedBackground) {
  var range = SpreadsheetApp.getActiveSpreadsheet().getRange(countRange);
  var backgrounds = range.getBackgrounds();

  var coloredCellsAmount = 0;
  for (var i = 0; i < backgrounds.length; ++i) {
    for (var j = 0; j < backgrounds[i].length; ++j) {
      var currentBackground = backgrounds[i][j];
      if (currentBackground == expectedBackground) {
        ++coloredCellsAmount;
      }
    }
  }

  return coloredCellsAmount;
};

Example usage:

=countColoredCellsInRange("Sheet!A2:A" ; "#00ff00")

Also, here is example of it at work (calculating percentages in "Progress graph" sheet).