CODEWITHSUNDEEP
google-sheets
JhWebDev
JhWebDev

Reputation: 402

How do you count the row colors in Google Sheets

I have a fairly large Google Sheets document that I need to set up to count how many rows there are based on a certain color.

In my document I am cross-comparing files found in two different folders on our companies servers. One folder (column 1) has nearly 800 individual files and the second folder (column 2) has around 250/300 individual files. When a file is the same between the two folders I will highlight the row (for the sake of argument) Orange. I want to set up google sheets to count how many rows have the orange color.

After researching it I found a way that you can set that up via VBA in Excel, but my document needs to be accessible to the rest of the team (via google drive) Is there a way to set up Google Sheets to do that?

Upvotes: 1

Views: 590

Answers (1)

Andy
Andy

Reputation: 2414

It depends on what you want it returned. Do you just want a number? Do you want a list of only "colored" rows? etc...

One method would be to use Appscript. In your Spreadsheet go to Tools -> Script Editor and create a new script:

//Get Sheet, Range, and Colors
var count = 0;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getDataRange();
var bgColors = range.getBackgrounds();

//Loops through ROWS to get # of X color
for (var i in bgColors) {
    if(bgColors[i][0] == "INSERT COLOR CODE HERE")
      count++;
}
//Do something with your count
ss.getRange("A1").setValue(count);

If you don't want to have to repeatedly call the function everytime, I suggest you put it in a trigger like onOpen(). If you need to do something using the data within the cells, it's similar, just rather than incrementing a count variable, get the values in the specific range and do whatever calculations.

Upvotes: 1

Related Questions