Principes de base d'Apps Script avec Google Sheets #4 : mise en forme des données

1. Introduction

Bienvenue dans la quatrième partie de la playlist d'ateliers de programmation sur les principes de base d'Apps Script avec Google Sheets.

En suivant cet atelier de programmation, vous apprendrez à mettre en forme les données de votre feuille de calcul dans Apps Script et à écrire des fonctions pour créer des feuilles de calcul organisées et remplies de données mises en forme extraites d'une API publique.

Points abordés

• Appliquer différentes opérations de mise en forme Google Sheets dans Apps Script.
• Découvrez comment transformer une liste d'objets JSON et leurs attributs en une feuille de données organisée avec Apps Script.

Avant de commencer

Il s'agit du quatrième atelier de programmation de la playlist "Principes de base d'Apps Script avec Google Sheets". Avant de commencer cet atelier de programmation, assurez-vous d'avoir terminé les ateliers de programmation précédents :

1. Macros et fonctions personnalisées
2. Feuilles de calcul, feuilles et plages
3. Utiliser les données

Prérequis

• Comprendre les concepts de base d'Apps Script abordés dans les ateliers de programmation précédents de cette playlist.
• Connaissances de base de l'éditeur Apps Script
• Connaissances de base de Google Sheets
• Possibilité de lire la notation A1 de Sheets
• Connaissances de base de JavaScript et de sa classe String

2. Configurer

Avant de continuer, vous avez besoin d'une feuille de calcul contenant des données. Comme précédemment, nous vous avons fourni une feuille de données que vous pouvez copier pour ces exercices. Suivez les instructions suivantes :

1. Cliquez sur ce lien pour copier la fiche de données, puis sur Créer une copie. La nouvelle feuille de calcul est placée dans votre dossier Google Drive et nommée "Copie de Mise en forme des données".
2. Cliquez sur le titre de la feuille de calcul et remplacez "Copie de Mise en forme des données" par "Mise en forme des données". Votre feuille doit se présenter comme suit, avec quelques informations de base sur les trois premiers films Star Wars :

3. Sélectionnez Extensions > Apps Script pour ouvrir l'éditeur de script.
4. Cliquez sur le titre du projet Apps Script et remplacez "Projet sans titre" par "Mise en forme des données". Cliquez sur Renommer pour enregistrer le nouveau titre.

Avec cette feuille de calcul et ce projet, vous êtes prêt à commencer l'atelier de programmation. Passez à la section suivante pour découvrir la mise en forme de base dans Apps Script.

3. Créer un menu personnalisé

Vous pouvez appliquer plusieurs méthodes de mise en forme de base dans Apps Script à vos feuilles de calcul. Les exercices suivants montrent quelques façons de mettre en forme les données. Pour vous aider à contrôler vos actions de mise en forme, nous allons créer un menu personnalisé avec les éléments dont vous aurez besoin. La procédure de création de menus personnalisés a été décrite dans l'atelier de programmation Utiliser les données, mais nous allons la résumer ici.

Implémentation

Créons un menu personnalisé.

1. Dans l'éditeur Apps Script, remplacez le code de votre projet de script par le code suivant :

/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the spreadsheet's user-interface object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
   .addItem('Format row header', 'formatRowHeader')
   .addItem('Format column header', 'formatColumnHeader')
   .addItem('Format dataset', 'formatDataset') 
  .addToUi();
}

2. Enregistrez votre projet de script.
3. Dans l'éditeur de script, sélectionnez onOpen dans la liste des fonctions, puis cliquez sur Exécuter. Cette commande exécute onOpen() pour recréer le menu de la feuille de calcul. Vous n'avez donc pas besoin de recharger la feuille de calcul.

Réviser le code

Examinons ce code pour comprendre comment il fonctionne. Dans onOpen(), la première ligne utilise la méthode getUi() pour acquérir un objet Ui représentant l'interface utilisateur de la feuille de calcul active à laquelle ce script est lié.

Les lignes suivantes créent un menu (Quick formats), ajoutent des éléments de menu (Format row header, Format column header et Format dataset) au menu, puis ajoutent le menu à l'interface de la feuille de calcul. Pour ce faire, utilisez respectivement les méthodes createMenu(caption), addItem(caption, functionName) et addToUi().

La méthode addItem(caption, functionName) crée une connexion entre le libellé de l'élément de menu et une fonction Apps Script qui s'exécute lorsque l'élément de menu est sélectionné. Par exemple, si vous sélectionnez l'élément de menu Format row header, Sheets tente d'exécuter la fonction formatRowHeader() (qui n'existe pas encore).

Résultats

Dans votre feuille de calcul, cliquez sur le menu Quick formats pour afficher les nouveaux éléments de menu :

Cliquer sur ces éléments provoque une erreur, car vous n'avez pas implémenté leurs fonctions correspondantes. Faisons-le maintenant.

4. Mettre en forme une ligne d'en-tête

Les ensembles de données dans les feuilles de calcul comportent souvent des lignes d'en-tête pour identifier les données de chaque colonne. Il est recommandé de mettre en forme les lignes d'en-tête pour les séparer visuellement du reste des données de la feuille de calcul.

Dans le premier atelier de programmation, vous avez créé une macro pour votre en-tête et ajusté son code. Ici, vous allez mettre en forme une ligne d'en-tête à partir de zéro à l'aide d'Apps Script. La ligne d'en-tête que vous allez créer mettra le texte d'en-tête en gras, colorera l'arrière-plan en bleu-vert foncé, colorera le texte en blanc et ajoutera des bordures pleines.

Implémentation

Pour implémenter l'opération de mise en forme, vous utiliserez les mêmes méthodes Spreadsheet service que précédemment, mais vous utiliserez également certaines des méthodes de mise en forme du service. Suivez les instructions suivantes :

1. Dans l'éditeur Apps Script, ajoutez la fonction suivante à la fin de votre projet de script :

/**
 * Formats top row of sheet using our header row style.
 */
function formatRowHeader() {
  // Get the current active sheet and the top row's range.
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
 
  // Apply each format to the top row: bold white text,
  // blue-green background, and a solid black border
  // around the cells.
  headerRange
    .setFontWeight('bold')
    .setFontColor('#ffffff')
    .setBackground('#007272')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

}

2. Enregistrez votre projet de script.

Réviser le code

Comme pour de nombreuses tâches de mise en forme, le code Apps Script à implémenter est simple. Les deux premières lignes utilisent des méthodes que vous avez déjà vues pour obtenir une référence à la feuille active actuelle (sheet) et à la première ligne de la feuille (headerRange)). La méthode Sheet.getRange(row, column, numRows, numColumns) spécifie la première ligne, en n'incluant que les colonnes contenant des données. La méthode Sheet.getLastColumn() renvoie l'index de la dernière colonne contenant des données dans la feuille. Dans notre exemple, il s'agit de la colonne E (url).

Remarque : Nous partons du principe que la ligne d'en-tête que nous souhaitons mettre en forme se trouve toujours sur la ligne 1, que nos données commencent en A1 et qu'elles s'étendent sur un certain nombre de colonnes contiguës. C'est acceptable pour cet exercice, mais en général, vous pouvez éviter ces hypothèses en permettant aux utilisateurs de spécifier les cellules à mettre en forme (au cas où la structure de leur feuille de calcul serait différente).

Le reste du code appelle simplement différentes méthodes Range pour appliquer les choix de mise en forme à toutes les cellules de headerRange. Pour que le code reste facile à lire, nous utilisons l'enchaînement de méthodes pour appeler chaque méthode de mise en forme l'une après l'autre :

• Range.setFontWeight(fontWeight) permet de définir l'épaisseur de la police sur "gras".
• Range.setFontColor(color) permet de définir la couleur de la police sur blanc.
• Range.setBackground(color) permet de définir la couleur d'arrière-plan sur un bleu-vert foncé.
• setBorder(top, left, bottom, right, vertical, horizontal, color, style) ajoute une bordure noire unie autour des cellules de la plage.

La dernière méthode comporte plusieurs paramètres. Examinons-les un par un. Les quatre premiers paramètres (tous définis sur true) indiquent à Apps Script que la bordure doit être ajoutée au-dessus, en dessous, et à gauche et à droite de la plage. Les cinquième et sixième paramètres (null et null) indiquent à Apps Script d'éviter de modifier les bordures dans la plage sélectionnée. Le septième paramètre (null) indique que la couleur de la bordure doit être noire par défaut. Enfin, le dernier paramètre spécifie le type de style de bordure à utiliser, parmi les options fournies par SpreadsheetApp.BorderStyle.

Résultats

Pour voir votre fonction de mise en forme en action, procédez comme suit :

1. Si ce n'est pas déjà fait, enregistrez votre projet de script dans l'éditeur Apps Script.
2. Cliquez sur l'élément de menu Formats rapides > Mettre en forme l'en-tête de ligne.

Remarque : Si vous utilisez un compte gmail.com, le message "Cette application n'est pas validée" peut s'afficher la première fois que vous utilisez 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 :

1. Dans la boîte de dialogue de l'application non validée, cliquez sur Avancé.
2. Cliquez sur Accéder à la manipulation des données et aux menus personnalisés (non sécurisé).
3. Cliquez sur Autoriser.

Tout au long de cet atelier de programmation, vous recevrez peut-être plusieurs invites d'autorisation. Pour en savoir plus sur ce processus, consultez Autorisation pour les services Google.

Les résultats doivent se présenter sous la forme suivante :

Vous venez d'automatiser une tâche de mise en forme. La section suivante applique la même technique pour créer un style de format différent pour les en-têtes de colonne.

5. Mettre en forme un en-tête de colonne

Si vous pouvez créer un en-tête de ligne personnalisé, vous pouvez également créer un en-tête de colonne. Les en-têtes de colonne améliorent la lisibilité de certains ensembles de données. Par exemple, la colonne titles de cette feuille de calcul peut être enrichie avec les choix de format suivants :

• Mettre le texte en gras
• Mettre le texte en italique
• Ajouter des bordures aux cellules
• Insérer des liens hypertexte à l'aide du contenu de la colonne url. Une fois ces liens hypertexte ajoutés, vous pouvez supprimer la colonne url pour nettoyer la feuille.

Vous allez ensuite implémenter une fonction formatColumnHeader() pour appliquer ces modifications à la première colonne de la feuille. Pour faciliter la lecture du code, vous allez également implémenter deux fonctions d'assistance.

Implémentation

Comme précédemment, vous devez ajouter une fonction pour automatiser la mise en forme des en-têtes de colonne. Suivez les instructions suivantes :

1. Dans l'éditeur Apps Script, ajoutez la fonction formatColumnHeader() suivante à la fin de votre projet de script :

/**
 * Formats the column header of the active sheet.
 */ 
function formatColumnHeader() {  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get total number of rows in data range, not including
  // the header row.
  var numRows = sheet.getDataRange().getLastRow() - 1;
  
  // Get the range of the column header.
  var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);
  
  // Apply text formatting and add borders.
  columnHeaderRange
    .setFontWeight('bold')
    .setFontStyle('italic')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
 
  // Call helper method to hyperlink the first column contents
  // to the url column contents.
  hyperlinkColumnHeaders_(columnHeaderRange, numRows); 
}

2. Ajoutez les fonctions d'assistance suivantes à la fin de votre projet de script, après la fonction formatColumnHeader() :

/**
 * Helper function that hyperlinks the column header with the
 * 'url' column contents. The function then removes the column.
 *
 * @param {object} headerRange The range of the column header
 *   to update.
 * @param {number} numRows The size of the column header.
 */
function hyperlinkColumnHeaders_(headerRange, numRows) {
  // Get header and url column indices.
  var headerColIndex = 1; 
  var urlColIndex = columnIndexOf_('url');  
  
  // Exit if the url column is missing.
  if(urlColIndex == -1)
    return; 
  
  // Get header and url cell values.
  var urlRange =
    headerRange.offset(0, urlColIndex - headerColIndex);
  var headerValues = headerRange.getValues();
  var urlValues = urlRange.getValues();
  
  // Updates header values to the hyperlinked header values.
  for(var row = 0; row < numRows; row++){
    headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
      + '","' + headerValues[row] + '")';
  }
  headerRange.setValues(headerValues);
  
  // Delete the url column to clean up the sheet.
  SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex);
}

/**
 * Helper function that goes through the headers of all columns
 * and returns the index of the column with the specified name
 * in row 1. If a column with that name does not exist,
 * this function returns -1. If multiple columns have the same
 * name in row 1, the index of the first one discovered is
 * returned.
 * 
 * @param {string} colName The name to find in the column
 *   headers. 
 * @return The index of that column in the active sheet,
 *   or -1 if the name isn't found.
 */ 
function columnIndexOf_(colName) {
  // Get the current column names.
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnHeaders =
    sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var columnNames = columnHeaders.getValues();
  
  // Loops through every column and returns the column index
  // if the row 1 value of that column matches colName.
  for(var col = 1; col <= columnNames[0].length; col++)
  {
    if(columnNames[0][col-1] === colName)
      return col; 
  }

  // Returns -1 if a column named colName does not exist. 
  return -1; 
}

3. Enregistrez votre projet de script.

Réviser le code

Examinons le code de chacune de ces trois fonctions séparément :

formatColumnHeader()

Comme vous vous y attendez probablement, les premières lignes de cette fonction définissent des variables qui font référence à la feuille et à la plage qui nous intéressent :

• La feuille active est stockée dans sheet.
• Le nombre de lignes dans l'en-tête de colonne est calculé et enregistré dans numRows. Ici, le code soustrait un pour que le nombre de lignes n'inclue pas l'en-tête de colonne : title.
• La plage couvrant l'en-tête de colonne est stockée dans columnHeaderRange.

Le code applique ensuite les bordures et la mise en gras à la plage d'en-têtes de colonnes, comme dans formatRowHeader(). Ici, Range.setFontStyle(fontStyle) est également utilisé pour mettre le texte en italique.

L'ajout d'hyperliens à la colonne d'en-tête étant plus complexe, formatColumnHeader() appelle hyperlinkColumnHeaders_(headerRange, numRows) pour s'en charger. Cela permet de garder le code propre et lisible.

hyperlinkColumnHeaders_(headerRange, numRows)

Cette fonction d'assistance identifie d'abord les index de colonne de l'en-tête (index 1 par défaut) et de la colonne url. Il appelle columnIndexOf_('url') pour obtenir l'index de la colonne d'URL. Si aucune colonne url n'est trouvée, la méthode se ferme sans modifier aucune donnée.

La fonction obtient une nouvelle plage (urlRange) qui couvre les URL correspondant aux lignes de la colonne d'en-tête. Pour ce faire, exécutez la méthode Range.offset(rowOffset, columnOffset), qui garantit que les deux plages auront la même taille. Les valeurs des colonnes headerColumn et url sont ensuite récupérées (headerValues et urlValues).

La fonction boucle ensuite sur chaque valeur de cellule d'en-tête de colonne et la remplace par une formule Sheets =HYPERLINK() construite avec l'en-tête et le contenu de la colonne url. Les valeurs d'en-tête modifiées sont ensuite insérées dans la feuille à l'aide de Range.setValues(values).

Enfin, pour que la feuille reste propre et pour éliminer les informations redondantes, Sheet.deleteColumn(columnPosition) est appelé pour supprimer la colonne url.

columnIndexOf_(colName)

Cette fonction d'assistance est une simple fonction utilitaire qui recherche un nom spécifique dans la première ligne de la feuille. Les trois premières lignes utilisent des méthodes que vous avez déjà vues pour obtenir une liste des noms d'en-tête de colonne à partir de la ligne 1 de la feuille de calcul. Ces noms sont stockés dans la variable columnNames.

La fonction examine ensuite chaque nom dans l'ordre. S'il en trouve une qui correspond au nom recherché, il s'arrête et renvoie l'index de la colonne. S'il atteint la fin de la liste des noms sans trouver le nom, il renvoie -1 pour indiquer que le nom n'a pas été trouvé.

Résultats

Pour voir votre fonction de mise en forme en action, procédez comme suit :

1. Si ce n'est pas déjà fait, enregistrez votre projet de script dans l'éditeur Apps Script.
2. Cliquez sur l'élément de menu Formats rapides > Mettre en forme l'en-tête de colonne.

Les résultats doivent se présenter sous la forme suivante :

Vous avez maintenant automatisé une autre tâche de mise en forme. Maintenant que les en-têtes de colonnes et de lignes sont mis en forme, la section suivante explique comment mettre en forme les données.

6. Mettre en forme votre ensemble de données

Maintenant que vous avez des en-têtes, créons une fonction qui met en forme le reste des données de votre feuille. Nous allons utiliser les options de mise en forme suivantes :

• Couleurs d'arrière-plan alternées pour les lignes (appelées bandes)
• Modifier les formats de date
• Appliquer des bordures
• Ajuster automatiquement toutes les colonnes et tous les lignes

Vous allez maintenant créer une fonction formatDataset() et une méthode d'assistance supplémentaire pour appliquer ces formats aux données de votre feuille.

Implémentation

Comme précédemment, ajoutez une fonction pour automatiser la mise en forme des données. Suivez les instructions suivantes :

1. Dans l'éditeur Apps Script, ajoutez la fonction formatDataset() suivante à la fin de votre projet de script :

/**
 * Formats the sheet data, excluding the header row and column.
 * Applies the border and banding, formats the 'release_date'
 * column, and autosizes the columns and rows.
 */
function formatDataset() {
  // Get the active sheet and data range.
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var fullDataRange = sheet.getDataRange();

  // Apply row banding to the data, excluding the header
  // row and column. Only apply the banding if the range
  // doesn't already have banding set.
  var noHeadersRange = fullDataRange.offset(
    1, 1,
    fullDataRange.getNumRows() - 1,
    fullDataRange.getNumColumns() - 1);

  if (! noHeadersRange.getBandings()[0]) {
    // The range doesn't already have banding, so it's
    // safe to apply it.
    noHeadersRange.applyRowBanding(
      SpreadsheetApp.BandingTheme.LIGHT_GREY,
      false, false);
  }

  // Call a helper function to apply date formatting
  // to the column labeled 'release_date'.
  formatDates_( columnIndexOf_('release_date') );
  
  // Set a border around all the data, and resize the
  // columns and rows to fit.
  fullDataRange.setBorder(
    true, true, true, true, null, null,
    null,
    SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  sheet.autoResizeColumns(1, fullDataRange.getNumColumns());
  sheet.autoResizeRows(1, fullDataRange.getNumRows());
}

2. Ajoutez la fonction d'assistance suivante à la fin de votre projet de script, après la fonction formatDataset() :

/** 
 * Helper method that applies a
 * "Month Day, Year (Day of Week)" date format to the
 * indicated column in the active sheet. 
 *
 * @param {number} colIndex The index of the column
 *   to format.
 */ 
function formatDates_(colIndex) {
  // Exit if the given column index is -1, indicating
  // the column to format isn't present in the sheet.
  if (colIndex < 0)
    return; 

  // Set the date format for the date column, excluding
  // the header row.
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
    .setNumberFormat("mmmm dd, yyyy (dddd)");
}

3. Enregistrez votre projet de script.

Réviser le code

Examinons le code de chacune de ces deux fonctions séparément :

formatDataset()

Cette fonction suit un modèle semblable à celui des fonctions de format précédentes que vous avez déjà implémentées. Tout d'abord, elle récupère les variables pour conserver les références à la feuille active (sheet) et à la plage de données (fullDataRange).

Ensuite, elle utilise la méthode Range.offset(rowOffset, columnOffset, numRows, numColumns) pour créer une plage (noHeadersRange) qui couvre toutes les données de la feuille, à l'exclusion des en-têtes de colonne et de ligne. Le code vérifie ensuite si cette nouvelle plage comporte déjà des bandes (à l'aide de Range.getBandings()). Cette étape est nécessaire, car Apps Script génère une erreur si vous essayez d'appliquer de nouvelles bandes à une plage qui en comporte déjà. Si aucune bande n'existe, la fonction ajoute une bande gris clair à l'aide de Range.applyRowBanding(bandingTheme, showHeader, showFooter). Sinon, la fonction passe à l'étape suivante.

L'étape suivante appelle la fonction d'assistance formatDates_(colIndex) pour mettre en forme les dates de la colonne intitulée release_date (décrite ci-dessous). La colonne est spécifiée à l'aide de la fonction d'assistance columnIndexOf_(colName) que vous avez implémentée précédemment.

Enfin, la mise en forme est terminée en ajoutant une autre bordure (comme précédemment). Chaque colonne et chaque ligne sont automatiquement redimensionnées pour s'adapter aux données qu'elles contiennent à l'aide des méthodes Sheet.autoResizeColumns(columnPosition) et Sheet.autoResizeColumns(columnPosition).

formatDates_(colIndex)

Cette fonction d'assistance applique un format de date spécifique à une colonne à l'aide de l'index de colonne fourni. Plus précisément, il met en forme les valeurs de date au format "Mois Jour, Année (Jour de la semaine)".

Remarque : Vous pouvez utiliser de nombreux formats de date et de nombre différents dans vos scripts et vos feuilles de calcul. Pour en savoir plus, consultez le guide Formats de date et de nombre.

Tout d'abord, la fonction vérifie que l'index de colonne fourni est valide (c'est-à-dire supérieur ou égal à 0). Sinon, il renvoie sans rien faire. Cette vérification permet d'éviter les erreurs qui pourraient se produire si, par exemple, la feuille ne comportait pas de colonne "release_date".

Une fois l'index de colonne validé, la fonction obtient la plage couvrant cette colonne (à l'exclusion de sa ligne d'en-tête) et utilise Range.setNumberFormat(numberFormat) pour appliquer la mise en forme.

Résultats

Pour voir votre fonction de mise en forme en action, procédez comme suit :

1. Si ce n'est pas déjà fait, enregistrez votre projet de script dans l'éditeur Apps Script.
2. Cliquez sur l'élément de menu Formats rapides > Mettre en forme l'ensemble de données.

Les résultats doivent se présenter sous la forme suivante :

Vous avez automatisé une autre tâche de mise en forme. Maintenant que vous disposez de ces commandes de mise en forme, ajoutons des données auxquelles les appliquer.

7. Récupérer et mettre en forme les données de l'API

Jusqu'à présent, dans cet atelier de programmation, vous avez vu comment utiliser Apps Script pour mettre en forme votre feuille de calcul. Vous allez ensuite écrire du code qui extrait des données d'une API publique, les insère dans votre feuille de calcul et les met en forme pour qu'elles soient lisibles.

Dans le dernier atelier de programmation, vous avez appris à extraire des données d'une API. Vous utiliserez les mêmes techniques ici. Dans cet exercice, nous allons utiliser l'API Star Wars (SWAPI) publique pour remplir votre feuille de calcul. Plus précisément, vous utiliserez l'API pour obtenir des informations sur les personnages principaux qui apparaissent dans les trois premiers films Star Wars.

Votre code appellera l'API pour obtenir une grande quantité de données JSON, analysera la réponse, placera les données dans une nouvelle feuille, puis mettra en forme la feuille.

Implémentation

Dans cette section, vous allez ajouter des éléments de menu supplémentaires. Chaque élément de menu appelle un script wrapper qui transmet des variables spécifiques à l'élément à la fonction principale (createResourceSheet_()). Vous implémenterez cette fonction et trois fonctions d'assistance supplémentaires. Comme auparavant, les fonctions d'assistance permettent d'isoler les parties logiquement compartimentées de la tâche et de rendre le code lisible.

Effectuez les actions suivantes :

1. Dans l'éditeur Apps Script, mettez à jour votre fonction onOpen() dans votre projet de script pour qu'elle corresponde à ce qui suit :

/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the Ui object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
    .addItem('Format row header', 'formatRowHeader')
    .addItem('Format column header', 'formatColumnHeader')
    .addItem('Format dataset', 'formatDataset')
    .addSeparator()
    .addSubMenu(ui.createMenu('Create character sheet')
                .addItem('Episode IV', 'createPeopleSheetIV')
                .addItem('Episode V', 'createPeopleSheetV')
                .addItem('Episode VI', 'createPeopleSheetVI')
                )
    .addToUi();
}

2. Enregistrez votre projet de script.
3. Dans l'éditeur de script, sélectionnez onOpen dans la liste des fonctions, puis cliquez sur Exécuter. Cela exécute onOpen() pour reconstruire le menu de la feuille de calcul avec les nouvelles options que vous avez ajoutées.
4. Pour créer un fichier Apps Script, à côté de Fichiers, cliquez sur Ajouter un fichier  > Script.
5. Nommez le nouveau script "API", puis appuyez sur Entrée. (Apps Script ajoute automatiquement une extension .gs au nom du fichier de script.)
6. Remplacez le code du nouveau fichier API.gs par le code suivant :

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode IV.
 */
function createPeopleSheetIV() {
  createResourceSheet_('characters', 1, "IV");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode V.
 */
function createPeopleSheetV() {
  createResourceSheet_('characters', 2, "V");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode VI.
 */
function createPeopleSheetVI() {
  createResourceSheet_('characters', 3, "VI");
}

/** 
 * Creates a formatted sheet filled with user-specified
 * information from the Star Wars API. If the sheet with
 * this data exists, the sheet is overwritten with the API
 * information.
 *
 * @param {string} resourceType The type of resource.
 * @param {number} idNumber The identification number of the film.
 * @param {number} episodeNumber The Star Wars film episode number.
 *   This is only used in the sheet name.
 */
function createResourceSheet_(
    resourceType, idNumber, episodeNumber) { 
  
  // Fetch the basic film data from the API. 
  var filmData = fetchApiResourceObject_(
      "https://swapi.dev/api/films/" + idNumber);

  // Extract the API URLs for each resource so the code can
  // call the API to get more data about each individually.
  var resourceUrls = filmData[resourceType];
  
  // Fetch each resource from the API individually and push
  // them into a new object list.
  var resourceDataList = []; 
  for(var i = 0; i < resourceUrls.length; i++){
    resourceDataList.push(
      fetchApiResourceObject_(resourceUrls[i])
    ); 
  } 
  
  // Get the keys used to reference each part of data within
  // the resources. The keys are assumed to be identical for
  // each object since they're all the same resource type.
  var resourceObjectKeys = Object.keys(resourceDataList[0]);
  
  // Create the sheet with the appropriate name. It
  // automatically becomes the active sheet when it's created.
  var resourceSheet = createNewSheet_(
      "Episode " + episodeNumber + " " + resourceType);
  
  // Add the API data to the new sheet, using each object
  // key as a column header. 
  fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);
  
  // Format the new sheet using the same styles the
  // 'Quick Formats' menu items apply. These methods all
  // act on the active sheet, which is the one just created.
  formatRowHeader();
  formatColumnHeader();   
  formatDataset();

}

6. Ajoutez les fonctions d'assistance suivantes à la fin du fichier de projet de script API.gs :

/** 
 * Helper function that retrieves a JSON object containing a
 * response from a public API.
 *
 * @param {string} url The URL of the API object being fetched.
 * @return {object} resourceObject The JSON object fetched
 *   from the URL request to the API.
 */
function fetchApiResourceObject_(url) {
  // Make request to API and get response.
  var response =
    UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  
  // Parse and return the response as a JSON object.
  var json = response.getContentText();
  var responseObject = JSON.parse(json); 
  return responseObject; 
}

/** 
 * Helper function that creates a sheet or returns an existing
 * sheet with the same name.
 *
 * @param {string} name The name of the sheet.
 * @return {object} The created or existing sheet
 *   of the same name. This sheet becomes active.
 */ 
function createNewSheet_(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Returns an existing sheet if it has the specified
  // name. Activates the sheet before returning.
  var sheet = ss.getSheetByName(name);
  if (sheet) {
    return sheet.activate();
  }
  
  // Otherwise it makes a sheet, set its name, and returns it.
  // New sheets created this way automatically become the active
  // sheet.
  sheet = ss.insertSheet(name); 
  return sheet; 
}

/** 
 * Helper function that adds API data to the sheet.
 * Each object key is used as a column header in the new sheet.
 *
 * @param {object} resourceSheet The sheet object being modified.
 * @param {object} objectKeys The list of keys for the resources.
 * @param {object} resourceDataList The list of API
 *   resource objects containing data to add to the sheet.
 */
function fillSheetWithData_(
    resourceSheet, objectKeys, resourceDataList) {
  // Set the dimensions of the data range being added to the sheet.
  var numRows = resourceDataList.length;
  var numColumns = objectKeys.length;
  
  // Get the resource range and associated values array. Add an
  // extra row for the column headers.
  var resourceRange =
    resourceSheet.getRange(1, 1, numRows + 1, numColumns);
  var resourceValues = resourceRange.getValues(); 
  
  // Loop over each key value and resource, extracting data to
  // place in the 2D resourceValues array.
  for (var column = 0; column < numColumns; column++) {

    // Set the column header.
    var columnHeader = objectKeys[column];
    resourceValues[0][column] = columnHeader;
    
    // Read and set each row in this column.
    for (var row = 1; row < numRows + 1; row++) {
      var resource = resourceDataList[row - 1];
      var value = resource[columnHeader];
      resourceValues[row][column] = value;
    }
  }
  
  // Remove any existing data in the sheet and set the new values.
  resourceSheet.clear()
  resourceRange.setValues(resourceValues);
}

7. Enregistrez votre projet de script.

Réviser le code

Vous venez d'ajouter beaucoup de code. Examinons chaque fonction individuellement pour comprendre comment elles fonctionnent :

onOpen()

Vous avez ajouté quelques éléments de menu à votre menu Quick formats. Vous avez défini une ligne de séparation, puis utilisé la méthode Menu.addSubMenu(menu) pour créer une structure de menu imbriquée avec trois nouveaux éléments. Les nouveaux éléments sont ajoutés avec la méthode Menu.addItem(caption, functionName).

Fonctions de wrapper

Les éléments de menu ajoutés font tous quelque chose de similaire : ils essaient de créer une feuille avec des données extraites de SWAPI. La seule différence est qu'ils se concentrent chacun sur un film différent.

Il serait pratique d'écrire une seule fonction pour créer la feuille et de faire en sorte que la fonction accepte un paramètre pour déterminer quel film utiliser. Toutefois, la méthode Menu.addItem(caption, functionName) ne vous permet pas de lui transmettre des paramètres lorsqu'elle est appelée par le menu. Alors, comment éviter d'écrire le même code trois fois ?

La réponse est fonctions wrapper. Il s'agit de fonctions légères que vous pouvez appeler et qui appellent immédiatement une autre fonction avec des paramètres spécifiques définis.

Ici, le code utilise trois fonctions d'encapsulation : createPeopleSheetIV(), createPeopleSheetV() et createPeopleSheetVI(). Les éléments de menu sont associés à ces fonctions. Lorsqu'un élément de menu est sélectionné, la fonction wrapper s'exécute et appelle immédiatement la fonction principale de création de feuille createResourceSheet_(resourceType, idNumber, episodeNumber), en transmettant les paramètres appropriés à l'élément de menu. Dans ce cas, cela signifie demander à la fonction de création de feuille de créer une feuille remplie de données sur les personnages principaux de l'un des films Star Wars.

Remarque : Si vous le souhaitez, vous pouvez créer d'autres fonctions wrapper pour remplir différents types de feuilles avec des données. Il vous suffit de savoir quel type de ressource demander et quel ID de film utiliser. Les types de ressources "planets" et "starships" sont des exemples. L'API accepte les nombres de 1 à 7 comme ID de film.

createResourceSheet_(resourceType, idNumber, episodeNumber)

Il s'agit de la fonction principale de création de feuilles pour cet exercice. À l'aide de fonctions d'assistance, il récupère les données de l'API, les analyse, crée une feuille, y écrit les données de l'API, puis la met en forme à l'aide des fonctions que vous avez créées dans les sections précédentes. Voici les détails :

Tout d'abord, la fonction utilise fetchApiResourceObject_(url) pour envoyer une requête à l'API afin de récupérer des informations de base sur le film. La réponse de l'API inclut une collection d'URL que le code peut utiliser pour obtenir plus d'informations sur des personnes spécifiques (appelées ressources) des films. Le code collecte toutes les données dans le tableau resourceUrls.

Ensuite, le code utilise fetchApiResourceObject_(url) à plusieurs reprises pour appeler l'API pour chaque URL de ressource dans resourceUrls. Les résultats sont stockés dans le tableau resourceDataList. Chaque élément de ce tableau est un objet qui décrit un personnage différent du film.

Les objets de données de ressources comportent plusieurs clés communes qui correspondent à des informations sur ce personnage. Par exemple, la clé "name" correspond au nom du personnage dans le film. Nous partons du principe que les clés de chaque objet de données de ressource sont toutes identiques, car elles sont censées utiliser des structures d'objet communes. La liste des clés étant nécessaire ultérieurement, le code la stocke dans resourceObjectKeys à l'aide de la méthode JavaScript Object.keys().

Ensuite, la fonction de compilateur appelle la fonction d'assistance createNewSheet_(name) pour créer la feuille dans laquelle les nouvelles données seront placées. L'appel de cette fonction d'assistance active également la nouvelle feuille.

Une fois la feuille créée, la fonction d'assistance fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) est appelée pour ajouter toutes les données de l'API à la feuille.

Enfin, toutes les fonctions de mise en forme que vous avez créées précédemment sont appelées pour appliquer les mêmes règles de mise en forme aux nouvelles données. Comme la nouvelle feuille est active, le code peut réutiliser ces fonctions sans modification.

fetchApiResourceObject_(url)

Cette fonction d'assistance est semblable à la fonction d'assistance fetchBookData_(ISBN) utilisée dans l'atelier de programmation précédent Utiliser des données. Il prend l'URL donnée et utilise la méthode UrlFetchApp.fetch(url, params) pour obtenir une réponse. La réponse est ensuite analysée dans un objet JSON à l'aide des méthodes HTTPResponse.getContextText() et JSON.parse(json) de JavaScript. L'objet JSON obtenu est ensuite renvoyé.

Remarque : Si vous travaillez souvent avec des API, cette fonction peut être utile lorsque vous avez besoin de récupérer des informations sur les API. Il est généralisé pour fonctionner avec presque toutes les URL d'API. Pensez donc à l'enregistrer pour une utilisation ultérieure.

createNewSheet_(name)

Cette fonction d'assistance est assez simple. Il vérifie d'abord si une feuille portant le nom indiqué existe dans la feuille de calcul. Si c'est le cas, la fonction active la feuille et la renvoie.

Si la feuille n'existe pas, la fonction la crée avec Spreadsheet.insertSheet(sheetName), l'active et renvoie la nouvelle feuille.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

Cette fonction d'assistance est chargée de remplir la nouvelle feuille avec les données de l'API. Il prend en paramètres la nouvelle feuille, la liste des clés d'objet et la liste des objets de ressources d'API. Chaque clé d'objet représente une colonne dans la nouvelle feuille, et chaque objet de ressource représente une ligne.

Tout d'abord, la fonction calcule le nombre de lignes et de colonnes nécessaires pour présenter les nouvelles données de l'API. Il s'agit de la taille de la liste des ressources et des clés, respectivement. La fonction définit ensuite une plage de sortie (resourceRange) dans laquelle les données seront placées, en ajoutant une ligne supplémentaire pour contenir les en-têtes de colonne. La variable resourceValues contient un tableau de valeurs 2D extrait de resourceRange.

La fonction effectue ensuite une boucle sur chaque clé d'objet de la liste objectKeys. La clé est définie comme en-tête de colonne, puis une deuxième boucle parcourt chaque objet de ressource. Pour chaque paire (ligne, colonne), les informations d'API correspondantes sont copiées dans l'élément resourceValues[row][column].

Remarque : N'oubliez pas que les lignes et les colonnes dans Sheets sont indexées à partir de 1, tandis que les tableaux JavaScript sont indexés à partir de 0. En raison de cette différence, nous devons souvent ajouter ou soustraire 1 aux index lorsque nous programmons dans Apps Script.

Une fois resourceValues rempli, la feuille de destination est effacée à l'aide de Sheet.clear() au cas où elle contiendrait des données provenant de clics précédents sur des éléments de menu. Enfin, les nouvelles valeurs sont écrites dans la feuille.

Résultats

Pour voir les résultats de votre travail, procédez comme suit :

1. Si ce n'est pas déjà fait, enregistrez votre projet de script dans l'éditeur Apps Script.
2. Cliquez sur l'élément de menu Formats rapides > Créer une fiche de personnage > Épisode IV.

Les résultats doivent se présenter sous la forme suivante :

Vous avez maintenant écrit du code pour importer des données dans Sheets et les mettre en forme automatiquement.

8. Conclusion

Bravo ! Vous avez terminé cet atelier de programmation. Vous avez découvert certaines des options de mise en forme de Sheets que vous pouvez inclure dans vos projets Apps Script. Vous avez également créé une application impressionnante qui importe et met en forme un grand ensemble de données d'API.

Avez-vous trouvé cet atelier de programmation utile ?

Oui Non

Ce que vous avez appris

• Appliquer différentes opérations de mise en forme Sheets avec Apps Script.
• Créer des sous-menus avec la fonction onOpen()
• Découvrez comment mettre en forme une liste d'objets JSON récupérés dans une nouvelle feuille de données avec Apps Script.

Termes clés :

• 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.
• Cellule active : cellule sélectionnée dans la feuille active, sur laquelle le curseur est positionné.
• Plage active : groupe d'une ou plusieurs cellules actuellement mises en surbrillance dans la feuille active.
• API : une interface de programmation d'application est un service que les programmes ou les scripts peuvent appeler pour demander des informations ou effectuer certaines actions.
• 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.
• Fonction d'assistance : fonction, généralement privée, appelée par une autre fonction pour effectuer une petite sous-tâche. Les fonctions d'assistance sont utilisées lorsque la sous-tâche doit être effectuée plusieurs fois à différents endroits.
• JSON : format de fichier permettant de définir des objets de données et leurs caractéristiques à partir de texte.
• onOpen() : s'il est défini dans un script, il s'agit d'un simple déclencheur qui se déclenche chaque fois qu'un fichier s'ouvre ou se recharge.
• Fonctions privées : fonction qui ne peut pas être appelée dans une bibliothèque ni par des clients via la communication client-serveur. Les fonctions privées Apps Script ont des noms qui se terminent par _.
• Plage : groupe d'une ou plusieurs cellules adjacentes dans une feuille. La classe Range** vous permet de lire et de mettre à jour des plages dans une feuille.
• Éditeur de script : éditeur de code intégré au navigateur pour Apps Script.
• Déclencheurs simples : sous-type de déclencheurs dans Apps Script avec des noms de fonction de déclencheur réservés et certaines restrictions.
• Feuille**: page unique d'une feuille de calcul. Terme parfois utilisé pour désigner un fichier Google Sheets dans Google Drive. La classe Sheet** vous permet d'accéder aux feuilles et de les modifier.
• 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 résidant dans Google Drive. La classe Spreadsheet** vous permet d'accéder aux feuilles de calcul et de les modifier.
• 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.
• Déclencheurs : événement associé à une fonction Apps Script. Lorsque l'événement (par exemple, l'ouverture d'une feuille de calcul) se produit, le déclencheur "se déclenche" et exécute automatiquement la fonction Apps Script associée.
• Service URL Fetch : service Apps Script qui permet aux scripts de se connecter à des points de terminaison d'URL pour envoyer des requêtes et recevoir des réponses.
• Fonctions wrapper : fonctions utilitaires simples utilisées pour appeler des fonctions plus complexes tout en leur transmettant un ensemble spécifique de paramètres.

Étape suivante

Le prochain atelier de programmation de cette playlist vous montre comment utiliser Apps Script pour visualiser des données dans un graphique et exporter des graphiques vers des présentations Google Slides.

Retrouvez le prochain atelier de programmation sur Représenter et présenter des données dans Slides.