Vous utilisez régulièrement Google Sheets et vous êtes familier avec ses fonctionnalités intégrées. Cependant, il peut arriver que vous ayez besoin d’effectuer une tâche spécifique pour laquelle il n’existe pas de fonction dans Google Sheets. Ne vous inquiétez pas, vous pouvez utiliser Google Apps Script pour créer vos propres fonctions et automatiser de nombreuses tâches dans Google Sheets.
Qu’est-ce que Google Apps Script ?
Google Apps Script (GAS) est une plateforme de développement qui vous permet de créer des applications qui s’intègrent aux applications et services de Google Workspace. Il utilise JavaScript moderne comme langage de script et ne nécessite aucune installation. Google vous propose un éditeur de code intégré qui vous permet de modifier vos scripts directement dans votre navigateur. Vos scripts s’exécutent sur les serveurs de Google.
GAS joue un rôle similaire à Visual Basic for Applications (VBA) dans Excel. Les deux sont utilisés pour étendre les fonctionnalités et s’intégrer à d’autres applications et services tiers.
Pourquoi utiliser Google Apps Script ?
Voici quelques raisons pour lesquelles vous pourriez avoir besoin de Google Apps Script :
- Créer des fonctions personnalisées dans Google Sheets.
- Ajouter des menus personnalisés, des boîtes de dialogue et des barres latérales dans Google Docs, Sheets et Forms.
- Interagir avec d’autres applications et services de Google Workspace, tels que Docs, Gmail, Calendar, Language, etc.
- Automatiser des tâches à l’aide de déclencheurs.
- Créer des add-ons pour Google Sheets, Docs, Slides et Forms, et les publier sur le Google Workspace Marketplace.
- Développer une interface utilisateur et la publier en tant qu’application web.
- Se connecter à des bases de données relationnelles externes, telles que Google Cloud SQL, MySQL, SQL Server et Oracle, via le service JDBC.
Les classes de Google Apps Script
Les classes sont des modèles qui encapsulent des données avec du code pour créer des objets. Historiquement, JavaScript ne disposait pas de classes, donc parler de classes peut causer de la confusion. Les classes décrites ici sont spécifiques aux implémentations de Google (SpreadsheetApp, GmailApp, etc.).
Google Apps Script fournit plusieurs classes de niveau supérieur. Ces classes principales vous permettent d’accéder aux fonctionnalités des autres applications et services de Google, par exemple :
- Google Sheets peut être accédé en utilisant la classe SpreadsheetApp.
- Google Docs peut être accédé en utilisant la classe DocumentApp.
- Google Drive peut être accédé en utilisant la classe DriveApp.
- Gmail peut être accédé en utilisant la classe GmailApp.
- Le service de langage peut être accédé en utilisant la classe LanguageApp.
- Et bien d’autres.
Apps Script fournit également des classes de base qui vous permettent d’accéder aux informations utilisateur, telles que les adresses e-mail et les noms d’utilisateur, et de contrôler les journaux et les boîtes de dialogue du script. Voici quelques exemples de classes de base :
- Browser – fournit l’accès aux boîtes de dialogue spécifiques à Google Sheets.
- Logger – permet au développeur d’écrire une valeur dans les journaux d’exécution.
- Session – fournit l’accès aux informations de session, telles que l’adresse e-mail de l’utilisateur (dans certaines circonstances) et le paramètre de langue.
Dans ce tutoriel sur Google Apps Script Sheets, nous utiliserons également certaines des classes mentionnées ci-dessus. Par exemple, nous utiliserons la classe SpreadsheetApp pour accéder aux fonctionnalités de Google Sheets et la classe LanguageApp pour accéder au service de langage de Google.
Comment commencer avec Google Apps Script ?
Maintenant que nous avons fait les présentations, passons à la pratique ! La meilleure façon d’apprendre Google Apps Script est d’écrire un peu de code. Pour commencer, vous avez besoin d’un compte Google, d’un navigateur et d’une connexion Internet.
Pour commencer, rendez-vous sur Google Drive et créez une nouvelle feuille de calcul. Donnez-lui un nom, par exemple “Ma première feuille de script”.
Vous pouvez ouvrir l’éditeur Apps Script en cliquant sur Extensions > Apps Script dans le menu (ou Outils > Éditeur de scripts si vous ne trouvez pas le menu Extensions).
Ceci ouvrira l’éditeur Apps Script dans un nouvel onglet de votre navigateur.
Comment utiliser l’éditeur Google Apps Script
Maintenant, nous allons vous montrer comment utiliser l’éditeur Apps Script. Par exemple, comment renommer votre projet, ajouter une nouvelle fonction et enregistrer vos modifications.
Comment renommer votre projet
Les scripts sont organisés en tant que projets. Par défaut, le nom du projet pour les scripts intégrés au fichier de feuille de calcul que vous venez de créer est “Untitled project”.
En haut à gauche, vous verrez le nom du projet. Cliquez dessus pour le renommer. Une petite fenêtre apparaîtra, vous permettant de saisir un nouveau titre de projet.
Il y a aussi un fichier Code.gs ouvert dans l’éditeur. Il contient une fonction par défaut, qui est vide, appelée myFunction().
function myFunction() { }Comment ajouter votre première fonction
Supprimez le bloc de code myFunction() pour vider votre éditeur. Ensuite, copiez et collez le code suivant :
function writeHelloWorld() {
var greeting = 'Hello world!';
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('A1').setValue(greeting);
}Votre code complet ressemblera à ceci :
function writeHelloWorld() {
var greeting = 'Hello world!';
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('A1').setValue(greeting);
}La fonction writeHelloWorld() ci-dessus écrit “Hello world!” dans la feuille de calcul. Elle démontre qu’en utilisant Apps Script, vous pouvez manipuler une feuille de calcul.
Le code utilise la classe SpreadsheetApp pour obtenir un objet représentant la feuille active à l’aide de la méthode getActiveSheet(). Ensuite, il obtient une seule cellule à l’adresse “A1” à l’aide de la méthode getRange(). Une autre méthode, setValue(), est ensuite appelée avec un argument de type chaîne de caractères pour écrire dans “A1”.
Comment enregistrer vos modifications
Remarquez qu’il y a une icône de cercle orange à gauche du nom du fichier Code.gs. Cela signifie que vos modifications n’ont pas encore été enregistrées.
Pour enregistrer vos modifications, appuyez sur Ctrl + S sur votre clavier. Vous pouvez également cliquer sur l’icône du disque.
Après cela, l’icône orange disparaîtra et vous pourrez exécuter votre script.
Comment exécuter Google Apps Script
Cliquez sur le bouton “Exécuter” pour exécuter votre fonction. La première fois que vous exécutez le script, vous devrez autoriser l’accès à vos données.
Cliquez sur le bouton “Examiner les autorisations”. Une autre fenêtre contextuelle apparaîtra, vous demandant de sélectionner un compte pour continuer. Ensuite, vous verrez peut-être un écran d’avertissement indiquant “Google n’a pas vérifié cette application”. Continuez quand même, car dans ce cas, nous savons que c’est sûr. Ensuite, autorisez le script à accéder à votre compte Google.
Une fois autorisé, la fonction writeHelloWorld() s’exécutera et vous verrez “Hello world!” dans “A1”.
Exemples de Google Apps Script
Maintenant, voyons quelques exemples intéressants en utilisant Apps Script.
Se connecter aux autres applications Google avec Google Apps Script
L’exemple suivant montre qu’un script GAS écrit dans une application (Sheets) peut être utilisé pour manipuler d’autres applications Google (Docs). Bien que trivial et inutile, cet exemple démontre une fonctionnalité très puissante de GAS !
Copiez et collez la fonction suivante dans votre éditeur, puis cliquez sur le bouton “Exécuter” pour l’exécuter.
function createDocument() {
var greeting = 'Hello world!';
var doc = DocumentApp.create('Hello_DocumentApp');
doc.setText(greeting);
doc.saveAndClose();
}Une fois autorisé, cela créera un nouveau document Google avec “Hello world!” écrit dessus.
Menu personnalisé avec Google Apps Script
Vous pouvez trouver un peu gênant de devoir toujours ouvrir l’éditeur lorsque vous exécutez votre code. Une solution simple consiste à ajouter un menu personnalisé.
Vous pouvez ajouter le code pour le menu personnalisé dans la fonction onOpen(). Ensuite, un déclencheur exécutera votre code à l’intérieur de cette fonction chaque fois que vous ouvrirez la feuille de calcul. Voici un exemple :
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Mon menu personnalisé')
.addItem('Premier élément', 'function1')
.addSeparator()
.addSubMenu(ui.createMenu('Sous-menu')
.addItem('Deuxième élément', 'function2'))
.addToUi();
}
function function1() {
SpreadsheetApp.getUi().alert('Vous avez cliqué sur le premier élément de menu !');
}
function function2() {
SpreadsheetApp.getUi().alert('Vous avez cliqué sur le deuxième élément de menu !');
}Note : Le paramètre e passé à la fonction est un objet d’événement. Il contient des informations sur le contexte qui a déclenché le déclencheur, mais son utilisation est facultative.
Pour le tester, sélectionnez la fonction onOpen() dans la liste déroulante, puis cliquez sur le bouton “Exécuter”.
Vous verrez “Mon menu personnalisé” dans le menu de votre feuille de calcul.
La fonction onOpen() est l’un des noms de fonction réservés de GAS. Chaque fois que vous ouvrez un document, un déclencheur intégré exécute d’abord cette fonction. Ces déclencheurs intégrés sont également appelés déclencheurs simples et n’ont pas besoin d’autorisation de l’utilisateur.
Les autres noms de fonction réservés incluent onEdit(), onInstall(), onSelectionChange(e), onFormSubmit(), doGet() et doPost(). Assurez-vous de ne pas utiliser ces noms comme noms de fonction.
Automatiser des tâches avec le déclencheur Google Apps Script
GAS vous permet de créer vos propres déclencheurs. Ces déclencheurs sont également appelés déclencheurs installables car vous devez les autoriser avant de les utiliser.
Avec les déclencheurs installables, vous pouvez configurer votre script pour s’exécuter à un certain événement (lors de l’ouverture ou de la modification d’un document, etc.) ou selon un horaire (chaque heure, chaque jour, etc.).
Remarquez le script suivant. La fonction getBitcoinPrice() récupère les données des dernières 24 heures de la plateforme de trading Bitstamp et les affiche dans une feuille de calcul.
function getBitcoinPrice() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("Feuille1");
var header = ['Timestamp','High','Low','Volume','Bid','Ask'];
sheet.getRange("A1:F1").setValues([header]);
var url = 'https://www.bitstamp.net/api/ticker/';
var response = UrlFetchApp.fetch(url);
if (response.getResponseCode() == 200) {
var json = JSON.parse(response);
var result = [];
result.push( new Date(json.timestamp *= 1000) ); // Timestamp
result.push(json.high); // High
result.push(json.low); // Low
result.push(json.volume); // Volume
result.push(json.bid); // Bid (highest buy order)
result.push(json.ask); // Ask (lowest sell order)
sheet.appendRow(result);
} else {
Logger.log(response);
}
}Une fois autorisé, exécutez la fonction manuellement en cliquant sur le bouton “Exécuter”.
Vous pouvez voir la liste des déclencheurs que vous avez créés sur la page des déclencheurs, qui peut être ouverte à partir du menu de gauche.
Exemples de fonctions personnalisées avec Google Apps Script
Google Sheets propose des centaines de fonctions intégrées, telles que SOMME, MOYENNE, CONCATÉNER, ainsi que des fonctions plus avancées, telles que RECHERCHEV, REGEXMATCH et QUERY. Lorsque celles-ci ne suffisent pas à vos besoins, vous pouvez utiliser GAS pour écrire vos propres fonctions.
Jetons un coup d’œil à quelques exemples de fonctions personnalisées.
Exemple 1. Fonction numérique personnalisée avec Google Apps Script
Voici une simple fonction personnalisée appelée SURFACECERCLE. La fonction calcule la surface d’un cercle en prenant un argument numérique unique, qui est le rayon du cercle, et renvoie la surface d’un cercle en utilisant la formule : PI * rayon². Elle valide également que le rayon est un nombre et qu’il n’est pas inférieur à 0.
/**
* Renvoie la surface du cercle à partir du rayon spécifié.
*
* @param {number} rayon
* @return {number}
* @customfunction
*/
function SURFACECERCLE(rayon) {
if (typeof rayon !== 'number' || rayon < 0) {
throw new Error('Le rayon doit être un nombre positif');
}
return Math.PI * Math.pow(rayon, 2);
}Pour utiliser la fonction, saisissez quelques valeurs de rayon dans votre feuille de calcul. Ensuite, tapez un signe égal suivi du nom de la fonction et de la valeur d’entrée. Par exemple, tapez =SURFACECERCLE(A2) et appuyez sur Entrée. Une chose intéressante ici est que vous pouvez voir l’auto-complétion affiche la description de la nouvelle fonction personnalisée.
Copiez la formule vers le bas et vous verrez le résultat.
Notez qu’il y a une erreur en B5. C’est parce que xxxxx n’est pas une valeur numérique. Ainsi, en utilisant =SURFACECERCLE(A5), vous obtenez un message d’erreur que nous avons défini dans le code.
Exemple 2. Fonction de chaîne personnalisée avec Google Apps Script
Dans l’exemple suivant, vous verrez comment les fonctions personnalisées peuvent être utilisées pour accéder à d’autres services Google, tels que le service de langue.
Les fonctions suivantes effectuent des traductions de l’anglais vers d’autres langues. Chacune des fonctions prend un argument de type chaîne et le traduit vers une autre langue : le français, l’espagnol ou l’italien.
function ANGLAIS_VERS_FRANCAIS(mots_en_anglais) {
return LanguageApp.translate(mots_en_anglais, 'en', 'fr');
}
function ANGLAIS_VERS_ESPAGNOL(mots_en_anglais) {
return LanguageApp.translate(mots_en_anglais, 'en', 'es');
}
function ANGLAIS_VERS_ITALIEN(mots_en_anglais) {
return LanguageApp.translate(mots_en_anglais, 'en', 'it');
}Exemple d’utilisation dans une feuille de calcul.
Les fonctions semblent bien fonctionner pour une phrase simple et non idiomatique. Vous pouvez utiliser ces fonctions pour vous aider à traduire des mots anglais vers d’autres langues. Cependant, il est toujours préférable de vérifier à nouveau avec des locuteurs natifs pour l’exactitude et la qualité des traductions.
Exemple 3. Fonction de date personnalisée avec Google Apps Script
La fonction suivante renvoie un tableau de dates, qui sont le premier jour de chaque mois pour l’année donnée, y compris leur jour. Cela démontre qu’une fonction personnalisée peut également renvoyer un tableau bidimensionnel.
function PREMIERJOURDUMOIS(année) {
var tableau = [];
for (var m = 0; m <= 11; m++) {
var premierJour = new Date(année, m, 1);
var nomDuJour = '';
switch(premierJour.getDay()) {
case 0:
nomDuJour = 'Dimanche';
break;
case 1:
nomDuJour = 'Lundi';
break;
case 2:
nomDuJour = 'Mardi';
break;
case 3:
nomDuJour = 'Mercredi';
break;
case 4:
nomDuJour = 'Jeudi';
break;
case 5:
nomDuJour = 'Vendredi';
break;
case 6:
nomDuJour = 'Samedi';
break;
}
tableau.push([(m+1) + '/1/' + année, nomDuJour]);
}
return tableau;
}Saisissez cette formule dans une cellule, par exemple, =PREMIERJOURDUMOIS(2021) en A1. Vous verrez un résultat comme celui-ci.
Exemple 4. Fonction web personnalisée pour l’importation de CSV avec Google Apps Script
Supposons que vous souhaitiez importer des données CSV à partir d’un fichier en ligne publié. Google Sheets dispose d’une fonction intégrée appelée IMPORTDATA pour cela. Mais lors de l’importation, vous voudrez peut-être ajouter des filtres. Par exemple, exclure plusieurs colonnes. Dans ce cas, vous pouvez utiliser IMPORTDATA en combinaison avec la fonction QUERY pour avoir plus d’options.
Une autre alternative consiste à écrire votre propre fonction en utilisant GAS pour éviter d’écrire plusieurs fonctions dans une seule cellule. Voici un exemple :
function IMPORTATIONPERSONNALISEECSV(url, colonnes) {
var contenuCSV = UrlFetchApp.fetch(url).getContentText();
var donneesCSV = Utilities.parseCsv(contenuCSV);
var colonnesRequises = colonnes.split(",");
var indexesDesColonnesRequises = [];
if (colonnesRequises.length > 0) {
for (var i = 0; i < donneesCSV[0].length; i++) {
if (colonnesRequises.includes((donneesCSV[0][i]))) {
indexesDesColonnesRequises.push(i);
}
}
}
if (indexesDesColonnesRequises.length > 0) {
return donneesCSV.map(r => indexesDesColonnesRequises.map(i => r[i]));
}
return donneesCSV;
}La fonction ci-dessus vous permet d’importer un fichier CSV à partir d’une URL et de choisir uniquement quelques colonnes à importer.
La fonction a deux paramètres : url et colonnes. Le deuxième paramètre (colonnes) accepte les noms de colonnes, chacun concaténé avec une virgule. Par exemple : “nom-colonne1,nom-colonne2,…”
Exemple d’utilisation dans une feuille de calcul.
- La cellule B1 contient l’URL d’un fichier CSV.
- La cellule B2 contient les colonnes à importer.
Puis-je utiliser des fonctions personnalisées avec ARRAYFORMULA ?
Vous ne pouvez pas imbriquer des fonctions personnalisées avec ARRAYFORMULA, mais vous pouvez modifier votre fonction pour qu’elle accepte et renvoie une plage sous forme de tableau bidimensionnel. Cela optimisera également les performances de votre feuille de calcul, car chaque appel de fonction personnalisée nécessite un appel au serveur Apps Script. Plus il y a d’appels de fonctions personnalisées, plus les performances de votre feuille de calcul seront lentes.
Voici un exemple d’une simple fonction personnalisée qui convertit les degrés Fahrenheit en Celsius :
function FtoC(valeur) {
return (valeur - 32) * 0.5556;
}Nous l’avons modifiée pour accepter à la fois une seule cellule et une plage de cellules, comme suit :
function FtoC(valeur) {
return Array.isArray(valeur) ? valeur.map(ligne => ligne.map(cellule => (cellule - 32) * 0.5556)) : (valeur - 32) * 0.5556;
}Ainsi, maintenant, vous pouvez taper =FtoC(A2:A5) et obtenir le résultat souhaité.
Une alternative à Google Apps Script : Coupler.io
Coder votre propre fonction GAS pour l’importation de données (comme nous l’avons montré précédemment avec la fonction IMPORTATIONPERSONNALISEECSV) peut nécessiter beaucoup d’efforts. Vous devrez tester différents scénarios, essayer différents critères de filtrage, etc.
Si vous recherchez une solution pratique, puissante et ne nécessitant aucune programmation, vous voudrez peut-être essayer Coupler.io. Il s’agit d’un outil d’intégration qui vous permet d’importer des données depuis des fichiers CSV et d’autres sources de données populaires dans Google Sheets.
Coupler.io offre également plus de fonctionnalités que la fonction IMPORTDATA. Pour une comparaison détaillée, vous voudrez peut-être consulter ce tableau de comparaison.
Limitations de Google Apps Script
Comme GAS s’exécute sur les serveurs de Google, Google impose des limites à son exécution, telles que :
- Un script ne peut pas s’exécuter en continu pendant plus de 6 minutes. Ainsi, par exemple, si vous utilisez une boucle dans votre fonction, assurez-vous qu’elle est exempte de bogues et qu’elle ne provoque pas une exécution sans fin. Évitez également de traiter un ensemble de données volumineux qui pourrait entraîner une exécution de script de plus de 6 minutes.
- Une fonction personnalisée ne peut pas s’exécuter pendant plus de 30 secondes. Ainsi, évitez de traiter de grandes données externes à l’aide de fonctions personnalisées. Sinon, vous risquez de voir une erreur “Délai d’exécution maximal dépassé”.
Veuillez noter que les limitations ci-dessus peuvent changer à l’avenir. Vous pouvez trouver la liste complète des limitations de GAS ici.
Où puis-je en savoir plus sur Google Apps Script
Dans ce tutoriel sur Google Apps Script, vous avez appris comment créer des fonctions personnalisées, des menus personnalisés et comment accéder à d’autres applications et services Google. Cependant, GAS est puissant et vous pouvez en faire beaucoup plus, comme créer des add-ons, publier des applications web et se connecter à des bases de données externes.
Si vous souhaitez en savoir plus sur Google Apps Script, une suggestion courante est d’apprendre JavaScript et de suivre les nombreux tutoriels disponibles sur Internet. Eh bien, il ne fait aucun doute que le nombre de tutoriels sur Internet est énorme et peut être déroutant.
Nous vous suggérons donc de commencer par les sources suivantes :
- Documentation sur Apps Script par Google
- Exemples, codelab, tutoriels vidéo, articles et autres ressources d’apprentissage pour Apps Script par Google
- Eloquent JavaScript – livre électronique de Marijn Haverbeke ; il est gratuit en version numérique
- Apps Script Blastoff! – cours d’introduction gratuit sur Apps Script par Ben Collins
- Entrées Stackoverflow pour Google Apps Script – posez des questions ici
- Problèmes ouverts d’Apps Script – postez ici tous les problèmes (ou demandes d’amélioration)
Bon apprentissage et amusez-vous bien ! :)
