Savez-vous comment trier et filtrer vos données en fonction de leur couleur dans Google Sheets ? Cette fonctionnalité très pratique vous permet d’organiser vos données en fonction de la couleur du texte ou des cellules.
Imaginez que vous ayez mis en évidence certaines lignes de données relatives à un client important. Grâce au tri et au filtrage par couleur dans Google Sheets, vous pouvez facilement faire remonter ces lignes en haut de votre ensemble de données, voire même ne montrer que ces lignes-là.
Ces méthodes sont également très utiles pour supprimer les doublons dans Google Sheets.
Et le meilleur dans tout ça, c’est qu’elles sont vraiment faciles à utiliser. Voyons comment :
Trier par couleur dans Google Sheets
Supposons que vous ayez un ensemble de données avec des lignes mises en évidence, comme par exemple tous les appartements dans cet ensemble de données :
Ajoutez un filtre (l’icône en forme d’entonnoir dans la barre d’outils, indiqué en rouge dans l’image ci-dessus).
Sur n’importe quelle colonne, cliquez sur le filtre et choisissez l’option « Trier par couleur ».
Vous pouvez filtrer par la couleur de fond de la cellule (comme le jaune dans cet exemple) ou par la couleur du texte.
Le résultat de l’application de ce tri est que toutes les lignes colorées seront placées en haut de votre ensemble de données.
Cela est extrêmement utile si vous souhaitez examiner tous les éléments en même temps. Une autre raison pourrait être s’il s’agit de doublons que vous avez mis en évidence et que vous pouvez maintenant supprimer.
Filtrer par couleur dans Google Sheets
La méthode de filtrage par couleur dans Google Sheets est très similaire à celle du tri par couleur.
Une fois que vous avez ajouté des filtres à votre ensemble de données, cliquez dessus pour faire apparaître le menu. Sélectionnez « Filtrer par couleur » puis choisissez de filtrer en fonction de la couleur de fond de la cellule ou de la couleur du texte.
Dans cet exemple, j’ai utilisé le filtrage par couleur dans Google Sheets pour afficher uniquement les lignes mises en évidence en jaune, ce qui facilite leur examen.
Il existe une option pour supprimer le filtre par couleur en le réglant sur « aucun », que l’on trouve dans le menu « Filtrer par couleur ». Cette option n’est pas disponible pour la méthode de tri par couleur.
Solution avec Apps Script
Lorsque j’ai initialement publié cet article, le tri et le filtrage par couleur n’étaient pas disponibles nativement dans Google Sheets, alors j’ai créé un petit script pour ajouter cette fonctionnalité à une feuille.
Celles-ci ont été ajoutées le 11 mars 2020. Vous pouvez en lire plus ici dans le blog de la mise à jour de Google Workspace.
Voici ma solution originale avec Apps Script, que je laisse ici par souci d’intérêt général.
Avec quelques lignes simples de code Apps Script, nous pouvons implémenter notre propre version.
Cet article vous montrera comment mettre en œuvre cette même fonctionnalité dans Google Sheets.
C’est une idée assez basique.
Nous devons connaître la couleur de fond de la cellule que nous voulons trier ou filtrer (saisie utilisateur 1). Ensuite, nous devons savoir quelle colonne utiliser pour effectuer le tri ou le filtrage (saisie utilisateur 2). Enfin, nous devons effectuer le tri ou le filtrage.
Donc, la première étape est de demander à l’utilisateur de saisir la cellule et les colonnes.
J’ai mis en place cette fonctionnalité de tri par couleur dans Google Sheets en utilisant une boîte de dialogue sans mode, qui permet à l’utilisateur de cliquer sur les cellules de la feuille Google indépendamment de la boîte de dialogue. Lorsque l’utilisateur a sélectionné la cellule ou la colonne, nous la stockons à l’aide du service Properties pour la récupérer lorsque nous voulons trier ou filtrer les données.
Tri par couleur avec Apps Script
Fonctionnement global de notre programme :
- Menu personnalisé pour exécuter le programme de tri par couleur dans Google Sheets
- Boîte de dialogue pour demander à l’utilisateur la cellule de couleur
- Enregistrer la cellule de couleur à l’aide du service Properties
- Deuxième boîte de dialogue pour demander à l’utilisateur la colonne de tri/filtrage
- Enregistrer la colonne de tri/filtrage à l’aide du service Properties
- Afficher les choix de couleur et de colonne et confirmer
- Récupérer les couleurs de fond de la colonne de tri/filtrage
- Ajouter une colonne d’aide aux données de la feuille avec ces couleurs de fond
- Trier/Filtrer cette colonne d’aide, en fonction de la cellule de couleur
- Effacer les valeurs dans la mémoire du Document Properties
Voyons chacune de ces sections plus en détail.
Ajouter un menu personnalisé (étape 1)
Il s’agit simplement de code Apps Script standard pour ajouter un menu personnalisé à votre feuille Google :
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Outil de couleur')
.addItem('Trier par couleur...', 'sortByColorSetupUi')
.addItem('Effacer les plages','clearProperties')
.addToUi();
}Demander à l’utilisateur de choisir la cellule et la colonne (étapes 2, 4 et 6)
J’utilise des boîtes de dialogue sans mode pour les questions, ce qui permet à l’utilisateur d’interagir avec la feuille et de cliquer directement sur les cellules qu’il souhaite sélectionner.
function sortByColorSetupUi() {
var colorProperties = PropertiesService.getDocumentProperties();
var colorCellRange = colorProperties.getProperty('colorCellRange');
var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
var title='No Title';
var msg = 'No Text';
// si colorCellRange n'existe pas
if(!colorCellRange) {
title = 'Sélectionnez la cellule de couleur';
msg = '<p>Veuillez cliquer sur la cellule avec la couleur de fond sur laquelle vous souhaitez trier, puis cliquez sur OK.</p>';
msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(1); google.script.host.close();" />';
dispStatus(title, msg);
}
// si colorCellRange existe et sortColumnLetter n'existe pas
if (colorCellRange && !sortColumnLetter) {
title = 'Sélectionnez la colonne de tri';
msg = '<p>Veuillez sélectionner la colonne sur laquelle vous souhaitez trier, ou cliquez sur une cellule de cette colonne. Cliquez sur OK lorsque vous êtes prêt.</p>';
msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(2); google.script.host.close();" />';
dispStatus(title, msg);
}
// si colorCellRange et sortColumnLetter existent
if(colorCellRange && sortColumnLetter) {
title= 'Affichage des plages de cellules de couleur et de tri';
msg = '<p>Confirmez les plages avant de trier :</p>';
msg += 'Plage de cellule de couleur : ' + colorCellRange + '<br />';
msg += 'Colonne de tri : ' + sortColumnLetter + '<br />';
msg += '<br /><input type="button" value="Trier par couleur" onclick="google.script.run.sortData(); google.script.host.close();" />';
msg += '<br /><br /><input type="button" value="Effacer les choix et sortir" onclick="google.script.run.clearProperties(); google.script.host.close();" />';
dispStatus(title,msg);
}
}
// Affichage de la boîte de dialogue sans mode
function dispStatus(title,html) {
var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
var html = typeof(html) !== 'undefined' ? html : '<p>No html provided.</p>';
var htmlOutput = HtmlService
.createHtmlOutput(html)
.setWidth(350)
.setHeight(200);
SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);
}
// Fonction d'aide pour passer de la boîte de dialogue 1 (sélection de la cellule de couleur) à la boîte de dialogue 2 (sélection de la colonne de tri)
function sortByColorHelper(mode) {
var mode = (typeof(mode) !== 'undefined')? mode : 0;
switch(mode) {
case 1:
setColorCell();
sortByColorSetupUi();
break;
case 2:
setSortColumn();
sortByColorSetupUi();
break;
default:
clearProperties();
}
}Les boutons sur les boîtes de dialogue utilisent l’API google.script.run côté client pour appeler des fonctions côté serveur d’Apps Script.
Ensuite, google.script.host.close() est également une API JavaScript côté client qui ferme la boîte de dialogue actuelle.
Enregistrer les choix de cellule et de colonne dans le stockage de propriétés (étapes 3 et 5)
Ces deux fonctions enregistrent les plages de cellules que l’utilisateur sélectionne dans le stockage de propriétés de la feuille :
// enregistre la plage de cellules de couleur dans les propriétés
function setColorCell() {
var sheet = SpreadsheetApp.getActiveSheet();
var colorCell = SpreadsheetApp.getActiveRange().getA1Notation();
var colorProperties = PropertiesService.getDocumentProperties();
colorProperties.setProperty('colorCellRange', colorCell);
}
// enregistre la plage de colonne de tri dans les propriétés
function setSortColumn() {
var sheet = SpreadsheetApp.getActiveSheet();
var sortColumn = SpreadsheetApp.getActiveRange().getA1Notation();
var sortColumnLetter = sortColumn.split(':')[0].replace(/(d)/g,'').toUpperCase(); // trouver la lettre de colonne
var colorProperties = PropertiesService.getDocumentProperties();
colorProperties.setProperty('sortColumnLetter', sortColumnLetter);
}En exécutant ces fonctions, nous avons l’adresse de la cellule de couleur (au format A1) et la lettre de la colonne de tri/filtrage enregistrées dans le stockage de propriétés de la feuille pour une utilisation future.
Trier les données (étapes 7, 8 et 9)
Une fois que nous avons sélectionné à la fois la cellule de couleur et la colonne de tri/filtrage, le flux du programme nous dirige vers le tri/filtrage effectif des données. C’est le bouton dans la troisième boîte de dialogue, qui, lorsqu’on clique dessus, exécute l’appel google.script.run.sortData();.
La fonction sortData est définie comme suit :
// trier les données en fonction de la cellule de couleur et de la colonne choisie
function sortData() {
// obtenir les propriétés
var colorProperties = PropertiesService.getDocumentProperties();
var colorCell = colorProperties.getProperty('colorCellRange');
var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
// extrait la lettre de colonne de la plage sélectionnée pour la colonne de tri
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
// obtenir un tableau de couleurs de fond à partir de la colonne de tri
var sortColBackgrounds = sheet.getRange(sortColumnLetter + 2 + ":" + sortColumnLetter + lastRow).getBackgrounds();
// supposer l'en-tête dans la ligne 1
// obtenir la couleur de fond de la cellule de tri
var sortColor = sheet.getRange(colorCell).getBackground();
// mapper les couleurs de fond à 1 si elles correspondent à la couleur de la cellule de tri, à 2 sinon
var sortCodes = sortColBackgrounds.map(function(val) {
return (val[0] === sortColor) ? [1] : [2];
});
// ajouter un en-tête de colonne au tableau des couleurs de fond
sortCodes.unshift(['Sort Column']);
// coller le tableau des couleurs de fond en tant que colonne d'aide à droite de la feuille de données
sheet.getRange(1,lastCol+1,lastRow,1).setValues(sortCodes);
sheet.getRange(1,lastCol+1,1,1).setHorizontalAlignment('center').setFontWeight('bold').setWrap(true);
// trier les données
var dataRange = sheet.getRange(2,1,lastRow,lastCol+1);
dataRange.sort(lastCol+1);
// ajouter un nouveau filtre sur toute la table de données
sheet.getDataRange().createFilter();
// effacer les valeurs dans le stockage des propriétés du document
clearProperties();
}Et enfin, nous voulons avoir un moyen de réinitialiser le stockage de propriétés afin de pouvoir recommencer.
Effacer le stockage des propriétés (étape 10)
Cette fonction simple supprimera toutes les paires clé/valeur stockées dans le stockage de propriétés de la feuille :
// effacer les propriétés
function clearProperties() {
PropertiesService.getDocumentProperties().deleteAllProperties();
}Exécuter le script de tri par couleur dans Google Sheets
Si vous regroupez tous ces extraits de code dans votre fichier Code.gs, vous devriez être en mesure d’exécuter onOpen, d’autoriser votre script, puis d’exécuter l’outil de tri par couleur à partir du nouveau menu personnalisé.
Voici l’outil de tri par couleur en action dans Google Sheets :
Vous pouvez voir comment toutes les lignes vertes sont triées en haut de mon ensemble de données.
Notez que cette fonctionnalité de tri par couleur est configurée pour fonctionner avec des ensembles de données qui commencent en cellule A1 (parce qu’elle s’appuie sur la méthode getDataRange(), qui fait la même chose). Certaines améliorations pourraient consister à la rendre plus généralisée (ou à demander à l’utilisateur de mettre en évidence l’ensemble de données au départ). Je n’ai pas non plus inclus de gestion des erreurs, intentionnellement pour rendre le script aussi simple que possible afin de faciliter la compréhension. Cependant, c’est quelque chose que vous devez prendre en compte si vous souhaitez rendre cette solution plus robuste.
Filtrer par couleur avec Apps Script
Le flux du programme est pratiquement identique, sauf que nous filtrons les données plutôt que de les trier. Le code est presque identique également, à l’exception des noms de variables qui sont différents et de la mise en œuvre d’un filtre plutôt que d’un tri.
Au lieu de trier les données, nous créons et ajoutons un filtre à l’ensemble de données pour ne montrer que les lignes teintées des couleurs correspondantes :
La partie du code concernant le filtre ressemble à ceci :
// supprimer le filtre existant de la plage de données si nécessaire
if (sheet.getFilter() !== null) {
sheet.getFilter().remove();
}
// ajouter un nouveau filtre sur toute la table de données
var newFilter = sheet.getDataRange().createFilter();
// créer de nouveaux critères de filtrage
var filterCriteria = SpreadsheetApp.newFilterCriteria();
filterCriteria.whenTextEqualTo(filterColor); // appliquer la valeur de la couleur de filtre en tant que valeur de filtre
newFilter.setColumnFilterCriteria(lastCol + 1, filterCriteria);Si vous aimez les défis, essayez de modifier le code de tri pour qu’il fonctionne avec l’exemple de filtrage.
Modèle de filtrage par couleur avec Apps Script
N’hésitez pas à copier le modèle de filtrage par couleur dans Google Sheets ici.
Ou consultez cette page GitHub pour obtenir le code directement depuis le dépôt GitHub.
Maintenant, vous savez comment trier et filtrer vos données par couleur dans Google Sheets. Amusez-vous bien !
