I found a script that allows you to count colored cells in google sheets. I would like to update this code to be similar to the CountIf function where you can also specify a criteria (specifically a partial text) but am unsure on how to add it
The original code that only counts colored cells is as follows
function countColoredCells(countRange,colorRef) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = SpreadsheetApp.getActiveSheet();
var activeformula = activeRange.getFormula();
var countRangeAddress = activeformula.match(/((.*),/).pop().trim();
var backGrounds = activeSheet.getRange(countRangeAddress).getBackgrounds();
var colorRefAddress = activeformula.match(/,(.*))/).pop().trim();
var BackGround = activeSheet.getRange(colorRefAddress).getBackground();
var countColorCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countColorCells = countColorCells + 1;
return countColorCells;
};
I attempted to update it myself as shown below but it does not work
function countColoredCells(countRange,colorRef,_criteria) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = SpreadsheetApp.getActiveSheet();
var activeformula = activeRange.getFormula();
var countRangeAddress = activeformula.match(/((.*),/).pop().trim();
var backGrounds = activeSheet.getRange(countRangeAddress).getBackgrounds();
var colorRefAddress = activeformula.match(/,(.*))/).pop().trim();
var BackGround = activeSheet.getRange(colorRefAddress).getBackground();
var _criteriaAddress = activeformula.match(/,(.*))/).pop().trim();
var _criteriatxt = activeSheet.getRange(_criteriaAddress).getBackground();
var countColorCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround && backGrounds[i][k] == _criteriatxt )
countColorCells = countColorCells + 1;
return countColorCells;
};