Principes de base d'Apps Script avec Google Sheets n° 1: Macros et AMP, Fonctions personnalisées

1. Bienvenue dans Apps Script

Qu'est-ce que Apps Script ?

Apps Script est une plate-forme de développement 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 vous simplifier la vie en rationalisant les tâches fastidieuses ou complexes dans Google Workspace.

Les fonctionnalités d'Apps Script comprennent les éléments suivants:

• Les services intégrés Apps Script vous permettent de lire, de mettre à jour et de manipuler les données de votre application Google Workspace à l'aide de scripts.
• Vous pouvez créer des scripts à l'aide de l'éditeur de code d'Apps Script, sans avoir à installer ni à 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 des principes de base d'Apps Script avec Google Sheets vous explique les bases de Apps Script et comment l'utiliser pour améliorer votre expérience Google Sheets. Cet atelier de programmation porte sur les principes de base d'Apps Script.

Le service Spreadsheet

Gagnez du temps et simplifiez-vous la vie en utilisant Apps Script pour étendre Google Sheets. Apps Script propose le service Spreadsheet qui permet aux scripts d'interagir avec les fichiers Google Sheets et les données qu'ils contiennent. Ce service vous permet d'automatiser les tâches de feuille de calcul courantes suivantes:

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

Points abordés

Cette playlist couvre tous les sujets à connaître pour commencer à utiliser Apps Script avec Google Sheets:

1. Macros et fonctions personnalisées
2. Feuilles de calcul, feuilles de calcul et plages
3. Utilisation des données
4. Format des données
5. Présenter des données sous forme de graphique dans Slides

Les ateliers de programmation de cette playlist sont conçus pour être lus dans l'ordre. Commencez par celui-ci, puis terminez-le par ordre afin d'optimiser l'apprentissage.

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

2. Introduction

Bienvenue dans le premier atelier de programmation de cette playlist. Dans cet atelier de programmation, vous apprendrez les bases de l'utilisation d'Apps Script avec Google Sheets. Plus précisément, cet atelier de programmation aborde 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 l'enregistrement effectué, vous pouvez activer une macro pour répéter ces actions ultérieurement à l'aide d'un élément de menu ou d'une touche de raccourci. Vous pouvez créer et mettre à jour 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 dans les fonctions intégrées de Sheets (telles que SUM ou AVERAGE), vous pouvez utiliser Apps Script pour écrire vos propres fonctions personnalisées afin d'effectuer des opérations simples et niche (telles que les conversions ou la concaténation de chaîne). Une fois créées, vous pouvez appeler ces fonctions dans Sheets comme vous le feriez dans une fonction intégrée. Vous pouvez également utiliser des fonctions personnalisées dans des formules de cellules que vous écrivez, en les associant à d'autres fonctions selon vos besoins.

Pour découvrir les concepts et les exigences de cet atelier de programmation, lisez la suite.

Points abordés

• Comment créer un script pour Google Sheets
• Utiliser l'éditeur Apps Script
• Comment créer et mettre à jour des macros.
• Créer votre première fonction personnalisée Sheets

Ce dont vous avez besoin

• Connaissances de base en JavaScript
• Des connaissances de base sur 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 offert de JavaScript d'introduction. Même si Google n'a pas créé ce cours, il devrait rassembler toutes les informations dont vous avez besoin.

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

3. Créer une macro dans Sheets

Généralement, lorsque vous travaillez dans des feuilles de calcul, vous pouvez entrer dans une boucle d'actions répétitives, en copiant les valeurs de cellule, de mise en forme, en créant des formules, etc. Cela peut s'avérer fastidieux et entraîner des erreurs. Pour automatiser des actions répétées, Google Sheets fournit des macros. Grâce aux macros, vous pouvez enregistrer une série d'actions dans une feuille. Une macro enregistrée vous permet de répéter plusieurs fois les mêmes actions dans une feuille de calcul en appuyant simplement sur une touche d'accès rapide.

Dans cette section, vous allez apprendre à créer une macro dans Sheets. Dans la section suivante, vous allez voir comment les macros sont créées avec Apps Script.

Avant de commencer

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

Une copie de l'exemple de feuille de calcul est à votre disposition dans le dossier Google Drive. Elle est nommée "Copie des 10 films les plus rentables (2018)".

Créer une macro

Maintenant que vous disposez d'une feuille de calcul sur laquelle vous pouvez travailler, 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 sur la ligne. Il s'agit de votre ligne d'en-tête.
2. Dans le menu, sélectionnez Extensions &gt ; Macros &gt ; Record Macros (Macros d'enregistrement).

Une fois l'enregistrement lancé, Google Sheets mémorise toutes les actions que vous effectuez dans la feuille de calcul: mise en surbrillance des cellules, ajout de données, changement de feuille de calcul, mise en forme, etc. Ces actions deviennent le "script" et se répètent une fois que vous avez enregistré et activé la macro par la suite.

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 Sheets pour faire référence à des cellules spécifiques. Les références absolues sont définies sur les emplacements précis 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 vers le bas par rapport à la sélection actuelle).

Cet atelier de programmation vous demande d'utiliser des références relatives pour vos macros.

4. Sélectionnez la ligne 1.

5. Changez la couleur de la ligne du haut de la section Fill Color, de "blanc" à magenta sombre 3.

6. Changez la couleur du premier couleur noir en blanc.

7. Pour mettre du texte en gras, appuyez sur Ctrl+B (ou sur Cmd+B sur macOS).
8. Pour figer la première ligne, sélectionnez Afficher &gt ; Figer &gt ; 1 ligne.

9. Cliquez sur Save (Enregistrer) dans la boîte de dialogue de la macro. Une nouvelle boîte de dialogue vous invite à nommer la macro. Saisissez le nom souhaité, puis cliquez sur Enregistrer.

L'interface utilisateur de Sheets vous permet de créer des macros spécialisées pour la mise en forme des en-têtes.

Activez votre macro

Vous pouvez appliquer votre nouvelle macro dans Sheets en procédant comme suit:

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

2. Dans cette nouvelle feuille, ajoutez du texte à A1:C2. N'hésitez pas à suivre les exemples de saisie ci-dessous:

3. Sélectionnez la première ligne.

4. Pour appliquer la macro à la zone sélectionnée, cliquez sur Extensions, puis sur Macros &gt ; Header (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 lorsque vous utilisez votre script pour la première fois. Nous l'utilisons pour avertir les utilisateurs susceptibles d'utiliser du code provenant d'auteurs inconnus ou non approuvés. 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 relancer la macro (en autorisant 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 efficacement. Dans la suite de cet atelier de programmation, vous apprendrez à rendre vos macros encore plus performantes. Voici le secret: lorsque vous enregistrez une macro, ce que vous faites est d'écrire un code Apps Script. En arrière-plan, Sheets crée le code correspondant aux actions de la macro. Dans la section suivante, vous allez apprendre à modifier le code directement en utilisant l'éditeur de navigateur d'Apps Script.

4. Macros dans l'éditeur de scripts

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.

Éditeur de scripts

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

Il vous permet d'écrire du code dans Apps Script et de l'exécuter sur des serveurs Google.

Remarque:Les macros et les scripts que vous créez dans cet atelier de programmation sont joints au fichier Google Sheets sur lequel ils fonctionnent, et vous pouvez y accéder à tout moment depuis le menu Extensions > Apps Script. Les scripts joints à une feuille de calcul Google Sheets de ce type sont appelés liés aux conteneurs.

Analyse de macro.gs

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

Regardez 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 cliqué sur la feuille de calcul pendant l'enregistrement, votre code peut être légèrement différent de celui-ci.

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

/** @OnlyCurrentDoc */

La plupart des scripts demandent des autorisations à l'utilisateur avant de pouvoir l'exécuter. Ces autorisations contrôlent les actions que l'utilisateur autorise le script. Lorsque le commentaire @OnlyCurrentDoc est présent dans un projet de script, Apps Script ne demande qu'une autorisation pour accéder à la feuille de calcul actuelle et la mettre à jour. Sans ce commentaire, Apps Script demanderait l'autorisation d'accéder à toutes les feuilles de calcul de l'utilisateur et de les mettre à jour. 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 comprendre comment Apps Script représente les instructions de votre macro, consultez 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. Reconnaître que Header() ne nécessite aucun paramètre, car les fonctions de macro dans Apps Script n'ont pas besoin d'entrée. Les accolades contiennent toujours le corps d'une fonction dans Apps Script.

Plus tard dans les playlists de cette playlist, vous découvrirez les cours et les concepts liés à la création de la macro. Pour l'instant, vous pouvez parcourir les descriptions de code suivantes pour avoir 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 la feuille de calcul active actuelle dans Sheets et la 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 qui consiste à cliquer sur la première ligne pour la mettre en surbrillance. C'est ce que l'on appelle l'activation. La première ligne stocke la feuille actuelle dans la variable sheet, tandis que la deuxième reçoit 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. L'appel spreadsheet.getCurrentCell().getRow() renvoie le nombre de lignes actuelles, tandis que sheet.getMaxColumns() renvoie le nombre maximal de colonnes dans la feuille.

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

Ce code devient plus complexe. Pour appeler efficacement des méthodes avec spreadsheet, le code superpose trois méthodes sur getActiveRangeList() afin d'éviter que le code n'appelle plusieurs fois cette méthode spreadsheet. À mesure que vous codez davantage à l'aide d'Apps Script, vous pouvez vous familiariser avec cette convention qui consiste à appeler plusieurs méthodes sur une même classe (également appelé chaînage de méthodes). Pour l'instant, vous pouvez lire ce qui suit pour obtenir une brève explication sur chaque méthode du bloc de code:

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

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

spreadsheet.getActiveSheet().setFrozenRows(1);

C'est le script que vous avez généré lorsque vous avez enregistré votre macro. Ne vous souciez pas des termes ou méthodes inconnus mentionnés ci-dessus. Cette description vise à vous aider à réfléchir à des idées sur lesquelles Apps Script se concentre sur une fonction de macro type, et à aborder les sujets qui seront abordés dans les prochains ateliers de programmation.

La section suivante traite de la manipulation du code de la fonction Header() pour montrer comment utiliser l'éditeur de scripts pour personnaliser davantage les macros.

Personnaliser des macros avec Apps Script

L'éditeur Apps Script affiche la macro que vous avez créée dans Google Sheets. En ajustant le contenu du corps de la fonction, vous pouvez personnaliser davantage les instructions de votre macro pour prendre des mesures différentes ou supplémentaires. Les exercices suivants présentent différentes manières de manipuler des macros avec l'éditeur de scripts.

Modifier les cellules concernées

Supposons que vous souhaitiez modifier votre macro afin 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 apporter 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 modifie la plage de cellules affectée par la macro 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 et fonctions personnalisées" comme nouveau nom du projet, puis cliquez sur Renommer.
4. Pour créer une feuille, cliquez sur Ajouter une feuille dans Sheets.

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

Le résultat suivant doit s'afficher dans votre nouvelle feuille:

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 de valeurs ou la notation A1 comme paramètre pour spécifier les cellules concernées.

Passons maintenant à la personnalisation des couleurs des 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 du remplissage ou du texte d'une plage. Suivez les instructions ci-dessous pour apprendre à personnaliser les couleurs de votre macro.

Ces instructions concernent la modification de 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 d'une notation 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 Run (Exécuter).

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

En changeant la notation hexadécimale dans les paramètres de setBackground(color) pour passer de #4c1130 (magenta sombre 3) à #afeeee (turque pâle, option non accessible dans Sheets, menu par défaut), vous modifiez l'attribut de couleur de la couleur d'arrière-plan de votre macro.

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

1. Dans Sheets, cliquez sur la première ligne pour vous assurer qu'elle est toujours en surbrillance.
2. Dans l'éditeur de script, remplacez la couleur de police #ffffff par #191970. La couleur de la police est donc définie 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 Run (Exécuter).

Revenez à Sheets pour afficher la couleur du texte de la ligne d'en-tête dans le bleu marine.

Vous venez de voir comment les actions Sheets sont enregistrées sous la forme de code Apps Script. Dans la section suivante, vous pouvez découvrir d'autres façons d'utiliser Apps Script pour travailler avec Google Sheets: les fonctions personnalisées.

5. Coder votre premier script: fonctions personnalisées

Comme la plupart des applications de feuille de calcul, Google Sheets propose plusieurs fonctions de formules intégrées, comme =SUM(), qui permettent d'effectuer rapidement des calculs sur des données de 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 vous explique comment créer une fonction personnalisée dans Apps Script qui effectue une conversion monétaire.

Créer un fichier de script

Suivez les instructions ci-dessous pour créer un script et créer votre script à l'aide de la même macro que celle utilisée pour créer votre première fonction personnalisée :

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

Un nouvel onglet appelé customFunctions.gs apparaît dans l'éditeur.

Maintenant que vous avez créé un script spécifique aux fonctions personnalisées, vous pouvez y ajouter du code.

Convertir les dollars américains en francs suisses

Supposons que vous souhaitiez modifier les données du top 10 des 10 meilleurs films de 2018 en vue d'afficher non seulement les valeurs brutes mondiales en dollars américains, mais aussi en francs suisses. Grâce aux fonctions personnalisées, vous pouvez le faire facilement. L'exercice suivant montre comment créer une fonction personnalisée pour convertir mathématiquement des valeurs en euros en valeurs francs.

Avant de pouvoir écrire votre première fonction personnalisée, modifiez votre ensemble de données de sorte qu'elle affiche un résultat correct. 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. Attribuez un libellé à la colonne "Worldwide_Gross (Swiss francs)" dans la cellule I1.

Vous disposez à présent d'une colonne dans laquelle stocker les résultats de votre fonction personnalisée de conversion. Vous pouvez ensuite créer votre première fonction personnalisée à l'aide de l'éditeur de scripts.

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 convertit 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 fonction, saisissez =USDTOCHF(H2).

Pour appliquer la formule au reste des cellules de la colonne:

8. Déplacez le curseur dans l'angle inférieur droit de la cellule I2, puis sélectionnez le petit cadre bleu (le curseur devrait se transformer en en cas de pointage vers le cadre bleu).
9. Faites glisser le cadre bleu vers le bas pour mettre en surbrillance la plage I3:I11.

La colonne I répertorie désormais les conversions en francs suisses des valeurs en dollars américains dans la colonne H.

Félicitations, vous avez créé votre première fonction personnalisée. La section suivante explique le code composant 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 de ce type sont fréquemment utilisés dans la programmation pour expliquer les fonctions.

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

Avec ces annotations, Apps Script utilise JSDoc pour vous permettre de documenter et de créer des suggestions de saisie semi-automatique pour votre code. Vous pouvez découvrir ci-dessous comment 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 à tout commentaire personnalisé du document. Cette annotation indique à Sheets de saisir automatiquement votre fonction personnalisée exactement comme Sheets le fait pour 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 à la description que vous avez placée dans le bloc de commentaires. Pour faciliter l'utilisation de vos fonctions personnalisées, assurez-vous qu'elles sont bien rédigées et complètes.

Examinons maintenant le code de la fonction USDTOCHF():

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

Comme indiqué précédemment, USDTOCHF() utilise la variable numérique dollar, 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 correspond à 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 d'entrée proviennent de la colonne H. La valeur de sortie swissFrancs est placée dans la cellule de la fonction (colonne I dans cet exemple).

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

Concaténez un préfixe de chaîne

Supposons que vous souhaitiez inclure la sortie numérique de la fonction USDTOCHF() pour inclure le préfixe suisse franc CHF. 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, mettez à jour l'annotation @return pour qu'elle renvoie une chaîne au lieu de 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 en franc suisse remplace désormais les valeurs de la colonne I:

Votre fonction personnalisée convertit désormais le dollar américain en francs suisses, mais génère également la devise avec un préfixe de chaîne.

Avancé: récupérer des données externes

Il s'agit d'un bon début pour une fonction personnalisée de base, mais dans cet exemple, nous partons du principe que le taux de change en francs suisses est constant. Supposons que vous souhaitiez plutôt utiliser le taux de change actuel. Ainsi, chaque fois que la feuille est actualisée, les valeurs sont recalculées pour représenter la conversion actuelle. Pour ce faire, vous devez connaître le taux de change actuel. Cette information n'est pas disponible dans Google Sheets, mais vous pouvez l'utiliser pour utiliser Apps Script.

Vous pouvez utiliser le code ci-dessous pour obtenir le taux de conversion actuel des francs suisses en dollars américains:

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 extrait le taux de change actuel d'un serveur d'informations financières à l'aide d'une API de taux de change tierce. Cette opération est effectuée à l'aide des services Apps Script tels que UrlFetchApp et CacheService. Ces concepts avancés n'entrent pas dans le cadre de cet atelier de programmation, mais vous pouvez commencer à découvrir la polyvalence d'Apps Script pour automatiser des tâches complexes dans Google Sheets.

Consignes relatives aux fonctions personnalisées

Félicitations, vous avez terminé les exercices pour les fonctions personnalisées. Lorsque vous utilisez des fonctions personnalisées dans vos projets, il est important de comprendre qu'elles présentent certaines restrictions. La liste suivante répertorie les limites détaillées du guide Fonctions personnalisées de Google Sheets:

• Ne créez pas de fonctions personnalisées qui nécessitent une autorisation utilisateur Créez plutôt vos fonctions personnalisées pour réaliser des tâches plus simples, comme des exemples de calcul de données, la modification de texte, etc. Consultez Utiliser les services Apps Script.
• Ne nommez pas une fonction personnalisée de la même manière qu'une autre fonction intégrée ou terminez 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 des valeurs déterministes (fixes) à des fonctions personnalisées qu'en tant qu'arguments. La transmission des arguments de variable, tels que le résultat de =RAND(), entrave la fonction personnalisée. Consultez les consignes pour les arguments.
• Ne créez pas de fonctions qui durent plus de 30 secondes. Si l'erreur se produit plus longtemps, le code de la fonction doit être simple et limité. Il est préférable de simplifier au maximum les calculs dans les fonctions personnalisées. Consultez les consignes concernant les valeurs renvoyées.

Vous pouvez désormais améliorer vos feuilles de calcul en utilisant l'éditeur de scripts 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 pour améliorer vos compétences en script.

6. Conclusion

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

Cet atelier de programmation vous a-t-il été utile ?

Oui Non

Points abordés

• Concepts de base d'Apps Script.
• Utiliser l'éditeur de scripts
• Créer et mettre à jour des macros Sheets.
• Créer des fonctions personnalisées pour Google Sheets

Termes clés

• Référence absolue: un paramètre d'enregistrement des macros spécifiant les actions des macros enregistrées est appliqué aux cellules exactes utilisées dans l'enregistrement.
• Actif (état): indique la feuille de calcul, la feuille, la plage ou la cellule spécifiées par l'utilisateur de la feuille de calcul actuellement affichée ou mise en surbrillance.
• Apps Script: plate-forme de développement d'applications rapide, basée sur JavaScript, qui permet d'étendre et d'automatiser facilement des applications Google Workspace.
• Autorisation : processus permettant à l'utilisateur d'autoriser Apps Script à accéder aux données utilisateur dans les services Google concernés ou à effectuer des actions pour le compte de l'utilisateur.
• Script lié à un conteneur: tout script lié à un document Google Workspace, tel qu'une feuille de calcul Google Sheets ou un document Google Docs, et créé à partir de ce document.
• Fonctions personnalisées : formules définies par l'utilisateur dans Apps Script, utilisées dans des feuilles de calcul pour des opérations simples.
• JSDoc: langage de balisage utilisé pour rédiger des explications sur les fichiers sources JavaScript.
• Macro : brève pour l'instruction de la macro. 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 sur le raccourci.
• Chaîne de méthode : idiome de programmation pour appeler plusieurs appels de méthode sur un objet, en fonction du fait que chaque appel de méthode renvoie l'objet.
• Plage : groupe d'une ou de plusieurs cellules adjacentes dans une feuille.
• Référence relative : un paramètre d'enregistrement des macros spécifiant les actions des macros enregistrées est appliqué aux cellules par rapport à la sélection de cellules actuelle.
• Éditeur de scripts : éditeur de code intégré au navigateur pour Apps Script.
• Feuille : page unique d'une feuille de calcul (semblable aux feuilles de calcul Microsoft Excel). Sert parfois à faire référence à un fichier Google Sheets dans Google Drive.
• SpreadsheetApp: cette classe est la classe parente du service Spreadsheet. Elle fournit un point de départ pour la lecture et la manipulation des données Google Sheets.
• Feuille de calcul: fichier Google Sheets stocké dans Google Drive.
• Service Spreadsheet : service Apps Script qui permet aux scripts de créer des fichiers Google Sheets, d'y accéder et de les modifier.

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

Étapes suivantes

L'atelier de programmation suivant de cette playlist présente les principales classes et la terminologie du service Tableur d'Apps Script. Ce service vous permet de contrôler avec précision les valeurs et la présentation des données dans Google Sheets à l'aide d'Apps Script.

Trouvez l'atelier de programmation suivant dans Sheets, Sheets et Ranges (Feuilles de calcul, Sheets et Plages).