POK 1 : artiste en détresse (google sheets et apps script)

• POK : Un peu d'Excel/Google Sheets pour mourir moins idiot
• Formation sheets-Pratique.com
• Récap fonctions qui existent.
• Générateur de formule pour extraire des données.
• Sheets-Pratique : Cours Apps Script
• Find inspiration with Apps Script samples

🏃‍➡️Sprints

Objectif général : optimiser les tableaux de comptabilité de ma soeur Laura.

Sprint 1

• [x] faire l'état de l’art de ce qui existe déjà MON, POK sur GS : 30 min (30 min)
• [x] suivre des tutos sur GS, consulter d’autres ressources et m’écrire une fiche récap des fonctionnalités : 5h30 (5h15)
• [x] récupérer les tableaux GS de Laura et établir un cahier des charges des améliorations à effectuer : 30 min (40 min)
• [x] améliorer les tableaux selon le CDC fixé : 2h (2h30)
• [x] envoyer les tableaux et explications à Laura : pas estimé (30 min)
• [x] rédiger ce que j’ai fait et mettre en forme GitHub : 1h30 (2h)

Total d'heures sprint 1 : 11h25
Etude post mortem : dans mon planning intitial, j'avais oublié de prévoir un temps d'envoie des tableaux et d'explications à Laura et j'ai un peu sous-estimé le temps sur google sheet et le temps de rédaction et de prise en main de GitHub.

Sprint 2

• [x] faire l'état de l’art de ce qui existe déjà MON, POK sur GAS : 30 min (30 min)
• [x] suivre des tutos sur GAS, consulter d’autres ressources et m’écrire une fiche récap des fonctionnalités : 4h (4h)
• [ ] établir un cahier des charges des améliorations à effectuer : 30 min
• [ ] améliorer les tableaux selon le CDC fixé : 3h30
• [ ] envoyer les tableaux et explication à Laura : 15 min
• [ ] rédiger ce que j’ai fait et mettre en forme GitHub : 1h15
• [x] regarder les tableaux de Laura puis choisir et essayer un projet 1 : pas prévu (2h40)
• [x] choisir et réaliser un projet 2 : pas prévu (30 min)
• [x] choisir et essayer un projet 3 : pas prévu (1h)

Total d'heures sprint 2 : 9h10 oui, techniquement il manque 50 min mais vu que j'avais dépassé de 1h25 au sprint 1, j'ai bien passé plus de 20h sur le POK au total
Etude post mortem : j'ai changé ce que je voulais faire en milieu de sprint pour faire des projets plus cohérents avec les outils de GAS. J'ai aussi changé ma manière de faire en écrivant au fur et à mesure sur Github au lieu de tout à la fin. Ainsi le sprint a été très chamboulé.

⌛Horodatage

Fonctionnalités diverses

Modifier toutes les largeurs de colonnes en même temps : sélectionner toutes les colonnes avec le curseur ou touche ctrl enfoncée → modifier

Collage spécial : coller uniquement au choix valeur, formule, mise en forme conditionnelle…

Masquer le quadrillage : affichage → afficher → quadrillage

Affichage date : entrer une date 01/04/2024 puis format → nombre → date et heure personnalisée → choisir sa préférence ex : “lun. 1”

Formes : déformer forme d’un dessin avec petit losange jaune, déplacer des formes doucement avec Maj + flèches clavier

Images : possibilité d’insérer une image dans une cellule et plus de possibilité sur image si insérer à partir d’un dessin (recadrer selon une forme, pivoter etc) Word Art : insertion → dessin → actions → word art

Liste déroulante : insertion → liste déroulante Données issue d'une liste déroulante définie : créer la liste de choix possible → sélectionner la plage où il faudra entrer ces choix → données → validation des données → menu déroulant (depuis une plage) → sélectionner la plage des choix

Historique des versions : penser à nommer la version actuelle quand j’ai une version que j’aime et que je veux retrouver facilement plus tard

Protéger des cellules : données → protéger des feuilles et des plages → saisir la règle

Fonctions

Récap fonctions qui existent.

Générateur de formule pour extraire des données.

Utiliser fonction : chercher une fonction avec bouton ∑ dans la barre d’outil OU insertion → fonction Plus d'infos sur les fonctions : ? en haut à côté de la formule, encore plus de détail possibles

Fixer une référence dans une formule : ajouter des $ à la référence. Ex : $B$8, le 1er $ fixe la colonne lors de la recopie et le 2ème $ fixe la ligne

Nommer une plage : sélectionner la plage → la nommer (pratique pour les formules)

Fonction SI : SI(expression logique; valeur si true; valeur si false) → ex : renvoie oui si majeur Ex : =SI(A2>17;"oui";"non")

Imbrication de fonction SI : ex : choix entre 3 tarifs notés en E1, E2 et E3 selon 3 âges =SI(A2>17;SI(A2>64;$E$3;$E$2);$E$1) → d’abord on voit si il est adulte, si oui on voit si il est retraité et on affiche le bon tarif figé avec les $

Fonction ET : ET(test 1; test 2; ...) → renvoie VRAI si tous les tests sont VRAI. Ex : pour vérifier si qqn a entre 18 et 64 ans =SI(ET(B2>=18;B2<65);"Oui";"Non")

Fonction OU : =OU(test 1; test 2; ...) → renvoie VRAI si au moins un des tests est VRAI

Fonction RECHERCHEV : permet d’envoyer automatiquement une valeur selon une autre valeur liée (par exemple le nom d’une ville selon une boutique) Combiner avec la fonction SIERREUR pour ne pas renvoyer le msg d’erreur si pas de donnée Ex : =SIERREUR(RECHERCHEV(B5;Stock!$A$2:$B$10;2;0);"-")

Graphiques

Graphique : sélectionner la plage de données → insertion → graphique (si données pas dans le bon sens, cliquer sur "intervertir ligne et colonne")

Graphique sparkline : graphique miniature inséré directement dans une cellule Ex : =SPARKLINE(B2:G2)

Mise en forme conditionnelle (MFC)

MFC : sélectionner données → format → mise en forme conditionnelle

Colorier la colonne du mois actuel : = MOIS(A$1) = MOIS(AUJOURDHUI()) Mise en forme conditionnelle → la formule personnalisée est → Bien vérifier que la date est rentrée au format date quitte à modifier son affichage

Surligner une ligne selon valeur case : =$A1=$B$6 Mise en forme conditionnelle → la formule personnalisée est → Ex : ici c’est la cellule B6 qui contient le mot recherché

Case à cocher pour choisir d’appliquer la MFC : modifier la formule de la MFC pour marquer =ET(condition;case figée) Ex dans le cas ci-dessus : =ET(MOIS(A$1) = MOIS(AUJOURDHUI());$B$8) car la case à cocher est en B8

Colorer les doublons d’une plage avec MFC : ajouter MFC et formule personnalisé =NB.SI(plage figée;A2)>1 A2 = cellule en haut à gauche de la plage Ex : =NB.SI($C$2:$C$19;A2)>1

Trier des données

Trier données selon plusieurs colonnes : Tout sélectionner → Données → Trier une plage → Options avancées de tri des plages Aussi la possibilité d’utiliser la fonction SORT

Filtre : données → créer un filtre

Vue filtrée : Données → Vue filtrée → Créer une vue filtrée (dispo aussi barre d’outils) Permet aux lecteurs de pouvoir changer de vue pour visualiser les données de manière différente ( ≠ filtre où l'affichage est figé pour les utilisateurs en lecture seule) et permet créer plusieurs vues différentes et de les nommer

Validation de données type mail, nombre, date, formule etc : données → validation des données → choisir le critère et le type de msg d’erreur (ex : texte d’une adresse e-mail valide)

Tableau croisé dynamique

TCD : sélectionner les données avec les en-têtes → insertion → TCD → insérer dans une nouvelle feuille → rentrer les bons paramètres dans l’éditeur de TCD Ex : ajouter la ligne “produit” → ajouter la ligne “provenance” → ajouter la valeur “nombre d’unités achetées” → ajouter la valeur “prix par unité” et modifier sa fonction en “average” pour avoir la moyenne et non la somme par défaut

Divers fonctionnalités et variables

Pour accéder à Apps script, il faut dans Google Sheets cliquer sur Extensions > Apps Script

Assigner un script à une forme : insérer une forme > cliquer sur les trois points à droite > Assigner le script

let coutTotalAnnuel = 100; déclarer une variable et lui atribuer une valeur Nommer les variables en commençant par une minuscule et ajouter les autres mots en commençant par une majuscule. Une variable peut stocker des valeurs au format texte entre '', "" ou `` On retrouve aussi var au lieu de let dans certains codes

console.log(); tester et vérifier la valeur d'une variable : console.log(coutTotalAnnuel) puis exécuter

const nbVentesJanvier = 45; déclarer une constante

//commentaire ajouter un commentaire /* si plusieurs lignes de commentaires */

valeur += 1; ou valeur++; augmenter une valeur de +1

texte1 += + texte2; concaténe du texte

let nombre2 = Number('10'); ou let nombre3 = '10' * 1; convertir un nombre au format texte

Math.max : trouver le max

setBackground: applique une couleur de fond (au format CSS) aux cellules setFontColor : applique une couleur (au format CSS) au texte des cellules toUpperCase : met en majuscule setFormula : insère une formule (en anglais) dans la cellule

Tableaux

const monTableau = []; créer un tableau

Accéder au valeur d'un tableau :

const texte1 = plageValeurs[0];  
console.log(tableau[1][0]); // affiche la valeur 2ème ligne et 1ère colonne

plageValeurs[0] = 'Sheets'; Modifier une valeur d'un tableau (ici la 1ère valeur)

plageValeurs.push('pain'); pour ajouter une valeur à la fin d'un tableau
plageValeurs.unshift('pain'); pour ajouter une valeur au début d'un tableau

pop : supprime la dernière valeur du tableau et shift : supprime la première valeur du tableau

console.log(listeDeCourses.length); longueur d'un tableau

Feuilles et cellules

const feuille = SpreadsheetApp.getActive().getSheetByName('Feuille 2'); : accéder à la valeur d'une autre feuille

Aplatir un tableau :

const feuille = SpreadsheetApp.getActiveSheet();
const plage = feuille.getRange('A1:C2');
const tableau = plage.getValues().flat();

console.log(tableau); // Affiche [ 1, 2, 3, 4, 5, 6 ] si tableau = [ [ 1, 2, 3], [ 4,5,6] ]

Modifier la valeur d'une cellule :

const feuille = SpreadsheetApp.getActiveSheet();
const cellule = feuille.getRange('A1');
cellule.setValue(1234); // Entre la valeur "1234" dans la cellule A1
// De même, utiliser getValues quand on veut modifier une plage de cellules

activate : active une feuille ou une cellule

appendRow : insère une ligne à la suite des données de la feuille : feuille.appendRow([1, 2, 3]);

clear : supprime complètement le contenu et la mise en forme de la feuille ou cellule

clearContents : supprime le contenu de la feuille ou cellule (en laissant la mise en forme)

deleteRow : supprime une ligne entière de la feuille (ici, la ligne 17) : feuille.deleteRow(17);

getActiveCell : retourne la cellule active de la feuille

getActiveRange : retourne la plage de cellules active de la feuille

getDataRange : retourne la plage de cellules de la feuille contenant des données

getName : retourne le nom de la feuille

getRange : retourne une cellule ou une plage de cellules de la feuille

getValue : récupère la valeur de la cellule

const feuille = SpreadsheetApp.getActiveSheet(); // on récupère la feuille active  
const cellule = feuille.getRange('A1'); // puis une constante pour la cellule A1 de la feuille  
const valeur = cellule.getValue(); // on utilise la méthode getValue pour récupérer la valeur
// On peut aussi rentrer le numéro de ligne et de colonne pour getRange : getRange('A1') = getRange(1, 1)

getValues : récupère les valeurs de la plage de cellules :

const plage = feuille.getRange('A1:E2');  
const tableau = plage.getValues(); //retourne un tableau, contenant un tableau pour chaque ligne de la plage

setValue : définit la valeur de la cellule (idem avec setValues)

conditions

if : teste une condition else : pour effectuer une autre action si la condition est fausse else if : pour ajouter des conditions intermédiaires

if (valeurA1 == '') {
  celluleA1.setValue('A compléter');
} else if (valeurA1 > 0) {
  console.log('Valeur = ' + valeurA1);
} else {
  console.log('Valeur non valide');
}

la condition ? valeur si vrai : valeur si faux

const age = 12;
const texte = 'Tarif appliqué : ' + (age < 16 ? 'enfant' : 'adulte');

console.log(texte); // Affiche : "Tarif appliqué : enfant"

for (let i = 1; i <= 12; i++) : déclaration variable, condition à vérifier, modification variable

while (tableau.length < 10) : condition à vérifier

&& : équivalent de la fonction Google Sheets ET || : équivalent de la fonction Google Sheets OU

fonctions

function et return :

// Calcule la moyenne de 2 nombres
function moyenne(nombre1, nombre2) {
  return (nombre1 + nombre2) / 2;
}

fonction fléchée : const moyenne = (nombre1, nombre2) => (nombre1 + nombre2) / 2;

Exos :

function compteur() {
  // Feuille active
  const feuille = SpreadsheetApp.getActiveSheet();
  // Cellule A2
  const celluleCompteur = feuille.getRange('A2');
  // Valeur de la cellule A2 + 1
  const compteur = celluleCompteur.getValue() + 1;
  // Changement de la valeur de la cellule A2
  celluleCompteur.setValue(compteur);
}

function ajoutLigne() {

  const ss = SpreadsheetApp.getActive(); // Pour éviter d'exécuter "getActive()" à double
  const feuille1 = ss.getSheetByName('Feuille 1');
  const feuille2 = ss.getSheetByName('Feuille 2');

  // Copie les valeurs de la ligne 1 de "Feuille 1" à la suite dans "Feuille 2"
  feuille2.appendRow(feuille1.getRange('1:1').getValues()[0]);
}

déclencheurs

onEdit : après la modification d'une valeur

onOpen : à l'ouverture du document

menu

formation sur les menus Voir dans la formation l'exemple pour convertir des cellules en majuscules ou minuscules

boîtes de dialogue

formation sur les boîtes de dialogue alert permet d'afficher une boîte de dialogue avec différents paramètres possibles

ChatGPT est votre ami

J'ai remarqué en faisant les exercices que ChatGPT est vraiment très compétent pour aider à générer les fonctions, trouver les fomrules. C'est efficient de d'abord demander à ChatGPT de créer la fomrule puis de la personnaliser ou modifier petit à petit.