Transformez vos big data en insights à l'aide de Google Sheets et Slides

1. Intégrer une analyse de big data à une présentation Slides

Les data scientists disposent de nombreux outils pour analyser le big data, mais les analystes doivent toujours justifier leurs 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. Cet atelier de programmation Google Apps Script de niveau intermédiaire utilise deux plates-formes de développement Google (Google Workspace et console Google Cloud) pour vous aider à automatiser cette dernière étape.

Les outils pour les développeurs de Google Cloud vous permettent d'analyser en profondeur les données. Vous pouvez ensuite insérer les résultats dans une feuille de calcul et générer une présentation de diapositives avec les données. C'est un moyen plus adapté de fournir des données à la direction. Dans cet atelier de programmation, vous apprendrez à utiliser l'API BigQuery de la console Cloud (en tant que service avancé Apps Script), ainsi que les services Apps Script intégrés pour Google Sheets et Google Slides.

Motivation

L'exemple d'application de cet atelier de programmation s'inspire des exemples de code suivants :

L'exemple d'application du service BigQuery Google Apps Script, disponible en 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 façons :

Leur application Node.js par rapport à notre application Apps Script.
Ils utilisent des API REST, tandis que nous utilisons des services Apps Script.
Ils utilisent Google Drive, mais pas Google Sheets, alors que cette application utilise Sheets, mais pas Drive.

Pour cet atelier de programmation, nous avons souhaité rassembler plusieurs technologies dans une seule application, tout en présentant des fonctionnalités et des API de Google Cloud d'une manière qui ressemble à un cas d'utilisation réel. L'objectif est de vous inciter à faire preuve d'imagination et à envisager d'utiliser à la fois la console Cloud et Google 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 Google BigQuery pour analyser le 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 données et des graphiques de Sheets vers une présentation Google Slides

Prérequis

Un navigateur Web avec accès à Internet
Un compte Google (l'approbation de l'administrateur peut être nécessaire pour les comptes Google Workspace)
Connaissances de base de Google Sheets
Possibilité de lire la notation A1 de Sheets
Connaissances de base en JavaScript
Avoir des connaissances en développement Apps Script (utile, mais pas obligatoire)

2. Enquête

Comment allez-vous utiliser cet atelier de programmation/tutoriel ?

Je vais le lire pour m'informer, et peut-être le transmettre à mes collègues techniques.

Je vais le parcourir autant que possible et essayer de faire le plus d'exercices possible.

Je vais faire tout mon possible pour terminer l'atelier de programmation.

Quel est votre niveau d'expérience avec les outils et API pour les développeurs Google Workspace ?

Débutant

Intermédiaire

Expert

Comment évalueriez-vous votre expérience avec Apps Script en particulier ?

Débutant

Intermédiaire

Expert

Quel est votre niveau d'expérience avec les outils et API pour les développeurs de la console Cloud ?

Débutant

Intermédiaire

Expert

3. Présentation

Maintenant que vous savez de quoi il s'agit, voici ce que vous allez faire :

Prenez un exemple Apps Script-BigQuery existant et faites-le fonctionner.
À partir de l'exemple, découvrez comment envoyer une requête à BigQuery et obtenir les résultats.
Créez une feuille de calcul Google Sheets et insérez-y les résultats de BigQuery.
Modifiez le code pour modifier légèrement les données renvoyées et insérées dans la feuille.
Utilisez le service Sheets dans Apps Script pour créer un graphique pour les données BigQuery.
Utilisez le service Slides pour créer une présentation Google Slides.
Ajoutez un titre et un sous-titre à la diapositive de titre par défaut.
Créez une diapositive avec un tableau de données et exportez-y les cellules de données de la feuille de calcul.
Créez 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 Google 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 Google Workspace.

Il utilise JavaScript côté serveur, semblable à Node.js, mais se concentre sur une intégration étroite à Google 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 différent de ceux que vous connaissez. Avec Apps Script, vous pouvez :

développer des scripts à l'aide d'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 à Google Workspace ainsi qu'à d'autres services fournis par Google ou par des tiers (à l'aide des services URL Fetch ou JDBC d'Apps Script) ;
Vous n'avez pas besoin d'écrire de code d'autorisation, car Apps Script s'en charge pour vous.
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.

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

En tant que service intégré
En tant que service avancé

Un service intégré dispose de méthodes de haut niveau pour interagir avec les données utilisateur, d'autres systèmes Google et des systèmes externes. Un service avancé est essentiellement un wrapper léger pour une API Google Workspace ou une API REST 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 complète). 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 utiliser 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. Par exemple, Google BigQuery ne propose pas de service intégré, mais le service BigQuery existe. Le service BigQuery est un service de la console Cloud qui vous permet d'utiliser l'API Google BigQuery pour exécuter des requêtes sur de grands corpus de données (par exemple, plusieurs téraoctets) et d'obtenir des résultats en quelques secondes.

Accéder à Sheets et Slides depuis Apps Script

Contrairement à BigQuery, Sheets et Slides disposent de services intégrés. Ils disposent également de services avancés pour accéder aux fonctionnalités disponibles uniquement dans l'API. Avant de passer au code, consultez la documentation des services Sheets et Slides intégrés. Notez qu'il existe également une documentation pour les services avancés de Sheets et Slides.

4. Tâche 1 : Exécuter BigQuery et consigner les résultats dans Sheets

Introduction

Nous allons effectuer une grande partie de cet atelier de programmation avec cette première tâche. En fait, une fois que vous aurez terminé, vous aurez quasiment réalisé la moitié de l'atelier. Cette section est divisée en plusieurs sous-sections. Vous y trouverez les informations suivantes :

Créez un projet Google Apps Script et un projet Cloud Console.
Activez l'accès au service avancé BigQuery.
Ouvrez l'éditeur de script et saisissez le code source de l'application.
Réalisez l'ensemble du processus d'autorisation d'application (OAuth2).
Exécutez l'application qui envoie une requête à BigQuery.
Examinez la nouvelle feuille de calcul Google Sheets créée à partir des résultats BigQuery.

Configuration

Pour créer un projet Apps Script, accédez à script.google.com et cliquez sur Nouveau projet.
Pour renommer votre projet Apps Script, cliquez sur Projet sans titre, saisissez un titre pour votre projet, puis cliquez sur Renommer.

Vous devrez ensuite créer un projet Cloud Console pour interroger les données dans BigQuery.

Pour créer un projet Cloud Console, utilisez ce lien raccourci pour créer un projet, donnez-lui un nom, puis cliquez sur Créer.

Une fois le projet créé, une notification s'affiche sur la page. Vérifiez que votre nouveau projet est sélectionné dans la liste des projets en haut de la page.
Cliquez sur Menu , puis accédez à API et services > Écran de consentement OAuth (lien direct).
Cliquez sur Interne > Créer pour créer une application destinée aux utilisateurs Google Workspace de votre organisation.
Dans le champ Nom de l'application, saisissez "Big Data Codelab".
Saisissez les adresses e-mail de contact dans les champs Adresse e-mail d'assistance utilisateur et Coordonnées du développeur.
Cliquez sur Enregistrer et continuer > Enregistrer et continuer.
Cliquez sur Plus dans la barre de navigation, puis sélectionnez Paramètres du projet (lien direct).
Copiez la valeur indiquée sous Numéro du projet. (Un champ ID de projet distinct sera utilisé plus loin dans l'atelier de programmation.)

Vous allez ensuite associer votre projet Apps Script au projet de la console Cloud.

Accédez à l'éditeur Apps Script, puis cliquez sur Paramètres du projet .
Sous "Projet Google Cloud Platform (GCP)", cliquez sur Changer de projet.
Saisissez le numéro du projet, puis cliquez sur Définir le projet.
Cliquez ensuite sur Éditeur pour commencer à ajouter le service avancé BigQuery.
À côté de Services, cliquez sur Ajouter un service .
Dans la boîte de dialogue "Ajouter un service", sélectionnez API BigQuery, puis cliquez sur Ajouter.

La dernière étape consiste à activer l'API BigQuery dans la console Cloud.

Pour ce faire, passez à la console Cloud, puis cliquez sur API et services > Tableau de bord. (Assurez-vous d'être toujours dans le projet que vous avez créé à l'étape 3.)
Cliquez sur Activer les API et les services.
Recherchez "bigquery", sélectionnez BigQuery API (et non BigQuery Data Transfer API), puis cliquez sur Activer pour l'activer.

Vous êtes maintenant prêt à saisir le code d'application, à suivre la procédure d'autorisation et à découvrir la première itération de cette application.

Importer et exécuter l'application

Dans l'éditeur de script, remplacez le bloc de code myFunction() par défaut par le code suivant :

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

/**
 * Runs a BigQuery query; puts results into a Sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://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 BigQuery 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 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());
}

Cliquez sur Enregistrer .
À côté de Code.gs, cliquez sur Plus > Renommer. Modifiez le titre de Code.gs en bq-sheets-slides.js.
Ensuite, examinons le code qui interroge BigQuery et écrit les résultats dans une feuille de calcul Google Sheets. Vous pouvez le voir 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 compilation serait fastidieuse si on devait l'effectuer manuellement pour se faire une idée de l'utilité de BigQuery.

La fonction déclare également une variable PROJECT_ID qui nécessite un ID de projet Cloud Console valide. L'instruction if sous la variable est là pour empêcher l'application de continuer sans l'ID de projet.

Basculez vers votre projet Cloud Console, cliquez sur Plus dans la barre de navigation, puis sélectionnez Paramètres du projet.
Copiez la valeur indiquée sous ID du projet.
Revenez à l'éditeur Apps Script, recherchez la variable PROJECT_ID dans bq-sheets-slides.js, puis ajoutez la valeur.
Cliquez sur Enregistrer > Exécuter.
Cliquez sur Examiner les autorisations pour continuer.

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 :

Dans la boîte de dialogue de l'application non validée, cliquez sur Avancé.
Cliquez sur Accéder à nom-de-votre-projet (non sécurisé).
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.

Une fois le script en cours d'exécution, le journal d'exécution intégré s'ouvre et enregistre les actions du script en temps réel.

Une fois que le journal d'exécution indique "Exécution terminée", accédez à votre espace Google Drive (drive.google.com) et recherchez la 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 le nom que vous avez attribué à la variable QUERY_NAME, si vous l'avez modifiée :
Ouvrez la feuille de calcul pour afficher les 10 mots les plus courants et leur nombre d'occurrences respectif, le tout trié par ordre décroissant :

Résumé de la tâche 1

Pour rappel, vous avez exécuté du code qui a interrogé toutes les œuvres de Shakespeare en examinant chaque mot de chaque pièce. Il a compté les mots et les a triés par ordre décroissant de fréquence d'apparition. Vous avez également utilisé le service intégré Apps Script pour Google Sheets afin d'afficher ces données.

Le code que vous avez utilisé pour bq-sheets-slides.js est également disponible 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 cet exemple original sur la page des services avancés BigQuery, qui exécutait une requête légèrement différente pour récupérer les mots les plus populaires de 10 caractères ou plus utilisés par Shakespeare. Vous pouvez également consulter un exemple dans son dépôt GitHub.

Si vous souhaitez découvrir d'autres requêtes que vous pouvez créer avec les œuvres de Shakespeare ou d'autres tables de données publiques, consultez Interroger les tables d'exemple BigQuery et ce dépôt GitHub.

Vous pouvez également exécuter des requêtes à l'aide de la page BigQuery de la console Cloud avant de les exécuter dans Apps Script. Pour le trouver, cliquez sur Menu , puis accédez à Interface utilisateur BigQuery > Espace de travail SQL (lien direct). Par exemple, voici à quoi ressemble notre requête dans l'interface graphique BigQuery :

BigQueryUI

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

L'objectif de runQuery() est d'utiliser BigQuery et d'envoyer les résultats de ses données dans une feuille de calcul Google Sheets. Nous devons ensuite créer un graphique à partir des données. Créons une fonction appelée createColumnChart() qui appelle la méthode newChart() de Sheets.

Dans l'éditeur Apps Script, ajoutez la fonction createColumnChart() à bq-sheets-slides.js après runQuery(). Le code extrait la feuille 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 les en-têtes de colonne.

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) 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 values.
  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);
}

La fonction createColumnChart() nécessite un paramètre d'objet de feuille de calcul. Nous devons donc mettre à jour runQuery() pour renvoyer un objet spreadsheet que nous pouvons transmettre à createColumnChart(). À la fin de runQuery(), renvoyez l'objet spreadsheet après avoir enregistré la création de la feuille :

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // NEW: Return the spreadsheet object for later use.
  return spreadsheet;
}

Créez une fonction createBigQueryPresentation() pour appeler runQuery() et createColumnChart(). La séparation logique des fonctionnalités BigQuery et chart-creation est une bonne pratique :

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

Vous avez effectué deux étapes importantes ci-dessus : renvoyer l'objet spreadsheet et créer la fonction d'entrée. Pour rendre runQuery() plus utilisable, nous devons déplacer la ligne de journal de runQuery() vers createBigQueryPresentation(). Votre méthode devrait désormais se présenter comme suit :

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet.
 */
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. Ce code se trouve également dans step2 du dépôt GitHub.

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

/**
 * Runs a BigQuery query; puts results into a sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://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 BigQuery 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 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 a columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) 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 values.
  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 to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

Dans l'éditeur de script, enregistrez votre projet de script. Sélectionnez ensuite createBigQueryPresentation dans la liste des fonctions, puis cliquez sur Exécuter. Une fois l'exécution terminée, une autre feuille de calcul est créée dans votre Google Drive, mais cette fois, un graphique est inclus dans la feuille à côté des données :

Feuille avec graphique

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

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

Dans l'éditeur Apps Script, ajoutez la fonction createSlidePresentation() à bq-sheets-slides.js après createColumnChart(). Tout le travail sur la présentation a lieu dans cette fonction. Commençons par créer une présentation, puis ajoutons un titre et un sous-titre à la diapositive de titre par défaut.

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
  * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the 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('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

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 with
  // the dimensions of the data range; fails if the sheet is 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]));
    }
  }

La dernière étape de createSlidePresentation() consiste à ajouter une diapositive, à importer le graphique à partir de notre feuille de calcul et à renvoyer l'objet Presentation. Ajoutez cet 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;
}

Maintenant que notre fonction est terminée, réexaminez sa signature. createSlidePresentation() requiert des paramètres d'objet de feuille de calcul et de graphique. Nous avons déjà modifié runQuery() pour renvoyer l'objet Spreadsheet. Nous devons maintenant modifier createColumnChart() de la même manière pour qu'il renvoie un objet graphique (EmbeddedChart). Retournez à createColumnChart() et ajoutez l'extrait de code suivant à la fin de la fonction :

  // NEW: Return the chart object for later use.
  return chart;
}

Étant donné que createColumnChart() renvoie désormais un objet graphique, nous devons enregistrer le graphique dans une variable. Nous transmettons ensuite à la fois les variables de feuille de calcul et de 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 votre createBigQueryPresentation() pour qu'il ressemble à ceci :

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * 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, votre application a besoin d'un ensemble supplémentaire d'autorisations de votre utilisateur pour afficher et gérer les présentations Google Slides. Une fois que vous aurez examiné et autorisé ces autorisations, l'application fonctionnera comme avant.
En plus de la feuille de calcul créée, vous devriez aussi obtenir une nouvelle présentation Slides contenant trois diapositives (titre, tableau de données, graphique de données), comme illustré ci-dessous :

7. Conclusion

Félicitations, vous avez créé une application qui utilise les deux côtés de Google Cloud. Il effectue 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 et, enfin, crée une présentation Google Slides contenant les données et les résultats du 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 pour le personnaliser dans 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 de la dernière tâche effectuées (à l'exception de PROJECT_ID), votre fichier bq-sheets-slides.js devrait maintenant ressembler à ceci :

/**
 * 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.
 */

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

/**
 * Runs a BigQuery query; puts results into a spreadsheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://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 BigQuery 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 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 a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) 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 values.
  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 and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
 * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the 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('using Google Cloud Console and Google Workspace 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 with
  // the dimensions of the data range; fails if the sheet is 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 to a spreadsheet,
 * 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());
}

Cet exemple de code est également disponible dans le dossier final du dépôt GitHub.

8. Ressources supplémentaires

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 Google pour les développeurs de manière programmatique.

Ressources pour cette application

Documentation

Site de documentation Google Apps Script
Apps Script : service Spreadsheet
Apps Script : service Slides
Apps Script : service avancé BigQuery

Vidéos

Informations et actualités

Blog Google Cloud Platform
Blog Google Cloud Data Analytics
Blog Google Developers
Twitter : Google Developers (@googledevs)
Blog des développeurs Google Workspace
Twitter : Google Workspace Developers (@workspacedevs)

Autres ateliers de programmation

Introduction

[Google Sheets] Principes de base d'Apps Script avec Google Sheets
[API REST] Utiliser les API Google Workspace et Google pour accéder aux fichiers et dossiers de votre Google Drive

Intermédiaire

[Apps Script] Outil de ligne de commande CLASP Apps Script
[Apps Script] Modules complémentaires Google Workspace pour Gmail
[Apps Script] Bots personnalisés pour Hangouts Chat
[API REST] Utiliser Google Sheets comme outil de création de rapports pour votre application
[API REST] Générer des présentations Google Slides à l'aide de l'API BigQuery

9. Étape suivante : les défis de programmation

Vous trouverez ci-dessous différentes façons d'améliorer ou d'augmenter l'échantillon que nous avons créé dans cet atelier de programmation. Cette liste n'est pas exhaustive, mais elle vous donne quelques idées pour passer à l'étape suivante.

Application : vous ne voulez pas être limité à l'utilisation de JavaScript ni par les restrictions imposées par Apps Script ? Portez cette application sur 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 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 de BigQuery pour en trouver un qui vous intéresse davantage.
BigQuery : nous avons mentionné précédemment d'autres requêtes que vous pouvez créer avec les œuvres de Shakespeare ou d'autres tables de données publiques. Vous les trouverez sur cette page Web et dans ce dépôt GitHub.
Sheets : testez d'autres types de graphiques dans la galerie de graphiques.
Sheets et BigQuery : utilisez votre propre ensemble de données volumineux dans une feuille de calcul. En 2016, l'équipe BigQuery a lancé une fonctionnalité permettant aux développeurs d'utiliser une feuille de calcul comme source de données. Pour en savoir plus, consultez Intégration de Google BigQuery à Google Drive.
Slides : ajoutez d'autres diapositives à la présentation générée, comme des images ou d'autres éléments liés à votre analyse big data. Voici la documentation de référence pour le service intégré Slides.
Google Workspace : utilisez d'autres services Google Workspace ou Google intégrés à partir d'Apps Script. Par exemple, Gmail, Agenda, Docs, Drive, 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.