I have a Google Sheets document with multiple date columns that use conditional formatting. The dates automatically change to either blue or yellow background colors based on certain conditions. I need to create a Google Apps Script that can scan through a specific cell range and count how many cells have the yellow background color, then put that count number into another cell. Is there a way to write a script that checks cell background colors and returns the total count? I want to do this for several different date ranges in my spreadsheet and get separate counts for each range.
Yes, counting by background color in Google Sheets using Google Apps Script is achievable, but there’s an important limitation to consider. The getBackground() function cannot retrieve colors set by conditional formatting – it only identifies colors that are directly applied to the cells. For your case, you need to implement a function that mimics the logic of your conditional formatting rules instead. For instance, if your conditional formatting colors dates yellow when they are within 30 days, ensure your script checks if each date fits that condition. This method is more effective than trying to read actual background colors. While you could theoretically use the Sheets API to handle conditional formatting colors, it adds unnecessary complexity. Hence, sticking to condition-based checks should provide the reliability and speed you need.
Totally agree - getBackground() doesn’t work with conditional formatting. Learned that the hard way too lol. Easiest fix is duplicating your conditional formatting logic in the script. If dates turn yellow based on certain criteria, just code that same criteria and count matches directly instead of reading colors.
Had this exact issue last month with a project tracker. Google Apps Script’s getBackground() won’t detect conditional formatting colors - it just returns null or the default color even when you see the yellow highlighting. Here’s what actually worked: I built a custom function that copies the conditional formatting logic in code. If your yellow highlighting shows past due dates or dates within a specific range, just recreate that same condition in your script using date comparisons. Loop through your range, test each cell against your criteria, and count the matches. Way more reliable than trying to read background colors, plus it runs faster since you’re not hammering the API with formatting checks.