CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Curse_USMC
Curse_USMC

Reputation: 33

Google sheets Count cells by color if value in cell is equal to 1 or more

I'm working in Google sheets. I'm not a developer but I got this function from a YouTube video. In my Google sheet I have a list of task in column A and these tasks award 0-5 points when complete. The function I have looks at the range specified counts how many of them are green. works fine but I only interested in counting how many tasks are complete that award points of 1 or more. Here is the function:

function countColoredCells(countRange,colorRef,rangeSum) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var range = activeSheet.getRange(countRange);
  var rangeHeight = range.getHeight();
  var rangeWidth = range.getWidth();
  
  var bg = range.getBackgrounds();  
  var colorCell = activeSheet.getRange(colorRef);
  var color = colorCell.getBackground();
  
  var count = 0;
  
  for(var i = 0; i < rangeHeight; i++)
    for(var j = 0; j < rangeWidth; j++)
      if( bg[i][j] == color )
        count = count+1;
  
  return count;
};

To explain this code a bit more it takes 3 augments. The First points to a that has a range in it. In my sheet in cell C174 I have written there C2:C173. So I give the function C174 for the first augment and it looks there for the range. The second augment takes a cell with the color you want to be counted. So I have the G5 cell Colored Green so I give the functions second augment G5 because I want it to count the green cells. The third augment is a little tricky to explain but you give the function a cell that as a Boolean check box thing and when you change it's value it refreshes the function to update it's count. Here is my google sheets https://docs.google.com/spreadsheets/d/1W05-UKB-bBGMqOUC5OO9XirxnRBfJeMOj3ZMHoENcSQ/edit?usp=sharing There is a second custom function I am using here that I got from the YouTube video that just looks at the range of colored and sums their value. Also if you are looking at this sheet and are confused. the values in the colored cells that are formatted to be hidden.

Upvotes: 1

Views: 843

Answers (2)

Cooper
Cooper

Reputation: 64040

Count Colors

function colorCounter() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");//source sheet
  const cs = sh.getDataRange().getBackgrounds();
  const vs = sh.getDataRange().getValues();
  let obj = { pA: [] };
  //color counter
  vs.forEach((r, i) => {
    r.forEach((c, j) => {
      if (!isNaN(c) && c >= 1) {
        if (!obj.hasOwnProperty(cs[i][j])) {
          obj[cs[i][j]] = { color: cs[i][j], count: 1, row: i + 1, col: j + 1 }
          obj.pA.push(cs[i][j]);
        } else {
          obj[cs[i][j]].count += 1;
        }
      }
    })
  })
  let vA = [["Color Counter"]];
  let cA = [["#ffffff"]];
  obj.pA.forEach(p => {
    cA.push([obj[p].color]);
    vA.push([obj[p].count])
  })
  const osh = ss.getSheetByName("Sheet1");//Display Sheet
  osh.clear();
  osh.getRange(1, 1, vA.length, 1).setValues(vA);
  osh.getRange(1, 1, cA.length, 1).setBackgrounds(cA);
}

Demo:

enter image description here

Upvotes: 1

Yuri Khristich
Yuri Khristich

Reputation: 14502

You can try to make these three changes in the function:

function countColoredCells(countRange,colorRef,rangeSum) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var range = activeSheet.getRange(countRange);
  var rangeHeight = range.getHeight();
  var rangeWidth = range.getWidth();
  
  var bg = range.getBackgrounds();
  var colorCell = activeSheet.getRange(colorRef);
  var color = colorCell.getBackground();

  var points_range = countRange.replace(/[A-z]+/g,'B');        // <--- HERE
  var points = activeSheet.getRange(points_range).getValues(); // <--- HERE
  
  var count = 0;
  
  for(var i = 0; i < rangeHeight; i++)
    for(var j = 0; j < rangeWidth; j++)
      if( bg[i][j] == color && points[i][0] > 0 ) // <--- HERE
        count = count+1;
  
  return count;
};

Upvotes: 2

Related Questions