Tutoriel Google Apps Script pour maîtriser les macros

Les cadres efficaces savent que le temps est le facteur limitant… Rien d'autre, peut-être, ne distingue autant les cadres efficaces que leur souci du temps.

Peter Drucker

Le temps est notre ressource la plus précieuse. Nous voulons le dépenser dans les activités les plus impactantes et les plus génératrices de valeur que nous pouvons, non seulement parce que celles-ci ont généralement la valeur monétaire la plus élevée, mais aussi pour nous mettre continuellement au défi et maximiser notre satisfaction au travail.

Il existe de nombreuses façons d'améliorer votre efficacité et votre productivité afin de mieux utiliser votre temps. Dans un article précédent sur Google Sheets, j'ai expliqué en quoi la puissance de la collaboration en ligne est l'une des clés de l'augmentation de la productivité.

Dans un autre article, j'ai démontré comment le langage de programmation Python peut être un puissant outil d'analyse et d'automatisation des tâches pour les professionnels de la finance.

En m'inspirant de cela, je souhaite maintenant présenter un didacticiel Google Apps Script. Google Apps Script vous permet d'écrire des scripts et des programmes en JavaScript pour automatiser, connecter et étendre les produits de la suite G de Google, notamment Sheets, Docs, Slides, Gmail, Drive et plusieurs autres. L'apprendre demande un investissement en temps, tout comme l'écriture des scripts, mais les gains de productivité et les opportunités supplémentaires qu'il ouvre en valent la peine.

Dans un premier temps, commençons par examiner un concept familier : les macros.

Enregistrer et utiliser des macros dans Google Sheets

Si vous avez passé beaucoup de temps à travailler avec Excel, vous êtes forcément entré en contact avec l'interface de macro VBA (Visual Basic pour Applications) d'Excel à un moment donné. Soit en les enregistrant ou en les écrivant vous-même, soit en vous appuyant sur ceux créés par d'autres.

Les macros sont un excellent moyen d'automatiser les workflows répétitifs et fastidieux. VBA n'est peut-être pas un langage que vous avez consacré beaucoup de temps à apprendre, mais sa beauté était que vous n'en aviez pas vraiment besoin pour devenir productif et créer vos propres macros. Vous pouvez simplement enregistrer le flux de travail que vous souhaitez automatiser, puis entrer dans le code et apporter les petites modifications nécessaires pour rendre la macro plus générale.

À certains égards, VBA est une excellente leçon oubliée sur la manière d'initier des personnes non techniques au codage . La façon dont vous pouvez enregistrer des actions et ensuite remplir le code pour une révision ultérieure est en effet une manière beaucoup plus pragmatique d'apprendre en lisant des manuels et en regardant passivement des didacticiels.

La même fonctionnalité d'enregistrement de VBA est disponible dans Google Sheets. Voici un exemple simple d'utilisation :

Commençons par quelques exemples de données, en utilisant une requête IMPORTHTML pour importer une table. Dans cet exemple, j'ai téléchargé une liste de Wikipedia des 15 plus grands fonds spéculatifs au monde. Cela va sans dire, mais, ceci est un exemple arbitraire; l'intention est que vous vous concentriez davantage sur l'application, sur le sujet.

Le processus d'enregistrement de macro est lancé via le chemin de menu suivant : Outils > Macros > Enregistrer une macro.

Nous passons ensuite en revue les actions (format PC) que nous souhaitons enregistrer :

1. Sélectionnez la première ligne
2. Appuyez sur Maj + Ctrl + Flèche vers le bas pour tout sélectionner
3. Ctrl + C pour copier
4. Maj + F11 pour créer une nouvelle feuille
5. Donnez un nouveau nom à la feuille
6. Appuyez sur Maj + Contrôle + V pour coller les valeurs

Une fois cela fait, appuyez sur le bouton Enregistrer dans la fenêtre de la macro en bas, donnez-lui un nom et un raccourci clavier facultatif.

Pour des actions plus simples qui peuvent être reproduites exactement à travers ces mêmes étapes, le processus se terminerait ici et vous pouvez commencer à utiliser votre macro immédiatement. Dans ce cas, cependant, nous devons apporter quelques modifications avant que le code ne soit utilisable. Par exemple, la feuille sur laquelle nous copions devra avoir un nom différent à chaque fois. Voyons comment procéder.

Écrire manuellement le script Google Apps

Nous allons maintenant voir les os de Google Apps Script pour la première fois ; la plate-forme de programmation qui s'exécute sur les serveurs de Google. Cela alimente nos macros et vous permet de créer des flux de travail très complexes, et même des modules complémentaires, pour les applications elles-mêmes. Il peut être utilisé pour automatiser non seulement le travail des feuilles de calcul, mais en fait presque tout ce qui est interconnecté dans la suite G de Google.

Le langage de programmation d'Apps Script est JavaScript , l'un des langages de programmation les plus populaires, ce qui signifie qu'il existe une multitude de ressources pour tous ceux qui souhaitent apprendre en profondeur. Mais, tout comme avec VBA, vous n'en avez pas vraiment besoin : vous pouvez utiliser la même fonctionnalité d'enregistrement et effectuer simplement les étapes que vous souhaitez pouvoir répéter automatiquement. La sortie de l'enregistrement peut sembler grossière et ne correspondra probablement pas parfaitement à ce que vous voulez faire, mais elle fournira un point de départ suffisamment solide. Faisons-le maintenant pour le script que nous venons d'enregistrer.

Lors de l'enregistrement, il est logique de faire attention à ne pas enregistrer accidentellement d'étapes supplémentaires que vous ne souhaitez pas voir capturées dans l'enregistrement final, mais c'est parfois difficile à éviter : quelque chose d'aussi simple que de sélectionner une autre cellule avant d'appuyer sur la touche Stop Le bouton d'enregistrement sera capturé et ensuite répété à chaque fois que vous exécuterez le script. La première étape lors de l'édition de notre script serait de le nettoyer et de supprimer toutes ces étapes. Plongeons-nous en allant dans Outils > Éditeur de script dans le menu Fichier.

Si vous connaissez JavaScript, vous le reconnaîtrez instantanément, et vous pourriez également être surpris de voir le mot-clé "var" au lieu de "let" ou "const" comme vous le verriez dans le JavaScript moderne. Cela reflète le fait que la version JavaScript d'Apps Script est assez ancienne et ne prend pas en charge la plupart des fonctionnalités les plus récentes du langage. Vers la fin, je présenterai cependant une solution de contournement pour ceux qui souhaitent utiliser les fonctionnalités linguistiques les plus récentes.

Lorsque vous exécutez le script pour la première fois, il vous demandera une autorisation, ce qui est logique, car les scripts peuvent modifier (et potentiellement supprimer) toutes vos données. Vous reconnaîtrez probablement le processus d'autorisation d'autres produits Google.

Nous pouvons maintenant commencer à modifier le code. Les modifications que nous devons apporter sont mineures, mais si vous le faites pour la première fois, cela peut nécessiter une recherche rapide dans la documentation Sheets Apps Script et/ou une recherche rapide d'un concept JavaScript, tel que l'utilisation de dates. Ici, le fait que JavaScript soit un langage si répandu est utile : une solution à tout problème auquel vous êtes confronté ou à une fonctionnalité qui vous vient à l'esprit peut généralement être trouvée rapidement si vous formulez votre terme de recherche de manière simple.

Les modifications apportées à cette version du script par rapport à la version originale enregistrée sont qu'au lieu du nom codé en dur pour la nouvelle feuille que nous créons, nous la nommons maintenant avec la date d'aujourd'hui à la place. De plus, nous modifions également le chemin de copie à la fin pour faire référence à cette nouvelle feuille. Les quatre dernières lignes montrent également comment effectuer certaines opérations de mise en forme, telles que la modification de la valeur d'une cellule, le redimensionnement des colonnes et le masquage du quadrillage.

 function createSnapshot() { var spreadsheet = SpreadsheetApp.getActive(); var date = new Date().toISOString().slice(0,10); var destination = spreadsheet.insertSheet(date); spreadsheet.getRange('HTML!A1:F1').activate(); spreadsheet.getSelection() .getNextDataRange(SpreadsheetApp.Direction.DOWN) .activate(); spreadsheet.getActiveRange() .copyTo(SpreadsheetApp.setActiveSheet(destination) .getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false); var sheet = spreadsheet.setActiveSheet(destination) sheet.getRange("D1").setValue("AUM $bn") sheet.setHiddenGridlines(true); sheet.getRange("A1:D1").setFontWeight("bold"); sheet.autoResizeColumns(1, 4); };

L'exécution du script maintenant montrera que la nouvelle feuille porte bien le nom de la date du jour et contient les informations copiées sous forme de valeurs (et non de formules) à partir de la feuille principale.

Des visualisations graphiques peuvent maintenant être ajoutées en utilisant le même processus d'enregistrement. Je l'ai utilisé pour créer trois graphiques simples.

Le nettoyage du code pour chacun ressemblera à ceci :

 function createColumnChart() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('C1:D16').activate(); var sheet = spreadsheet.getActiveSheet(); chart = sheet.newChart() .asColumnChart() .addRange(spreadsheet.getRange('B1:D16')) .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS) .setTransposeRowsAndColumns(false) .setNumHeaders(-1) .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH) .setOption('useFirstColumnAsDomain', true) .setOption('curveType', 'none') .setOption('domainAxis.direction', 1) .setOption('isStacked', 'absolute') .setOption('series.0.color', '#0b5394') .setOption('series.0.labelInLegend', 'AUM $bn') .setPosition(19, 6, 15, 5) .build(); sheet.insertChart(chart); };

Encore une fois, ne vous inquiétez pas si certaines des options semblent déroutantes : tout est généré automatiquement, il vous suffit d'en comprendre suffisamment pour supprimer les étapes inutiles et peut-être apporter de petites modifications plus tard.

Exemples de script Google Apps avancés : connexion de Sheets à Google Drive et Slides

Tout commence maintenant à prendre forme, mais que se passe-t-il si le résultat réel que nous voulons n'est pas une feuille de calcul mais une présentation ? Si tel est le cas, alors la plupart du travail à partir d'ici pourrait encore être manuel, et nous n'avons pas gagné beaucoup de temps si nous devons le faire de manière récurrente.

Explorons maintenant à quoi cela pourrait ressembler pour automatiser la création d'une présentation à l'aide des exemples de données de notre feuille de calcul.

Cet exercice devient maintenant plus avancé pour deux raisons :

1. Nous devrons nous familiariser avec la façon de travailler avec Google Slides (et Google Drive) en plus de Sheets.
2. Dans Slides, ou lorsque vous travaillez entre les applications G Suite en général, aucune fonctionnalité "Enregistrer une macro" n'est disponible. Cela signifie que vous devez en savoir suffisamment sur Apps Script (et être à l'aise pour parcourir la documentation de chacun des produits G Suite) pour écrire des scripts à partir de zéro.

Cet exemple suivant est destiné à fournir quelques blocs de construction de base pour vous aider à démarrer et à vous familiariser.

Pour commencer, créons un modèle que nous souhaitons ensuite remplir de contenu à l'aide de notre script. Voici deux diapositives de présentation simples que j'ai rassemblées :

Ensuite, vous devrez obtenir l'ID de ce modèle car vous devrez vous y référer dans votre script. Inconsciemment, vous aurez vu cet identifiant plusieurs fois car il s'agit en fait de la séquence aléatoire de caractères et de chiffres que vous voyez dans l'URL de votre navigateur :

https://docs.google.com/presentation/p/ this_is_your_presentation_ID /edit#slide=id.p.

Nous devons maintenant ajouter les lignes suivantes à notre script d'origine. Cela demandera à nouveau une autorisation, cette fois pour accéder à votre Google Drive.

 function createPresentation() { var template; var template = DriveApp.getFileById(templateId); var copy = template.makeCopy("Weekly report " + date).getId(); var presentation = SlidesApp.openById(copy); }

Vous ne verrez aucun retour visuel immédiat si vous exécutez cet extrait de code, mais si vous regardez dans le dossier de votre Google Drive où vous aviez stocké le modèle, vous constaterez qu'une copie de celui-ci a bien été créée, et qu'elle a aujourd'hui date dans le nom du fichier. Nous partons du bon pied !

Utilisons maintenant plus de blocs de construction pour commencer à le remplir de contenu, par programmation plutôt qu'à la main. Ajoutez les lignes suivantes à la même fonction :

 presentation.getSlides()[0] .getPageElements()[0] .asShape() .getText() .setText("Weekly Report " + date);

Maintenant, les choses deviennent un peu plus intéressantes, car nous avons changé la première page pour inclure la date d'aujourd'hui. Dans Slides, comme dans Sheets, vous travaillez avec des objets (représentés par des classes) qui ont chacun des propriétés et des méthodes (c'est-à-dire des fonctionnalités associées). Celles-ci sont organisées dans une hiérarchie, SpreadsheetsApp, DriveApp ou SlidesApp étant l'objet de niveau supérieur. Dans l'extrait de code ci-dessus, nous devons parcourir cette hiérarchie étape par étape pour atteindre l'élément que nous voulons modifier, dans ce cas : le texte dans une zone de texte. Concrètement, cela signifie atteindre les objets Presentation, Slide, PageElement et Shape, jusqu'à ce que nous arrivions enfin à l'objet TextRange que nous voulons modifier.

Garder une trace du type d'objet avec lequel vous traitez peut être déroutant et les bogues résultant de la tentative d'application d'une opération au mauvais objet peuvent être difficiles à résoudre. Malheureusement, la fonctionnalité d'aide et les messages d'erreur dans l'éditeur de script lui-même ne fournissent pas toujours beaucoup de conseils ici, le bon côté étant qu'une telle attention améliorera au moins vos pratiques de contrôle de la qualité.

Après avoir créé la présentation et mis à jour le titre, il est maintenant temps d'y insérer l'un de nos nouveaux graphiques. En gardant à l'esprit la hiérarchie des objets, le code suivant devrait maintenant avoir un sens :

 var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getSheetByName(date); var chart = sheet.getCharts()[0]; var position = {left: 25, top: 75}; var size = {width: 480, height: 300}; presentation.getSlides()[1] .insertSheetsChart(chart, position.left, position.top, size.width, size.height);

Si vous exécutez le script complet, la présentation de sortie devrait ressembler à ceci :

Espérons que cet exemple illustre les principes et vous inspire pour vous lancer dans votre propre expérimentation. Si vous y réfléchissez, je suis sûr que vous pouvez trouver au moins quelques exemples de travaux manuels effectués dans votre entreprise aujourd'hui qui devraient vraiment être automatisés de cette manière. Servant à libérer du temps pour réfléchir, analyser et appliquer un jugement, plutôt que de mélanger mécaniquement des données d'un format et/ou d'un endroit à un autre. Amélioration de l'expérience de développement Comme mentionné précédemment, la version de JavaScript prise en charge dans Google Apps Script est ancienne et la fonctionnalité de l'éditeur de script en ligne est très limitée. Si vous enregistrez simplement une macro ou écrivez quelques dizaines de lignes, vous ne le remarquerez pas vraiment. Si toutefois, vous avez des plans ambitieux pour automatiser tous les aspects de vos rapports hebdomadaires ou mensuels, ou si vous souhaitez créer des plugins, alors vous serez heureux de savoir qu'il existe un outil en ligne de commande qui vous permet de développer en utilisant votre environnement de développement préféré. .

Si vous êtes à un tel niveau de compétence, vous souhaiterez probablement également profiter des fonctionnalités les plus récentes offertes par JavaScript, et potentiellement encore plus, car avec l'outil de ligne de commande, vous pouvez également développer en TypeScript.

Utilisation de Python pour la programmation Google Sheets

Si vous trouvez que travailler avec Apps Script n'est pas votre tasse de thé, il existe d'autres options, selon le cas d'utilisation. Si vous souhaitez effectuer des calculs numériques plus avancés, vous connecter avec des API ou des bases de données, ou simplement préférer le langage de programmation Python à JavaScript, alors Google Colaboratory est un produit inestimable. Il vous offre un bloc-notes Jupyter exécuté sur les serveurs de Google qui vous permet d'écrire des scripts Python qui s'intègrent de manière transparente à vos fichiers Google Drive et, via la bibliothèque "gspread", facilite le travail avec vos données de feuille de calcul.

J'ai décrit de nombreux avantages de Python dans un article sur la façon de l'utiliser pour les fonctions financières, qui sert également d'introduction en douceur à l'utilisation des blocs-notes Python et Jupyter dans un contexte commercial et financier. Un avantage très important pour moi est que, contrairement à Apps Script, le bloc-notes Python dans Colaboratory est interactif, vous voyez donc les résultats (ou message d'erreur) après l'exécution de chaque ligne ou petit bloc de code.

L'automatisation est addictive

Ce didacticiel Google Apps Script a montré un aperçu de ce qui est possible grâce au langage de codage de Google. Les possibilités sont pratiquement infinies. Cependant, si vous n'avez pas de formation technique, les exemples de code peuvent sembler intimidants et vous pensez peut-être que les gains de productivité tirés de l'apprentissage de Google Apps Script pourraient ne pas être suffisants pour compenser l'investissement important en termes de temps nécessaire. pour l'apprendre.

Ceci, bien sûr, dépend de nombreux facteurs, y compris le type de rôle que vous avez, ou comptez avoir, à l'avenir. Mais même si vous ne vous attendez pas à faire quelque chose de similaire aux exemples présentés ici, avoir une compréhension de ce qui est possible et de la quantité de travail qu'il faudrait mettre en œuvre peut déclencher des réflexions et des idées sur la façon d'améliorer la productivité dans votre entreprise, par exemple. vos clients ou vous-même personnellement.

Personnellement, je peux attester de la satisfaction de m'asseoir et d'appuyer sur un bouton qui complète une heure de travail manuel fastidieux en moins d'une minute. Après avoir fait cela pour la 50e fois, vous serez reconnaissant pour les quelques heures passées à tout bricoler en premier lieu, ce qui a finalement servi à libérer votre temps pour des activités à plus forte valeur ajoutée. Après un certain temps, ces avantages d'évolutivité deviennent addictifs.