Pour analyser le big data, les data scientists disposent aujourd'hui d'une multitude d'outils, mais au final, ne devez-vous pas toujours justifier ces résultats auprès de la direction ? Il est difficile de présenter des chiffres bruts sur papier ou dans une base de données aux principales parties prenantes. Pour vous aider dans cette étape finale, cet atelier de programmation Google Apps Script de niveau intermédiaire s'appuie sur deux plates-formes de développement Google : G Suite et Google Cloud Platform (GCP).
Les outils pour les développeurs de Google Cloud vous permettent d'effectuer une analyse approfondie des données, puis d'intégrer les résultats dans une feuille de calcul et de générer une présentation avec ces données. Vous disposez ainsi d'un support plus adapté pour présenter les résultats à la direction. Dans cet atelier de programmation, vous allez apprendre à utiliser l'API BigQuery de GCP (en tant que service avancé Apps Script), ainsi que les services Apps Script intégrés pour Google Sheets et Google Slides.
Motivation/Art antérieur
L'exemple d'application de cet atelier de programmation s'inspire de ces autres exemples de code :
- L'exemple d'application du service BigQuery Google Apps Script et Open Source sur GitHub
- L'application exemple présentée dans la vidéo pour les développeurs Générer des diapositives à partir de données de feuille de calcul et publiée dans cet article de blog
- Exemple d'application présenté dans l'atelier de programmation de l'API Google Slides
Bien que l'application exemple de l'atelier de programmation de l'API Slides comporte également BigQuery et Slides, elle diffère de l'application exemple de cet atelier de programmation de plusieurs manières :
- Application Node.js et application Apps Script
- Utilise des API REST alors que nous utilisons des services Apps Script
- Utilise Google Drive, mais pas Google Sheets, alors que cette application utilise Sheets, mais pas Drive
Pour cet atelier de programmation, nous avons souhaité rassembler autant de technologies que possible dans une seule application, tout en présentant les fonctionnalités et les API de Google Cloud d'une manière qui ressemble davantage à un cas d'utilisation réel. L'objectif est de vous inciter à faire preuve d'imagination et à envisager d'exploiter GCP et G Suite pour résoudre les problèmes difficiles auxquels peuvent être confrontés votre organisation ou vos clients.
Points abordés
- Utiliser Google Apps Script avec différents services Google (GCP et G Suite)
- Utiliser Google BigQuery pour effectuer une analyse big data
- Créer une feuille de calcul Google Sheets et y insérer des données
- Créer un graphique dans Sheets
- Transférer des graphiques et des données de Sheets dans une présentation Google Slides
Prérequis
- Accès à Internet et à un navigateur Web
- Un compte Google (l'approbation de l'administrateur peut être nécessaire pour les comptes G Suite)
- Compétences de base en JavaScript
- Avoir des connaissances en développement Apps Script (utile, mais pas obligatoire)
Comment allez-vous utiliser cet atelier de programmation/tutoriel ?
Comment évalueriez-vous votre expérience avec les outils et API pour les développeurs G Suite ?
Comment évalueriez-vous votre expérience avec Apps Script en particulier ?
Comment évalueriez-vous votre niveau d'expérience avec les outils et API pour les développeurs GCP ?
Maintenant que vous savez de quoi il s'agit, qu'allez-vous faire exactement ?
- Faire fonctionner un exemple Apps Script-BigQuery existant
- À partir de cet exemple, découvrez comment envoyer une requête à BigQuery et obtenir ses résultats.
- Créer une feuille de calcul Google Sheets et y importer les résultats de BigQuery
- Modifiez légèrement le code pour modifier légèrement les données renvoyées et ajoutées à la feuille.
- Utiliser le service Sheets dans Apps Script pour créer un graphique pour les données issues de BigQuery
- Utiliser le service Slides pour créer une présentation
- Ajoutez un titre et un sous-titre à la diapositive de titre par défaut créée automatiquement pour toutes les nouvelles présentations.
- Créez une diapositive avec un tableau de données, puis importez-y les cellules de données de la feuille de calcul.
- Ajoutez une autre diapositive et insérez-y le graphique de la feuille de calcul.
Commençons par quelques informations générales sur Apps Script, BigQuery, Sheets et Slides.
Google Apps Script et BigQuery
Google Apps Script est une plate-forme de développement G Suite 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 G Suite.
Google Apps Script est une plate-forme JavaScript côté serveur semblable à Node.js. Cependant, elle repose sur l'intégration étroite à G Suite et à d'autres services Google, plutôt que sur l'hébergement rapide d'applications basées sur des événements asynchrones. Elle propose aussi 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 à G Suite, ainsi qu'à d'autres services fournis par Google ou par des tiers (via les services Apps Script
URLfetchouJdbc) ; - 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 : L'apprentissage d'Apps Script n'entre pas dans le cadre de cet atelier de programmation. Vous disposez d'une multitude de ressources en ligne pour vous familiariser avec ce langage de programmation. La documentation officielle comprend une présentation incluant des guides de démarrage rapide, des tutoriels, ainsi que des vidéos. Enfin, n'oubliez pas l'atelier de programmation d'introduction à Apps Script, que vous devez suivre avant de commencer celui-ci.
Pour communiquer avec d'autres technologies Google, Apps Script propose deux méthodes :
- service intégré/natif
- Le service avancé
Un service intégré fournit des méthodes de haut niveau que vous pouvez utiliser pour accéder à G Suite ou aux données sur les produits Google. Il peut aussi fournir d'autres méthodes utilitaires. Un service avancé est simplement un wrapper de bas niveau pour les API G Suite 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, les développeurs doivent privilégier 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. C'est le cas de Google BigQuery : il ne propose pas de service intégré, mais il offre un service avancé. ce qui est toujours mieux que pas de service du tout. Si vous ne connaissez pas encore BigQuery, il s'agit d'un service GCP 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.
Accéder à Google Sheets et Slides depuis Apps Script
Contrairement à BigQuery, Google Sheets et Slides comportent des services intégrés (ainsi que des services avancés, que vous n'utiliserez que pour accéder à des fonctionnalités fournies uniquement dans l'API). Avant de passer au code, consultez la documentation des services Sheets et Slides intégrés. Bien sûr, il existe également de la documentation pour les services avancés. Vous trouverez celle de Sheets et de Slides, respectivement.
Introduction
Avec cette première tâche, vous allez remplir une grande partie des objectifs de cet atelier de programmation. En fait, une fois cette section terminée, vous aurez quasiment réalisé la moitié de l'atelier de programmation. Vous allez effectuer les opérations suivantes, qui sont divisées en plusieurs sous-sections :
- 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
Configuration
- a) Accédez à
script.google.comet créez un projet Apps Script . Il existe différentes lignes de produit G Suite, et la création d'un projet peut différer selon la version que vous utilisez. Si vous n'utilisez que votre compte Gmail et que vous n'avez jamais développé de projets, un écran vide s'affiche avec un bouton permettant de créer votre premier projet :
b) Sinon, vous verrez peut-être tous vos projets et un grand bouton + Nouveau en haut à gauche. Cliquez dessus.
c) Si aucune des options ci-dessus ne correspond à votre situation, votre écran peut ressembler à celui ci-dessous. Si c'est le cas, recherchez l'icône de menu hamburger en haut à gauche, puis sélectionnez + Nouveau script.
d) Pour ceux qui préfèrent la ligne de commande. Votre outil est clasp. Plus précisément, vous exécuterez la commande clasp create.
e) La dernière façon de créer un projet de script consiste simplement à accéder au lien raccourci : https://script.google.com/create.
- Quelle que soit la technique utilisée pour démarrer un projet, vous devriez être redirigé vers l'éditeur de code Apps Script, un écran qui ressemble à ceci :
- Cliquez sur Fichier > Enregistrer, puis attribuez un nom à votre projet.
- Vous devez ensuite créer un projet dans la console Google Cloud pour exécuter des requêtes BigQuery.
- Créez un projet, nommez-le, sélectionnez un compte de facturation, puis cliquez sur CRÉER.
- Une fois le projet créé, une notification apparaît dans l'angle supérieur droit de la page. Cliquez sur l'entrée Créer un projet : <Nom du projet> pour ouvrir le projet.
- Cliquez sur l'icône de menu
en haut à gauche, puis accédez à API et services > Identifiants. Cliquez sur l'onglet Écran d'autorisation OAuth (lien direct). - Dans le champ Nom de l'application, saisissez "Big Data Codelab" (Atelier de programmation Big Data), puis cliquez sur le bouton Enregistrer en bas de la page.
- Cliquez sur l'icône à trois points
en haut à droite pour développer le menu, puis sélectionnez Paramètres du projet (lien direct). - Copiez la valeur indiquée sous Numéro du projet. (Il existe un champ distinct ID du produit que nous utiliserons plus tard dans l'atelier de programmation.)
- Dans l'éditeur Apps Script, cliquez sur Ressources > Projet Cloud Platform.
- Saisissez le numéro du projet dans la zone de texte, puis cliquez sur Définir le projet. Lorsque vous y êtes invité, cliquez sur Confirmer.
- Une fois l'opération terminée, cliquez sur le bouton Fermer pour fermer la boîte de dialogue.
- Maintenant que vous avez configuré un nouveau projet, vous devez activer le service avancé BigQuery. Pour ce faire, déroulez le menu Resources (Ressources) -> Advanced Google Services (Services avancés Google) et définissez la valeur du curseur sur on (activé) pour l'API BigQuery.
- Au bas de la page, une note indique "Ces services doivent également être activés dans le tableau de bord des API Google Cloud Platform". Cliquez sur ce lien pour ouvrir un autre onglet du navigateur vers la console pour les développeurs (ou "devconsole").
- Dans la console de développement, cliquez sur le bouton + Activer les API et services en haut de la page, recherchez "bigquery", sélectionnez l'API BigQuery (et non l'API BigQuery Data Transfer), puis cliquez sur Activer pour l'activer. Laissez cet onglet de navigateur ouvert.
REMARQUE : Une fois l'API activée, il est possible que la page affiche un message du type "Pour utiliser cette API, vous devez créer des identifiants…". Ne vous en souciez pas pour le moment : Apps Script s'en chargera pour vous.
- De retour dans l'onglet de navigateur de l'éditeur de code, vous êtes toujours dans le menu "Advanced Google Services" (Services avancés Google). Cliquez donc sur OK pour fermer la boîte de dialogue et revenir à l'éditeur de code. Cliquez sur le nom du projet en haut de la page et attribuez-lui le nom de votre choix ("démo BigQuery" ou un nom similaire, par exemple). Nous avons choisi "dernière étape".
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.
Importer et exécuter l'application
- Copiez le code affiché dans l'encadré ci-dessous et collez-le dans l'éditeur de code, à la place du contenu actuel :
// 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 = '';
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/googleworkspace/apps-script-samples/blob/main/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());
}
Enregistrez maintenant le fichier que vous venez de créer, mais renommez-le de Code.gs en bq-sheets-slides.js. À quoi sert ce code ? Nous vous avons déjà dit qu'il interroge BigQuery et écrit les résultats dans une nouvelle feuille Google Sheets, mais en quoi consiste cette requête ? Vous le trouverez en haut de 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.
- Nous sommes presque prêts à essayer cette fonctionnalité. Comme vous pouvez le voir en haut de cet extrait de code, un ID de projet valide est requis. Nous devons donc ajouter le vôtre au code de l'application. Pour l'obtenir, revenez à la fenêtre ou à l'onglet du navigateur qui contient la page de la console pour les développeurs. (Nous vous avons dit de le laisser ouvert, vous vous souvenez ?)
- En haut à gauche de l'avatar de votre compte Google, vous trouverez le sélecteur de menu déroulant (
). Cliquez dessus, puis sélectionnez Paramètres du projet. Vous verrez le nom, l'ID et le numéro du projet. Copiez l'ID du projet et définissez la variable PROJECT_IDen haut debq-sheets-slides.jssur la valeur que vous avez obtenue dans la console de développement. REMARQUE : Si le sélecteur de menu ne répond pas, actualisez la page. - L'instruction
ifest là pour empêcher l'application de continuer à s'exécuter sans ID de projet. Une fois que vous avez ajouté la vôtre, enregistrez le fichier et exécutez le code en remontant dans la barre de menu et en sélectionnant Run > Run function > runQuery (Exécuter > Exécuter la fonction > runQuery), puis cliquez sur la boîte de dialogue Review Permissions (Examiner les autorisations). Cette application n'est pas validée. Vous trouverez ci-dessous un GIF animé (pour une autre application) illustrant les prochaines étapes :
- Une fois que vous avez demandé à examiner les autorisations, une nouvelle boîte de dialogue s'affiche, comme indiqué ci-dessus. Choisissez le compte Google approprié pour exécuter le script, sélectionnez Advanced (Paramètres avancés), faites défiler la page vers le bas, puis cliquez sur "Go to <YOUR PROJECT NAME> (unsafe)" (Accéder à <NOM DE VOTRE PROJET> (non sécurisé)) pour accéder à l'écran d'autorisation de l'application OAuth2. (Pour en savoir plus sur la procédure de validation, découvrez pourquoi cet écran s'affiche entre vous et la boîte de dialogue d'autorisation OAuth2 ci-dessous.)
REMARQUE : Une fois que vous avez autorisé l'application, vous n'avez plus besoin de répéter ce processus à chaque exécution. Cette boîte de dialogue ne s'affichera de nouveau que lorsque vous atteindrez la tâche 3 dans la suite de ce tutoriel. Vous serez alors invité à autoriser la création et la gestion de présentations Google Slides. - Une fois que vous avez cliqué sur Autoriser dans la boîte de dialogue OAuth2, le script commence à s'exécuter. Une boîte de dialogue jaune pastel s'affiche en haut de l'écran. Il s'exécute assez rapidement. Vous ne remarquerez peut-être pas qu'il est en cours d'exécution ou qu'il est terminé.
- Cette boîte de dialogue disparaît une fois l'opération terminée. Si vous ne la voyez pas, cela signifie probablement que l'opération est terminée. Accédez donc à votre espace Google Drive (
drive.google.com) 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 variableQUERY_NAME:
- Ouvrez la feuille de calcul. Vous verrez normalement 10 lignes de mots triés par ordre décroissant selon le nombre d'occurrences :
Résumé de la tâche 1
Vous venez d'exécuter 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'y placer ces données et de les rendre facilement exploitables.
Le code de bq-sheets-slides.js (le nom de fichier que nous avons choisi) que vous avez collé ci-dessus (à l'exception de PROJECT_ID, qui doit avoir un véritable ID de projet) se trouve également dans le dossier step1 du dépôt GitHub de cet atelier de programmation à l'adresse github.com/googlecodelabs/bigquery-sheets-slides. Le code s'inspire de l'exemple original de la page des services avancés BigQuery, qui exécutait une requête légèrement différente : quels sont les mots les plus populaires utilisés par Shakespeare avec 10 caractères ou plus ? Vous pouvez également consulter cet exemple dans son dépôt GitHub.
Si vous souhaitez essayer d'autres requêtes sur les œuvres de Shakespeare ou d'autres tables de données publiques, consultez cette page Web et celle-ci. Quelle que soit la requête que vous utilisez, vous pouvez toujours la tester dans la console BigQuery avant de l'exécuter dans Apps Script. Les développeurs ont accès à l'interface utilisateur de BigQuery à l'adresse bigquery.cloud.google.com. Par exemple, voici à quoi ressemble notre requête dans l'UI BigQuery :
Bien que les étapes ci-dessus aient utilisé l'éditeur de code Apps Script, vous pouvez également choisir de développer localement via la ligne de commande. Si vous préférez, créez un script nommé bq-sheets-slides.js, collez-y le code ci-dessus, puis importez-le dans Google avec la commande clasp push. (Si vous l'avez manqué plus tôt, voici à nouveau le lien vers clasp et comment l'utiliser.)
L'objectif de runQuery() est de communiquer avec BigQuery et d'envoyer ses résultats dans une feuille de calcul. Nous devons maintenant créer un graphique avec les données. Pour ce faire, créons une fonction createColumnChart() qui appelle la méthode newChart() de la feuille.
- Créer un graphique : Ajoutez le corps de
createColumnChart()présenté ci-dessous àbq-sheets-slides.jsjuste aprèsrunQuery(). Elle 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.
/**
* 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);
}- Renvoyer la feuille de calcul Dans le code ci-dessus,
createColumnChart()a besoin de l'objet spreadsheet. Nous devons donc modifierrunQuery()pour qu'il renvoie l'objetspreadsheetafin de pouvoir le transmettre àcreateColumnChart(). Après avoir créé la feuille Google Sheet, retournez l'objetspreadsheetà la fin derunQuery(), juste après la ligne de journal :
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
// NEW: Return the spreadsheet object for later use.
return spreadsheet;
}- Fonction
createBigQueryPresentation()Driving. La séparation logique des fonctionnalités BigQuery et chart-creation est une excellente idée. Créons maintenant une fonctioncreateBigQueryPresentation()pour lancer l'application, en appelant les deuxrunQuery()etcreateColumnChart(). Le code que vous ajoutez doit ressembler à ceci :
/**
* Runs a BigQuery query, adds data and a chart in a Sheet.
*/
function createBigQueryPresentation() {
var spreadsheet = runQuery();
createColumnChart(spreadsheet);
}- Rendez le code plus réutilisable. 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 derunQuery(), nous devons déplacer cette ligne de journal. Une idée du meilleur emplacement ? Si vous avez réponducreateBigQueryPresentation(), 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()); // MOVED HERE
createColumnChart(spreadsheet);
}Une fois les modifications ci-dessus effectuées (à l'exception de PROJECT_ID), votre fichier bq-sheets-slides.js devrait maintenant se présenter comme suit (il est également disponible dans le dossier step2 du dépôt GitHub) :
// 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 = '';
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/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
*
* @returns {Sheet} Returns a sheet with results
* @see http://developers.google.com/apps-script/reference/spreadsheet/sheet
*/
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);
}
/**
* 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);
}Enregistrez le fichier, puis accédez au haut de l'éditeur de code et exécutez createBigQueryPresentation() au lieu de runQuery(). Après avoir exécuté la requête, vous accédez à une autre feuille Google Sheets, mais cette fois, un graphique s'affiche à côté des données :
Dans la dernière partie de l'atelier de programmation, 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.
- Créez une présentation. Toutes les actions effectuées sur la présentation seront exécutées dans
createSlidePresentation(), que nous allons ajouter àbq-sheets-slides.jsjuste aprèscreateColumnChart(). Commençons par créer une présentation, puis ajoutons un titre et un sous-titre à la diapositive de titre par défaut qui est fournie avec toutes les nouvelles présentations.
/**
* 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');- Ajoutez un tableau de données. L'étape suivante dans
createSlidePresentation()consiste à importer les données des cellules à partir de la feuille de calcul Google Sheets dans notre nouvelle présentation. Ajoutez cet extrait de code à la fonction :
// 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]));
}
}- Importer un 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'objetPresentation. 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;
}- Graphique des retours : 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. Nous avons déjà modifiérunQuery()pour renvoyer l'objetSpreadsheet. Nous devons maintenant modifiercreateColumnChart()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 decreateColumnChart():
// NEW: Return chart object for later use
return chart;
}- Mettre à jour
createBigQueryPresentation(). CommecreateColumnChart()renvoie le graphique, nous devons l'enregistrer dans une variable, puis transmettre à la fois la feuille de calcul et le graphique àcreateSlidePresentation(). Puisque nous consignons l'URL de la feuille de calcul récemment créée, consignons également l'URL de la nouvelle présentation Slides. Mettez à jour votrecreateBigQueryPresentation()pour qu'il ressemble à ceci :
/**
* 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); // UPDATED
var deck = createSlidePresentation(spreadsheet, chart); // NEW
Logger.log('Results slide deck created: %s', deck.getUrl()); // NEW
}- Enregistrez et exécutez de nouveau
createBigQueryPresentation(). Avant de l'exécuter, sachez que votre application a désormais besoin d'un ensemble supplémentaire d'autorisations de votre utilisateur pour afficher et gérer vos présentations Google Slides. Une fois cette autorisation accordée, l'application fonctionnera comme avant.
- 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 avez créé une application qui exploite les deux côtés de Google Cloud en effectuant une requête Google BigQuery qui interroge l'un de ses ensembles de données publics, crée une feuille de calcul Google Sheets pour stocker les résultats, ajoute un graphique basé sur les données qui viennent d'être récupérées et, enfin, crée une présentation Google Slides contenant les résultats ainsi que le graphique de la feuille de calcul.
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. Nous espérons que cet exemple vous inspirera et vous donnera envie de le personnaliser pour vos propres projets. À la fin de cet atelier de programmation, nous vous donnerons quelques suggestions pour améliorer davantage cet exemple d'application.
Une fois les modifications ci-dessus effectuées (à l'exception de PROJECT_ID), votre fichier bq-sheets-slides.js devrait maintenant ressembler à ceci (vous le trouverez également dans le dossier final du dépôt GitHub) :
bq-sheets-slides.js
/**
* 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 = '';
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/googleworkspace/apps-script-samples/blob/main/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());
}Le "second fichier" de cet atelier de programmation, à savoir le fichier manifeste Apps Script appsscript.json, ne joue aucun rôle dans cet atelier. Pour y accéder, accédez à l'onglet du navigateur de l'éditeur de code, puis sélectionnez Afficher > Afficher le fichier manifeste dans le menu en haut de l'écran. Le contenu doit ressembler à ceci :
appsscript.json
{
"timeZone": "America/Los_Angeles",
"dependencies": {
"enabledAdvancedServices": [{
"userSymbol": "BigQuery",
"serviceId": "bigquery",
"version": "v2"
}]
},
"exceptionLogging": "STACKDRIVER"
}Le fichier manifeste est un fichier de configuration au niveau du système qu'Apps Script utilise pour savoir quel environnement d'exécution doit être disponible pour votre application. Le contenu d'un fichier manifeste ne fait pas partie de cet atelier de programmation, mais vous pouvez vous faire une idée de son fonctionnement.
Vous trouverez ci-dessous d'autres ressources qui vous permettront d'approfondir les aspects abordés dans cet atelier de programmation et de découvrir d'autres moyens d'accéder aux outils de développement Google de manière programmatique. Nous nous efforçons d'assurer la synchronisation de cet atelier de programmation avec le dépôt.
Ressources pour cette application
- Lien vers cet atelier de programmation : g.co/codelabs/bigquery-sheets-slides
- repo de code source
- Vidéo et article de blog pour les développeurs
- Session Google Cloud NEXT 2018
Documentation
- Site de documentation sur Google Apps Script
- Service Spreadsheet Apps Script
- Service Slides dans 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)
- 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
- Connecteur de données Google Sheets pour BigQuery (annonce)
- Intégration de Google BigQuery à Google Drive (link1, link2)
- 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 G Suite
- Développeurs G Suite sur Twitter (@googleworkspace)
- Newsletter mensuelle des développeurs G Suite
Autres ateliers de programmation
Introduction
- [API REST] API G Suite et Google (API Drive)
- [App Maker] Créer une application Web de base de données dans App Maker
Intermédiaire
- [Apps Script] Outil de ligne de commande CLASP Apps Script
- [Apps Script] Modules complémentaires Gmail
- [Apps Script] Module complémentaire Docs et API Natural Language de GCP
- [Apps Script] Framework de bot Hangouts Chat
- [API REST] Outil de création de rapports personnalisés (API Sheets)
- [API REST] Générateur de diapositives personnalisées pour l'analyseur BigQuery de licence GitHub (API Slides et BigQuery)
Applications de référence
- Convertisseur Markdown vers Google Slides (API Slides)
Vous trouverez ci-dessous différents "défis de programmation" qui vous permettront d'améliorer ou d'étendre l'exemple que nous avons créé dans cet atelier de programmation. Cette liste n'est pas exhaustive, mais elle devrait vous donner quelques idées pour passer à l'étape suivante.
- Application. Vous ne voulez pas être limité par l'utilisation de JavaScript ou par les restrictions imposées par Apps Script ? Portez cette application vers votre langage de programmation favori qui utilise les API REST pour Google BigQuery, Sheets et Slides.
- BigQuery. Testez une autre requête pour l'ensemble de données Shakespeare. Trouvez une requête qui vous intéresse. Vous trouverez un autre exemple de requête dans l'application exemple Apps Script BigQuery d'origine.
- BigQuery. Testez d'autres ensembles de données publics BigQuery. Vous trouverez peut-être un ensemble de données qui vous sera plus utile.
- BigQuery. Nous avons déjà mentionné d'autres requêtes que vous pouvez essayer sur les œuvres de Shakespeare ou d'autres tables de données publiques. Nous voulions également partager à nouveau cette page Web et celle-ci.
- Sheets. Testez d'autres types de graphiques.
- Sheets et BigQuery Inversez la situation : vous avez peut-être un grand ensemble de données dans une feuille de calcul quelque part. En 2016, l'équipe BigQuery a introduit la fonctionnalité permettant aux développeurs d'utiliser une feuille de calcul comme source de données (pour en savoir plus, consultez les articles de blog un et deux).
- Slides. Ajoutez d'autres diapositives à la présentation générée, comme des images ou d'autres éléments liés à votre analyse de big data. Pour vous aider à démarrer, voici un guide sur le service intégré Slides.
- G Suite. Trouvez des utilisations pour d'autres services G Suite ou Google intégrés à partir d'Apps Script, par exemple : Gmail, Google Drive, Agenda, Docs, Maps, Analytics, YouTube, etc., ainsi que d'autres services avancés. Pour en savoir plus, consultez la présentation de référence pour les services intégrés et avancés.