CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Dingo Bruce
Dingo Bruce

Reputation: 405

Counting cells with particular background color in Google Sheets

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.

Upvotes: 2

Views: 7489

Answers (3)

Doug
Doug

Reputation: 7057

Here is a function I put together for something similar. Rather than a hard-coded value, or needing to know the color beforehand; the first input is the cell that you want to grab the color from.

/**
 * Counts the number of cells with the given color within the range.
 *
 * @param  {"B32"}    target_color_cell   Cell reference to extract the color to count.
 * @param  {"A1:F22"} range_count_color   Range to cycle through and count the color.
 * @returns           integer             The count of the number of cells within range_count_color 
 *                                        which match the color of target_color_cell.
 * @customfunction
 */
function count_cells_by_color( target_color_cell, range_count_color ) {
  if (typeof target_color_cell !== "string")
    throw new Error( "target_color_cell reference must be enclosed in quotes." );
  if (typeof range_count_color !== "string")
    throw new Error( "range_count_color reference must be enclosed in quotes." );
  
  var count=0;
  var target_color = SpreadsheetApp.getActiveSheet().getRange( target_color_cell ).getBackground();
  var range_colors = SpreadsheetApp.getActiveSheet().getRange( range_count_color ).getBackgrounds();
  var t=""
  for(var row=0; row<range_colors.length;row++){
    for(var column=0;column<range_colors[row].length;column++){
      let cell_color = range_colors[row][column];
      if(cell_color == target_color){
        count = count+1;
      }
    }
  }
  
  return count;
}

How to use this:

  1. Create or Open up your Google Sheet.

  2. At the top of the page navigate to Extensions -> App Scripts

  3. Delete the sample that is there and paste in the code from the above block.

  4. Click the "Save" button.

  5. Within any cell of your spreadsheet where you now want to do this calculation enter the "=" button and then specify the function name "count_cells_by_color". For example the following will take the background color from cell A1, and count the number of times it appears in cells A1 through G15.

    =count_cells_by_color("A1", "A1:G15")

Upvotes: 0

Jezor
Jezor

Reputation: 3426

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).

Upvotes: 1

Tom Woodward
Tom Woodward

Reputation: 1713

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

Upvotes: 1

Related Questions