CODEWITHSUNDEEP
javascriptregexgoogle-apps-scriptgoogle-sheets
kestrelol
kestrelol

Reputation: 83

Count rows by background color failed

Hey guys I am in google sheets excel and I want to count how many rows have the sheet based in background color.

I have the next code:

function countColoredCells(countRange,colorRef) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  var countCells = 0;
  for (var i = 0; i < backGrounds.length; i++)
    for (var k = 0; k < backGrounds[i].length; k++)
      if ( backGrounds[i][k] == BackGround )
        countCells = countCells + 1;
  return countCells;
};

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Contar",
    functionName : "countColoredCells"
  }];
  spreadsheet.addMenu("Contar numero de casillas por color", entries);
}

Code

But when i put the next code doesn't work? Shows error. What's happens?

=countcoloredcells(B1:B9,A1)

Error here

Error log here

Error with change of code suggested by Chris W

Error with change of code suggested by Chris W PART 2

Upvotes: 1

Views: 3588

Answers (3)

Pedro Rom&#227;o
Pedro Rom&#227;o

Reputation: 2327

Use this code instead. The arguments need to be passed as strings.

/**
* Usage countColoredCells("A1:B3";"C5")
* @param {range} countRange Range to be evaluated
* @param {colorRef} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/

function countColoredCells(countRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();

  var backgrounds = activeSheet.getRange(countRange).getBackgrounds();
  var colorRefBackground = activeSheet.getRange(colorRef).getBackground();

  var count = 0;

  for(var i=0;i<backgrounds.length;i++)
    for(var j=0;j<backgrounds[0].length;j++)
      if( backgrounds[i][j] == colorRefBackground )
        count=count+1;
  return count;
};

Upvotes: 1

TheMaster
TheMaster

Reputation: 50445

The script you posted uses a unusual method of getting the range argument. It fails at regex because at your location, Sheets uses ; as delimiter rather than , making the match null. Regardless, Taking the argument directly should work. So, Try changing the original code

From 

var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();

To 

var countRangeAddress = countRange
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var colorRefAddress = colorRef

You could also try replacing the regex match , with ; ( like match(/\((.*)\;/).)

Usage should be like:

=countcoloredcells("B1:B9";"A1")

Upvotes: 2

Chris
Chris

Reputation: 2107

When you run activeformula.match(/\((.*)\,/) I think there are instances when .match() is returning null and therefore you can not call .pop(). You'll have to do something like this...

var match = activeformula.match(/\((.*)\,/);
if(match) var countRangeAddress = match.pop().trim(); //if match is not null...

But of course deal with not having that variable in the rest of your script.

Upvotes: 1

Related Questions