I am trying to count colored cells in my google sheet project with this script :

function nbSelonCouleur(range) {
  var r = SpreadsheetApp.getActiveRange();
  var adresse = r.getFormula().match(/(?<=\().*(?=;)/g)

  try{
    var f = SpreadsheetApp.getSheetByName(adresse.split('!')[0].replace("'",""));
    var adresse = adresse.split('!')[1];
  }
  catch(e){
    var f = SpreadsheetApp.getActiveSheet();
  }
  
  var couleurs = f.getRange(adresse).getBackgrounds();
  var couleur = r.getBackgrounds();
  var nb = 0;
  for (var i = 0; i < couleurs.length; i  )
    for (var k = 0; k < couleurs[i].length; k  )
      if ( couleurs[i][k] == couleur )
        nb  ;
  return nb;
};

Here is the formula of my cell :

=nbSelonCouleur('Planning EDMZI'!$AT$17:$AT,$B$5)

I dont know how to solve the error, here is the error : Exception : Argument cannot be null:a1Notation (line 13)

Just to add : $B$5 is a cell where there is a checkbox that actualise things when i made a change.

CodePudding user response：

Count Colors

function colorCounter() {
  const ss = SpreadsheetApp.getActive();
  const ui = SpreadsheetApp.getUi();
  const sh = ss.getActiveSheet();
  const cA = sh.getDataRange().getBackgrounds();
  let co = { pA: [] };
  cA.forEach((r, i) => {
    r.forEach((c, j) => {
      if (!co.hasOwnProperty(c)) {
        co[c] = { count: 0, locations: [] }
        co[c].count = 1;
        co[c].locations.push(sh.getRange(i   1, j   1).getA1Notation());
        co.pA.push(c);
      } else {
        co[c].count  = 1;
        co[c].locations.push(sh.getRange(i   1, j   1).getA1Notation());
      }
    });
  });
  let html = '';
  co.pA.forEach(c => {
    html  =`<br><textarea cols="50" rows="5"style="background:${c}" />${co[c].count}/${co[c].locations.join(', ')}</textarea>`;
  })
  Logger.log(html);
  ui.showModelessDialog(HtmlService.createHtmlOutput(html),'Colors')
}

CodePudding user response：

Thx for your help ! Someone finally found the solution !

function nbSelonCouleur(range) {
  var r = SpreadsheetApp.getActiveRange();
  var adresse = r.getFormula().match(/(?<=\().*(?=,)/g)

  try{
    var f = SpreadsheetApp.getSheetByName(adresse.split('!')[0].replace("'",""));
    var adresse = adresse.split('!')[1];
  }
  catch(e){
    var f = SpreadsheetApp.getActiveSheet();
  }
  
  var couleurs = f.getRange(adresse).getBackgrounds();
  var couleur = r.getBackgrounds();
  var nb = 0;
  for (var i = 0; i < couleurs.length; i  )
    for (var k = 0; k < couleurs[i].length; k  )
      if ( couleurs[i][k] == couleur )
        nb  ;
  return nb;
};

It is an US version so at line 3 i had to replace ";" to ","