Principes de base d'Apps Script avec Google Sheets n° 4: Format des données

1. Introduction

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

Cet atelier de programmation vous explique comment mettre en forme les données de votre feuille de calcul dans Apps Script et écrire des fonctions permettant de créer des feuilles de calcul organisées avec des données formatées extraites d'une API publique.

Points abordés

• Appliquer différentes opérations de mise en forme de 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 sur la playlist The Fundamentals of Apps Script with Google Sheets. Avant de commencer ces ateliers de programmation, veillez à suivre ceux précédents:

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

Ce dont vous avez besoin

• Une compréhension des thèmes de base d'Apps Script explorés dans les précédents ateliers de programmation de cette playlist.
• Connaissances de base de l'éditeur Apps Script
• Des connaissances de base sur Google Sheets
• Possibilité de lire la notation A1 de Sheets
• Bonne connaissance de JavaScript et de sa classe String

2. Configurer

Avant de continuer, vous avez besoin d'une feuille de calcul contenant quelques données. Comme auparavant, nous vous proposons une feuille de données que vous pouvez copier pour ces exercices. Procédez comme suit:

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

3. Sélectionnez Extensions> Apps Script pour ouvrir l'éditeur de scripts.
4. Cliquez sur le titre du projet Apps Script et remplacez-le par "Sans titre" et par "Format de données". Cliquez sur Renommer pour enregistrer le changement de titre.

Avec cette feuille de calcul et ce projet, vous pouvez commencer l'atelier de programmation. Passez à la section suivante pour commencer à vous familiariser avec 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 à Apps Script pour vos feuilles de calcul. Les exercices suivants présentent plusieurs façons de mettre en forme les données. Pour vous aider à contrôler vos actions de mise en forme, vous devez 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 Utilisation des données. Toutefois, nous allons le récapituler ici.

Implémentation

Créons un menu personnalisé.

1. Dans l'éditeur Apps Script, remplacez le code de votre projet de script par 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 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 le projet de script.
3. Dans l'éditeur de script, sélectionnez onOpen dans la liste des fonctions, puis cliquez sur Run (Exécuter). onOpen() permet de recréer le menu de la feuille de calcul. Vous n'avez donc pas besoin de l'actualiser.

Réviser le code

Passons en revue ce code pour en comprendre le fonctionnement. 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), y ajoutent des éléments (Format row header, Format column header et Format dataset), puis ajoutent le menu à l'interface de la feuille de calcul. Pour ce faire, utilisez les méthodes createMenu(caption), addItem(caption, functionName) et addToUi(), respectivement.

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

Les résultats

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

Si vous cliquez sur ces éléments, vous rencontrerez une erreur, car vous n'avez pas implémenté les fonctions correspondantes. Nous allons donc continuer.

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

Les ensembles de données contiennent souvent des lignes d'en-tête qui permettent d'identifier les données de chaque colonne. Nous vous conseillons de mettre en forme les lignes d'en-tête pour les séparer visuellement des autres 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. Dans cet exemple, 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 affichera le texte de l'en-tête en gras, colorera l'arrière-plan bleu-vert foncé, colorera le texte en blanc et ajoutera des bordures nettes.

Implémentation

Pour implémenter la mise en forme, vous devez utiliser les mêmes méthodes que pour le service Feuille de calcul que vous avez utilisées précédemment, mais vous allez également utiliser certaines méthodes de mise en forme du service. Procédez comme suit:

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 le projet de script.

Réviser le code

Comme de nombreuses tâches de mise en forme, le code Apps Script est très simple à mettre en œuvre. Les deux premières lignes utilisent les méthodes que vous avez vues précédemment 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, y compris uniquement les colonnes contenant des données. La méthode Sheet.getLastColumn() affiche 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 : Supposons que la ligne d'en-tête que nous souhaitons mettre en forme se trouve toujours dans la ligne 1, que nos données commencent dans la plage A1 et qu'elle s'étende sur un certain nombre de colonnes contiguës. Cela convient pour cet exercice, mais en règle générale, vous pouvez éviter ces hypothèses en permettant aux utilisateurs de spécifier les cellules à mettre en forme (dans le cas où la structure de leur feuille de calcul diffère).

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

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

La dernière méthode comportant plusieurs paramètres, examinons le fonctionnement de chacun d'entre eux. Les quatre premiers paramètres (tous définis sur true) indiquent à Apps Script 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) permettent d'accéder à Apps Script pour éviter de modifier des limites dans la plage sélectionnée. Le septième paramètre (null) indique que la couleur de la bordure doit être par défaut noire. Enfin, le dernier paramètre spécifie le type de style de bordure à utiliser, parmi les options fournies par SpreadsheetApp.BorderStyle.

Les résultats

Pour observer votre mise en forme, procédez comme suit:

1. Si vous ne l'avez pas encore fait, enregistrez votre projet de script dans l'éditeur Apps Script.
2. Cliquez sur l'élément de menu Formats rapides > En-tête de ligne de format.

Remarque:Si vous utilisez un compte gmail.com, une boîte de dialogue "n'est pas validée" peut s'afficher 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:

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

Au cours de cet atelier de programmation, vous pouvez recevoir plusieurs invites d'autorisation. Pour en savoir plus sur cette procédure, consultez l'article Autorisation concernant 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 générer un en-tête de ligne personnalisé, vous pouvez également en créer un dans une colonne. Les en-têtes de colonne augmentent la lisibilité de certains ensembles de données. Par exemple, la colonne titles de cette feuille de calcul peut être améliorée avec les options de format suivantes:

• Mettre le texte en gras
• Mettre du texte en italique
• Ajouter une bordure à la cellule
• Insérer des liens hypertextes à l'aide du contenu de la colonne url Une fois les liens hypertextes ajoutés, vous pouvez supprimer la colonne url pour faciliter le nettoyage de 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 mettre en œuvre 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. Procédez comme suit:

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 le projet de script.

Réviser le code

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

formatColumnHeader()

Comme vous vous en doutez sans doute, 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 contenues dans l'en-tête de colonne est calculé et enregistré dans numRows. Ici, le code en soustrait un afin 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 des en-têtes de colonne, comme dans formatRowHeader(). Ici, Range.setFontStyle(fontStyle) est également utilisé pour mettre du texte en italique.

L'ajout de liens hypertextes à la colonne d'en-tête est plus complexe. formatColumnHeader() appelle hyperlinkColumnHeaders_(headerRange, numRows) pour s'occuper de cette tâche. Ainsi, le code est bien organisé et lisible.

hyperlinkColumnHeaders_(headerRange, numRows)

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

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

La fonction passe ensuite d'une boucle à l'autre sur la valeur de la 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 garantir la propreté de la feuille et éliminer les informations redondantes, Sheet.deleteColumn(columnPosition) est appelé pour supprimer la colonne url.

columnIndexOf_(colName)

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

La fonction examine ensuite chaque nom dans l'ordre. Si elle correspond au nom recherché, elle s'arrête et renvoie l'index de la colonne. Si le nom est atteint à la fin de la liste sans indiquer son nom, la valeur renvoyée est -1 pour signaler que le nom est introuvable.

Les résultats

Pour observer votre mise en forme, procédez comme suit:

1. Si vous ne l'avez pas encore fait, enregistrez votre projet de script dans l'éditeur Apps Script.
2. Cliquez sur l'élément de menu Formats rapides > En-tête de colonne du format.

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

Vous venez d'automatiser une autre tâche de mise en forme. La section suivante montre comment mettre en forme les données à l'aide des en-têtes de colonne et de ligne.

6. Mettre en forme votre ensemble de données

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

• Couleurs d'arrière-plan des lignes en alternance (également appelées bandes)
• Modifier les formats de date
• Application de bordures
• Automatiser toutes les colonnes et lignes

À présent, vous allez créer une fonction formatDataset() et une méthode d'assistance supplémentaire pour appliquer ces formats à vos données de feuille.

Implémentation

Comme précédemment, ajoutez une fonction permettant d'automatiser la mise en forme des données. Procédez comme suit:

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 le projet de script.

Réviser le code

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

formatDataset()

Le fonctionnement de cette fonction est semblable à celui des fonctions de format précédentes que vous avez déjà implémentées. Tout d'abord, il obtient des variables pour conserver les références à la feuille active (feuille) et à la plage de données (fullDataRange).

Ensuite, la méthode Range.offset(rowOffset, columnOffset, numRows, numColumns) permet de créer une plage (noHeadersRange) qui couvre toutes les données de la feuille, à l'exception des en-têtes de colonne et de ligne. Le code vérifie ensuite si cette nouvelle plage a des bandes existantes (à l'aide de Range.getBandings()). Cette opération est nécessaire, car Apps Script génère une erreur si vous essayez d'appliquer un nouveau bandes là où c'est le cas. Si l'utilisation de bandes n'existe pas, la fonction ajoute une bande gris clair à l'aide de Range.applyRowBanding(bandingTheme, showHeader, showFooter). Sinon, elle continue.

L'étape suivante appelle la fonction d'assistance formatDates_(colIndex) pour mettre en forme les dates de la colonne "release_date' (décrit 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 se termine par l'ajout d'une autre bordure (comme précédemment), et redimensionne automatiquement chaque colonne et ligne afin de l'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 formate les valeurs de date sous la forme "jour/mois", "année" ou "jour de la semaine".

Remarque: Vous pouvez utiliser différents formats de date et de nombre dans vos scripts et feuilles de calcul. Pour plus d'informations, consultez le guide des formats de date et de nombre.

Tout d'abord, la fonction vérifie que l'index de colonne fourni est valide (c'est-à-dire 0 ou supérieur). Sinon, il se réactive sans rien faire. Cette vérification permet d'éviter les erreurs susceptibles de survenir si, par exemple, la feuille ne comporte 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.

Les résultats

Pour observer votre mise en forme, procédez comme suit:

1. Si vous ne l'avez pas encore fait, enregistrez votre projet de script dans l'éditeur Apps Script.
2. Cliquez sur l'élément de menu Formats rapides > Ensemble de données de format.

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

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

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

Dans cet atelier de programmation, vous avez appris à utiliser Apps Script comme une autre méthode de mise en forme de votre feuille de calcul. Ensuite, vous allez rédiger 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 les rendre lisibles.

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

Votre code appelle l'API pour obtenir une grande quantité de données JSON, analyser la réponse, placer les données dans une nouvelle feuille, puis mettre 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 à un élément à la fonction principale (createResourceSheet_()). Vous allez mettre en œuvre cette fonction, ainsi que trois autres fonctions d'assistance. Comme précédemment, les fonctions d'assistance permettent d'isoler des parties logiquement compartimentées de la tâche et de rendre le code lisible.

Procédez comme suit:

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

/**
 * 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 le projet de script.
3. Dans l'éditeur de script, sélectionnez onOpen dans la liste des fonctions, puis cliquez sur Run (Exécuter). onOpen() permet de recréer le menu de la feuille de calcul avec les nouvelles options que vous avez ajoutées.
4. Pour créer un fichier Apps Script, cliquez sur FichiersAjouter > Script.
5. Nommez le nouveau script &API; puis appuyez sur Entrée. (Apps Script ajoute automatiquement une extension .gs au nom de fichier du 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 du 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 le projet de script.

Réviser le code

Vous venez d'ajouter beaucoup de code. Examinons chaque fonction une par une pour en comprendre le fonctionnement:

onOpen()

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

Fonctions de wraps

Tous les éléments du menu ont un fonctionnement semblable: ils tentent de créer une feuille avec des données extraites de SWAPI. La seule différence est que chacun se concentre sur un film différent.

Il serait pratique de créer une fonction unique pour créer la feuille et demander un paramètre pour déterminer le film à utiliser. Cependant, la méthode Menu.addItem(caption, functionName) ne vous permet pas d'y transmettre des paramètres lorsque le menu l'appelle. Mais comment éviter d'écrire le même code trois fois ?

La réponse est les fonctions de wrapper. Il s'agit de fonctions légères qui peuvent appeler immédiatement une autre fonction avec des paramètres spécifiques.

Ici, le code utilise trois fonctions de wrapper : createPeopleSheetIV(), createPeopleSheetV() et createPeopleSheetVI(). Les éléments de menu sont associés à ces fonctions. Lorsqu'un utilisateur clique sur un élément de menu, la fonction du wrapper s'exécute et appelle immédiatement la fonction principale du générateur de feuilles createResourceSheet_(resourceType, idNumber, episodeNumber), en transmettant les paramètres appropriés pour l'élément de menu. Dans ce cas de figure, vous devez demander à la fonction Créateur de feuilles de créer une feuille remplie de données de personnages majeurs dans un film Star Wars.

Remarque: Si vous le souhaitez, vous pouvez créer d'autres fonctions de wrapper pour insérer différents types de feuilles dans différentes données. Vous devez simplement connaître le type de ressource à demander et l'ID du film à utiliser. Exemples de types de ressources : planets'starships' et l'API accepte les chiffres de 1 à 7 en tant qu'ID de films.

createResourceSheet_(resourceType, idNumber, episodeNumber)

Il s'agit de la fonction principale du générateur de feuilles pour cet exercice. À l'aide de certaines fonctions d'assistance, elle obtient les données d'API, les analyse, crée une feuille de calcul, écrit les données d'API dans la feuille, puis les met en forme à l'aide des fonctions que vous avez créées dans les sections précédentes. Examinons les détails plus en détail:

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

Ensuite, le code utilise la fonction fetchApiResourceObject_(url) de manière répétée 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 courantes qui correspondent à des informations sur ce caractère. Par exemple, la clé"name'" correspond au nom du personnage dans le film. Nous considérons que les clés de chaque objet de données de ressource sont toutes identiques, car elles sont destinées à utiliser des structures d'objets communes. Cette liste de clés sera nécessaire ultérieurement. Le code stocke donc cette liste 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 où seront placées les nouvelles données. En appelant cette fonction d'assistance, vous activez é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. La nouvelle feuille étant active, le code peut réutiliser ces fonctions sans y apporter de 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 les données. Elle utilise 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 JavaScript JSON.parse(json). L'objet JSON obtenu est ensuite renvoyé.

Remarque: Si vous travaillez beaucoup avec les API, cette fonction peut s'avérer utile lorsque vous devez récupérer des informations sur les API. Elle est généralisée pour fonctionner avec la quasi-totalité des URL d'API. Nous vous conseillons donc de l'enregistrer pour une utilisation ultérieure.

createNewSheet_(name)

Cette fonction d'assistance est assez simple. Il vérifie d'abord si une feuille du nom donné existe dans la feuille de calcul. Dans ce 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 comme paramètres la nouvelle feuille, la liste des clés d'objet et la liste des objets de ressources API en tant que paramètres. Chaque clé d'objet représente une colonne de 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 respectivement de la taille de la liste des ressources et des clés. La fonction définit ensuite une plage de sortie (resourceRange) où 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 passe ensuite en 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 passe par chaque objet de ressource. Pour chaque paire (ligne, colonne) les informations de l'API correspondantes sont copiées dans l'élément resourceValues[row][column].

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

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

Les résultats

Vous pouvez consulter les résultats de votre travail en procédant comme suit:

1. Si vous ne l'avez pas encore fait, enregistrez votre projet de script dans l'éditeur Apps Script.
2. Cliquez sur l'élément de menu Formats rapides &gt ; Créer une feuille de caractères &gt ; Épisode IV.

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

Vous venez d'écrire du code pour importer des données dans Sheets et les mettre en forme automatiquement.

8. Conclusion

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

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

Oui Non

Voici 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()
• Formater une liste d'objets JSON récupérée dans une nouvelle feuille de données avec Apps Script

Termes clés:

• 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.
• Cellule active : cellule unique en surbrillance dans la feuille active avec le curseur.
• Plage active: groupe d'une ou plusieurs cellules actuellement mises en évidence 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 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.
• Fonction d'assistance : fonction (généralement privée) appelée par une autre personne pour réaliser une petite tâche. Les fonctions d'assistance sont utilisées lorsque la tâche secondaire doit être exécutée à plusieurs reprises à 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 déclencheur simple qui se déclenche chaque fois qu'un fichier s'ouvre ou s'actualise.
• Fonctions privées: fonction qui ne peut pas être appelée dans le cadre d'une bibliothèque ou par des clients via la communication serveur-client. Les noms des fonctions privées d'Apps Script se terminent par _.
• Plage**: groupe d'une ou de plusieurs cellules adjacentes dans une feuille. La classe Range** vous permet de lire et de mettre à jour les plages au sein d'une feuille.
• Éditeur de scripts: é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éclencheurs réservés et certaines restrictions.
• Feuille**: une seule page d'une feuille de calcul. Sert parfois à faire référence à un fichier Google Sheets dans Google Drive. La classe Sheet** vous permet d'accéder à des feuilles et de les modifier.
• 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. La classe Spreadsheet** vous permet d'accéder aux feuilles de calcul et de les modifier.
• Service Spreadsheet : service Apps Script qui permet aux scripts de créer des fichiers Google Sheets, d'y accéder et de les modifier.
• Déclencheurs : événement associé à une fonction Apps Script. Lorsque l'événement se produit (une feuille de calcul en cours d'ouverture, par exemple), le déclencheur se déclenche et exécute automatiquement la fonction Apps Script associée.
• Service de récupération d'URL: service Apps Script qui permet aux scripts de se connecter aux points de terminaison d'URL pour envoyer des requêtes et recevoir des réponses.
• Fonctions d'enveloppement : fonctions utilitaires simples permettant d'appeler des fonctions plus complexes tout en leur transmettant un ensemble spécifique de paramètres.

Étapes suivantes

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.

Trouvez l'atelier de programmation suivant dans Graphique et présenter les données dans Slides.