I have a master sheet in Google Spreadsheet as below;

what i want is to get color counts from a given tab/sheets(Column A) in google spreadsheet. According to my research in stackoverflow, i found the code which works perfect in an active single sheet (

Multiple Sheets

function getBGColorCountMultipleSheets(col, shts, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var shts = shts || ['Sheet1', 'Sheet2', 'Sheet0'];
  var col = col || 1;
  let colors = { pA: [] };
  ss.getSheets().filter(s => ~shts.indexOf(s.getName())).forEach(sh => {
    let bgs = sh.getRange(1, col, sh.getMaxRows()).getBackgrounds().flat();
    bgs.forEach((c, i) => {
      if (!colors.hasOwnProperty(c)) {
        colors[c] = 1;
        colors.pA.push(c);
      } else {
        colors[c]  = 1;
      }
    });
  });
  let html = '<style>td,th{border:1px solid black;width:25px;}</style><table>';
  //add this to remove white filter(p => p != "#ffffff")
  colors.pA.forEach(p => {
    html  = `<tr><td style="background-color:${p};"></td><td>${colors[p]}</td></tr>`;
  });
  html  = '</table>'
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), 'Color Count');
}

CodePudding user response：

Here is the working code for counting colored cells in multiple sheets in a single google spreadsheet:

function goToSheet(countRange,colorRef,sheetName) {

    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    SpreadsheetApp.setActiveSheet(spreadsheet.getSheetByName(sheetName));
    var activeRange = SpreadsheetApp.getActiveRange();
    var activeSheet = activeRange.getSheet();
    var formula = activeRange.getFormula();

    const backgrounds = SpreadsheetApp.getActive()
      .getSheetByName(sheetName)
      .getRange(countRange)
      .getBackgrounds();
    var colorRefBackground = activeSheet.getRange(colorRef).getBackgrounds();

    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;

}

I would like to thank user https://stackoverflow.com/users/8404453/themaster who helped me to achieve it and also, i changed

var colorRefBackground = activeSheet.getRange(colorRef).getBackground()

to

var colorRefBackground = activeSheet.getRange(colorRef).getBackgrounds()

which returns the background colors of the cells in the range.