Google sheets: How to update script to count colored cells with specific text

57 Views Asked by At

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;

};
0

There are 0 best solutions below