Google Sheets : faire des critères complexes pour SUMIF() et autres
Une fonction comme SUMIF (SOMME.SI si vous utilisez des formules en français) permet de faire une somme des éléments d’une plage répondant à un critère. On retrouve le même concept pour AVERAGEIF, SUMIFS ou encore COUNTIF. Sauf que quand on regarde la documentation, les critères semblent très basiques… Par exemple, si ont veut sommer les valeurs positives de la colonne A, la formule est SUMIF(A:A, ">0"). Si on veut sommer les lignes de la colonne A pour lesquelles les lignes de la colonne B sont « Validé », c’est SUMIF(B:B, "Validé", A:A). Mais comment faire si on souhaite appliquer une formule à une plage et utiliser le résultat pour valider le critère ?
Prenons cet exemple de données d’entrée :
Ce sont des simples opérations financières, avec une date et une valeur. Imaginons maintenant qu’on souhaite faire la somme des valeurs par mois. De manière intuitive, on se dit que pour chaque mois, il faut un SUMIF de la colonne B si le mois de la colonne A est celui du mois considéré. Par exemple, pour Janvier, on aurait envie de faire quelque chose comme SUMIF(MONTH(A:A), 1, B:B) mais ça ne marche pas. On se dit qu’il faudrait créer un range avec les mois de la colonne A, sans forcément créer une colonne pour ça (car oui, créer une colonne intermédiaire est une solution simple efficace). Bonne nouvelle, il existe la fonction qu’il nous faut ! C’est ARRAYFORMULA ! Si dans une cellule, vous mettez ARRAYFORMULA(DATE(A:A)), vous verrez la colonne se remplir avec les mois des dates. Il suffit donc d’injecter ça dans notre SUMIF.
Voici ce que ça pourrait donner :
La colonne D et E sont créées manuellement. La formule en pour la colonne F est SUMIF(ARRAYFORMULA(MONTH(A:A));D:D;B:B). L’utilisation de la colonne D (qu’on peut masquer, car il est vrai que visuellement c’est pas foufou) permet d’avoir la même formule dans toutes les cellules de la colonne F sans devoir incrémenter manuellement le numéro du mois dans la formule (le D:D remplace le numéro du mois). Essayez de le coder, vous verrez que c’est plus facile 😉
Google Apps Script : la valeur cachée de ItemType
Si vous faites joujou avec des Google Forms et du Apps Script, vous êtes sans doute tombé sur l’énumération ItemType. L’interface Item représente un élément d’un formulaire et il y a plusieurs classes qui répondent à cette interface, comme TextItem qui permet de saisir du texte ou DateItem qui permet de choisir une date. Sans juger de la qualité OO du design, il faut noter que Item possède une méthode getType() qui renvoie une valeur de l’énumération ItemType. Cela permet de caster ensuite un Item dans le « bon » type pour le traiter selon son type réel (je n’invente rien, ce mode opératoire est proposé au début de la description de l’interface).
Voici la liste des valeurs de l’énumération d’après sa documentation :
CHECKBOXCHECKBOX_GRIDDATEDATETIMEDURATIONGRIDIMAGELISTMULTIPLE_CHOICEPAGE_BREAKPARAGRAPH_TEXTSCALESECTION_HEADERTEXTTIMEVIDEO
Cela veut dire que la méthode ordinal() sur un membre de cette énumération renvoie une valeur entre 0 et 15.
Moi, j’ai un formulaire avec un champ pour uploader un fichier :
Je ne vois aucun type correspondant dans l’énumération, pas grave, je vais tester. J’installe un trigger pour exécuter le script suivant quand une réponse est postée. Que pensez-vous que je vais obtenir dans mes logs lors de la prochaine réponse ?
function onFormSubmitted(event) {
var itemResponse = event.response.getItemResponses()[0];
var title = itemResponse.getItem().getTitle();
var response = itemResponse.getResponse();
console.log(title + ' : ' + response);
var type = itemResponse.getItem().getType();
console.log(type);
console.log(type.toString());
console.log(type.ordinal());
}
Dans le journal des exécutions, je vois :
13 juin 2019 à 15:59:07 DÉBOGAGE Mon fichier : 1rTvMQfzkp3UgVn4aj4-BOlf_6s2rB4 13 juin 2019 à 15:59:07 DÉBOGAGE FILE_UPLOAD 13 juin 2019 à 15:59:07 DÉBOGAGE FILE_UPLOAD 13 juin 2019 à 15:59:07 DÉBOGAGE 16.0
Attends, je teste un truc ! J’exécute cette fonction :
function testEnum() {
Logger.log(FormApp.ItemType.VIDEO);
Logger.log(FormApp.ItemType.FILE_UPLOAD);
}
Que vois-je dans les journaux ?
[19-06-13 16:19:34:803 CEST] VIDEO [19-06-13 16:19:34:803 CEST] undefined
Bim ! C’est ça qui est bon ! Que de fun !
Du coup, si dans ton script, tu veux savoir si tu traites un Item de type FILE_UPLOAD, et bien il faut ruser… Voici par exemple une fonction pour générer des liens vers les fichiers uploadés :
function onFormSubmitted(event) {
var itemResponse = event.response.getItemResponses()[0];
var response = itemResponse.getResponse();
var type = itemResponse.getItem().getType();
if (type == "FILE_UPLOAD") {
console.log(response);
console.log(typeof response);
for(var u = 0; u < response.length; u++) {
var url = 'https://drive.google.com/open?id=' + response[u];
console.log('Uploaded file URL = ' + url);
}
}
}
On obtient dans les logs :
13 juin 2019 à 16:28:29 DÉBOGAGE [1tLkWu6ij1qcb3ZFeCl7ZTnRYKL1zK7]
13 juin 2019 à 16:28:29 DÉBOGAGE object
13 juin 2019 à 16:28:29 DÉBOGAGE Uploaded file URL =
https://drive.google.com/open?id=1tLkWu6ij1qcb3ZFeCl7ZTnRYKL1zK7
Ah oui ! N’oublions pas cette magie du JavaScript pour qui un tableau est de type Object. La réponse à un tel Item est bien un tableau des ID des fichiers téléchargés, même s’il n’y a qu’un seul fichier.
Pierre Gradot 