Cet article vous apprendra à utiliser Google Apps Script dans Google Sheets. Nous partagerons des exemples de scripts et la façon dont ils affectent les feuilles de calcul. Nous expliquerons également certains concepts clés tels que les fonctions personnalisées et les types de données.
Entrons dans le vif du sujet !
Principaux enseignements :
- Dans Google Sheets, Apps Script peut être utilisé pour automatiser la saisie de données, générer des rapports, nettoyer et formater des données, entre autres.
- Pour utiliser Google Apps Script dans Google Sheets, rendez-vous à l'adresse suivante Extension > Apps Script et tapez ou collez votre code.
- Les concepts clés d'Apps Script comprennent les fonctions personnalisées, les déclencheurs, les propriétés et les méthodes, les services Apps Script, la gestion des erreurs et les bibliothèques.
Qu'est-ce que Google Apps Script ?
Google Apps Script est une plateforme basée sur le cloud qui vous permet de créer vos propres programmes pour travailler avec vos applications Google (comme Gmail, Docs et Sheets). Ces programmes exécutent des tâches automatiquement. Ils peuvent envoyer des courriels ou organiser des données. Ils peuvent également ajouter de nouvelles fonctionnalités à vos applications, comme des menus ou des boutons personnalisés.
Google Apps Script utilise un langage de programmation simple appelé JavaScript. Il n'est pas nécessaire d'être un expert en codage pour l'écrire.
En termes simples, voici comment fonctionnent les scripts Google Apps :
- Vous écrivez des instructions simples (code) dans l'éditeur Apps Script.
- Les serveurs de Google lisent vos instructions et effectuent les tâches que vous souhaitez.
- Les résultats apparaissent dans vos applications Google. Il peut s'agir de nouveaux menus, de tâches automatisées ou d'applications personnalisées.
Comment utiliser Apps Script dans Google Sheets ?
Comment utiliser Google Apps Script dans Google Sheets ?
- Ouvrez votre feuille Google.
- Cliquez sur “Extensions“dans la barre de menu et sélectionnez “Apps Script”. Cela ouvrira un nouvel onglet avec l'éditeur Apps Script.
3. Vous verrez Code.gs. Il s'agit du fichier principal dans lequel vous écrirez votre code JavaScript. Il se trouve dans le menu “Editor”.
4. Tapez ou collez votre code. Il se peut que vous ayez besoin d'apprendre le Javascript pour savoir comment écrire du code, ou que vous ayez besoin de savoir comment écrire du code. peut générer du code avec Gemini ou ChatGPT. Mais voici le script que nous utiliserons pour ce tutoriel :
Quel est le scénario derrière ce script ? Supposons que nous ayons une liste de contacts dans une feuille Google et que nous souhaitions l'utiliser pour une campagne d'e-mailing de base. Le script récupérera toutes les données nécessaires, telles que l'adresse électronique et le nom, pour composer et envoyer des courriels.
5. Cliquez sur le bouton “Économiser”pour enregistrer le script.
6. Dans le coin supérieur gauche, renommez votre projet et cliquez sur “Exécuter.” Le script enverra des courriels à toutes les adresses de votre feuille.
7. Les scripts Apps Script nécessitent une autorisation pour accéder à vos données Google Sheets. Vous serez invité à accorder ces autorisations lorsque vous exécuterez un script.
8. Vous pouvez également configurer un déclencheur pour exécuter le script automatiquement selon un calendrier (par exemple, quotidien ou hebdomadaire). Pour ce faire, passez votre souris sur les icônes de gauche et cliquez sur “Déclencheurs.”
9. Cliquez sur le bouton “+ Ajouter un déclencheur”bouton ".
10. Allez à la source de l'événement et choisissez “Axé sur le temps.”
11. Sélectionnez un type de temps.
- Date et heure précises : Pour un déclenchement unique.
- Minuterie de minutes : Exécuter à un intervalle spécifié en minutes (par exemple, toutes les 15 minutes).
- Minuterie horaire : Exécuter à un intervalle spécifié en heures (par exemple, toutes les 3 heures).
- Minuterie de jour : A exécuter quotidiennement à une heure précise.
- Chronomètre de semaine : Courir certains jours de la semaine à une heure précise.
- Minuterie mensuelle : A exécuter certains jours du mois à une heure précise.
12. Cliquez sur “Économiser.” Le déclencheur est maintenant actif.
Voici l'e-mail que j'ai reçu après avoir exécuté le script.
Et voilà ! Vous savez maintenant comment utiliser Google Apps Script dans Google Sheets. Mais examinons d'autres cas d'utilisation et voyons comment Apps Script améliore les feuilles de calcul.
Exemples pratiques de scripts d'application en action
1. Automatiser la saisie des données
Vous pouvez utiliser les scripts Google Apps dans Google Sheets pour automatiser la saisie des données.
La saisie manuelle des données est sujette aux fautes de frappe. Avec l'automatisation, Avec Apps Script, vous réduisez les risques d'erreurs humaines. Apps Script peut extraire des données d'autres sources (comme des sites web, des formulaires ou des courriels) et les insérer automatiquement dans votre feuille de calcul.
Imaginons que vous receviez par courriel des reçus pour des dépenses professionnelles. Apps Script peut scanner ces courriels, extraire les chiffres pertinents (date, montant, catégorie) et les organiser proprement dans votre feuille de calcul de suivi des dépenses.
Écrivons et expliquons un scénario :
Que signifie ce texte ?
Nous supposons que vous disposez d'un libellé Gmail appelé “Reçus” dans lequel vous stockez les courriels de dépenses. Le script récupère les 5 premiers fils de discussion (conversations) non lus de ce libellé. Il parcourt en boucle chaque fil de discussion et ses messages individuels (courriels).
Il extrait ensuite les détails des dépenses. Gardez à l'esprit que nous avons fortement simplifié cette partie. Le script suppose un format spécifique pour les dépenses dans le corps du message (par exemple, “$15.99” et “25/07/2024”).
Ensuite, si le script trouve à la fois le montant et la date, il récupère la feuille de calcul active et ajoute une nouvelle ligne avec les données extraites (et l'objet de l'e-mail pour référence).
Enfin, après traitement, le script marque le fil de discussion comme lu pour éviter les doublons.
Remarque : Notre script est simplifié. L'exactitude des données dépend entièrement du format de l'e-mail et de la capacité du script à l'analyser correctement. En outre, il se peut que vous ayez des colonnes supplémentaires dans votre système de suivi des dépenses (par exemple, la catégorie, le mode de paiement).
2. Générer des rapports
Vous pouvez également utiliser Google Apps Script dans Google Sheets pour générer des rapports.
Pensez à ces rapports fastidieux que vous devez faire - peut-être des résumés de ventes, des mises à jour de projets ou des notes d'étudiants. Apps Script peut alléger ce processus en extrayant ces données d'une feuille Google ou d'autres endroits.
Disons que vous suivez vos ventes hebdomadaires dans une feuille Google. Apps Script peut :
- Saisissez les derniers chiffres de vente de votre feuille.
- Calculez les ventes totales, les ventes par produit et peut-être même la croissance par rapport à la semaine précédente.
- Faire un diagramme circulaire ou graphique linéaire et un résumé.
- Envoyez le rapport par courrier électronique à votre supérieur tous les lundis matin.
Vous le configurez une fois, puis Google Apps Script fait le reste, semaine après semaine !
Voici un exemple de script, que nous expliquerons plus loin.
function generateSalesReport() {
// Obtenir les données relatives aux ventes
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“WeeklySales”) ;
var data = sheet.getDataRange().getValues() ;
// Calculer les ventes totales et les ventes par produit
var totalSales = 0 ;
var salesByProduct = {} ;
for (var i = 1 ; i < data.length ; i++) { // Sauter la ligne d'en-tête
var product = data[i][1] ;
var sales = data[i][2] ;
totalSales += sales ;
salesByProduct[product] = (salesByProduct[product] || 0) + sales ;
}
// Créer le rapport
var report = “<h2>Rapport hebdomadaire des ventes</h2>\n\n”\n"\n"\n"\n"\n"\n" ;
report += “<p>Total des ventes : $” + totalSales.toFixed(2) + “</p>\n\n”\n"\n"\n"\n"\n"\n" ;
report += “<h3>Ventes par produit :</h3>\n\n”\n"\n"\n"\n"\n"\n" ;
for (var product in salesByProduct) {
report += “<p>” + produit + “ : $” + salesByProduct[produit].toFixed(2) + “</p>\n\n”\n"\n"\n"\n"\n"\n" ;
}
// Envoyer le courriel
var emailSubject = “Rapport hebdomadaire des ventes” ;
var emailBody = report ;
MailApp.sendEmail(“YOUR_EMAIL_ADDRESS”, emailSubject, “”, {htmlBody : emailBody}) ; // Inclure votre adresse e-mail
}
Lecture recommandée : Les 5 meilleurs modèles de vente de Google Sheets
Que signifie ce texte ?
Le script récupère toutes les valeurs de la feuille “WeeklySales”. Il parcourt les données en boucle et calcule les ventes totales et les ventes par produit. Il crée ensuite un rapport HTML avec les données calculées. Enfin, le script utilise MailApp.sendEmail pour envoyer le rapport par courrier électronique.
MailApp.sendEmail
Voici une feuille Google regroupant toutes les ventes :
Voici à quoi ressemble le rapport des ventes hebdomadaires totales dans l'e-mail :
Remarque : Il s'agit d'un script simplifié. Modifiez “WeeklySales” si votre feuille porte un nom différent. Remplacez “VOTRE_ADRESSE_EMIL” par l'adresse électronique du destinataire.
3. Nettoyer et formater les données
Le nettoyage et la mise en forme des données est une autre façon d'utiliser les scripts d'application dans Google Sheets.
Imaginez que vous ayez une feuille de calcul désordonnée contenant des informations sur vos clients. Certains noms comportent peut-être des espaces supplémentaires, les dates sont dans des formats différents ou certaines cellules contiennent des fautes de frappe. Le nettoyage manuel de ces données serait un véritable casse-tête. C'est là que les scripts d'application interviennent pour vous sauver la mise.
Vous pouvez utiliser les scripts Google Apps pour analyser vos données, trouver et résoudre des problèmes spécifiques, par exemple :
- Espaces supplémentaires : Supprime automatiquement les espaces supplémentaires avant et après les noms.
- Formats incorrects : Modifier les dates de “29/07/2024” à “29 juillet 2024” pour que tout corresponde.
- Fautes de frappe : Si le nom d'un produit est mal orthographié, le script peut le corriger.
Vous pouvez également utiliser les scripts Google Apps pour organiser les informations et combiner ou diviser les données.
Créons un Javascript pour cet exemple.
Imaginez que vous disposiez d'une feuille Google contenant des commandes de clients. Chaque ligne contient des informations telles que l'identifiant de la commande, le nom du client, le produit, la quantité et la date de la commande. Vous souhaitez organiser les commandes par date, en plaçant les commandes les plus récentes en haut de la feuille.
Notre script pourrait ressembler à ceci :
function sortSheetByDate() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ;
var range = sheet.getRange(“A2:E26”) ; // Ajustez la plage pour couvrir vos données
// Obtenir les données
var data = range.getValues() ;
// Vérifier si les dates sont au format objet Date et les convertir en chaînes de caractères.
data.forEach(function(row) {
if (Object.prototype.toString.call(row[4]) === ‘[object Date]’) {
row[4] = Utilities.formatDate(row[4], SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), ‘dd/MM/yyyy’) ;
}
});
// Analyser correctement les dates et les trier
data.sort(function(a, b) {
var dateA = new Date(a[4].split(‘/’).reverse().join(‘-‘)) ;
var dateB = new Date(b[4].split(‘/’).reverse().join(‘-‘)) ;
return dateB - dateA ; // Tri du plus récent au plus ancien
});
// Remettre les données triées dans la feuille
range.setValues(data) ;
}
Que signifie ce texte ?
Le script recherche l'ordre des informations que vous souhaitez trier. Il prend ensuite soigneusement toutes les informations de chaque formulaire de commande - nom du client, date de la commande, etc. - et les place dans des cellules séparées. C'est là que la magie opère ! Le script examine chaque date de commande et détermine les commandes les plus récentes et les plus anciennes.
Données dont les dates ne sont pas dans l'ordre chronologique :
Les données avec les dates dans l'ordre chronologique après l'utilisation du script :
Concepts Apps Script essentiels à connaître et à utiliser dans Google Sheets
1. Fonctions personnalisées
Les fonctions personnalisées vous permettent de créer vos propres fonctions que vous pouvez utiliser directement dans Google Sheets, tout comme les fonctions intégrées (SOMME, MOYENNE, etc.) Ces fonctions sont écrites en JavaScript dans Apps Script et peuvent effectuer diverses tâches, allant de simples calculs à des manipulations de données complexes.
Voici un exemple de fonction personnalisée :
function DOUBLE(input) {
retour de l'entrée * 2 ;
}
Cette fonction personnalisée double les nombres dans une cellule. Après avoir ajouté la fonction personnalisée à l'éditeur de script, retournez à votre feuille de calcul.
Supposons que la cellule A1 contienne le nombre 580.
- Tapez =DOUBLE(A1) dans la cellule B1.
=DOUBLE(A1)
- Google Sheets exécute votre fonction et lui attribue la valeur 1160 dans la cellule B1.
- La fonction calcule 580 * 2 = 1160.
- La cellule B1 affiche le résultat : 1160.
Différence entre une fonction personnalisée et un code général que vous écrivez dans Google Apps Script
La principale différence entre une fonction personnalisée et tout autre code réside dans la manière dont vous l'utilisez et l'appelez dans Google Sheets.
Un code de fonction personnalisé doit renvoyer une valeur, comme :
return input * 2 ;.
Une fonction ne peut modifier aucune partie de la feuille de calcul ni déclencher d'autres actions. Elle ne peut que calculer et renvoyer un résultat en fonction de ses entrées.
De plus, pour utiliser une fonction personnalisée, vous devez entrer le code =functionName() dans une cellule sur la feuille de calcul, par exemple :
=DOUBLE(A1)
2. Déclencheurs
Vous devez également vous familiariser avec les déclencheurs si vous souhaitez utiliser Google Apps Script dans Google Sheets.
Imaginez que vous ayez une tâche dans Google Sheets qui doit se dérouler automatiquement, sans que vous ayez à cliquer sur un bouton. C'est là que les déclencheurs entrent en jeu ! Un déclencheur est comme un petit réveil que vous mettez en place dans votre code Apps Script. Il indique à votre script de se réveiller et d'exécuter une partie spécifique de votre code lorsqu'un événement particulier se produit dans votre feuille de calcul.
Vous pouvez personnaliser les déclencheurs pour qu'ils exécutent votre script à l'ouverture d'une feuille, à la modification d'une cellule, à l'envoi d'un formulaire ou même à une date et une heure spécifiques.
Vous pouvez également utiliser des déclencheurs pour actualiser vos données. Par exemple, un déclencheur pourrait automatiquement intégrer les derniers cours de la bourse ou les dernières informations météorologiques.
3. Propriétés et méthodes
Les propriétés et les méthodes vous permettent de modifier le fonctionnement de Google Sheets. Vous pouvez écrire des scripts qui formatent automatiquement les cellules ou déplacent les données. Par exemple, au lieu de modifier manuellement la couleur de centaines de cellules, votre script peut le faire instantanément.
Les propriétés sont les caractéristiques ou les attributs d'un objet. Dans Google Sheets, il pourrait s'agir de
- La couleur d'arrière-plan d'une cellule :
cell.getBackground()
- La valeur à l'intérieur d'une cellule :
cell.getValue()
- La taille de la police du texte :
cell.getFontSize()
Vous pouvez connaître la valeur actuelle d'une propriété ou la définir pour modifier son apparence ou son comportement.
Les méthodes, quant à elles, sont comme les verbes ou les actions qu'un objet peut effectuer. Par exemple, une cellule dans Google Sheets peut.. :
- Modifier son contenu :
cell.setValue(“Hello !”)
- S'effacer complètement :
cell.clearContent()
- Fusionner avec les cellules voisines :
cell.mergeAcross()
Vous “appelez” une méthode pour que cela se produise. C'est comme donner une instruction.
Voici un exemple de script simple :
function formatImportantCells() {
var sheet = SpreadsheetApp.getActiveSheet() ;
var range = sheet.getRange(“A1:B10”) ; // Obtenir un groupe de cellules
var cells = range.getCells() ; // Obtenir des cellules individuelles
for (var i = 0 ; i < cells.length ; i++) {
if (cells[i].getValue() > 100) { // Vérifier la valeur
cells[i].setBackground(“yellow”) ; // Définir une propriété (couleur)
}
}
}
Ce script vérifie les valeurs des cellules A1 à B10. Si une valeur est supérieure à 100, le script rend l'arrière-plan de la cellule jaune.
En bref :
- Les propriétés décrivent : Ils vous renseignent sur l'état actuel des choses.
- Les méthodes le font : Ils font bouger les choses.
- Notation en points : Vous utilisez un point pour accéder aux propriétés et aux méthodes (par exemple, cell.getValue()).
cell.getValue()
4. Types de données
Les types de données sont un autre concept que vous devez apprendre pour utiliser Google Apps Script dans Google Sheets.
Les types de données sont des étiquettes qui indiquent à Apps Script le type d'information qu'il traite. Tout comme vous feriez la distinction entre des pommes et des oranges, Apps Script a besoin de savoir s'il travaille avec des nombres, du texte, des dates ou d'autres types.
Mais pourquoi les types de données ont-ils de l'importance dans Google Sheets ?
Il y a au moins trois raisons à cela :
- Calculs corrects : Imaginez que vous additionnez des dépenses. Si Apps Script pense que vos chiffres sont en fait du texte, vous obtiendrez des résultats bizarres. Les types de données garantissent la précision des calculs.
- Formatage correct : Vous voulez que vos dates ressemblent à des dates et que vos numéros de téléphone se comportent comme des numéros de téléphone. Les types de données vous permettent de contrôler l'affichage et l'utilisation des données dans votre feuille de calcul.
- Prévention des erreurs : Si vous essayez d'utiliser une date comme s'il s'agissait d'un nombre, Apps Script peut signaler une erreur. Connaître les bons types de données vous permet d'éviter ces problèmes et d'écrire un code qui fonctionne bien.
Supposons que vous suiviez les ventes et que vous disposiez d'une colonne dans Google Sheets avec le nombre d'articles vendus. Lorsque vous utilisez Apps Script pour calculer le total des ventes, il doit comprendre que cette colonne contient des nombres, et pas seulement des caractères aléatoires.
Par exemple, le script ci-dessous mentionne le type de données :
if (valueType === “number”) {
Voici la répartition :
- valueType : Cette variable stocke le résultat de l'opérateur typeof value. L'opérateur typeof est une fonctionnalité de JavaScript qui indique directement le type de données d'une variable.
- === “number” : Cette comparaison vérifie si le type de valeur est strictement égal à la chaîne “number”. Si c'est le cas, le script sait qu'il a affaire à une valeur numérique et l'ajoute au total des ventes.
Nous venons d'aborder quelques concepts ou termes relatifs aux scripts d'applications. Il en existe d'autres, tels que Apps Script Services, Error Handling et Libraries.
Conseils et meilleures pratiques pour Apps Script
1. Apprendre le Javascript
L'apprentissage de JavaScript vous donne une base solide pour comprendre le fonctionnement de Google Apps Script. Il devient beaucoup plus facile de comprendre le code que vous trouvez en ligne, de dépanner vos propres projets et d'écrire des scripts plus avancés.
Une fois que vous aurez assimilé les bases du JavaScript, vous manipulerez des données de manière très intelligente et interagirez avec d'autres services sur le web.
En outre, JavaScript est un langage largement utilisé, de sorte que vos nouvelles compétences ne seront pas perdues. Vous pouvez les appliquer à d'autres projets que Google Apps Script. Par exemple, vous pouvez créer des sites web simples ou même travailler sur des projets de développement web complexes.
2. Écrire un code efficace et facile à maintenir
Votre code est comme une recette, et une bonne recette est.. :
- Efficace : Il ne gaspille pas d'ingrédients ni de temps. Un code efficace s'exécute plus rapidement et utilise moins de votre quota d'Apps Script (la limite de ce que vos scripts peuvent faire).
- Maintenable : Il est facile à comprendre et à mettre à jour ultérieurement. Un code facile à maintenir vous évite des maux de tête lorsque vous devez apporter des modifications ultérieurement.
Voici quelques conseils supplémentaires pour Google Apps Script :
- Ne nommez pas vos variables “x” ou “temp”. Choisissez des noms qui décrivent ce qu'elles stockent. Par exemple, au lieu de “data”, utilisez “customerNames” ou “orderDates”.”
- Divisez votre code en fonctions plus petites et réutilisables. Chaque fonction devrait idéalement effectuer une tâche spécifique. Votre code sera ainsi mieux organisé et plus facile à tester.
- Les choses ne se passent pas toujours comme prévu. Utilisez les blocs “try...catch” pour rattraper les erreurs de manière élégante, ce qui évite à votre script de se bloquer de manière inattendue.
- Si vous vous retrouvez à écrire le même code plusieurs fois, créez une fonction pour cela. Ce principe est appelé “Don't Repeat Yourself” (DRY).
3. Déboguer intelligemment
Dans Google Apps Script, les bogues sont des erreurs ou des comportements inattendus dans votre code. Le débogage est le processus qui consiste à trouver et à corriger ces problèmes.
Lorsque Apps Script rencontre un problème, il affiche généralement un message d'erreur. Ce message est votre premier indice. Il vous indique souvent ce qui n'a pas fonctionné. Vous saurez si vous avez utilisé une variable qui n'existe pas, si vous avez divisé par zéro, ou autre. Vous saurez également où l'erreur s'est produite, comme la ligne de code à l'origine du problème.
Apps Script dispose d'un débogueur intégré. C'est comme une version plus puissante de “console.log()”. Vous pouvez mettre votre code en pause, le parcourir ligne par ligne et inspecter les variables en temps réel.
Supposons que vous ayez ce code :
function calculateArea(length, width) {
var area = length * width ;
console.log(“La zone est : ” + zone) ;
}
calculateArea(10,) ; // Remarquez l'absence de l'argument de la largeur
Vous l'exécutez et il se bloque avec une erreur : “TypeError : Cannot read property ‘*’ of undefined”. Que se passe-t-il ?
- Lire l'erreur : Il indique que vous essayez d'utiliser l'opérateur ‘*’ (multiplication) sur quelque chose d'indéfini.
- Vérifier le code : Vous vous rendez compte que vous avez oublié de donner une valeur à l'argument “width”.
Corriger l'erreur : Remplacer la dernière ligne par :
calculateArea(10, 5) ;
Utilisez les scripts Google Apps pour améliorer vos Google Sheets
Félicitations ! Vous venez de découvrir la puissance d'Apps Script dans Google Sheets. Dans cet article, nous avons vu des cas d'utilisation pratiques et partagé des exemples de scripts. Vous êtes désormais parfaitement équipé pour utiliser Google Apps Script dans Google Sheets.
En automatisant des tâches, en personnalisant des fonctions et même en créant des applications web interactives, vous êtes en bonne voie pour devenir un ninja du tableur. Continuez à apprendre, à créer des scripts et à repousser les limites de ce qui est possible avec Google Sheets.
FAQ
Comment ajouter l'éditeur de script à Google Sheets ?
L'éditeur de script est intégré à Google Sheets. Vous n'avez pas besoin de l'ajouter séparément.
Comment ouvrir l'éditeur de script dans Google Sheets ?
Allez dans Extensions > Apps Script. Cela ouvrira l'éditeur de script dans un nouvel onglet.
Où se trouve l'éditeur de script dans Google Sheets ?
L'éditeur de script ne se trouve pas physiquement dans votre feuille Google. Il s'ouvre dans un onglet ou une fenêtre distincte lorsque vous y accédez par le menu Extensions. Allez dans Extensions > Apps Script.
English 
Español 
Português do Brasil 
Français