GSP240

Présentation

Pour analyser le big data, les data scientists disposent aujourd'hui d'une multitude d'outils, mais lesquels sont à même de convaincre vos dirigeants et les autres parties prenantes lorsqu'il s'agit d'expliquer et de justifier vos analyses ? Des chiffres bruts sur le papier ou dans une base de données ne suffiront pas. Pour vous aider dans cette étape finale, cet atelier Google Apps Script s'appuie sur deux plates-formes de développement Google : Workspace et Google Cloud.

Grâce aux outils pour les développeurs de Google Cloud, vous pouvez collecter et traiter vos données. Vous pouvez ensuite créer une présentation contenant des diapositives et des feuilles de calcul pour fournir à vos dirigeants et aux autres personnes concernées des analyses pertinentes et des insights perspicaces qui les impressionneront à coup sûr.

Dans cet atelier, vous apprendrez à utiliser l'API BigQuery de Google Cloud (en tant que service avancé Apps Script), ainsi que les services Apps Script intégrés pour Google Sheets et Google Slides.

Ce sera aussi l'occasion de vous pencher sur un scénario très proche d'une situation de la vie réelle. L'application utilisée présente des fonctionnalités et des API de Google Cloud. L'objectif : vous apprendre à exploiter Google Cloud et Workspace pour résoudre les problèmes difficiles auxquels peuvent être confrontés votre organisation et vos clients.

Points abordés

• Utiliser Google Apps Script avec différents services Google
• Utiliser BigQuery pour effectuer une analyse de big data
• Créer une feuille de calcul Google Sheets, y insérer des données et créer un graphique à partir des données de la feuille de calcul
• Transférer le graphique et les données de feuille de calcul dans différentes diapositives d'une présentation Google Slides

Préparation

Avant de cliquer sur le bouton "Démarrer l'atelier"

Lisez ces instructions. Les ateliers sont minutés, et vous ne pouvez pas les mettre en pause. Le minuteur, qui démarre lorsque vous cliquez sur Démarrer l'atelier, indique combien de temps les ressources Google Cloud resteront accessibles.

Cet atelier pratique vous permet de suivre vous-même les activités dans un véritable environnement cloud, et non dans un environnement de simulation ou de démonstration. Nous vous fournissons des identifiants temporaires pour vous connecter à Google Cloud le temps de l'atelier.

Pour réaliser cet atelier :

• vous devez avoir accès à un navigateur Internet standard (nous vous recommandons d'utiliser Chrome) ;

Remarque : Ouvrez une fenêtre de navigateur en mode incognito/navigation privée pour effectuer cet atelier. Vous éviterez ainsi les conflits entre votre compte personnel et le temporaire étudiant, qui pourraient entraîner des frais supplémentaires facturés sur votre compte personnel.

• vous disposez d'un temps limité ; une fois l'atelier commencé, vous ne pouvez pas le mettre en pause.

Remarque : Si vous possédez déjà votre propre compte ou projet Google Cloud, veillez à ne pas l'utiliser pour réaliser cet atelier afin d'éviter que des frais supplémentaires ne vous soient facturés.

Introduction

Google Apps Script et BigQuery

Google Apps Script est une plate-forme de développement Workspace fonctionnant à un niveau plus élevé que les API REST de Google. Ce service offre un environnement de développement et d'hébergement d'applications sans serveur accessible à des développeurs présentant différents niveaux de compétence. En bref, Apps Script est un environnement d'exécution JavaScript sans serveur, conçu pour l'automatisation, l'extension et l'intégration de Workspace.

Google Apps Script est une plate-forme JavaScript côté serveur semblable à Node.js. Toutefois, ce service repose sur une intégration étroite à Workspace et à d'autres services Google, plutôt que sur l'hébergement rapide d'applications basées sur des événements asynchrones. De plus, il propose un environnement de développement qui peut être très différent de ceux que vous connaissez. Avec Apps Script, vous pouvez :

• développer dans un éditeur de code basé sur un navigateur, ou bien localement à l'aide de clasp, l'outil de déploiement de ligne de commande pour Apps Script ;
• coder dans une version spécialisée de JavaScript, personnalisée pour accéder à Workspace ainsi qu'à d'autres services fournis par Google ou par des tiers (via les services Apps Script URLfetch et Jdbc) ;
• vous éviter l'étape du codage des autorisations, celle-ci étant assurée par Apps Script ;
• vous décharger de l'hébergement de votre application, celle-ci résidant et s'exécutant sur des serveurs Google dans le cloud.

Remarque : Pour en savoir plus sur Apps Script, consultez la documentation officielle, qui vous propose une présentation accompagnée de guides de démarrage rapide, ainsi que des tutoriels et des vidéos.

Pour communiquer avec d'autres technologies Google, Apps Script propose deux méthodes :

• Le service intégré
• Le service avancé

Un service intégré fournit des méthodes de haut niveau que vous pouvez utiliser pour accéder à Workspace ou aux données sur les produits Google. Il peut aussi fournir d'autres méthodes utilitaires. Un service avancé est simplement un wrapper léger pour les API Workspace ou REST de Google. Les services avancés fournissent une couverture complète des API REST et offrent souvent plus de possibilités que les services intégrés. Ils requièrent cependant un codage plus complexe (tout en restant plus faciles à utiliser que l'API REST elle-même).

Avant d'utiliser les services avancés, vous devez aussi les activer pour un projet de script. Lorsque cela est possible, il est préférable d'opter pour un service intégré, car ce type de service est plus simple à utiliser et plus puissant que les services avancés. Toutefois, certaines API Google ne comportent pas de services intégrés, auquel cas le service avancé est la seule option. BigQuery, par exemple, ne propose pas de service intégré. Un service avancé est toutefois disponible, ce qui est toujours mieux que pas de service du tout.

Remarque : Si vous ne connaissez pas encore BigQuery, il s'agit d'un service Google Cloud qui permet d'exécuter des requêtes simples (ou complexes) sur d'immenses corpus de données (de l'ordre de plusieurs téraoctets) et d'obtenir des résultats en quelques secondes, et non au bout de plusieurs heures ou plusieurs jours.

Accéder à Google Sheets et Slides depuis Apps Script

BigQuery n'est disponible qu'en tant que service avancé Apps Script. Cependant, Google Sheets et Slides comportent des services Apps Script intégrés et des services avancés, qui permettent, par exemple, d'accéder à des fonctionnalités fournies uniquement dans l'API et non disponibles en tant que fonctionnalités intégrées. Dans la mesure du possible, choisissez un service intégré plutôt qu'un service avancé équivalent, car le service intégré fournit des constructions de haut niveau et des appels pratiques qui simplifient le développement.

Remarque : Un peu de révision avant de passer au code ? Consultez les sections Service Sheets et Service Slides.

Tâche 1 : Interroger BigQuery et consigner les résultats dans Sheets

Avec cette première tâche, vous allez remplir une grande partie des objectifs de cet atelier. À la fin de cette section, vous aurez quasiment réalisé la moitié de l'atelier.

Vous allez maintenant faire ce qui suit :

• Démarrer un nouveau projet Google Apps Script
• Activer l'accès au service avancé BigQuery
• Accéder à l'éditeur de développement et saisir le code source de l'application
• Réaliser l'ensemble du processus d'autorisation d'application (OAuth2)
• Exécuter l'application qui envoie une requête à BigQuery
• Afficher une nouvelle feuille de calcul Google Sheets créée à partir des résultats de BigQuery

Créer un projet Apps Script

1. Accédez à script.google.com et créez un projet Apps Script. Pour cet atelier, cliquez sur le lien Créer un script Apps Script.

2. L'éditeur de code Apps Script s'affiche :

3. Attribuez un nom au projet : cliquez sur le nom de projet en haut de la page ("Projet sans titre" dans la figure ci-dessus).

4. Dans la boîte de dialogue Renommer le projet, attribuez le nom de votre choix au projet ("BigQuery", "Sheets" ou "démonstration Slides", par exemple), puis cliquez sur Renommer.

Activer le service avancé BigQuery

Activez le service avancé BigQuery pour le nouveau projet et activez l'API BigQuery.

1. Cliquez sur l'icône Ajouter un service à côté de Services.

2. Dans la boîte de dialogue Ajouter un service, sélectionnez les services et API concernés.

3. Accédez à la console Cloud, puis sélectionnez Menu de navigation > API et services > Bibliothèque.

4. Saisissez ou collez API BigQuery dans le champ de recherche, puis cliquez sur API BigQuery.

5. Cliquez sur Activer pour activer l'API BigQuery si nécessaire.

6. Revenez au projet. La boîte de dialogue Ajouter un service doit encore être ouverte.

7. Sélectionnez API BigQuery, puis cliquez sur Ajouter pour fermer la boîte de dialogue.

Saisir et exécuter le code d'application

Vous êtes maintenant prêt à saisir le code d'application, à suivre la procédure d'autorisation et à découvrir le premier résultat concret de cette application.

1. Copiez le code affiché dans l'encadré ci-dessous et collez-le dans l'éditeur de code, à la place du contenu actuel :

/** * Copyright 2018 Google LLC * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0. * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ // Filename for data results var QUERY_NAME = "Most common words in all of Shakespeare's works"; // Replace this value with your Google Cloud API project ID var PROJECT_ID = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup'); /** * Runs a BigQuery query; puts results into Sheet. You must enable * the BigQuery advanced service before you can run this code. * @see http://developers.google.com/apps-script/advanced/bigquery#run_query * @see http://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs * * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results * @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet */ function runQuery() { // Replace sample with your own BigQuery query. var request = { query: 'SELECT ' + 'LOWER(word) AS word, ' + 'SUM(word_count) AS count ' + 'FROM [bigquery-public-data:samples.shakespeare] ' + 'GROUP BY word ' + 'ORDER BY count ' + 'DESC LIMIT 10' }; var queryResults = BigQuery.Jobs.query(request, PROJECT_ID); var jobId = queryResults.jobReference.jobId; // Wait for BQ job completion (with exponential backoff). var sleepTimeMs = 500; while (!queryResults.jobComplete) { Utilities.sleep(sleepTimeMs); sleepTimeMs *= 2; queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId); } // Get all results from BigQuery. var rows = queryResults.rows; while (queryResults.pageToken) { queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, { pageToken: queryResults.pageToken }); rows = rows.concat(queryResults.rows); } // Return null if no data returned. if (!rows) { return Logger.log('No rows returned.'); } // Create the new results spreadsheet. var spreadsheet = SpreadsheetApp.create(QUERY_NAME); var sheet = spreadsheet.getActiveSheet(); // Add headers to Sheet. var headers = queryResults.schema.fields.map(function(field) { return field.name.toUpperCase(); }); sheet.appendRow(headers); // Append the results. var data = new Array(rows.length); for (var i = 0; i < rows.length; i++) { var cols = rows[i].f; data[i] = new Array(cols.length); for (var j = 0; j < cols.length; j++) { data[i][j] = cols[j].v; } } // Start storing data in row 2, col 1 var START_ROW = 2; // skip header row var START_COL = 1; sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); }

2. Enregistrez le fichier que vous venez de créer : cliquez sur l'icône Enregistrer le projet dans la barre de menu ou appuyez sur Ctrl+S.

3. Pour renommer le fichier, cliquez sur les trois points à côté du nom de fichier, puis cliquez sur Renommer.

4. Remplacez le nom du fichier par bq-sheets-slides.gs, puis appuyez sur "Entrée".

À quoi sert ce code ? Vous savez qu'il interroge BigQuery et écrit les résultats dans une nouvelle feuille Google Sheets, mais en quoi consiste cette requête ?

5. Examinez le code de requête dans la fonction runQuery() :

SELECT LOWER(word) AS word, SUM(word_count) AS count FROM [bigquery-public-data:samples.shakespeare] GROUP BY word ORDER BY count DESC LIMIT 10

Cette requête analyse les œuvres de Shakespeare, qui font partie de l'ensemble de données public de BigQuery, et indique les 10 mots les plus fréquents dans ses ouvrages, triés par ordre décroissant. Il suffit d'imaginer à quel point une telle recherche serait fastidieuse si on devait l'effectuer manuellement pour se faire une idée de l'utilité de BigQuery.

Vous avez presque terminé ! Vous avez besoin d'un ID de projet valide pour définir la variable PROJECT_ID dans le début du fichier bq-sheets-slides.gs.

6. Remplacez <YOUR_PROJECT_ID> par l'ID de votre projet, qui est indiqué dans le panneau de gauche.

Voici un exemple de code contenant un ID de projet. La valeur PROJECT_ID que vous allez utiliser sera différente.

Exemple de code :

// Filename for data results var QUERY_NAME = "Most common words in all of Shakespeare's works"; // Replace this value with your Google Cloud API project ID var PROJECT_ID = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup'); Remarque : Dans cet extrait de code, le rôle de l'instruction if est d'empêcher l'application de continuer à s'exécuter si l'ID de projet est manquant. Remarque : Si le sélecteur de menu ne répond pas, actualisez la page.

7. Enregistrez le fichier, puis exécutez votre code en cliquant sur l'option Exécuter dans la barre de menu.

8. Cliquez ensuite sur Examiner les autorisations.

Remarque : Si vous recevez le message d'erreur Exception: Service BigQuery API has not been enabled for your Apps (Exception : Le service d'API BigQuery n'a pas été activé pour vos applications), supprimez le service d'API BigQuery et ajoutez-le à nouveau.

9. Dans la boîte de dialogue "Sélectionnez un compte dans qwiklabs.net", cliquez sur votre nom d'utilisateur, puis sur Autoriser.

Remarque : Une fois que vous avez autorisé l'application, vous n'avez plus besoin de répéter ce processus lors des exécutions suivantes. Cette boîte de dialogue ne s'affichera de nouveau que lorsque vous atteindrez la section "Placer les données des résultats dans une présentation" dans la suite de l'atelier. Vous serez alors invité à autoriser la création et la gestion de présentations Google Slides.

10. Un petit message s'affiche en haut pendant que la fonction s'exécute.

Il disparaît une fois que la fonction est exécutée. Si vous ne le voyez pas, cela indique probablement que l'exécution de la fonction est terminée.

11. Accédez à votre espace Google Drive et recherchez une nouvelle feuille de calcul Google Sheets nommée Most common words in all of Shakespeare's works (Mots les plus récurrents dans l'ensemble de l'œuvre de Shakespeare) ou portant le nom que vous avez attribué à la variable QUERY_NAME.

12. Ouvrez la feuille de calcul. Vous verrez normalement 10 lignes de mots avec leur nombre d'occurrences respectif, le tout trié par ordre décroissant.

Cliquez sur Vérifier ma progression pour valider l'objectif. Interroger BigQuery et consigner les résultats dans Sheets

Résumé

Récapitulons ce que vous venez de faire. Vous avez exécuté un code qui a interrogé toute l'œuvre de Shakespeare. Cela ne représente peut-être pas un volume astronomique de données, mais une telle quantité de textes dépasse de loin celle que nous pourrions examiner nous-mêmes si nous devions lire tous les mots de chaque pièce, puis les trier par ordre décroissant en fonction du nombre d'occurrences. Non seulement vous avez demandé à BigQuery de réaliser cette tâche à votre place, mais vous avez aussi utilisé le service intégré d'Apps Script pour Google Sheets afin d'organiser les données et de les rendre facilement exploitables.

Vous avez toujours la possibilité de tester la requête dans la console BigQuery avant de l'exécuter dans Apps Script. Les développeurs ont accès à l'interface utilisateur de BigQuery.

1. Accédez à la console Cloud, puis sélectionnez Menu de navigation > BigQuery.

2. Cliquez sur OK dans la boîte de dialogue "Bienvenue sur BigQuery dans la console Cloud".

La console BigQuery s'ouvre.

3. Saisissez votre code dans l'éditeur de requête, puis cliquez sur Exécuter :

SELECT LOWER(word) AS word, sum(word_count) AS count FROM `bigquery-public-data.samples.shakespeare` GROUP BY word ORDER BY count DESC LIMIT 10

Tâche 2 : Créer un graphique dans Google Sheets

Revenez à l'éditeur de script. Pour le moment, vous avez codé une application qui a analysé les œuvres de Shakespeare, et a ensuite trié puis présenté les résultats dans Sheets. Dans ce code, la fonction runQuery() communique avec BigQuery et envoie ses résultats dans une feuille Google Sheets. Vous allez maintenant ajouter du code pour créer un graphique à partir de ces données. Dans cette section, vous allez créer une fonction createColumnChart() qui appelle la méthode newChart() de Sheets pour créer un graphique à partir des données.

La fonction createColumnChart() extrait la feuille contenant les données et demande la création d'un graphique sous forme de colonnes à partir de toutes les données disponibles. La plage de données commence à la cellule A2, car la première ligne contient des en-têtes de colonne et non des données.

1. Créez le graphique : ajoutez la fonction createColumnChart() à bq-sheets-slides.gs juste après runQuery(), après la dernière ligne de code :

/** * Uses spreadsheet data to create columnar chart. * @param {Spreadsheet} Spreadsheet containing results data * @returns {EmbeddedChart} visualizing the results * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart */ function createColumnChart(spreadsheet) { // Retrieve the populated (first and only) Sheet. var sheet = spreadsheet.getSheets()[0]; // Data range in Sheet is from cell A2 to B11 var START_CELL = 'A2'; // skip header row var END_CELL = 'B11'; // Place chart on Sheet starting on cell E5. var START_ROW = 5; // row 5 var START_COL = 5; // col E var OFFSET = 0; // Create & place chart on the Sheet using above params. var chart = sheet.newChart() .setChartType(Charts.ChartType.COLUMN) .addRange(sheet.getRange(START_CELL + ':' + END_CELL)) .setPosition(START_ROW, START_COL, OFFSET, OFFSET) .build(); sheet.insertChart(chart); }

2. Renvoyez la feuille de calcul : dans le code ci-dessus, il est nécessaire d'indiquer l'objet spreadsheet dans createColumnChart(). Vous devez donc modifier l'application pour qu'elle renvoie l'objet spreadsheet afin de le transmettre à createColumnChart(). Une fois que la création de la feuille de calcul Google Sheets est consignée, renvoyez l'objet à la fin de runQuery().

3. Remplacez la dernière ligne (commençant par Logger.log) par les lignes suivantes :

Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // Return the spreadsheet object for later use. return spreadsheet; }

4. Initialisez la fonction createBigQueryPresentation() : la séparation logique des fonctionnalités BigQuery et chart-creation est une excellente idée. Créez une fonction createBigQueryPresentation() pour lancer l'application, en appelant ces deux éléments et createColumnChart(). Le code ajouté doit ressembler à ceci :

/** * Runs a BigQuery query, adds data and a chart in a Sheet. */ function createBigQueryPresentation() { var spreadsheet = runQuery(); createColumnChart(spreadsheet); }

5. Placez la fonction createBigQueryPresentation() juste après le bloc de code suivant :

// Filename for data results var QUERY_NAME = "Most common words in all of Shakespeare's works"; // Replace this value with your Google Cloud API project ID var PROJECT_ID = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup');

6. Rendez le code plus facile à réutiliser : ci-dessus, vous avez effectué deux étapes importantes ; vous avez renvoyé l'objet spreadsheet et vous avez créé une fonction d'initialisation. Que se passe-t-il si un collègue veut réutiliser runQuery(), mais sans que l'URL soit consignée ?

Pour faciliter l'utilisation générale de runQuery(), déplacez cette ligne de journal. Une idée du meilleur emplacement ? Si vous avez répondu createBigQueryPresentation(), vous avez raison !

Une fois la ligne de journal déplacée, le code doit ressembler à ceci :

/** * Runs a BigQuery query, adds data and a chart in a Sheet. */ function createBigQueryPresentation() { var spreadsheet = runQuery(); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); createColumnChart(spreadsheet); }

Une fois les modifications ci-dessus effectuées, le script bq-sheets-slides.js doit se présenter comme suit (à l'exception de PROJECT_ID) :

/** * Copyright 2018 Google LLC * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0. * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ // Filename for data results var QUERY_NAME = "Most common words in all of Shakespeare's works"; // Replace this value with your Google Cloud API project ID var PROJECT_ID = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup'); /** * Runs a BigQuery query, adds data and a chart in a Sheet. */ function createBigQueryPresentation() { var spreadsheet = runQuery(); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); createColumnChart(spreadsheet); } /** * Runs a BigQuery query; puts results into Sheet. You must enable * the BigQuery advanced service before you can run this code. * @see http://developers.google.com/apps-script/advanced/bigquery#run_query * @see http://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs * * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results * @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet */ function runQuery() { // Replace sample with your own BigQuery query. var request = { query: 'SELECT ' + 'LOWER(word) AS word, ' + 'SUM(word_count) AS count ' + 'FROM [bigquery-public-data:samples.shakespeare] ' + 'GROUP BY word ' + 'ORDER BY count ' + 'DESC LIMIT 10' }; var queryResults = BigQuery.Jobs.query(request, PROJECT_ID); var jobId = queryResults.jobReference.jobId; // Wait for BQ job completion (with exponential backoff). var sleepTimeMs = 500; while (!queryResults.jobComplete) { Utilities.sleep(sleepTimeMs); sleepTimeMs *= 2; queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId); } // Get all results from BigQuery. var rows = queryResults.rows; while (queryResults.pageToken) { queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, { pageToken: queryResults.pageToken }); rows = rows.concat(queryResults.rows); } // Return null if no data returned. if (!rows) { return Logger.log('No rows returned.'); } // Create the new results spreadsheet. var spreadsheet = SpreadsheetApp.create(QUERY_NAME); var sheet = spreadsheet.getActiveSheet(); // Add headers to Sheet. var headers = queryResults.schema.fields.map(function(field) { return field.name.toUpperCase(); }); sheet.appendRow(headers); // Append the results. var data = new Array(rows.length); for (var i = 0; i < rows.length; i++) { var cols = rows[i].f; data[i] = new Array(cols.length); for (var j = 0; j < cols.length; j++) { data[i][j] = cols[j].v; } } // Start storing data in row 2, col 1 var START_ROW = 2; // skip header row var START_COL = 1; sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // Return the spreadsheet object for later use. return spreadsheet; } /** * Uses spreadsheet data to create columnar chart. * @param {Spreadsheet} Spreadsheet containing results data * @returns {EmbeddedChart} visualizing the results * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart */ function createColumnChart(spreadsheet) { // Retrieve the populated (first and only) Sheet. var sheet = spreadsheet.getSheets()[0]; // Data range in Sheet is from cell A2 to B11 var START_CELL = 'A2'; // skip header row var END_CELL = 'B11'; // Place chart on Sheet starting on cell E5. var START_ROW = 5; // row 5 var START_COL = 5; // col E var OFFSET = 0; // Create & place chart on the Sheet using above params. var chart = sheet.newChart() .setChartType(Charts.ChartType.COLUMN) .addRange(sheet.getRange(START_CELL + ':' + END_CELL)) .setPosition(START_ROW, START_COL, OFFSET, OFFSET) .build(); sheet.insertChart(chart); }

7. Enregistrez le fichier.

8. Puis, dans la barre de menu, cliquez sur runQuery et sélectionnez createBigQueryPresentation dans le menu déroulant.

9. Ensuite, cliquez sur Exécuter.

Après avoir exécuté la requête, vous accédez à une autre feuille Google Sheets dans Google Drive, mais cette fois, un graphique s'affiche à côté des données :

Tâche 3 : Placer les données des résultats dans une présentation

Dans la dernière partie de l'atelier, vous allez créer une autre présentation Google Slides, renseigner le titre et le sous-titre de la diapositive de titre, puis ajouter deux nouvelles diapositives, l'une reprenant chacune des cellules de données et l'autre pour le graphique.

1. Créez une présentation : commencez par créer une présentation, puis ajoutez un titre et un sous-titre à la diapositive de titre par défaut qui est fournie avec toutes les nouvelles présentations. Toutes les actions effectuées sur la présentation sont exécutées via la fonction createSlidePresentation(), que vous ajoutez à bq-sheets-slides.gs juste après le code de fonction createColumnChart() :

/** * Create presentation with spreadsheet data & chart * @param {Spreadsheet} Spreadsheet with results data * @param {EmbeddedChart} Sheets chart to embed on slide * @returns {Presentation} Slide deck with results */ function createSlidePresentation(spreadsheet, chart) { // Create the new presentation. var deck = SlidesApp.create(QUERY_NAME); // Populate the title slide. var [title, subtitle] = deck.getSlides()[0].getPageElements(); title.asShape().getText().setText(QUERY_NAME); subtitle.asShape().getText().setText('via GCP and G Suite APIs:\n' + 'Google Apps Script, BigQuery, Sheets, Slides');

2. Ajoutez le tableau de données : l'étape suivante à effectuer dans createSlidePresentation() consiste à importer les données des cellules à partir de Google Sheets dans notre nouvelle présentation. Ajoutez cet extrait de code à la fonction createSlidePresentation() :

// Data range to copy is from cell A1 to B11 var START_CELL = 'A1'; // include header row var END_CELL = 'B11'; // Add the table slide and insert an empty table on it of // the dimensions of the data range; fails if Sheet empty. var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK); var sheetValues = spreadsheet.getSheets()[0].getRange( START_CELL + ':' + END_CELL).getValues(); var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length); // Populate the table with spreadsheet data. for (var i = 0; i < sheetValues.length; i++) { for (var j = 0; j < sheetValues[0].length; j++) { table.getCell(i, j).getText().setText(String(sheetValues[i][j])); } }

3. Importez le graphique : la dernière étape à effectuer dans createSlidePresentation() consiste à créer une diapositive supplémentaire, importer le graphique à partir de notre feuille de calcul et renvoyer l'objet Presentation. Ajoutez ce dernier extrait de code à la fonction :

// Add a chart slide and insert the chart on it. var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK); chartSlide.insertSheetsChart(chart); // Return the presentation object for later use. return deck; }

4. Renvoyez le graphique : maintenant que la fonction finale est terminée, réexaminez sa signature. Oui, createSlidePresentation() requiert à la fois une feuille de calcul et un objet graphique. Vous avez déjà modifié runQuery() pour renvoyer l'objet Spreadsheet. Nous devons maintenant modifier createColumnChart() de la même manière pour renvoyer l'objet graphique (EmbeddedChart). Pour ce faire, revenez à votre application et ajoutez une dernière ligne à la fin de createColumnChart() :

// Return chart object for later use return chart; }

5. Modifiez createBigQueryPresentation() : comme createColumnChart() renvoie le graphique, vous devez enregistrer celui-ci dans une variable, puis transmettre la feuille de calcul et le graphique à createSlidePresentation(). Puisque vous consignez l'URL de la feuille de calcul que vous venez de créer, vous pouvez également consigner l'URL de la nouvelle présentation Slides. Remplacez ce bloc de code :

/** * Runs a BigQuery query, adds data and a chart in a Sheet. */ function createBigQueryPresentation() { var spreadsheet = runQuery(); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); createColumnChart(spreadsheet); }

Par les lignes de code suivantes :

/** * Runs a BigQuery query, adds data and a chart in a Sheet, * and adds the data and chart to a new slide presentation. */ function createBigQueryPresentation() { var spreadsheet = runQuery(); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); var chart = createColumnChart(spreadsheet); var deck = createSlidePresentation(spreadsheet, chart); Logger.log('Results slide deck created: %s', deck.getUrl()); }

Après toutes ces modifications, le script bq-sheets-slides.gs doit maintenant se présenter comme suit, excepté pour l'identifiant PROJECT_ID :

bq-sheets-slides.gs - final version

// Filename for data results var QUERY_NAME = "Most common words in all of Shakespeare's works"; // Replace this value with your Google Cloud API project ID var PROJECT_ID = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup'); /** * Runs a BigQuery query; puts results into Sheet. You must enable * the BigQuery advanced service before you can run this code. * @see http://developers.google.com/apps-script/advanced/bigquery#run_query * @see http://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs * * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results * @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet */ function runQuery() { // Replace sample with your own BigQuery query. var request = { query: 'SELECT ' + 'LOWER(word) AS word, ' + 'SUM(word_count) AS count ' + 'FROM [bigquery-public-data:samples.shakespeare] ' + 'GROUP BY word ' + 'ORDER BY count ' + 'DESC LIMIT 10' }; var queryResults = BigQuery.Jobs.query(request, PROJECT_ID); var jobId = queryResults.jobReference.jobId; // Wait for BQ job completion (with exponential backoff). var sleepTimeMs = 500; while (!queryResults.jobComplete) { Utilities.sleep(sleepTimeMs); sleepTimeMs *= 2; queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId); } // Get all results from BigQuery. var rows = queryResults.rows; while (queryResults.pageToken) { queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, { pageToken: queryResults.pageToken }); rows = rows.concat(queryResults.rows); } // Return null if no data returned. if (!rows) { return Logger.log('No rows returned.'); } // Create the new results spreadsheet. var spreadsheet = SpreadsheetApp.create(QUERY_NAME); var sheet = spreadsheet.getActiveSheet(); // Add headers to Sheet. var headers = queryResults.schema.fields.map(function(field) { return field.name.toUpperCase(); }); sheet.appendRow(headers); // Append the results. var data = new Array(rows.length); for (var i = 0; i < rows.length; i++) { var cols = rows[i].f; data[i] = new Array(cols.length); for (var j = 0; j < cols.length; j++) { data[i][j] = cols[j].v; } } // Start storing data in row 2, col 1 var START_ROW = 2; // skip header row var START_COL = 1; sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data); // Return the spreadsheet object for later use. return spreadsheet; } /** * Uses spreadsheet data to create columnar chart. * @param {Spreadsheet} Spreadsheet containing results data * @returns {EmbeddedChart} visualizing the results * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart */ function createColumnChart(spreadsheet) { // Retrieve the populated (first and only) Sheet. var sheet = spreadsheet.getSheets()[0]; // Data range in Sheet is from cell A2 to B11 var START_CELL = 'A2'; // skip header row var END_CELL = 'B11'; // Place chart on Sheet starting on cell E5. var START_ROW = 5; // row 5 var START_COL = 5; // col E var OFFSET = 0; // Create & place chart on the Sheet using above params. var chart = sheet.newChart() .setChartType(Charts.ChartType.COLUMN) .addRange(sheet.getRange(START_CELL + ':' + END_CELL)) .setPosition(START_ROW, START_COL, OFFSET, OFFSET) .build(); sheet.insertChart(chart); // Return the chart object for later use. return chart; } /** * Create presentation with spreadsheet data & chart * @param {Spreadsheet} Spreadsheet with results data * @param {EmbeddedChart} Sheets chart to embed on slide * @returns {Presentation} Returns a slide deck with results * @see http://developers.google.com/apps-script/reference/slides/presentation */ function createSlidePresentation(spreadsheet, chart) { // Create the new presentation. var deck = SlidesApp.create(QUERY_NAME); // Populate the title slide. var [title, subtitle] = deck.getSlides()[0].getPageElements(); title.asShape().getText().setText(QUERY_NAME); subtitle.asShape().getText().setText('via GCP and G Suite APIs:\n' + 'Google Apps Script, BigQuery, Sheets, Slides'); // Data range to copy is from cell A1 to B11 var START_CELL = 'A1'; // include header row var END_CELL = 'B11'; // Add the table slide and insert an empty table on it of // the dimensions of the data range; fails if Sheet empty. var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK); var sheetValues = spreadsheet.getSheets()[0].getRange( START_CELL + ':' + END_CELL).getValues(); var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length); // Populate the table with spreadsheet data. for (var i = 0; i < sheetValues.length; i++) { for (var j = 0; j < sheetValues[0].length; j++) { table.getCell(i, j).getText().setText(String(sheetValues[i][j])); } } // Add a chart slide and insert the chart on it. var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK); chartSlide.insertSheetsChart(chart); // Return the presentation object for later use. return deck; } /** * Runs a BigQuery query, adds data and a chart in a Sheet, * and adds the data and chart to a new slide presentation. */ function createBigQueryPresentation() { var spreadsheet = runQuery(); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); var chart = createColumnChart(spreadsheet); var deck = createSlidePresentation(spreadsheet, chart); Logger.log('Results slide deck created: %s', deck.getUrl()); }

6. Enregistrez et exécutez de nouveau createBigQueryPresentation(). Avant de l'exécuter, vous serez invité à fournir un ensemble supplémentaire d'autorisations pour afficher et gérer vos présentations Google Slides.
7. Accédez à Mon Drive. En plus de la feuille Google Sheets créée, vous devriez aussi y trouver une nouvelle présentation Slides contenant trois diapositives (titre, table de données, graphique de données), comme illustré ci-dessous :

Félicitations !

Vous venez de créer une application qui exploite les deux faces de Google Cloud : vous avez exécuté une requête qui interroge un ensemble de données BigQuery public, crée une feuille Google Sheets pour stocker les résultats, ajoute un graphique à partir des données extraites, et enfin crée une présentation Google Slides contenant les résultats et un graphique.

Voilà pour le côté technique. Mais plus globalement, vous avez transformé une analyse big data en résultats que vous pouvez présenter à des parties prenantes, tout cela grâce à du code et de façon entièrement automatisée. Vous pouvez maintenant vous inspirer de cet atelier, en le personnalisant pour vos propres projets.

Terminer votre quête

Cet atelier d'auto-formation fait partie des quêtes Workspace: Integrations for Data et BigQuery Basics for Data Analysts. Une quête est une série d'ateliers associés qui constituent un parcours de formation. Si vous terminez une quête, vous obtenez un badge attestant de votre réussite. Vous pouvez rendre publics les badges que vous recevez et ajouter leur lien dans votre CV en ligne ou sur vos comptes de réseaux sociaux. Inscrivez-vous à n'importe quelle quête contenant cet atelier pour obtenir immédiatement les crédits associés. Découvrez toutes les quêtes disponibles dans le catalogue Google Cloud Skills Boost.

Vous cherchez un atelier challenge pratique pour démontrer vos compétences BigQuery et valider vos connaissances ? À la fin de cette quête, terminez cet atelier challenge supplémentaire pour recevoir un badge numérique Google Cloud exclusif.

En savoir plus

Le code figurant dans cet atelier est également disponible dans GitHub. Nous nous efforçons de synchroniser continuellement cet atelier avec le dépôt. Vous trouverez ci-dessous d'autres ressources qui vous permettront d'approfondir les aspects abordés dans cet atelier et de découvrir d'autres moyens d'accéder aux outils Google pour les développeurs de manière programmatique.

Documentation

• Site de documentation Google Apps Script
• Service Spreadsheet Apps Script
• Service Slides Apps Script
• Service avancé BigQuery Apps Script

Vidéos similaires et générales

• Un autre secret Google (Apps) (vidéo de présentation d'Apps Script)
• Accéder à Google Maps à partir d'une feuille de calcul (vidéo)
• Autres vidéos dans la bibliothèque de vidéos Google Apps Script
• Série de vidéos Launchpad Online
• Série de vidéos G Suite Dev Show

Actualités associées et générales

• Intégration de BigQuery à Google Drive (2016 : lien 1, lien 2)
• Blog Google Developers
• Blog Google Cloud Platform
• Blog sur le big data et le machine learning de Google Cloud
• Compte Twitter de Google Developers (@GoogleDevs)
• Blog des développeurs Workspace
• Compte Twitter des développeurs Workspace (@GSuiteDevs)
• Newsletter mensuelle des développeurs Workspace
• Centre de formation Google Workspace

Dernière mise à jour du manuel : 6 mars 2023

Dernier test de l'atelier : 6 mars 2023

Copyright 2024 Google LLC Tous droits réservés. Google et le logo Google sont des marques de Google LLC. Tous les autres noms d'entreprises et de produits peuvent être des marques des entreprises auxquelles ils sont associés.