CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user3696118
user3696118

Reputation: 353

How to count for specific string and color?

I've been looking into how to count cells with the countif function, and how to count cells that are colored using scripts and custom functions (like this thing: http://pastebin.com/4Yr095hV), but how would i count cells with a specific string AND color?

Example, I want to count every cell containing the word "one" that has a fill color of white.

EDIT: I was told to add what i had so far, but I am not sure what was meant by that. For counting cells with a specific string I used:

=COUNTIF(A1:A247,"string")

and for counting cells that are colored i used this what was on this page: https://webapps.stackexchange.com/questions/23881/google-spreadsheet-calculating-shaded-cells

but i still don't know how to combine these two TOGETHER.

EDIT: For those looking for this answer, I've found a way to utilize the script Tom posted, and adjusted a line within it.

For Tom's script to work with "wildcards", i used something called .indexOf to always look for any cells containing the string (effectively treating it as if there is always a star before and after the string). On line 32 of his script, I altered it to this:

.map   (function(e,i,a) { if (e.toString().toUpperCase().indexOf(this.toString().toUpperCase()) >= 0){ return 1 } else { return 0 } },str))

So now whenever I want to look for a White cell containing the string "Apple1", it will count it regardless of if it's written as "OrangeApple1B" or whatever. And the casing doesn't matter since it seems like this script always converts the given string to Upper Case anyways.

I am still trying to find out how to incorporate this on a totally different spreadsheet though (using something like IMPORTRANGE to count cells on a TOTALLY DIFFERENT SHEET using this script)...

Upvotes: 2

Views: 1364

Answers (1)

Tom
Tom

Reputation: 1320

function countIfStringAndColor(r, str, color) { 

  var COLORS = {
       "BLACK":"#000000",
       "DARK GRAY 4":"#434343",
       "DARK GRAY 3":"#666666",
       "DARK GRAY 2":"#999999",
       "DARK GRAY 1":"#B7B7B7",
       "GRAY":"#CCCCCC"
      };  

  var range = SpreadsheetApp
                .getActive()
                .getActiveSheet()
                .getRange(r.toString());

  color = color.indexOf("#") == 0 ? color : COLORS[color.toString().toUpperCase()];

  return range
          .getBackgrounds()
          .reduce(function(a,b)   { return  a.concat(b) })
          .map   (function(e,i,a) { return  e.toString().toUpperCase() === this.toString().toUpperCase(); },color)

          .map(function(e,i,a) { return  [e, this[i]] }, 
             range
               .getValues()
               .reduce(function(a,b)   { return a.concat(b) })
               .map   (function(e,i,a) { return e.toString().toUpperCase() === this.toString().toUpperCase() },str))

          .filter(function(e,i,a) {return a[i][0] && a[i][1] })
          .length;
}

METHOD OF OPERATION

  1. The function takes three arguments: Range (String), String, String
  2. The associative array 'COLORS' is supplied to convert the common names of colors to hex format. There are about 90 more colors in the list that I didn't supply for space reasons. I can get you the full list if you would like.
  3. Grabbing the Range.
  4. Checks to see if color is already in hex format. If not it tries to find a common name key in COLORS and return the hex value. From here out everything is toString() and toUpperCase() to help prevent errors.
  5. The code from here out is one chain of array manipulation that will produce the solution for the function to return.
  6. Grab the needed background colors.
  7. .reduce, coupled with .concat (both Array Methods), is used to flatten the background color array. It changes it from a rectangular array of arrays to a one dimensional list.
  8. .map goes through each element of the array and applies the given function. In this case we are seeing if the array element (e) is the same as the color supplied. Take note of how 'color' is called outside the closing curly bracket. It is the 'thisArg', and the 'this' inside the function is an image of it. The array is now reduced to a series of true/false elements.
  9. This map is used to combine the two arrays, 'color' and 'str'. The indented part right below is the same steps we used to get 'color' to a series of true/false elements, but now applied to 'str'. All those operations are performed while 'str' is being called as the thisArg for the current map function. The map function then returns a single array of the form [color,str] which is made up of many elements of [true,false] [true,true] [false,false] pairs.
  10. We are only interested in the solutions where both 'color' and 'str' are true, so we can use .filter to remove all the other elements, leaving use with an array of only [true, true] pairs.
  11. Each [true, true] pair is a unique solution to the equation. We can just grab the length of the array to see how many solutions we have found! This is the value that is passed to the return at the beginning.

Upvotes: 3

Related Questions