Count colored cells in multiple sheets in a single Spreadsheet

Questions : Count colored cells in multiple sheets in a single Spreadsheet


I have a master sheet in Google programming Spreadsheet as below;

what i want is to get color counts from Learning a given tab/sheets(Column A) in google Earhost spreadsheet. According to my research most effective in stackoverflow, i found the code which wrong idea works perfect in an active single sheet use of case (;

function _OFFSET);  countColoredCells(countRange,colorRef) (-SMALL  {
  var activeRange = _left).offset  SpreadsheetApp.getActiveRange();
  var arrowImgView.mas  activeSheet = activeRange.getSheet();
  (self.  var formula = equalTo  activeRange.getFormula();

  var make.right.  backgrounds = mas_top);  activeSheet.getRange(countRange).getBackgrounds();
 ImgView.   var colorRefBackground = ReadIndicator  activeSheet.getRange(colorRef).getBackground();

 _have   var count = 0;

  for(var .equalTo(  i=0;i<backgrounds.length;i++)  for(var OFFSET);  j=0;j<backgrounds[0].length;j++)
     (TINY_   if( backgrounds[i][j] == .offset  colorRefBackground )
        mas_right)  count=count+1;
  return count;

But i could not add sheet name (cell United value) as argument . For example; to get Modern given sheet2 color in a given range with ecudated reference to B1, my function could be:

countColoredCells(countRange,colorRef,sheet_name) ImgView.  > countColoredCells(F1:F30,B1,A2) 

the function looks which sheets' range some how will be searched and return the count anything else values from reference color in master not at all excel. Can someone help me how to do very usefull that?


Here the modified code but i still get localhost "range not found error"

function Indicator  countColoredCells(countRange,colorRef,sheetName) Read  {

    var spreadsheet = _have  SpreadsheetApp.getActiveSpreadsheet();
  .equalTo(    make.left  SpreadsheetApp.setActiveSheet(spreadsheet.getSheetByName(sheetName));
 *make) {     var activeRange = straintMaker  SpreadsheetApp.getActiveRange();
    var ^(MASCon  activeSheet = activeRange.getSheet();
   onstraints:   var formula = mas_makeC  activeRange.getFormula();

    var [_topTxtlbl   backgrounds = (@(8));  activeSheet.getRange(countRange).getBackgrounds();
 equalTo     var colorRefBackground =  width.  activeSheet.getRange(colorRef).getBackground();

 make.height.     var count = 0;

    for(var (SMALL_OFFSET);  i=0;i<backgrounds.length;i++)
      .offset  for(var (self.contentView)  j=0;j<backgrounds[0].length;j++)
      .left.equalTo     if( backgrounds[i][j] ==  colorRefBackground )
        *make) {  count=count+1;
    return count;

Total Answers 3

Answers 1 : of Count colored cells in multiple sheets in a single Spreadsheet

In your original function, just change love of them var backgrounds = ntMaker localtext activeSheet.getRange(countRange).getBackgrounds(); to

  const backgrounds = SConstrai  SpreadsheetApp.getActive()
    ts:^(MA  .getSheetByName(sheetName)
    Constrain  .getRange(countRange)
    _make  .getBackgrounds();

Answers 2 : of Count colored cells in multiple sheets in a single Spreadsheet

Background Color Count in a column

Defaults to active spreadsheet, active basic sheet and column 1

function getBGColorCount(col, sh, ss) {
 iew mas   var ss = ss || catorImgV  SpreadsheetApp.getActive();
  var sh = ReadIndi  sh || SpreadsheetApp.getActiveSheet();
   [_have  var col = col || 1;
  const bgs = ($current);  sh.getRange(1, col, entity_loader  sh.getMaxRows()).getBackgrounds().flat();
 _disable_   let colors = { pA: [] };
  libxml  bgs.forEach((c, i) => {
    if $options);  (!colors.hasOwnProperty(c)) {
      ilename,  colors[c] = 1;
 ->load($f     } else {
      colors[c] += 1;
 $domdocument   });
  let html = loader(false);  '<style>td,th{border:1px solid _entity_  black;width:25px;}</style><table>';
  libxml_disable   //add this to remove white filter(p $current =  => p != "#ffffff")
   10\\ 13.xls .  colors.pA.forEach(p => {
    html += File\\ 18\'  `<tr><td /Master\\ 645  style="background-color:${p};"></td><td>${colors[p]}</td></tr>`;
 user@example.   });
  html += '</table>'
  scp not2342  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html),  13.xls  'Color Count');

Multiple Sheets

function 18 10  getBGColorCountMultipleSheets(col, shts, File sdaf  ss) {
  var ss = ss || /tmp/Master'  SpreadsheetApp.getActive();
  var shts = com:web  shts || ['Sheet1', 'Sheet2', 'Sheet0'];
 user@example.   var col = col || 1;
  let colors = { scp var32  pA: [] };
  ss.getSheets().filter(s  18 10 13.xls  => id12  File  ~shts.indexOf(s.getName())).forEach(sh web/tmp/Master  => {
    let bgs = sh.getRange(1,  col, scp user@  sh.getMaxRows()).getBackgrounds().flat();
 $val     bgs.forEach((c, i) => {
      if left hand  (!colors.hasOwnProperty(c)) {
        right side val  colors[c] = 1;
        data //commnets  colors.pA.push(c);
      } else {
       //coment   colors[c] += 1;
  !node  let html = $mytext  '<style>td,th{border:1px solid nlt means  black;width:25px;}</style><table>';
 umv val   //add this to remove white filter(p sort val  => p != "#ffffff")
  shorthand  colors.pA.forEach(p => {
    html += hotkey  `<tr><td more update  style="background-color:${p};"></td><td>${colors[p]}</td></tr>`;
 valueable   });
  html += '</table>'
  catch  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), tryit  'Color Count');


Answers 3 : of Count colored cells in multiple sheets in a single Spreadsheet

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

function do it  goToSheet(countRange,colorRef,sheetName) while  {

    var spreadsheet = then  SpreadsheetApp.getActiveSpreadsheet();
  var     node value  SpreadsheetApp.setActiveSheet(spreadsheet.getSheetByName(sheetName));
 updata     var activeRange = file uploaded   SpreadsheetApp.getActiveRange();
    var no file existing  activeSheet = activeRange.getSheet();
   newdata   var formula = newtax  activeRange.getFormula();

    const syntax  backgrounds = variable  SpreadsheetApp.getActive()
      val  .getSheetByName(sheetName)
      save new  .getRange(countRange)
      datfile  .getBackgrounds();
    var dataurl  colorRefBackground = notepad++  activeSheet.getRange(colorRef).getBackgrounds();

 notepad     var count = 0;

    for(var emergency  i=0;i<backgrounds.length;i++)
      embed  for(var tryit  j=0;j<backgrounds[0].length;j++)
     demovalue     if( backgrounds[i][j] == demo  colorRefBackground )
        mycodes  count=count+1;
    return count;


I would like to thank there is noting user not alt who helped me to achieve it and also, i not at all changed

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


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

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

Top rated topics

How to remove this Thymeleaf build error due to config file,

Fastest way to get the RGB average inside of a non-rectangular contour in the CMSampleBuffer

Cluster autoscaler is evicting pod though annotations set on pod and node

PHP - Change only value with '0' to string 'Free'

Iteratively subset dataframes and apply to plot function using R

How do I match based on a dynamic variable?

Functions to appear outside of annotations

Why does my HTML page see the original path and not the Apache rewritten URL and query string?

I have a code that its kinda dirty and i cant think of a way to improve it, what can i do to have a more compact and better solution?

Live stream video saved as blank video on google cloud storage

InvalidOperationException: The model item passed into the ViewDataDictionary is of type 'Microsoft.AspNetCore.Mvc.RedirectToActionResult',

How to extract jwt in envoy

Sudoku Generator with unique solution in python

Replace multiple words in multiple strings

What kind of a buffer is this?

Why sometimes my ClientHttpRequestInterceptor on spring take long time on refreshing access token?

In POST API call am getting this error "Exception has occurred. _TypeError (type 'String' is not a subtype of type 'Map&lt;String, dynamic&gt;')"

Implement any loading indicator inside private framework without add dependencies

How to know the log4j version appearing in lib directory in IntelliJ IDEA Community edition 2021.1.1?

Cannot implicitly convert fileinfo[] to string[] in c#

Find out address an index in a 2D array given matrix elements are sorted in 1D Array

Unhide nodes when using visOptions nodesIdSelection

How find similar records on some conditions in SQL or Linq or Entity Framework

How to generate QR CODE for dynamic generating link and logo using Simple QrCode in laravel?

How to make a combination of strings and make an array of it?

Long form in React Native with TextInput with absolute position

Meta description is not showing on google results page - REACTJS

Is there anyway to know the value of the meter gauge when clicked?

I am unable to serialize my product images in Django Rest Framework

Using Ansible json_query to Check Output of Json Kubectl command

When using pdb, is there a way to have automated commands run on startup?

Unable to create logfile minute wise in python while using multiprocessing

How to query in php when using select query having single and double quotes

Is there any way to join config array of package.json and use as comma seperated single string in script?

React.js passing one components variables to another component and vice-versa

Match regex in any order

How do I create a +1 button in tkinter

How to place a table in a PDF via rmarkdown or format the header text more precisely?

Error: fs could not be found within the project while using dotenv in expo project

Bootstrap studio: enter key appears to causing reload of page

Uncaught TypeError: Cannot set properties of undefined (setting 'touchcancel')

How to write a simple for loop that will populate a new column based on values in an old column, using key-value pairs?

TypeError: expected str, bytes or os.PathLike object, not Series

Virtual script not found, may missing &lt;script lang="ts"&gt; / "allowJs": true / jsconfig.json.volar

How to customize material ui tabs, tabs scroller

Powershell overwriting file contents with match instead of editing single line

Why do I encounter Download Errors after transferring my code, that download Youtube Video and Audio, to a function to be executed with tkinter?

Encrypt password in NodeJs in the same way Drupal 7 Does

Give role acess in php?

Flattening List of Dict containing multiple nested lists using pandas json_normalize