0
862
VistasGoogle Sheets Script to count cells with color with several criteria
In a google sheets table, I use a script that allows me to count the cells in color in a column that displays dates as values. Each date over 11 days is colored in red, and my script works very well.
In another column, I indicate the name of each salesman. What I would like is to be able to count the number of red cells that belong to each salesman.
The date column is column "G" and the salesman column is column "F" (In my script it doesn't matter because I indicate in which column to count).
My Sheet:
https://docs.google.com/spreadsheets/d/1BFd57xpFOJMOAjuRaiO6_cLCMMxQKDwi_TB6dOCDb9A/edit#gid=0
The formula for my script is:
=CompteCouleurs($G6:$G;C4)
"$G6:$G" is the range to count "C4" is the sample of the color to search
My script is:
function SommeCouleurs(plage,couleur) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formule = activeRange.getFormula();
var laplage = formule.match(/\((.*)\;/).pop();
var range = activeSheet.getRange(laplage);
var bg = range.getBackgrounds();
var values = range.getValues();
var lacouleur = formule.match(/\;(.*)\)/).pop();
var colorCell = activeSheet.getRange(lacouleur);
var color = colorCell.getBackground();
var total = 0;
for(var i=0;i<bg.length;i++)
for(var j=0;j<bg[0].length;j++)
if( bg[i][j] == color )
total=total+(values[i][j]*1);
return total;
};
function CompteCouleurs(plage,couleur) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formule = activeRange.getFormula();
var laplage = formule.match(/\((.*)\;/).pop();
var range = activeSheet.getRange(laplage);
var bg = range.getBackgrounds();
var values = range.getValues();
var lacouleur = formule.match(/\;(.*)\)/).pop();
var colorCell = activeSheet.getRange(lacouleur);
var color = colorCell.getBackground();
var count = 0;
for(var i=0;i<bg.length;i++)
for(var j=0;j<bg[0].length;j++)
if( bg[i][j] == color )
count=count+1;
return count;
};
1 Respuestas
Responde la pregunta0
First, expand your area to G and F =CompteCouleurs($F6:$G;C4)
Then as you only take column G into account, you can omit for(var j=0;j<bg[0].length;j++)
Therefore you can check color in G and salesman in F as follows if( bg[i][1] == color && values[i][0] == salesman )
If you want to include the salesman as parameter, you will have to change the regex as follows to retrieve each argument (args will be an array)
var args = formule.match(/\(.*\)/g)[0].match(/[A-Z0-9:]+/g)
I recommand also to add a dummy parameter (checkbox) to update the calculation
Correction of your script:
function compteCouleursSi(plage, couleur, vendeur) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formule = activeRange.getFormula();
// array of arguments
var args = formule.match(/(?<=\().*(?=\))/g)[0].split(/[;|,]/)
var laplage = args[0];
if (laplage.includes('!')) {
var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(laplage.split('!')[0].replace(/'/g,'')).getRange(laplage.split('!')[1].trim());
}else{
var range = activeSheet.getRange(laplage);
}
var bg = range.getBackgrounds();
var values = range.getValues();
var lacouleur = args[1];
var color = activeSheet.getRange(lacouleur).getBackground();
var salesman = args[2];
var who = activeSheet.getRange(salesman).getValue()
var total = 0;
for (var i = 0; i < bg.length; i++)
if (bg[i][1] == color && values[i][0] == who)
total += 1;
return total;
};
function sommeCouleursSi(plage, couleur, vendeur) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formule = activeRange.getFormula();
// array of arguments
var args = formule.match(/(?<=\().*(?=\))/g)[0].split(/[;|,]/)
var laplage = args[0];
if (laplage.includes('!')) {
var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(laplage.split('!')[0].replace(/'/g,'')).getRange(laplage.split('!')[1].trim());
}else{
var range = activeSheet.getRange(laplage);
}
var bg = range.getBackgrounds();
var values = range.getValues();
var lacouleur = args[1];
var color = activeSheet.getRange(lacouleur).getBackground();
var salesman = args[2];
var who = activeSheet.getRange(salesman).getValue()
var total = 0;
for (var i = 0; i < bg.length; i++)
if (bg[i][1] == color && values[i][0] == who)
total += values[i][1];
return total;
};
