Principes de base d'Apps Script avec Google Sheets n° 3: avec les données

1. Introduction

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

Dans cet atelier de programmation, vous allez apprendre à utiliser la manipulation de données, les menus personnalisés et la récupération des données de l'API publique dans Apps Script afin d'améliorer votre expérience avec Sheets. Vous continuerez à utiliser les classes SpreadsheetApp, Spreadsheet, Sheet et Range dans les précédents ateliers de programmation de cette playlist.

Points abordés

  • Importer des données à partir d'une feuille de calcul personnelle ou partagée dans Drive
  • Créer un menu personnalisé avec la fonction onOpen()
  • Comment analyser et manipuler les valeurs de données de chaîne dans des cellules Google Sheets.
  • Extraire et manipuler les données d'objet JSON depuis une source API publique

Avant de commencer

Il s'agit du troisième atelier de programmation sur la playlist Fundamentals 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

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

Les exercices de cet atelier de programmation nécessitent une feuille de calcul pour fonctionner. Pour créer une feuille de calcul à utiliser lors des exercices suivants:

  1. Créez une feuille de calcul dans Google Drive. Pour ce faire, accédez à Drive Driveet sélectionnez New > Google Sheets. Votre nouvelle feuille de calcul s'ouvre. Le fichier est enregistré dans votre dossier Drive.
  2. Cliquez sur le titre de la feuille de calcul et remplacez-le de "Feuille de calcul sans titre" par "Manipulation des données et menus personnalisés". Votre feuille doit se présenter comme suit:

545c02912de7d110.png

  1. Pour ouvrir l'éditeur de scripts, cliquez sur Extensions, puis sur Apps Script.
  2. Cliquez sur le titre du projet Apps Script et remplacez-le par "Project sans titre" par "Manipulation des données et menus personnalisés". Cliquez sur Renommer pour enregistrer le changement de titre.

Si votre feuille de calcul et votre projet sont vides, vous pouvez commencer l'atelier. Passez à la section suivante pour commencer à vous familiariser avec les menus personnalisés.

3. Présentation: importer des données à l'aide d'un élément de menu personnalisé

Apps Script vous permet de définir des menus personnalisés qui peuvent apparaître dans Google Sheets. Vous pouvez également utiliser les menus personnalisés dans Google Docs, Google Slides et Google Forms. Lorsque vous définissez un élément de menu personnalisé, vous créez un libellé de texte et vous l'associez à une fonction Apps Script de votre projet de script. Vous pouvez ensuite ajouter le menu à l'interface utilisateur pour qu'il s'affiche dans Google Sheets:

D6b694da6b8c6783.png

Lorsqu'un utilisateur clique sur un élément de menu personnalisé, la fonction Apps Script que vous avez associée s'exécute. C'est un moyen rapide d'exécuter des fonctions Apps Script sans avoir à ouvrir l'éditeur de scripts. Elle permet également à d'autres utilisateurs de la feuille de calcul d'exécuter votre code sans savoir quoi que ce soit ni comment fonctionne Apps Script. Pour eux, il s'agit d'un autre élément du menu.

Les éléments de menu personnalisés sont définis dans le onOpen()déclencheur simple que vous allez découvrir dans la section suivante.

4. Fonction onOpen()

Les déclencheurs simples dans Apps Script vous permettent d'exécuter un code Apps Script spécifique en réponse à certaines conditions ou certains événements. Lorsque vous créez un déclencheur, vous définissez l'événement qui va l'activer et fournissez une fonction Apps Script qui s'exécute.

onOpen() est un exemple de déclencheur simple. Elles sont faciles à configurer. Il vous suffit d'écrire une fonction Apps Script nommée onOpen(), et Apps Script l'exécute chaque fois que la feuille de calcul associée est ouverte ou actualisée:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
 /* ... */ 
}

Implémentation

Créons un menu personnalisé.

  1. Remplacez le code de votre projet de script par le code suivant:
/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addToUi();
}
  1. Enregistrez le projet de script.

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 trois lignes suivantes permettent de créer le menu (Book-list), d'ajouter un élément de menu (Load Book-list) à ce menu, puis d'ajouter 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é de l'élément de menu et la fonction Apps Script qui s'exécute lorsque l'élément de menu est sélectionné. Dans ce cas, si vous sélectionnez l'élément de menu Load Book-list, Sheets tente d'exécuter la fonction loadBookList() (qui n'existe pas encore).

Les résultats

Exécutez maintenant cette fonction pour voir si elle fonctionne:

  1. Dans Google Sheets, actualisez votre feuille de calcul. Remarque: cette commande ferme généralement l'onglet de votre éditeur de scripts.
  2. Pour rouvrir l'éditeur de scripts, sélectionnez Tools > Script editor (Outils > Éditeur de scripts).

Une fois la feuille de calcul actualisée, le nouveau menu Book-list devrait s'afficher dans la barre de menu:

687dfb214f2930ba.png

Cliquez sur Liste de livres pour afficher le menu qui s'affiche:

8a4a391fbabcb16a.png

La section suivante crée le code de la fonction loadBookList() et vous permet d'interagir avec les données dans Apps Script en lisant d'autres feuilles de calcul.

5. Importer les données issues de vos feuilles de calcul

Maintenant que vous avez créé un menu personnalisé, vous pouvez créer des fonctions qui peuvent être exécutées en cliquant sur l'élément de menu.

Actuellement, le menu personnalisé Book-list comporte un élément de menu: Load Book-list. la fonction appelée lorsque vous sélectionnez l'élément de menu Load Book-list, loadBookList(), n'existe pas dans votre script. Par conséquent, si vous sélectionnez List-list > Load Book-list, une erreur se produit:

Bf4dcef066e7041d.gif

Vous pouvez corriger cette erreur en implémentant la fonction loadBookList().

Implémentation

Vous souhaitez que le nouvel élément de menu remplisse les données de la feuille de calcul. Vous devez donc mettre en œuvre loadBookList() pour lire les données des livres depuis une autre feuille de calcul et les copier dans celle-ci:

  1. Ajoutez le code suivant à votre script sous onOpen() :
/** 
 * Creates a template book list based on the
 * provided 'codelab-book-list' sheet.
 */
function loadBookList(){
  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive using
  // the spreadsheet's ID. 
  var bookSS = SpreadsheetApp.openById(
    "1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo" 
  );

  // Gets the sheet, data range, and values of the
  // spreadsheet stored in bookSS.
  var bookSheet = bookSS.getSheetByName("codelab-book-list");
  var bookRange = bookSheet.getDataRange();
  var bookListValues = bookRange.getValues();

  // Add those values to the active sheet in the current
  // spreadsheet. This overwrites any values already there.
  sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth()) 
    .setValues(bookListValues);
  
  // Rename the destination sheet and resize the data
  // columns for easier reading.
  sheet.setName("Book-list");
  sheet.autoResizeColumns(1, 3);
}
  1. Enregistrez le projet de script.

Réviser le code

Comment fonctionne cette fonction ? La fonction loadBookList() utilise principalement des méthodes des classes Spreadsheet, Sheet et Range introduites par les précédents ateliers de programmation. En tenant compte de ces concepts, vous pouvez décomposer le code loadBookList() en quatre sections:

1: Identifiez la feuille de destination

La première ligne utilise SpreadsheetApp.getActiveSheet() pour obtenir une référence à l'objet feuille actuel et la stocke dans la variable sheet. Les données seront copiées dans cette feuille.

2: Identifiez les données sources

Les lignes suivantes établissent quatre variables faisant référence aux données source que vous récupérez:

  • bookSS stocke une référence à la feuille de calcul à partir de laquelle le code lit les données. Le code trouve la feuille de calcul en fonction de son ID. Dans cet exemple, nous avons fourni l'ID d'une feuille de calcul source à lire, puis nous l'avons ouverte à l'aide de la méthode SpreadsheetApp.openById(id).
  • bookSheet stocke une référence à une feuille de bookSS qui contient les données que vous souhaitez. Le code identifie la feuille à lire à l'aide de son nom, codelab-book-list.
  • bookRange stocke une référence à une plage de données dans bookSheet. La méthode Sheet.getDataRange() renvoie la plage contenant toutes les cellules non vides de la feuille. C'est un moyen simple de vous assurer que vous obtenez une plage couvrant toutes les données d'une feuille, sans inclure de ligne ni de colonne vide.
  • bookListValues est un tableau 2D contenant toutes les valeurs extraites des cellules dans bookRange. La méthode Range.getValues() génère ce tableau en lisant les données de la feuille source.

3: Copier les données de la source à la destination

La section de code suivante copie les données bookListValues dans sheet, puis renomme la feuille:

4: Mettre en forme la feuille de destination

Sheet.setName(name) permet de remplacer le nom de la feuille de destination par Book-list. La dernière ligne de la fonction utilise Sheet.autoResizeColumns(startColumn, numColumns) pour redimensionner les trois premières colonnes de la feuille de destination, ce qui vous permet de lire les nouvelles données plus facilement.

Les résultats

Vous pouvez voir cette fonction en action. Dans Google Sheets, sélectionnez List-list > Load Book-list pour exécuter la fonction permettant de remplir votre feuille de calcul:

3c797e1e2b9fe641.gif

Vous disposez désormais d'une feuille avec la liste des titres des livres, des auteurs et des numéros ISBN à 13 chiffres. Dans la section suivante, vous allez apprendre à modifier et à mettre à jour les données de cette liste de livres à l'aide de la manipulation de chaîne et de menus personnalisés.

6. Présentation: nettoyer les données de la feuille de calcul

Les informations de votre livre sont désormais sur votre livre. Chaque ligne fait référence à un livre en particulier, en indiquant son titre, son auteur et le numéro ISBN dans des colonnes distinctes. Toutefois, ces données brutes peuvent aussi poser problème:

  1. Pour certaines lignes, le titre et l'auteur sont placés ensemble dans la colonne du titre, et séparés par une virgule ou une chaîne &&t;;
  2. Certaines lignes ne comportent pas de titre ou d'auteur.

Dans les sections suivantes, vous allez corriger ces problèmes en nettoyant les données. Pour le premier problème, vous allez créer des fonctions qui lisent la colonne de titre et divisent le texte chaque fois qu'une virgule ou un point par un séparateur se trouve, en insérant les sous-chaînes d'auteur et de titre correspondantes dans les colonnes appropriées. Pour le deuxième problème, vous allez écrire un code qui recherche automatiquement des informations de livre manquantes à l'aide d'une API externe et les ajoute à votre feuille.

7. Ajouter des éléments au menu

Vous allez créer trois éléments de menu pour contrôler les opérations de nettoyage des données que vous allez mettre en œuvre.

Implémentation

Mettez à jour onOpen() pour inclure les éléments de menu supplémentaires dont vous aurez besoin. Procédez comme suit :

  1. Dans votre projet de script, mettez à jour votre code onOpen() pour qu'il corresponde à ce qui suit:
/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addSeparator()
    .addItem(
      'Separate title/author at first comma', 'splitAtFirstComma')
    .addItem(
      'Separate title/author at last "by"', 'splitAtLastBy')
    .addSeparator()
    .addItem(
      'Fill in blank titles and author cells', 'fillInTheBlanks')
    .addToUi();
}
  1. Enregistrez le projet de script.
  2. 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. Ainsi, vous n'avez pas besoin de l'actualiser.

Dans ce nouveau code, la méthode Menu.addSeparator() crée un séparateur horizontal dans le menu pour organiser visuellement les groupes d'éléments de menu associés. Les nouveaux éléments de menu sont ensuite ajoutés en dessous, avec les libellés Separate title/author at first comma, Separate title/author at last "by" et Fill in blank titles and author cells.

Les résultats

Dans votre feuille de calcul, cliquez sur le menu Book-list pour afficher les nouveaux éléments de menu:

580c806ce8fd4872.png

Cliquez sur ces nouveaux éléments pour générer une erreur, car vous n'avez pas implémenté les fonctions correspondantes. Nous allons donc continuer.

8. Scinder le texte sur des délimiteurs

L'ensemble de données que vous avez importé dans votre feuille de calcul comporte plusieurs cellules où l'auteur et le titre sont combinés de façon incorrecte dans une cellule par une virgule:

ca91c43c4e51d6b5.png

Cette opération est souvent effectuée via des colonnes distinctes. Google Sheets fournit une fonction SPLIT() qui divise les chaînes en colonnes. Cependant, les ensembles de données comportent souvent des problèmes qui ne peuvent pas être résolus avec Sheets. Vous pouvez alors écrire le code Apps Script afin d'effectuer les opérations complexes nécessaires au nettoyage et à l'organisation des données.

Pour commencer à nettoyer vos données, commencez par implémenter une fonction appelée splitAtFirstComma() qui divise l'auteur et le titre dans leurs cellules respectives lorsque des virgules sont trouvées.

La fonction splitAtFirstComma() doit suivre les étapes ci-dessous:

  1. Obtenez la plage représentant les cellules sélectionnées.
  2. Vérifiez si les cellules de la plage comprennent une virgule.
  3. Dans ce cas, séparez la chaîne en deux (et seulement deux) sous-chaînes à l'emplacement de la première virgule. Pour simplifier les choses, vous pouvez considérer que toute virgule indique un motif de chaîne [authors], [title] " de chaîne. Vous pouvez également supposer que plusieurs virgules apparaissent dans la cellule. Il est recommandé de scinder la première virgule de la chaîne.
  4. Définissez les sous-chaînes en tant que nouveau contenu des cellules de titre et d'auteur correspondantes.

Implémentation

Pour mettre en œuvre ces étapes, vous devez utiliser les mêmes méthodes que celles du service Feuille de calcul que celles utilisées précédemment, mais vous devez également utiliser JavaScript pour manipuler les données de chaîne. Procédez comme suit:

  1. Dans l'éditeur Apps Script, ajoutez la fonction suivante à la fin de votre projet de script:
/**
 * Reformats title and author columns by splitting the title column
 * at the first comma, if present.
 */
function splitAtFirstComma(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where commas are found. Assumes the presence
  // of a comma indicates an "authors, title" pattern.
  for (var row = 0; row < titleAuthorValues.length; row++){
    var indexOfFirstComma =
        titleAuthorValues[row][0].indexOf(", ");

    if(indexOfFirstComma >= 0){
      // Found a comma, so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];

      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(indexOfFirstComma + 2);

      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(0, indexOfFirstComma);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}
  1. Enregistrez le projet de script.

Réviser le code

Examinons le nouveau code qui comprend trois sections principales:

1: Récupérez les valeurs de titre en surbrillance

Les trois premières lignes définissent trois variables qui font référence aux données actuelles de la feuille:

  • activeRange représente la plage actuellement mise en surbrillance par l'utilisateur lors de l'appel de la fonction splitAtFirstComma(). Pour simplifier cet exercice, nous pouvons supposer que l'utilisateur ne le fait que lorsqu'il met en surbrillance les cellules de la colonne A.
  • titleAuthorRange représente une nouvelle plage qui couvre les mêmes cellules que activeRange, mais inclut également une colonne de droite. titleAuthorRange est créé à l'aide de la méthode Range.offset(rowOffset, columnOffset, numRows, numColumns). Cette plage doit être remplacée par le code, qui doit permettre d'insérer tous les auteurs trouvés dans la colonne "title".
  • titleAuthorValues est un tableau 2D des données extraites de titleAuthorRange à l'aide de Range.getValues().

2: analyse de chaque titre et fractionnement sur la première virgule détectée

La section suivante examine les valeurs dans titleAuthorValues pour rechercher des virgules. JavaScript pour la boucle est utilisé pour examiner toutes les valeurs dans la première colonne de titleAuthorValues. Lorsqu'une sous-chaîne (", ") est détectée à l'aide de la méthode JavaScript String indexOf(), le code effectue les opérations suivantes:

  1. La valeur de la chaîne de cellule est copiée dans la variable titlesAndAuthors.
  2. L'emplacement de la virgule est déterminé à l'aide de la méthode JavaScript String indexOf().
  3. La méthode JavaScript String string() est appelée deux fois pour obtenir la sous-chaîne avant le délimiteur et la sous-chaîne après le délimiteur.
  4. Les sous-chaînes sont copiées dans le tableau 2D titleAuthorValues, en remplaçant les valeurs existantes à cette position. Étant donné que nous considérons un modèle "[authors]", [title]", l'ordre des deux sous-chaînes est inversé pour qu'il apparaisse dans la première colonne et les auteurs dans la deuxième.

Remarque: Lorsque le code ne trouve pas de virgule, les données de la ligne ne sont pas modifiées.

3: Copiez à nouveau les nouvelles valeurs dans la feuille

Une fois que toutes les valeurs de la cellule de titre ont été examinées, le tableau de titre titleAuthorValues 2D mis à jour est copié dans la feuille de calcul à l'aide de la méthode Range.setValues(values).

Les résultats

Vous pouvez désormais voir les effets de la fonction splitAtFirstComma() en action. Essayez de le lancer en sélectionnant Séparer le titre/l'auteur à la première virgule dans le menu après avoir sélectionné...

...une cellule:

A24763b60b305376.gif

...ou plusieurs cellules:

89c5c89b357d3713.gif

Vous venez de créer une fonction Apps Script qui traite les données Sheets. Vous allez ensuite implémenter la deuxième fonction de séparateur.

9. Séparer du texte à l'aide de délimiteurs

En examinant les données d'origine, vous pouvez constater un autre problème. Tout comme certains des formats de données, les titres et les auteurs d'une même cellule se présentent comme suit : [author], [title]", tandis que d'autres mettent en forme "[title] par [authors]" :

41f0dd5ac63b62f4.png

Implémentation

Vous pouvez résoudre ce problème en utilisant la même technique que dans la section précédente, en créant une fonction appelée splitAtLastBy(). Cette fonction ressemble à splitAtFirstComma(), mais la seule différence est qu'elle recherche un schéma de texte légèrement différent. Pour mettre en œuvre cette fonction, procédez comme suit:

  1. Dans l'éditeur Apps Script, ajoutez la fonction suivante à la fin de votre projet de script:
/** 
 * Reformats title and author columns by splitting the title column
 * at the last instance of the string " by ", if present.
 */
function splitAtLastBy(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where " by " substrings are found. Assumes
  // the presence of a " by " indicates a "title by authors"
  // pattern.
  for(var row = 0; row < titleAuthorValues.length; row++){
    var indexOfLastBy =
        titleAuthorValues[row][0].lastIndexOf(" by ");
    
    if(indexOfLastBy >= 0){
      // Found a " by ", so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);
      
      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}
  1. Enregistrez le projet de script.

Réviser le code

Il existe quelques différences importantes entre ce code et splitAtFirstComma() :

  1. Sous-chaîne by, sert de délimiteur de chaîne au lieu de ,.
  2. Dans cet exemple, la méthode JavaScript String.lastIndexOf(substring) est utilisée à la place de String.indexOf(substring). Cela signifie que s'il existe plusieurs " by " sous-chaînes dans la chaîne initiale, toutes, sauf la dernière " by, sont supposées faire partie du titre.
  3. Après avoir fractionné la chaîne, la première sous-chaîne est définie comme titre et la deuxième comme auteur (il s'agit de l'ordre inverse de splitAtFirstComma()).

Les résultats

Vous pouvez désormais voir les effets de la fonction splitAtLastBy() en action. Essayez de le lancer en sélectionnant Séparer le titre/L'auteur en dernier &c&by;; élément de menu après avoir sélectionné...

...une cellule:

4E6679E134145975.gif

...ou plusieurs cellules:

3c879c572c61e62f.gif

Vous avez terminé cette section de l'atelier de programmation. Vous pouvez désormais utiliser Apps Script pour lire et modifier des données de chaîne dans une feuille, et utiliser des menus personnalisés pour exécuter différentes commandes Apps Script.

Dans la section suivante, vous allez apprendre à améliorer cet ensemble de données en remplissant des cellules vides avec des données extraites d'une API publique.

10. Présentation: récupérer des données à partir d'API publiques

Jusqu'à présent, vous avez affiné votre ensemble de données pour résoudre des problèmes de mise en forme des titres et des auteurs, mais il manque encore quelques informations dans l'ensemble de données. Il est indiqué dans les cellules ci-dessous:

af0dba8cb09d1a49.png

Vous ne pouvez pas obtenir les données manquantes en utilisant des opérations de chaîne sur les données dont vous disposez actuellement. Vous devez récupérer les données manquantes à partir d'une autre source. Vous pouvez le faire dans Apps Script en demandant des informations à des API externes pouvant fournir des données supplémentaires.

Les API sont des interfaces de programmation d'application. Il s'agit d'un terme de manière générale, mais c'est essentiellement un service que vos programmes et scripts peuvent appeler pour demander des informations ou effectuer certaines actions. Dans cette section, vous appelez une API accessible au public pour demander des informations sur les livres que vous pouvez insérer dans les cellules vacantes de votre feuille.

Cette section vous explique comment:

  • Demandez des données de livre à une source API externe.
  • Extrayez le titre et les informations sur l'auteur des données renvoyées, puis écrivez-les dans votre feuille de calcul.

11. Extraire des données externes avec UrlFetch

Avant d'explorer le code compatible avec votre feuille de calcul, vous pouvez apprendre à utiliser des API externes dans Apps Script en créant une fonction d'aide spécifique pour demander des informations concernant des livres à partir de l'API Open Library.

Notre fonction d'assistance, fetchBookData_(ISBN), utilise un numéro ISBN à 13 chiffres pour un livre et renvoie des données sur ce livre. Elle se connecte à l'API Open Library et en récupère des informations, puis analyse l'objet JSON renvoyé.

Implémentation

Pour mettre en œuvre cette fonction d'assistance, procédez comme suit:

  1. Dans l'éditeur Apps Script, ajoutez le code suivant à la fin de votre script:
/**
 * Helper function to retrieve book data from the Open Library
 * public API.
 *
 * @param {number} ISBN - The ISBN number of the book to find.
 * @return {object} The book's data, in JSON format.
 */
function fetchBookData_(ISBN){
  // Connect to the public API.
  var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
      + ISBN + "&jscmd=details&format=json";
  var response = UrlFetchApp.fetch(
      url, {'muteHttpExceptions': true});
  
  // Make request to API and get response before this point.
  var json = response.getContentText();
  var bookData = JSON.parse(json); 
  
  // Return only the data we're interested in.
  return bookData['ISBN:' + ISBN];
}
  1. Enregistrez le projet de script.

Réviser le code

Ce code se divise en deux sections principales:

1: La requête API

Dans les deux premières lignes, fetchBookData_(ISBN) se connecte à l'API publique d'Open Library à l'aide du point de terminaison d'URL de l'API et du service de récupération d'URL d'Apps Script.

La variable url est simplement une chaîne d'URL, comme une adresse Web. Il pointe vers un emplacement sur les serveurs Open Library. Il comprend également trois paramètres (bibkeys, jscmd et format) qui indiquent aux serveurs Open Library les informations que vous demandez et comment structurer la réponse. Dans ce cas, vous devez fournir le numéro ISBN du livre et demander des informations détaillées à renvoyer au format JSON.

Une fois que vous avez créé la chaîne d'URL, le code envoie une requête à l'établissement et reçoit une réponse. Pour ce faire, utilisez la méthode UrlFetchApp.fetch(url, params). Il envoie une demande d'informations à l'URL externe que vous fournissez et stocke la réponse obtenue dans la variable response. En plus de l'URL, le code définit le paramètre facultatif muteHttpExceptions sur true. Ce paramètre signifie que votre code ne s'arrêtera pas si la requête génère une erreur d'API. Au lieu de cela, la réponse d'erreur est renvoyée.

La requête renvoie un objet HTTPResponse qui est stocké dans la variable response. Les réponses HTTP comprennent un code de réponse, les en-têtes HTTP et le contenu de la réponse principale. Les informations qui nous intéressent ici sont le contenu JSON principal. Le code doit donc l'extraire, puis l'analyser pour localiser et renvoyer les informations souhaitées.

2: analysez la réponse de l'API et renvoyez les informations qui vous intéressent

Dans les trois dernières lignes de code, la méthode HTTPResponse.getContentText() renvoie le contenu principal de la réponse sous forme de chaîne. Cette chaîne est au format JSON, mais l'API Open Library définit le contenu et le format exacts. La méthode JSON.parse(jsonString) convertit la chaîne JSON en objet JavaScript afin que différentes parties des données puissent être facilement extraites. Enfin, la fonction renvoie les données correspondant au numéro ISBN du livre.

Les résultats

Maintenant que vous avez implémenté fetchBookData_(ISBN), d'autres fonctions dans votre code peuvent trouver des informations sur les livres associés à leur numéro ISBN. Cette fonction vous permet de remplir les cellules de votre feuille de calcul.

12. Écrire des données d'API dans une feuille de calcul

Vous pouvez à présent implémenter une fonction fillInTheBlanks() qui effectue les opérations suivantes:

  1. Identifiez les données de titre et d'auteur manquantes dans la plage de données active.
  2. Récupérez les données manquantes d'un livre en appelant l'API Open Library à l'aide de la méthode d'assistance fetchBookData_(ISBN).
  3. Mettez à jour les valeurs manquantes pour le titre ou l'auteur dans leurs cellules respectives.

Implémentation

Pour mettre en œuvre cette nouvelle fonction:

  1. Dans l'éditeur Apps Script, ajoutez le code suivant à la fin de votre projet de script:
/**
 * Fills in missing title and author data using Open Library API
 * calls.
 */ 
function fillInTheBlanks(){
  // Constants that identify the index of the title, author,
  // and ISBN columns (in the 2D bookValues array below). 
  var TITLE_COLUMN = 0;
  var AUTHOR_COLUMN = 1;
  var ISBN_COLUMN = 2;

  // Get the existing book information in the active sheet. The data
  // is placed into a 2D array.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
  var bookValues = dataRange.getValues();

  // Examine each row of the data (excluding the header row).
  // If an ISBN is present, and a title or author is missing,
  // use the fetchBookData_(isbn) method to retrieve the
  // missing data from the Open Library API. Fill in the
  // missing titles or authors when they're found.
  for(var row = 1; row < bookValues.length; row++){   
    var isbn = bookValues[row][ISBN_COLUMN];
    var title = bookValues[row][TITLE_COLUMN];
    var author = bookValues[row][AUTHOR_COLUMN];
   
    if(isbn != "" && (title === "" || author === "") ){
      // Only call the API if you have an ISBN number and
      // either the title or author is missing.
      var bookData = fetchBookData_(isbn);

      // Sometimes the API doesn't return the information needed.
      // In those cases, don't attempt to update the row.
      if (!bookData || !bookData.details) {
        continue;
      }

      // The API might not return a title, so only fill it in
      // if the response has one and if the title is blank in
      // the sheet.
      if(title === "" && bookData.details.title){
        bookValues[row][TITLE_COLUMN] = bookData.details.title; 
      }

      // The API might not return an author name, so only fill it in
      // if the response has one and if the author is blank in
      // the sheet.
      if(author === "" && bookData.details.authors
          && bookData.details.authors[0].name){
        bookValues[row][AUTHOR_COLUMN] =
          bookData.details.authors[0].name; 
      }
    }
  }
  
  // Insert the updated book data values into the spreadsheet.
  dataRange.setValues(bookValues);
}
  1. Enregistrez le projet de script.

Réviser le code

Ce code comprend trois sections:

1: Lire les informations existantes sur le livre

Les trois premières lignes de la fonction définissent des constantes pour rendre le code plus lisible. Dans les deux lignes suivantes, la variable bookValues permet de conserver une copie locale des informations sur le livre de la feuille. Le code lit les informations de bookValues, utilise l'API pour compléter les informations manquantes et réécrit ces valeurs dans la feuille de calcul.

2: Récupérez les informations manquantes à l'aide de la fonction d'assistance

Le code est lu en boucle sur chaque ligne de bookValues afin de trouver les titres ou les auteurs manquants. Afin de réduire le nombre d'appels d'API tout en améliorant l'efficacité, le code n'appelle l'API que si les conditions suivantes sont remplies:

  1. La colonne "ISBN" de la ligne est associée à une valeur.
  2. La cellule du titre ou de l'auteur sur la ligne est vide.

Si les conditions sont remplies, le code appelle l'API à l'aide de la fonction d'assistance fetchBookData_(isbn) que vous avez implémentée précédemment et stocke le résultat dans la variable bookData. Les informations manquantes doivent maintenant être insérées dans la feuille.

Il vous suffit d'ajouter les informations bookData à notre feuille de calcul. Toutefois, il y a un avertissement. Malheureusement, les API publiques telles que l'API Open Library Book n'ont parfois pas les informations que vous demandez ou peuvent rencontrer un autre problème. Si vous estimez que chaque requête API aboutit, votre code ne sera pas suffisamment robuste pour gérer des erreurs inattendues.

Pour s'assurer que votre code peut gérer les erreurs d'API, il doit vérifier que la réponse de l'API est valide avant d'essayer de l'utiliser. Une fois que le code dispose de bookData, il vérifie simplement que bookData et bookData.details existent avant toute tentative de lecture. S'il en manque une, cela signifie que l'API ne disposait pas des données souhaitées. Dans ce cas, la commande continue indique au code d'ignorer cette ligne. Vous ne pouvez pas remplir les cellules manquantes, mais au moins votre script ne va pas planter.

3: Insérer les nouvelles informations dans la feuille de calcul

La dernière partie du code contient des vérifications similaires permettant de vérifier que l'API a renvoyé le titre et les informations sur l'auteur. Le code ne met à jour le tableau bookValues que si le titre ou la cellule de l'auteur d'origine sont vides et si l'API a renvoyé une valeur que vous pouvez placer dans cette table.

La boucle se ferme une fois que toutes les lignes de la feuille ont été examinées. La dernière étape consiste à réécrire le tableau bookValues mis à jour dans la feuille de calcul à l'aide de Range.setValues(values).

Les résultats

Vous pouvez à présent terminer le nettoyage des données de vos livres. Procédez comme suit :

  1. Si ce n'est pas encore fait, sélectionnez la plage A2:A15 dans votre feuille et sélectionnez Liste de livres > Séparer le titre/l'auteur à la première virgule pour corriger les erreurs de virgule.
  2. Si ce n'est pas déjà fait, sélectionnez la plage A2:A15 dans votre feuille de calcul, puis sélectionnez Liste de livres > Séparer le titre/l'auteur à la fin du libellé pour nettoyer les problèmes.
  3. Pour remplir toutes les cellules restantes, sélectionnez Liste de livres &gt ; Remplir les titres vides et les cellules d'auteur:

826675a3437adbdb.gif

13. Conclusion

Félicitations, vous avez terminé cet atelier de programmation. Vous avez appris à créer des menus personnalisés pour activer différentes parties de votre code Apps Script. Vous avez également appris à importer des données dans Google Sheets à l'aide des services Apps Script et d'API publiques. Il s'agit d'une opération courante dans le traitement d'une feuille de calcul. Grâce à Apps Script, vous pouvez importer des données provenant d'un large éventail de sources. Enfin, vous avez vu comment utiliser les services Apps Script et JavaScript pour lire, traiter et insérer des données dans des feuilles de calcul.

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

Oui Non

Voici ce que vous avez appris :

  • Importer des données à partir d'une feuille de calcul Google
  • Créer un menu personnalisé dans la fonction onOpen()
  • Comment analyser et manipuler les valeurs de données de chaîne.
  • Comment appeler des API publiques à l'aide du service de récupération d'URL
  • Comment analyser des données d'objet JSON récupérées d'une source API publique.

Étapes suivantes

L'atelier de programmation suivant de cette playlist aborde plus en détail le format des données dans une feuille de calcul.

Trouvez le prochain atelier de programmation sur Mise en forme des données.