Principes de base d'Apps Script avec Google Sheets #3 : travailler avec des données

1. Introduction

Bienvenue dans la troisième partie de la playlist d'ateliers de programmation "Principes de base d'Apps Script avec Google Sheets".

En suivant cet atelier de programmation, vous apprendrez à utiliser la manipulation de données, les menus personnalisés et la récupération de données d'API publiques dans Apps Script pour améliorer votre expérience Sheets. Vous continuerez à travailler avec les classes SpreadsheetApp, Spreadsheet, Sheet et Range présentées dans les ateliers de programmation précédents de cette playlist.

Points abordés

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

Avant de commencer

Il s'agit du troisiè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

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

Les exercices de cet atelier de programmation nécessitent une feuille de calcul. Pour créer une feuille de calcul à utiliser dans ces exercices, procédez comme suit :

1. Créez une feuille de calcul dans votre Google Drive. Pour ce faire, accédez à l'interface Drive, puis sélectionnez Nouveau > Google Sheets. La 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 "Feuille de calcul sans titre" par "Manipulation de données et menus personnalisés". Votre feuille devrait se présenter comme suit :

3. Pour ouvrir l'éditeur de script, cliquez sur Extensions > Apps Script.
4. Cliquez sur le titre du projet Apps Script et remplacez "Projet sans titre" par "Manipulation de données et menus personnalisés". Cliquez sur Renommer pour enregistrer le nouveau titre.

Vous êtes prêt à commencer l'atelier avec une feuille de calcul et un projet vides. Passez à la section suivante pour en savoir plus sur les menus personnalisés.

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.

3. Présentation : importer des données avec un élément de menu personnalisé

Apps Script vous permet de définir des menus personnalisés qui peuvent s'afficher dans Google Sheets. Vous pouvez également utiliser des 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 l'associez à une fonction Apps Script dans votre projet de script. Vous pouvez ensuite ajouter le menu à l'UI pour qu'il s'affiche dans Google Sheets :

Lorsqu'un utilisateur clique sur un élément de menu personnalisé, la fonction Apps Script que vous y avez associée s'exécute. Il s'agit d'un moyen rapide d'exécuter des fonctions Apps Script sans avoir à ouvrir l'éditeur de script. Il permet également aux autres utilisateurs de la feuille de calcul d'exécuter votre code sans avoir besoin de connaître son fonctionnement ni celui d'Apps Script. Pour eux, il s'agit simplement d'un autre élément de menu.

Les éléments de menu personnalisés sont définis dans la fonction de déclencheur simple onOpen(), que nous aborderons dans la section suivante.

4. Fonction onOpen()

Les déclencheurs simples dans Apps Script 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 le déclenche et fournissez une fonction Apps Script qui s'exécute pour l'événement.

onOpen() est un exemple de déclencheur simple. Elles sont faciles à configurer : il vous suffit d'écrire une fonction Apps Script nommée onOpen(). Apps Script l'exécute chaque fois que la feuille de calcul associée est ouverte ou rechargé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() {
 /* ... */ 
}

Concept clé : Les déclencheurs simples sont faciles à configurer et à utiliser, mais ils sont soumis à plusieurs restrictions. En raison de ces limites, onOpen() n'est utilisé que pour créer des éléments de menu.

Les déclencheurs sont un sujet complexe qui dépasse le cadre de cet atelier de programmation. Pour l'instant, vous pouvez considérer onOpen() comme un gestionnaire d'événements qui ajoute des éléments de menu chaque fois que vous chargez une feuille de calcul.

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();
}

2. Enregistrez votre projet de script.

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 trois lignes suivantes créent le menu (Book-list), ajoutent un élément de menu (Load Book-list) à ce 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 la fonction Apps Script qui s'exécute lorsque l'élément de menu est sélectionné. Dans ce cas, la sélection de l'élément de menu Load Book-list entraîne une tentative d'exécution de la fonction loadBookList() (qui n'existe pas encore) par Sheets.

Résultats

Exécutez cette fonction maintenant pour vérifier qu'elle fonctionne :

1. Dans Google Sheets, actualisez votre feuille de calcul. Remarque : Cela ferme généralement l'onglet de l'éditeur de script.
2. Rouvrez l'éditeur de script en sélectionnant Outils > Éditeur de script.

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

En cliquant sur Book-list, vous pouvez voir le menu qui s'affiche :

La section suivante crée le code de la fonction loadBookList() et présente une façon d'interagir avec les données dans Apps Script : lire 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.

Pour le moment, le menu personnalisé Book-list ne comporte qu'un seul élément : 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, la sélection de Book-list > Load Book-list (Liste de livres > Charger la liste de livres) génère une erreur :

Pour corriger cette erreur, implémentez la fonction loadBookList().

Implémentation

Vous souhaitez que le nouvel élément de menu remplisse la feuille de calcul avec des données à utiliser. Vous allez donc implémenter loadBookList() pour lire les données de livres à partir d'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);
}

2. Enregistrez votre projet de script.

Réviser le code

Comment fonctionne cette fonctionnalité ? La fonction loadBookList() utilise principalement des méthodes des classes Spreadsheet, Sheet et Range présentées dans les ateliers de programmation précédents. En gardant ces concepts à l'esprit, 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 de feuille actuel et la stocke dans la variable sheet. Il s'agit de la feuille dans laquelle les données seront copiées.

2) Identifiez les données sources

Les lignes suivantes établissent quatre variables qui font référence aux données sources 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 à l'aide de son identifiant. Dans cet exemple, nous avons fourni l'ID d'une feuille de calcul source à partir de laquelle lire les données, puis nous avons ouvert la feuille de calcul à l'aide de la méthode SpreadsheetApp.openById(id).
• bookSheet stocke une référence à une feuille dans bookSS qui contient les données souhaitées. Le code identifie la feuille à partir de laquelle lire les données par 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 d'obtenir une plage couvrant toutes les données d'une feuille sans inclure de lignes ni de colonnes vides.
• bookListValues est un tableau 2D contenant toutes les valeurs extraites des cellules de bookRange. La méthode Range.getValues() génère ce tableau en lisant les données de la feuille source.

3) Copiez les données de la source vers la destination.

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

• Sheet.getRange(row, column, numRows, numColumns) permet d'identifier l'emplacement où copier les données dans sheet.
• Les méthodes Range.getHeight() et Range.getWidth() permettent de mesurer la taille des données et de définir une plage de destination de mêmes dimensions.
• Range.setValues(values) copie le tableau 2D de bookListValues dans la plage de destination, en écrasant toutes les données qui s'y trouvent déjà.

4) Mettez en forme la feuille de destination

Sheet.setName(name) permet de renommer la feuille de destination en 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 plus facilement les nouvelles données.

Résultats

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

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

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

Vous disposez désormais d'informations sur les livres dans votre feuille. Chaque ligne fait référence à un livre spécifique, dont le titre, l'auteur et le numéro ISBN sont indiqués dans des colonnes distinctes. Toutefois, vous pouvez également constater certains problèmes avec ces données brutes :

1. Pour certaines lignes, le titre et l'auteur sont placés ensemble dans la colonne "Titre", séparés par une virgule ou la chaîne " par ".
2. Certaines lignes ne contiennent pas le titre ni l'auteur du livre.

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 "Titre" et divisent le texte chaque fois qu'une virgule ou un délimiteur " par " est trouvé, en plaçant les sous-chaînes d'auteur et de titre correspondantes dans les colonnes appropriées. Pour le deuxième problème, vous allez écrire du code qui recherche automatiquement les informations manquantes sur les livres à l'aide d'une API externe et les ajoute à votre feuille.

7. Ajouter des éléments de menu

Vous devez créer trois éléments de menu pour contrôler les opérations de nettoyage des données que vous allez implémenter.

Implémentation

Mettons à 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();
}

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. onOpen() s'exécute pour reconstruire le menu de la feuille de calcul. Vous n'avez donc pas besoin de recharger la feuille de calcul.

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.

Remarque : Les éléments de menu s'affichent dans Sheets dans l'ordre dans lequel vous les ajoutez dans le code onOpen().

Résultats

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

Cliquer sur ces nouveaux éléments provoque une erreur, car vous n'avez pas implémenté les fonctions correspondantes. Nous allons donc le faire maintenant.

8. Fractionner le texte en fonction des délimiteurs de virgule

Dans l'ensemble de données que vous avez importé dans votre feuille de calcul, l'auteur et le titre sont incorrectement combinés dans une même cellule à l'aide d'une virgule :

Il est courant de scinder des chaînes de texte en plusieurs colonnes dans une feuille de calcul. Google Sheets propose une fonction SPLIT() qui divise les chaînes en colonnes. Toutefois, les ensembles de données présentent souvent des problèmes qui ne peuvent pas être facilement résolus avec les fonctions intégrées de Sheets. Dans ce cas, vous pouvez écrire du code Apps Script pour effectuer les opérations complexes nécessaires au nettoyage et à l'organisation de vos données.

Commencez par nettoyer vos données en implémentant 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 effectuer les étapes suivantes :

1. Obtenez la plage représentant les cellules actuellement sélectionnées.
2. Vérifiez si les cellules de la plage contiennent une virgule.
3. Si des virgules sont présentes, divisez la chaîne en deux sous-chaînes (et seulement deux) à l'emplacement de la première virgule. Pour simplifier, vous pouvez supposer que chaque virgule indique un modèle de chaîne "[auteurs], [titre]". Vous pouvez également supposer que, si plusieurs virgules apparaissent dans la cellule, il convient de diviser la chaîne à la première virgule.
4. Définissez les sous-chaînes comme nouveau contenu des cellules de titre et d'auteur respectives.

Implémentation

Pour implémenter ces étapes, vous utiliserez les mêmes méthodes de service de feuille de calcul que celles que vous avez utilisées auparavant, mais vous devrez également utiliser JavaScript pour manipuler les données de chaîne. Suivez les instructions suivantes :

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);
}

2. Enregistrez votre projet de script.

Réviser le code

Examinons le nouveau code, qui se compose de trois sections principales :

1) Récupérer les valeurs des titres mis en évidence

Les trois premières lignes établissent 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 lorsque la fonction splitAtFirstComma() a été appelée. Pour simplifier cet exercice, nous pouvons supposer que l'utilisateur ne le fait que lorsqu'il met en surbrillance des cellules de la colonne A.
• titleAuthorRange représente une nouvelle plage qui couvre les mêmes cellules que activeRange, mais inclut également une colonne supplémentaire à droite. titleAuthorRange est créé à l'aide de la méthode Range.offset(rowOffset, columnOffset, numRows, numColumns). Le code a besoin de cette plage étendue, car il a besoin d'un emplacement pour placer les auteurs qu'il trouve dans la colonne "Titre".
• titleAuthorValues est un tableau de données à deux dimensions extrait de titleAuthorRange à l'aide de Range.getValues().

2) Examinez chaque titre et divisez-le au premier délimiteur de virgule trouvé.

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

1. La valeur de chaîne de la 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 slice() est appelée deux fois pour obtenir la sous-chaîne avant le séparateur de virgule et la sous-chaîne après le séparateur.
4. Les sous-chaînes sont recopiées dans le tableau 2D titleAuthorValues, ce qui écrase les valeurs existantes à cette position. Étant donné que nous partons du principe que le modèle est "[auteurs], [titre] ", l'ordre des deux sous-chaînes est inversé pour placer le titre dans la première colonne et les auteurs dans la seconde.

Remarque : Lorsque le code ne trouve pas de virgule, il laisse les données de la ligne inchangées.

3) Copiez les nouvelles valeurs dans la feuille.

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

Important : Le code aurait pu mettre à jour chaque cellule de la feuille de calcul individuellement à chaque fois qu'il trouvait une virgule, en appelant Range.setValue(value) chaque fois qu'une cellule devait être modifiée. Toutefois, cela entraînerait un code beaucoup plus lent, car chaque appel Range.setValue(value) nécessite une communication avec le serveur.

Lorsque vous mettez à jour des données Sheets, il est presque toujours préférable d'obtenir d'abord les valeurs dans un tableau 2D, de mettre à jour le tableau, puis de renvoyer les données dans la feuille avec un seul appel Range.setValues(values).

Résultats

Vous pouvez maintenant voir les effets de la fonction splitAtFirstComma() en action. Essayez de l'exécuter en sélectionnant l'élément de menu Séparer le titre/l'auteur à la première virgule après avoir sélectionné…

… une cellule :

...ou plusieurs cellules :

Remarque : Vous pouvez sélectionner n'importe quelle colonne de texte dans votre feuille de calcul. Utilisez cette fonction pour trouver les virgules et diviser le texte selon les règles que vous avez définies. Toutefois, si le code trouve un délimiteur de virgule, il écrase la cellule située immédiatement à droite de la cellule d'origine.

Vous avez maintenant créé une fonction Apps Script qui traite les données Sheets. Ensuite, vous allez implémenter la deuxième fonction de fractionnement.

9. Fractionner le texte selon les délimiteurs "par"

En examinant les données d'origine, vous pouvez identifier un autre problème. Alors que certains formats de données affichent les titres et les auteurs dans une même cellule sous la forme "[auteurs], [titre]", d'autres formats affichent les auteurs et les titres sous la forme "[titre] par [auteurs]" :

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 a un rôle similaire à celui de splitAtFirstComma(). La seule différence réelle est qu'elle recherche un modèle de texte légèrement différent. Pour implémenter 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);
}

2. Enregistrez votre projet de script.

Réviser le code

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

1. La sous-chaîne " by " est utilisée comme délimiteur de chaîne, au lieu de ", ".
2. Ici, la méthode JavaScript String.lastIndexOf(substring) est utilisée à la place de String.indexOf(substring). Cela signifie que s'il existe plusieurs sous-chaînes " by " dans la chaîne initiale, toutes, sauf la dernière, sont considérées comme faisant partie du titre.by
3. Après avoir divisé la chaîne, la première sous-chaîne est définie comme titre et la seconde comme auteur (l'ordre est inversé par rapport à splitAtFirstComma()).

Résultats

Vous pouvez maintenant voir les effets de la fonction splitAtLastBy() en action. Essayez de l'exécuter en sélectionnant l'élément de menu Séparer le titre/l'auteur à la dernière occurrence de "par" après avoir sélectionné…

… une cellule :

...ou plusieurs cellules :

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 apprendrez à améliorer cet ensemble de données en remplissant les cellules vides avec des données issues d'une API publique.

10. Présentation : obtenir des données à partir d'API publiques

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

Vous ne pouvez pas obtenir les données manquantes en utilisant des opérations sur les chaînes de caractères des données dont vous disposez actuellement. Vous devrez alors obtenir les données manquantes auprès d'une autre source. Vous pouvez le faire dans Apps Script en demandant des informations à des API externes qui peuvent fournir des données supplémentaires.

Les API sont des interfaces de programmation d'application. Il s'agit d'un terme général, mais il s'agit essentiellement d'un service que vos programmes et scripts peuvent appeler pour demander des informations ou effectuer certaines actions. Dans cette section, vous allez appeler une API publique pour demander des informations sur les livres que vous pourrez insérer dans les cellules vides de votre feuille.

Cette section vous explique comment :

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

11. Récupérer des données externes avec UrlFetch

Avant de vous plonger dans le code qui fonctionne directement avec votre feuille de calcul, vous pouvez apprendre à utiliser les API externes dans Apps Script en créant une fonction d'assistance spécifiquement pour demander des informations sur les livres à partir de l'API Open Library publique.

Remarque  : Open Library est une archive Internet qui s'efforce de créer une page pour chaque livre publié. Pour en savoir plus, consultez la documentation de leur API sur leur site Web officiel.

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

Implémentation

Pour implémenter 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];
}

2. Enregistrez votre projet de script.

Remarque : Dans Apps Script, les noms de fonctions se terminant par _ (un trait de soulignement) sont considérés comme privés. D'autres scripts ne peuvent pas appeler ces fonctions lorsqu'elles sont présentes dans une bibliothèque ni par les clients lors de la communication entre le serveur et le client. Il s'agit de sujets avancés. Par conséquent, si vous savez qu'une fonction ne sera utilisée que par le script actuel, il est recommandé de terminer le nom d'une fonction par _.

Réviser le code

Ce code est divisé en deux sections principales :

1) La requête API

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

La variable url n'est qu'une chaîne d'URL, comme une adresse Web. Il pointe vers un emplacement sur les serveurs Open Library. Il inclut également trois paramètres (bibkeys, jscmd et format) qui indiquent aux serveurs Open Library les informations que vous demandez et la façon de structurer la réponse. Dans ce cas, vous fournissez le numéro ISBN du livre et demandez à ce que des informations détaillées soient renvoyées au format JSON.

Remarque : Chaque API publique définit ses propres formats pour effectuer des requêtes et renvoyer des résultats. Pour que votre code communique efficacement avec une API, vous devez respecter les formats qu'elle définit. Pour la bibliothèque Open Library, les formats de requête et de réponse sont décrits dans la documentation de l'API Open Library Books. Le format de réponse utilisé est JSON (JavaScript Object Notation), qui est simplement une façon particulière d'organiser les données. Le format JSON est semblable à HTML ou XML, mais il est généralement plus facile à lire pour les humains.

Une fois la chaîne d'URL créée, le code envoie une requête à l'emplacement et reçoit une réponse. Pour ce faire, exécutez 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. À la place, 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 incluent un code de réponse, des en-têtes HTTP et le contenu principal de la réponse. Les informations qui nous intéressent ici sont le contenu JSON principal. Le code doit donc l'extraire, puis analyser le JSON 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.

Remarque : Si vous pensez avoir besoin de vous familiariser avec JSON, consultez la documentation JavaScript sur l'utilisation de JSON ou la référence de la classe JSON.

Résultats

Maintenant que vous avez implémenté fetchBookData_(ISBN), d'autres fonctions de votre code peuvent trouver des informations sur n'importe quel livre à l'aide de son numéro ISBN. Vous utiliserez cette fonction pour remplir les cellules de votre feuille de calcul.

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

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

1. Identifiez les données manquantes concernant le titre et l'auteur dans la plage de données active.
2. Récupérez les données manquantes d'un livre spécifique 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 les cellules correspondantes.

Implémentation

Pour implémenter cette nouvelle fonction, procédez comme suit :

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);
}

2. Enregistrez votre projet de script.

Réviser le code

Ce code est divisé en trois sections :

1) Lisez 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 est utilisée pour conserver une copie locale des informations du livre de la feuille. Le code lira les informations de bookValues, utilisera l'API pour compléter les informations manquantes et réécrira ces valeurs dans la feuille de calcul.

Remarque : Cette disposition est différente de splitAtFirstComma() et splitAtLastBy(). Dans ces fonctions, le code n'examinait que les titres actuellement mis en surbrillance et leurs lignes. Ici, vous examinez l'intégralité de la feuille à l'aide de Spreadsheet.getDataRange().

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

Le code parcourt chaque ligne de bookValues pour trouver les titres ou auteurs manquants. Pour 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 comporte une valeur.
2. La cellule du titre ou de l'auteur de 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. Il devrait maintenant contenir les informations manquantes que vous souhaitez insérer dans la feuille.

Il ne reste plus qu'à ajouter les informations bookData à notre feuille de calcul. Il y a toutefois un bémol. Malheureusement, il arrive que les API publiques, comme l'API Open Library Book, ne disposent pas des informations que vous demandez ou rencontrent un autre problème qui les empêche de les fournir. Si vous partez du principe que chaque requête API aboutira, votre code ne sera pas assez robuste pour gérer les 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 de tenter de l'utiliser. Une fois le code bookData, il effectue une simple vérification pour s'assurer que bookData et bookData.details existent avant de tenter de les lire. Si l'un des deux est manquant, cela signifie que l'API ne disposait pas des données que vous recherchiez. 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 plantera pas.

3) Réécrire les informations mises à jour dans la feuille

La dernière partie du code comporte des vérifications similaires pour s'assurer que l'API a renvoyé les informations sur le titre et l'auteur. Le code ne met à jour le tableau bookValues que si la cellule d'origine du titre ou de l'auteur est vide et que l'API a renvoyé une valeur que vous pouvez y placer.

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

Résultats

Vous pouvez maintenant terminer de nettoyer les données de vos livres. Procédez comme suit :

1. Si vous ne l'avez pas encore fait, mettez en surbrillance la plage A2:A15 dans votre feuille, puis sélectionnez Liste de livres > Séparer le titre/l'auteur à la première virgule pour résoudre les problèmes de virgules.
2. Si vous ne l'avez pas encore fait, mettez en surbrillance la plage A2:A15 dans votre feuille, puis sélectionnez Liste de livres > Séparer le titre/l'auteur au dernier "par" pour résoudre les problèmes liés à "par".
3. Pour remplir toutes les cellules restantes, sélectionnez Liste de livres > Remplir les cellules vides pour les titres et les auteurs :

13. Conclusion

Bravo ! 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 vu comment importer des données dans Google Sheets à l'aide des services Apps Script et des API publiques. Il s'agit d'une opération courante dans le traitement des feuilles de calcul. Apps Script vous permet d'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 de feuille de calcul.

Avez-vous trouvé cet atelier de programmation utile ?

Oui Non

Ce que vous avez appris

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

Concepts clés :

1. onOpen() est un déclencheur simple utilisé pour créer des éléments de menu. Les déclencheurs simples sont soumis à différentes restrictions que vous devez respecter.
2. Lorsque vous lisez ou écrivez des données dans des cellules de feuille de calcul, il est recommandé de lire ou d'écrire une plage entière à la fois avec Range.getValues() ou Range.setValues(values). Ne lisez ni ne définissez chaque cellule individuellement, car cela prendra plus de temps.
3. Les noms de fonctions Apps Script se terminant par _ (un trait de soulignement) sont considérés comme privés. D'autres scripts ne peuvent pas appeler ces fonctions lorsqu'elles sont présentes dans une bibliothèque ni par les clients via la communication client-serveur. Si vous savez qu'une fonction ne sera utilisée que par le script actuel, il est recommandé de terminer le nom de la fonction par _.
4. Lorsque vous utilisez des données renvoyées par des API externes, validez-les avant de les utiliser. Cela réduit les erreurs dans votre code si l'API renvoie un résultat inattendu.

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.

Étape suivante

Le prochain atelier de programmation de cette playlist explique plus en détail comment mettre en forme les données dans une feuille de calcul.

Retrouvez le prochain atelier de programmation sur la page Formatage des données.