Principes de base d'Apps Script avec Google Sheets 1 : macros et fonctions personnalisées

Principes de base d'Apps Script avec Google Sheets :

macros et fonctions personnalisées

À propos de cet atelier de programmation

Dernière mise à jour : sept. 18, 2024

Rédigé par Google Workspace Developer Relations

1. Bienvenue dans Apps Script

Qu'est-ce qu'Apps Script ?

Apps Script est une plate-forme de développement d'applications qui vous permet d'automatiser, de personnaliser et d'étendre votre expérience Google Workspace. Apps Script vous permet de gagner du temps et de limiter les efforts en simplifiant les tâches fastidieuses ou complexes dans Google Workspace.

Voici quelques exemples de fonctionnalités Apps Script :

• Les services intégrés d'Apps Script vous permettent de lire, de mettre à jour et de manipuler les données de vos applications Google Workspace à l'aide de scripts.
• Vous pouvez créer des scripts à l'aide de l'éditeur de code intégré à Apps Script. Il n'est pas nécessaire d'installer ni d'exécuter de logiciel de développement de code.
• Vous pouvez concevoir des interfaces utilisateur pour les éditeurs Google Workspace qui vous permettent d'activer des scripts directement à partir de ces éditeurs à l'aide d'éléments de menu, de boîtes de dialogue et de barres latérales.

Cette playlist d'ateliers de programmation sur les principes de base d'Apps Script avec Google Sheets vous explique les principes de base d'Apps Script et comment l'utiliser pour améliorer votre expérience Google Sheets. Cet atelier de programmation est axé sur les principes de base d'Apps Script.

Service de feuille de calcul

Vous pouvez utiliser Apps Script pour étendre Google Sheets afin de gagner du temps et de l'énergie. Apps Script fournit le service Spreadsheet, qui permet aux scripts d'interagir avec vos fichiers Google Sheets et les données qu'ils contiennent. Vous pouvez utiliser ce service pour automatiser les tâches courantes suivantes dans les feuilles de calcul :

• Créez ou modifiez une feuille de calcul.
• Lire et mettre à jour les données, les formules et la mise en forme des cellules
• Créez des boutons et des menus personnalisés.
• Importer et exporter des données depuis d'autres applications Google ou des sources tierces
• Partagez des feuilles de calcul et contrôlez l'accès à celles-ci.

Points abordés

Cette playlist aborde tous les sujets dont vous aurez besoin pour commencer à utiliser Apps Script avec Google Sheets :

1. Macros et fonctions personnalisées
2. Feuilles de calcul, feuilles et plages
3. Utiliser les données
4. Mise en forme des données
5. Représenter et présenter des données dans Slides

Les ateliers de programmation de cette playlist sont conçus pour être lus dans l'ordre. Commencez donc par celui-ci et suivez-les dans l'ordre pour une expérience d'apprentissage optimale.

Passez à la section suivante pour en savoir plus sur le contenu de cet atelier de programmation.

2. Introduction

Bienvenue dans le premier atelier de programmation de cette playlist. Dans cet atelier de programmation, vous allez découvrir les bases de l'utilisation d'Apps Script avec Google Sheets. Plus précisément, cet atelier de programmation se concentre sur deux concepts clés : les macros et les fonctions personnalisées.

Une macro est une série d'actions enregistrées dans Google Sheets. Une fois enregistrée, vous pouvez activer une macro pour répéter ces actions ultérieurement à l'aide d'un élément de menu ou d'un raccourci clavier. Vous pouvez créer et modifier vos propres macros dans Google Sheets et dans l'éditeur de code Apps Script.

Dans l'éditeur de code Apps Script, vous pouvez également créer des fonctions personnalisées. Comme pour les fonctions intégrées proposées par Sheets (comme SUM ou AVERAGE), vous pouvez utiliser Apps Script pour écrire vos propres fonctions personnalisées pour des opérations simples et spécifiques (comme les conversions ou la concaténation de chaînes). Une fois créées, vous pouvez appeler ces fonctions dans Sheets comme vous le feriez pour une fonction intégrée. Vous pouvez également utiliser des fonctions personnalisées dans les formules de cellules que vous écrivez, en les combinant avec d'autres fonctions si nécessaire.

Lisez la suite pour découvrir les concepts et les exigences de cet atelier de programmation.

Points abordés

• Découvrez comment créer un script pour Google Sheets.
• Découvrez comment parcourir l'éditeur Apps Script.
• Découvrez comment créer et modifier des macros.
• Découvrez comment créer votre première fonction personnalisée Sheets.

Prérequis

• Connaissances de base de JavaScript
• Connaissances de base de Google Sheets
• Possibilité de lire la notation A1 de Sheets

Remarque : Apps Script est basé sur JavaScript. Si vous débutez avec JavaScript, Codecademy propose un cours d'introduction à JavaScript sans frais. Bien que Google n'ait pas créé ce cours, il devrait couvrir tout ce que vous devez savoir.

Vous avez terminé les présentations. Passez à la section suivante pour commencer à utiliser les macros.

3. Créer une macro dans Sheets

En général, lorsque vous travaillez dans des feuilles de calcul, vous pouvez vous retrouver dans une boucle d'actions répétitives (copier des valeurs de cellules, mettre en forme, créer des formules, etc.), ce qui peut devenir fastidieux et entraîner des erreurs. Pour automatiser les actions répétées, Google Sheets propose des macros. Les macros vous permettent d'enregistrer une série d'actions dans une feuille de calcul. Avec une macro enregistrée, vous pouvez répéter les mêmes actions ailleurs dans une feuille de calcul en appuyant simplement sur un raccourci clavier.

Dans cette section, vous allez apprendre à créer une macro dans Sheets. Dans la section suivante, vous verrez comment les macros sont créées à l'aide d'Apps Script.

Avant de commencer

Avant de continuer, vous avez besoin d'une feuille de calcul contenant des données. Nous vous en avons fourni un : cliquez sur ce lien pour copier la feuille de données, puis sur Créer une copie.

Une copie de la feuille de calcul exemple à utiliser est placée dans votre dossier Google Drive et nommée "Copie de Top 10 des films les plus rentables (2018)".

Créer une macro

Maintenant que vous disposez d'une feuille de calcul, vous pouvez enregistrer une macro dans Google Sheets. Dans cet exemple, vous allez créer une macro qui met en forme une ligne d'en-tête pour vos données. Il vous suffit de procéder comme suit :

1. Cliquez sur la cellule A1 pour placer le curseur dans la ligne. Il s'agit de la ligne d'en-tête.
2. Dans le menu, sélectionnez Extensions> Macros> Enregistrer une macro.

Une fois l'enregistrement lancé, Google Sheets mémorise chaque action que vous effectuez dans la feuille de calcul : mise en surbrillance de cellules, ajout de données, passage à d'autres feuilles, mise en forme, etc. Ces actions deviennent le "script" qui se répète une fois que vous avez enregistré et activé la macro.

3. Dans la boîte de dialogue "Macro", sélectionnez Référence relative.

Termes clés : les macros peuvent utiliser deux types de références dans Google Sheets pour faire référence à des cellules spécifiques. Les références absolues sont fixées aux emplacements exacts enregistrés (par exemple, la cellule A5), tandis que les références relatives sont appliquées en utilisant la sélection actuelle de l'utilisateur comme point de départ (par exemple, la quatrième cellule en dessous de la sélection actuelle).

Dans cet atelier de programmation, vous devez utiliser des références relatives pour vos macros.

4. Sélectionnez la ligne 1.

5. Modifiez la couleur de remplissage de la première ligne en remplaçant le blanc par le magenta foncé 3.

6. Modifiez la couleur du texte de la première ligne, qui passe du noir au blanc.

7. Pour mettre le texte en gras, appuyez sur Ctrl+B (ou Cmd+B sur macOS).
8. Pour figer la première ligne, sélectionnez Affichage > Figer > 1 ligne.

9. Cliquez sur Enregistrer dans la boîte de dialogue des macros. Une nouvelle boîte de dialogue vous invite à nommer la macro. Saisissez le nom "En-tête", puis cliquez sur Enregistrer.

À l'aide de l'interface utilisateur de Sheets, vous avez créé une macro spécialisée dans la mise en forme des en-têtes.

Activer votre macro

Pour appliquer votre nouvelle macro dans Sheets, suivez ces instructions :

1. Pour créer une feuille, cliquez sur Ajouter une feuille .

2. Dans la nouvelle feuille, ajoutez du texte à A1:C2. N'hésitez pas à suivre les exemples d'entrées ci-dessous :

3. Mettez en surbrillance la première ligne.

4. Pour appliquer la macro à la zone sélectionnée, cliquez sur Extensions> Macros> En-tête.
5. Autorisez la macro en suivant les instructions à l'écran.

Remarque : Si vous utilisez un compte gmail.com, il est possible qu'une boîte de dialogue d'application non validée s'affiche lors de la première utilisation de votre script. Google utilise cette information pour avertir les utilisateurs qui pourraient utiliser du code provenant d'auteurs inconnus ou non fiables. Si cette boîte de dialogue s'affiche, vous pouvez continuer, car vous êtes l'auteur du script. Suivez les instructions à l'écran pour continuer à autoriser le script. Le processus d'obtention de cette autorisation est appelé autorisation.

6. Répétez l'étape 4 pour exécuter à nouveau la macro (l'autorisation arrête la première exécution).

Félicitations ! Vous avez appris à appliquer des macros dans Sheets. Votre feuille de calcul devrait se présenter comme suit :

Les macros vous permettent de créer des feuilles de calcul de manière efficace. Dans la prochaine partie de cet atelier de programmation, vous apprendrez à rendre vos macros encore plus puissantes. Voici le secret : lorsque vous enregistrez une macro, vous écrivez en réalité du code Apps Script. En arrière-plan, Sheets crée le code correspondant aux actions de la macro. Dans la section suivante, vous apprendrez à modifier le code directement à l'aide de l'éditeur Apps Script dans le navigateur.

4. Macros dans l'éditeur de script

Lorsque vous créez une macro, Google Sheets enregistre vos actions en tant que fonction Apps Script. Lorsque vous activez la macro, Google Sheets appelle la fonction Apps Script pour appliquer ces actions dans le même ordre.

L'éditeur de script

Maintenant que vous avez créé une macro, vous pouvez examiner son code. Pour afficher le script de macro, cliquez sur Extensions > Apps Script pour ouvrir l'éditeur de code du navigateur pour Apps Script.

L'éditeur de script vous permet d'écrire du code dans Apps Script et d'exécuter ces scripts sur les serveurs Google.

Remarque : Les macros et les scripts que vous créez dans cet atelier de programmation sont associés au fichier Google Sheets sur lequel ils fonctionnent. Vous pouvez y accéder à tout moment depuis le menu Extensions > Apps Script de Sheets. Les scripts associés à une feuille de calcul Google Sheets de cette manière sont appelés scripts liés à un conteneur.

Analyse de macros.gs

Examinez le script actuel. Sheets a créé le fichier de script macros.gs lorsque vous avez enregistré la macro Header, en le remplissant avec une fonction Apps Script correspondante appelée Header. Lorsque vous activez la macro Header, Sheets exécute cette fonction.

Consultez l'image ci-dessous pour vous familiariser avec la structure de votre fonction de macro dans Apps Script. Si vous avez enregistré les étapes dans un ordre différent ou si vous avez cliqué sur la feuille de calcul pendant l'enregistrement, votre code peut être légèrement différent.

La première ligne est un commentaire d'annotation qui affecte l'autorisation :

/** @OnlyCurrentDoc */

La plupart des scripts demandent à l'utilisateur certaines autorisations avant de pouvoir s'exécuter. Ces autorisations contrôlent ce que l'utilisateur autorise le script à faire. Lorsque le commentaire @OnlyCurrentDoc est présent dans un projet de script, Apps Script ne demande l'autorisation d'accéder à la feuille de calcul actuelle et de la modifier. Sans ce commentaire, Apps Script demanderait l'autorisation d'accéder à tous les feuilles de calcul de l'utilisateur et de les modifier. Il est toujours recommandé d'inclure cette annotation lorsque vous ne travaillez qu'avec un seul fichier. L'enregistreur de macros ajoute automatiquement ce commentaire pour vous.

Pour comprendre comment Apps Script représente les instructions de votre macro, vous pouvez examiner la fonction :

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setBackground('#4c1130')
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
};

Ce code s'exécute lorsque vous activez la macro Header. Après function, le libellé Header() définit le nom de la fonction et ses paramètres. Notez que Header() ne nécessite aucun paramètre, car les fonctions de macro dans Apps Script n'ont pas besoin d'entrées. Les accolades entourent toujours le corps d'une fonction dans Apps Script.

Les ateliers de programmation suivants de cette playlist expliquent les classes et les concepts impliqués dans la création de la macro. Pour l'instant, vous pouvez consulter les descriptions de code suivantes pour obtenir une idée générale de ses composants et de leur rôle dans la création de votre macro. Prenons la première ligne :

var spreadsheet = SpreadsheetApp.getActive();

Ici, getActive() renvoie un objet représentant le fichier de feuille de calcul actif actuel dans Sheets et le définit sur la nouvelle variable spreadsheet.

var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();

Ces lignes correspondent à l'action de cliquer sur la première ligne pour la mettre en surbrillance. C'est ce qu'on appelle l'activation. La première ligne stocke la feuille de calcul actuelle dans la variable sheet, tandis que la deuxième ligne récupère l'intégralité de la première ligne à l'aide de la méthode getRange(), puis appelle activate() pour l'activer. La première ligne est spécifiée à l'aide des numéros de ligne et de colonne spécifiques. L'appel spreadsheet.getCurrentCell().getRow() renvoie le numéro de la ligne actuelle, tandis que sheet.getMaxColumns() renvoie le nombre maximal de colonnes de la feuille.

spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');

Ce bout de code devient plus complexe. Pour appeler efficacement des méthodes avec spreadsheet, le code empile trois méthodes sur getActiveRangeList() afin d'éviter que le code n'appelle cette méthode spreadsheet de manière redondante plus d'une fois. Plus vous coderez avec Apps Script, plus vous vous familiariserez avec cette convention d'appel de plusieurs méthodes sur une même classe (également appelée chaînage de méthodes). Pour l'instant, vous pouvez lire les explications brèves suivantes sur chaque méthode dans le bloc de code :

• getActiveRangeList() renvoie le RangeList actif actuel dans spreadsheet. Dans ce cas, il s'agit simplement de la première ligne activée par la ligne précédente.
• Les méthodes setBackground(color) et setFontColor(color) modifient les attributs de couleur des cellules de la plage active.
• setFontWeight(fontWeight) : ajuste la graisse de la police pour les cellules de la plage active.

Enfin, la dernière ligne fige la première ligne de la macro :

spreadsheet.getActiveSheet().setFrozenRows(1);

Il s'agit du script que vous avez généré lorsque vous avez enregistré votre macro. Ne vous inquiétez pas si vous ne connaissez pas certains termes ou méthodes mentionnés ci-dessus. La description est destinée à vous faire réfléchir à certaines des idées sur lesquelles Apps Script se concentre dans une fonction de macro typique, ainsi qu'aux sujets abordés dans les futurs ateliers de programmation.

La section suivante se concentre sur la manipulation du code de la fonction Header() pour montrer comment utiliser l'éditeur de script afin de personnaliser davantage les macros.

Personnaliser les macros avec Apps Script

L'éditeur Apps Script affiche la macro que vous avez créée précédemment dans Google Sheets. En ajustant le contenu du corps de la fonction, vous pouvez personnaliser davantage les instructions de votre macro pour qu'elle effectue des actions différentes ou supplémentaires. Les exercices suivants montrent différentes façons de manipuler les macros avec l'éditeur de script.

Modifier les cellules concernées

Supposons que vous souhaitiez modifier votre macro pour qu'elle n'affecte que les 10 premières colonnes de la première ligne au lieu de la ligne entière. Vous pouvez supprimer la macro et l'enregistrer à nouveau. Toutefois, vous pouvez effectuer ces modifications directement à l'aide de l'éditeur Apps Script. Voici une façon de procéder :

1. Dans l'éditeur de script, remplacez sheet.getMaxColumns() par 10. Cette modification change la plage de cellules que la macro affecte dans la feuille de calcul.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
    /* sheet.getMaxColumns() replaced with 10.*/
  spreadsheet.getActiveRangeList().setBackground('#4c1130')
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

2. Pour enregistrer votre script, cliquez sur Enregistrer .
3. Pour renommer votre projet, saisissez "Macros and Custom Functions" (Macros et fonctions personnalisées) comme nouveau nom de projet, puis cliquez sur Rename (Renommer).
4. Pour créer une feuille, cliquez sur Ajouter une feuille  dans Sheets.

5. Dans l'éditeur de script, dans la liste des fonctions, sélectionnez Header, puis cliquez sur Exécuter.

Dans votre nouvelle feuille de calcul, vous devriez obtenir le résultat suivant :

En modifiant la plage active ou cible, votre macro n'affecte désormais qu'une partie de la première ligne. De nombreuses méthodes Apps Script utilisent une plage ou la notation A1 comme paramètre pour spécifier les cellules sur lesquelles agir.

Ensuite, nous allons apprendre à personnaliser les couleurs de vos macros.

Modifier les couleurs de votre macro

Pour vous aider à concevoir le jeu de couleurs des macros ou d'autres éléments dans Sheets, Apps Script peut modifier la couleur de remplissage ou du texte d'une plage. Suivez les instructions ci-dessous pour apprendre à personnaliser les couleurs de votre macro.

Ces instructions expliquent comment modifier la couleur d'arrière-plan de votre macro :

1. Dans Sheets, revenez à la feuille contenant les données d'origine (Feuille 1).
2. Cliquez sur la première ligne pour la mettre en surbrillance.
3. Dans l'éditeur de script, remplacez la couleur d'arrière-plan #4c1130 par #afeeee. Ces valeurs représentent différentes couleurs à l'aide de la notation triplet hexadécimale.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
  spreadsheet.getActiveRangeList().setBackground('#afeeee')
    /* #4c1130 replaced with #afeeee.*/
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

4. Pour enregistrer votre script, cliquez sur Enregistrer .
5. Dans la liste des fonctions, sélectionnez Header, puis cliquez sur Exécuter.

Dans Sheets, la couleur de remplissage de l'arrière-plan des 10 premières colonnes de la première ligne est remplacée par une couleur turquoise personnalisée :

En remplaçant la notation hexadécimale de la couleur dans les paramètres de setBackground(color) de #4c1130 (magenta foncé 3) à #afeeee (turquoise clair, une option non accessible dans le menu des couleurs par défaut de Sheets), vous modifiez l'attribut de couleur de l'arrière-plan de votre macro.

Vous avez maintenant modifié la couleur d'arrière-plan définie par votre macro. Si vous souhaitez également modifier la couleur du texte, changez le deuxième code couleur.

1. Dans Sheets, cliquez sur la première ligne pour vous assurer qu'elle est toujours mise en surbrillance.
2. Dans l'éditeur de script, remplacez la couleur de la police #ffffff par #191970. La macro définit alors la couleur de la police sur bleu marine.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
  spreadsheet.getActiveRangeList().setBackground('#afeeee')
  .setFontColor('#191970')/* #ffffff replaced with #191970.*/
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

3. Pour enregistrer votre script, cliquez sur Enregistrer .
4. Dans la liste des fonctions, sélectionnez Header, puis cliquez sur Exécuter.

Revenez à Sheets pour voir que la couleur du texte de la ligne d'en-tête est désormais bleu marine.

Vous avez maintenant vu comment les macros sont en fait des actions Sheets enregistrées sous forme de code Apps Script. Dans la section suivante, vous découvrirez une autre façon dont Apps Script peut vous aider à travailler avec Google Sheets : les fonctions personnalisées.

5. Coder votre premier script : fonctions personnalisées

Comme la plupart des applications de tableur, Google Sheets propose plusieurs fonctions de formule intégrées, telles que =SUM(), qui permettent d'effectuer des calculs rapides sur les données d'une feuille de calcul. Les fonctions personnalisées sont simplement des fonctions que vous spécifiez à l'aide d'Apps Script. Une fois que vous avez défini une fonction personnalisée, vous pouvez l'utiliser n'importe où dans votre feuille de calcul, comme une fonction intégrée.

Cette section explique comment créer une fonction personnalisée dans Apps Script qui effectue une conversion monétaire.

Créer un fichier de script

En utilisant la même feuille de calcul et le même projet de script que dans la section "Macros", suivez ces instructions pour apprendre à créer un script (que vous pourrez utiliser pour créer votre première fonction personnalisée) :

1. Pour créer un fichier Apps Script, revenez à l'éditeur de script.
2. À côté de Fichiers, cliquez sur Ajouter un fichier  > Script.
3. Nommez le nouveau fichier de script customFunctions, puis appuyez sur Entrée. (Apps Script ajoute automatiquement une extension .gs au nom du fichier de script.)

Un nouvel onglet nommé customFunctions.gs s'affiche dans l'éditeur.

Maintenant que vous avez créé un script spécifiquement pour les fonctions personnalisées, vous pouvez le remplir avec du code.

Convertir des dollars américains en francs suisses

Supposons que vous souhaitiez modifier les données de "Top 10 des films les plus rentables de 2018" pour afficher non seulement les valeurs brutes mondiales en dollars américains, mais aussi en francs suisses. Les fonctions personnalisées vous permettent de le faire facilement. L'exercice suivant montre comment créer une fonction personnalisée pour convertir mathématiquement vos valeurs en dollars en valeurs en francs.

Avant de pouvoir écrire votre première fonction personnalisée, modifiez votre ensemble de données pour que la fonction puisse afficher un résultat approprié. Procédez comme suit :

1. Dans Sheets, effectuez un clic droit sur la colonne H.
2. Dans le menu qui s'affiche, cliquez sur Insérer 1 à droite.

3. Nommez la colonne "Worldwide_Gross (Swiss francs)" dans la cellule I1.

Vous disposez désormais d'une colonne pouvant stocker les résultats de votre fonction personnalisée de conversion. Vous pouvez ensuite utiliser l'éditeur de script pour créer votre première fonction personnalisée.

4. Dans customFunctions.gs, remplacez le code de myFunction() par le code suivant :

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The converted total of Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

Il s'agit du code qui convertira les dollars américains en francs suisses. Suivez les instructions ci-dessous pour découvrir comment exécuter une fonction personnalisée dans Sheets.

5. Pour enregistrer votre script, cliquez sur Enregistrer .
6. Dans Sheets, sélectionnez la cellule I2.
7. Dans la barre de fonctions, saisissez =USDTOCHF(H2).

Pour appliquer la formule aux autres cellules de la colonne :

8. Placez le curseur en bas à droite de la cellule I2, puis sélectionnez le petit carré bleu (le curseur doit se transformer en  lorsqu'il pointe sur le carré bleu).
9. Faites glisser le cadre bleu vers le bas pour mettre en surbrillance la plage I3:I11.

La colonne I liste désormais les conversions en francs suisses des valeurs en dollars américains de la colonne H.

Félicitations, vous venez de créer votre première fonction personnalisée. La section suivante explique le code qui constitue USDTOCHF().

Analyse de USDTOCHF()

Les commentaires initiaux détaillent l'objectif du code :

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */

Les blocs de commentaires comme celui-ci sont fréquemment utilisés en programmation pour expliquer le rôle des fonctions.

Dans ce commentaire, vous pouvez identifier deux parties : la description de la fonction (pour convertir les dollars en francs) et les annotations qui décrivent les paramètres et le type de retour de la fonction.

Avec les annotations, Apps Script utilise JSDoc pour vous aider à documenter votre code et à créer des suggestions de saisie semi-automatique. Vous trouverez ci-dessous des informations sur la façon dont chaque annotation utilisée dans USDTOCHF() vous aide à développer vos scripts Apps Script :

• @param : vous pouvez utiliser l'annotation @param pour décrire chaque paramètre transmis à la fonction.
• @return : vous pouvez utiliser l'annotation @return pour décrire ce que renvoie la fonction.
• @customfunction : vous devez toujours ajouter @customfunction dans le commentaire de documentation de toute fonction personnalisée. Cette annotation indique à Sheets de saisir automatiquement votre fonction personnalisée, tout comme Sheets saisit automatiquement les fonctions intégrées lorsque vous saisissez un nom de fonction dans une cellule, comme illustré ci-dessous :

Notez que le texte qui s'affiche dans le pop-up de saisie semi-automatique correspond exactement au texte de description que vous avez placé dans le bloc de commentaires. Pour faciliter l'utilisation de vos fonctions personnalisées, assurez-vous que les descriptions que vous créez sont bien rédigées et complètes.

Ensuite, concentrez-vous sur le code de la fonction USDTOCHF() :

function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

Comme indiqué précédemment, USDTOCHF() prend la variable numérique "dollars", la multiplie par un taux de change fixe et renvoie une valeur convertie en francs suisses dans la variable numérique swissFrancs. Le paramètre d'entrée est la valeur contenue dans la cellule spécifiée lors de l'ajout de la fonction personnalisée à une cellule. Dans cet exemple, les montants en dollars saisis proviennent de la colonne H. La valeur de sortie swissFrancs est placée dans la cellule de la fonction (colonne I dans cet exemple).

Comme vous le verrez dans la section suivante, les fonctions personnalisées peuvent fonctionner avec des valeurs numériques ou de chaîne.

Concaténer un préfixe de chaîne

Supposons que vous souhaitiez que le résultat numérique de la fonction USDTOCHF() inclue le préfixe CHF du franc suisse. Vous pouvez le faire avec Apps Script en utilisant l'opérateur de concaténation (+),, comme indiqué dans les instructions suivantes :

1. Dans l'éditeur de script, modifiez l'annotation @return pour renvoyer une chaîne au lieu d'un nombre.
2. Remplacez return swissFrancs par return 'CHF' + swissFrancs.

L'opérateur + ajoute la chaîne CHF au début de la valeur contenue dans swissFrancs. Le code devrait se présenter ainsi :

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {string} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99;
  return 'CHF' + swissFrancs;
}

3. Pour enregistrer votre script, cliquez sur Enregistrer .

La chaîne du franc suisse précède désormais les valeurs de la colonne I :

Votre fonction personnalisée convertit désormais les dollars américains en francs suisses, mais affiche également la devise avec un préfixe de chaîne.

Avancé : extraire des données externes

C'est un bon début pour une fonction personnalisée de base, mais cet exemple suppose que le taux de change entre le dollar et le franc suisse est constant. Supposons que vous souhaitiez utiliser le taux de change actuel. Ainsi, chaque fois que la feuille est rechargée, les valeurs sont recalculées pour représenter la conversion actuelle. Pour ce faire, vous devez trouver le taux de change actuel. Cette information n'est pas facilement disponible dans Google Sheets, mais vous pouvez heureusement utiliser Apps Script pour l'obtenir.

Vous pouvez utiliser un code comme celui ci-dessous pour obtenir le taux de change actuel entre le franc suisse et le dollar américain :

function USDTOCHF(dollars){
  // Gets a cache that is common to all users of the script.
  var cache = CacheService.getScriptCache();

  // Accesses the memory location (rates.CHF) of the script cache.
  var rate = cache.get('rates.CHF');

  // If a cache miss occurs, the program fetches the current
  // CHF rate from an API and stores the rate in the cache
  // for later convenience.
  if (!rate) {
    var response =
UrlFetchApp.fetch('https://api.exchangeratesapi.io/latest?base=USD');
    var result = JSON.parse(response.getContentText());
    rate = result.rates.CHF;
    cache.put('rates.CHF', rate);
  }
  // Converts dollars to CHF according to the latest rate.
  var swissFrancs = dollars * rate;
  // Returns the CHF value.
  return 'CHF' + swissFrancs;
}

Ce code récupère le taux de change actuel à partir d'un serveur d'informations financières à l'aide d'une API de taux de change tierce. Pour ce faire, utilisez les services Apps Script tels que UrlFetchApp et CacheService. Ces concepts avancés ne sont pas abordés dans cet atelier de programmation, mais vous pouvez commencer à constater la polyvalence d'Apps Script pour automatiser des tâches complexes dans Google Sheets.

Consignes concernant les fonctions personnalisées

Félicitations, vous avez terminé les exercices sur les fonctions personnalisées. Lorsque vous utilisez des fonctions personnalisées dans vos projets, il est important de comprendre qu'elles sont soumises à certaines restrictions. La liste suivante récapitule les limites détaillées dans le guide Fonctions personnalisées dans Google Sheets :

• Ne créez pas de fonctions personnalisées nécessitant une autorisation de l'utilisateur. Créez plutôt vos fonctions personnalisées pour effectuer des tâches plus simples, comme des calculs sur des exemples de données, l'édition de texte, etc. Consultez Utiliser les services Apps Script.
• Ne donnez pas à une fonction personnalisée le même nom qu'une autre fonction intégrée et ne terminez pas le nom par un trait de soulignement. Consultez les consignes de dénomination.
• Ne transmettez pas d'arguments variables aux fonctions personnalisées. Vous ne pouvez transmettre que des valeurs déterministes (fixes) aux fonctions personnalisées en tant qu'arguments. La transmission d'arguments variables, tels que le résultat de =RAND(), interrompra la fonction personnalisée. Consultez les consignes relatives aux arguments.
• Ne créez pas de fonctions qui prennent plus de 30 secondes. Si l'opération prend plus de temps, une erreur se produit. Veillez donc à ce que le code de la fonction soit simple et limité en portée. Il est préférable de garder les calculs effectués dans les fonctions personnalisées aussi simples que possible. Consultez les Consignes relatives aux valeurs renvoyées.

Vous pouvez désormais améliorer vos feuilles de calcul en utilisant l'éditeur de script pour travailler avec des macros et créer des fonctions personnalisées. Dans la section suivante, vous pourrez passer en revue ce que vous avez appris et ce que vous pouvez faire ensuite pour améliorer vos compétences en matière de script.

6. Conclusion

Vous avez terminé le premier atelier de programmation sur les principes de base d'Apps Script avec Google Sheets. Vous avez appris les concepts de base d'Apps Script en créant et en modifiant des macros et des fonctions personnalisées dans Sheets. Vous pourrez approfondir vos connaissances sur Apps Script dans le prochain atelier de programmation.

Avez-vous trouvé cet atelier de programmation utile ?

OuiNon

Points abordés

• Concepts de base d'Apps Script.
• Découvrez comment parcourir l'éditeur de script.
• Découvrez comment créer et mettre à jour des macros Sheets.
• Découvrez comment créer des fonctions personnalisées pour Google Sheets.

Termes clés

• Référence absolue : paramètre d'enregistrement de macro indiquant que les actions de macro enregistrées sont appliquées aux cellules exactes utilisées dans l'enregistrement.
• Active (état) : indique que la feuille de calcul, la feuille, la plage ou la cellule spécifiées sont celles que l'utilisateur de la feuille de calcul consulte ou met en surbrillance actuellement.
• Apps Script : plate-forme de développement d'applications rapide, basée sur JavaScript, qui permet d'étendre rapidement et facilement les applications Google Workspace et d'automatiser le travail.
• Autorisation : processus par lequel l'utilisateur accorde des autorisations à Apps Script pour accéder à ses données dans les services Google concernés ou effectuer d'autres actions en son nom.
• Script lié à un conteneur : tout script lié à un document Google Workspace (par exemple, une feuille de calcul ou un document Google) et créé à partir de celui-ci.
• Fonctions personnalisées : formules définies par l'utilisateur dans Apps Script, utilisées dans les feuilles de calcul pour des opérations simples.
• JSDoc : langage de balisage utilisé pour rédiger des notes explicatives sur les fichiers sources basés sur JavaScript.
• Macro : abréviation de "macroinstruction". Série d'actions Google Sheets enregistrées que vous pouvez répéter à l'aide d'un élément de menu ou d'un raccourci clavier.
• Chaînage de méthodes : idiome de programmation permettant d'appeler plusieurs méthodes sur un objet, en s'appuyant sur le fait que chaque appel de méthode renvoie l'objet.
• Plage : groupe d'une ou plusieurs cellules adjacentes dans une feuille.
• Référence relative : paramètre d'enregistrement de macro qui spécifie que les actions de macro enregistrées sont appliquées aux cellules par rapport à la sélection de cellule actuelle.
• Éditeur de script : éditeur de code intégré au navigateur pour Apps Script.
• Feuille : page unique d'une feuille de calcul (semblable aux classeurs Microsoft Excel). Terme parfois utilisé pour désigner un fichier Google Sheets dans Google Drive.
• SpreadsheetApp : cette classe sert de classe parente pour le service Spreadsheet et fournit un point de départ pour le code qui lit ou manipule les données Google Sheets.
• Feuille de calcul : fichier Google Sheets qui se trouve dans Google Drive.
• Service de feuilles de calcul : service Apps Script qui permet aux scripts de créer, d'accéder et de modifier des fichiers Google Sheets.

Pour en savoir plus sur Apps Script, consultez la documentation Apps Script.

Étape suivante

Le prochain atelier de programmation de cette playlist présente les classes et la terminologie de base du service Spreadsheet d'Apps Script. Ce service vous permet de contrôler précisément les valeurs et la présentation des données dans Google Sheets à l'aide d'Apps Script.

Retrouvez le prochain atelier de programmation sur la page Feuilles de calcul, Sheets et plages.