Cette page a été traduite par l'API Cloud Translation.

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

1. De l'analyse du big data à la présentation de diapositives

Il existe de nombreux outils permettant aux data scientists d'effectuer des analyses de big data, mais au final, les analystes doivent encore justifier les résultats auprès de la direction. De nombreux chiffres sur papier ou dans une base de données sont difficiles à présenter aux principaux partenaires. Cet atelier de programmation Google Apps Script de niveau intermédiaire utilise deux plates-formes de développement Google (Google Workspace et la console Google Cloud) pour vous aider à automatiser cette dernière étape.

Les outils pour les développeurs de Google Cloud vous permettent d'effectuer une analyse approfondie des données. Vous pouvez ensuite prendre les résultats, les insérer dans une feuille de calcul et générer une présentation de diapositives avec les données. Il s'agit d'une méthode plus adaptée pour fournir des données à la direction. Cet atelier de programmation porte sur l'API BigQuery de la console Cloud (en tant que service avancé Apps Script) et les services Apps Script intégrés pour Google Sheets et Google Slides.

Motivation

L'application exemple présentée dans cet atelier de programmation s'inspire des autres exemples de code suivants:

L'exemple d'application BigQuery Service 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 des données d'une feuille de calcul et publiée dans cet article de blog
L'application exemple présentée dans l'atelier de programmation sur l'API Google Slides.

Bien que l'exemple d'application de l'API Slides intègre également BigQuery et Slides, il diffère de l'exemple d'application de cet atelier de plusieurs manières:

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

Pour cet atelier de programmation, nous souhaitions regrouper plusieurs technologies dans une seule application, tout en présentant des fonctionnalités et des API de Google Cloud à l'image d'un cas d'utilisation réel. L'objectif est de vous inciter à laisser libre cours à votre imagination, et d'envisager d'utiliser à la fois la console Cloud et Google Workspace pour résoudre des problèmes complexes pour votre organisation et vos clients.

Points abordés

Utiliser Google Apps Script avec différents services Google
Comment 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 ayant accès à Internet
Un compte Google (les comptes Google Workspace peuvent nécessiter l'approbation d'un administrateur)
Connaissances de base sur Google Sheets
Capacité à lire la notation A1 Sheets
Connaissances de base concernant JavaScript
Une connaissance du développement Apps Script est utile, mais pas obligatoire.

2. Enquête

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

<ph type="x-smartling-placeholder"></ph> Je vais les lire à titre indicatif, et les transmettre éventuellement à des collègues techniques.

Faire un maximum d'exercices et essayer autant d'exercices que possible

Que diriez-vous de suivre l'intégralité de l'atelier de programmation ?

Comment évalueriez-vous votre expérience avec les outils pour les développeurs et API ?

Débutant

Intermédiaire

Expert

Comment qualifieriez-vous votre expérience avec Apps Script plus précisément ?

Débutant

Intermédiaire

Expert

Comment évalueriez-vous votre expérience avec les outils pour les développeurs de la console Cloud ? API ?

<ph type="x-smartling-placeholder"></ph> Débutant

Intermédiaire

Expert

3. Présentation

Maintenant que vous savez en quoi consiste cet atelier de programmation, voici ce que vous allez faire:

Prendre un exemple Apps Script-BigQuery existant et le mettre en œuvre
À 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 afin de 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 Sheets.
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 supérieur aux API REST de Google. Il s'agit d'un environnement de développement et d'hébergement d'applications sans serveur accessible à tous les développeurs. En bref, Apps Script est un environnement d'exécution JavaScript sans serveur pour l'automatisation, l'extension et l'intégration de Google Workspace.

Elle utilise du code JavaScript côté serveur, semblable à Node.js, mais se concentre sur une intégration étroite avec Google Workspace et d'autres services Google, plutôt que sur l'hébergement rapide et asynchrone d'applications basées sur des événements. Il dispose également d'un environnement de développement pouvant différer de celui auquel vous êtes habitué. Avec Apps Script, vous pouvez :

Vous pouvez développer des scripts à l'aide d'un éditeur de code basé sur un navigateur, mais aussi en local avec clasp, l'outil de déploiement en ligne de commande pour Apps Script.
Écrivez du code dans une version spécialisée de JavaScript, personnalisée pour accéder à Google Workspace ainsi qu'à d'autres services Google ou externes (à l'aide des services URL Fetch ou JDBC d'Apps Script).
Évitez d'écrire du code d'autorisation, car Apps Script s'en charge pour vous.
vous n'avez pas besoin d'héberger votre application, celle-ci résidant et s'exécute sur les serveurs de 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 de l'API REST et offrent souvent plus de possibilités que les services intégrés, mais ils nécessitent davantage de complexité de code (tout en étant plus faciles à utiliser que l'API REST complète). Les services avancés doivent également être activés pour un projet de script avant de pouvoir les utiliser.

Dans la mesure du possible, les développeurs doivent utiliser un service intégré, car ils sont plus faciles à utiliser et offrent plus que des services avancés. Cependant, certaines API Google ne comportent pas de services intégrés. Un service avancé peut donc être la seule option. Par exemple, Google BigQuery n'a 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 effectuer des requêtes sur des corpus de données volumineux (par exemple, de plusieurs téraoctets). Il peut tout de même fournir des résultats en quelques secondes.

Accéder à Sheets et Slides d'Apps Script

Contrairement à BigQuery, Sheets et Slides intègrent des services. Elles disposent également de services avancés pour accéder à des fonctionnalités disponibles uniquement dans l'API. Consultez la documentation des services intégrés Sheets et Slides avant de passer au code. Notez qu'il existe également des documents sur les services avancés de Sheets et Slides.

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

Introduction

Dans cette première tâche, nous allons réaliser une grande partie de cet atelier de programmation. À la fin de cet atelier de programmation, vous aurez déjà parcouru à peu près la moitié du chemin. Voici ce que vous découvrirez en plusieurs sous-sections:

Créez un projet Google Apps Script et un projet dans la console Cloud.
Activer l'accès au service avancé BigQuery
Ouvrez l'éditeur de script et saisissez le code source de l'application.
Découvrez le processus d'autorisation de l'application (OAuth2).
Exécuter l'application qui envoie une requête à BigQuery
Examinez la nouvelle feuille de calcul Google Sheets créée à l'aide des résultats BigQuery.

Configuration

Pour créer un projet Apps Script, accédez à script.google.com, puis 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 devez ensuite créer un projet dans la console Cloud pour interroger des données dans BigQuery.

Pour créer un projet dans la console Cloud, créez un projet à l'aide de ce lien de raccourci, attribuez-lui un nom, puis cliquez sur Créer.

Une fois le projet créé, une notification s'affiche sur la page. Assurez-vous que votre nouveau projet est sélectionné dans la liste des projets en haut de la page.
Cliquez sur Menu et accédez à API et Services > Écran de consentement OAuth (lien direct)
Cliquez sur Interne > Créer pour créer une application pour les utilisateurs Google Workspace de votre organisation.
Dans le champ App name (Nom de l'application), saisissez "Big Data Codelab".
Saisissez des adresses e-mail de contact dans les champs 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 de projet. (Vous utiliserez un champ ID de projet distinct dans la suite de l'atelier de programmation.)

Vous allez maintenant connecter votre projet Apps Script au projet de la console Cloud.

Accédez à l'éditeur Apps Script et 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 "Add a service" (Ajouter un service), sélectionnez BigQuery API (API BigQuery), puis cliquez sur Add (Ajouter).

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

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

Vous êtes maintenant prêt à saisir le code de l'application, à suivre le processus d'autorisation et à faire fonctionner la première itération de cette application.

Importer l'application et l'exécuter

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. Remplacez le titre Code.gs par bq-sheets-slides.js.
Passons maintenant en revue le code qui interroge BigQuery et écrit les résultats dans une feuille de calcul Google Sheets. Vous pouvez l'afficher dans la partie supérieure 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 BigQuery, et indique les 10 mots qui apparaissent le plus souvent dans ses œuvres, triés par ordre décroissant. Vous vous faites une idée de l'utilité de BigQuery lorsque vous imaginez le travail que cela représenterait pour effectuer cette compilation manuellement.

La fonction déclare également une variable PROJECT_ID qui nécessite un ID de projet valide dans Cloud Console. L'instruction if sous la variable a pour but d'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, localisez la variable PROJECT_ID dans bq-sheets-slides.js et 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. lorsque vous utilisez votre script pour la première fois. Google l'utilise pour avertir les utilisateurs susceptibles d'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 "Application non validée", cliquez sur Paramètres avancés.
Cliquez sur Accéder à nom-du-projet (non sécurisé).
Cliquez sur Autoriser.

Tout au long de cet atelier de programmation, vous pouvez recevoir plusieurs invites d'autorisation. Pour en savoir plus sur cette procédure, consultez la page Autorisation pour les services Google.

Une fois que le script commence à s'exécuter, le journal d'exécution intégré s'ouvre et consigne les actions du script en temps réel.

Lorsque le journal d'exécution indique "Exécution terminée", accédez à Google Drive (drive.google.com) et recherchez la feuille de calcul Google Sheets intitulée "Most common words in all of Shakespeare's works" (Mots les plus courants dans toutes les œuvres 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 total, triés par ordre décroissant:

Résumé de la tâche 1

Pour résumer, vous avez exécuté un 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 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 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 consistait à exécuter 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 trouverez également un exemple dans son dépôt GitHub.

Si vous êtes intéressé par d'autres requêtes que vous pouvez créer avec les œuvres de Shakespeare ou d'autres tables de données publiques, consultez Comment interroger les exemples de tables BigQuery et ce dépôt GitHub.

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

BigQueryUI

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

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

Dans l'éditeur Apps Script, ajoutez la fonction createColumnChart() à bq-sheets-slides.js après runQuery(). Le code récupère la feuille et demande un graphique en colonnes avec toutes les données. La plage de données commence à la cellule A2 puisque 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 à la fois runQuery() et createColumnChart(). Séparer logiquement la fonctionnalité BigQuery de la création de graphiques 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 de feuille de calcul 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 maintenant 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);
}

Avec les modifications ci-dessus (à l'exception de PROJECT_ID), votre bq-sheets-slides.js devrait maintenant se présenter comme suit. Ce code se trouve également dans le fichier 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 Run (Exécuter). Une fois l'opération terminée, une autre feuille de calcul est créée dans votre Google Drive, mais cette fois, un graphique est inclus à côté des données:

Feuille avec graphique

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

La dernière partie de l'atelier de programmation consiste à 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 le diaporama 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 de 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 dans 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 la fonction est terminée, examinez à nouveau sa signature. Le createSlidePresentation() nécessite à la fois des paramètres d'objet de feuille de calcul et de graphique. Nous avons déjà ajusté runQuery() pour renvoyer l'objet Spreadsheet, mais nous devons apporter une modification similaire à createColumnChart() afin qu'il renvoie un objet graphique (EmbeddedChart). Revenez à createColumnChart() et ajoutez l'extrait de code suivant à la fin de la fonction:

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

Comme createColumnChart() renvoie désormais un objet graphique, nous devons enregistrer le graphique dans une variable. Nous transmettons ensuite à la fois les variables de la feuille de calcul et du graphique à createSlidePresentation(). De plus, puisque nous enregistrons l'URL de la feuille de calcul nouvellement créée, enregistrons également l'URL de la nouvelle présentation de diapositives. Modifiez votre createBigQueryPresentation() pour qu'il se présente comme suit:

/**
 * 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 à nouveau createBigQueryPresentation(). Toutefois, avant de pouvoir s'exécuter, votre application a besoin d'un ensemble supplémentaire d'autorisations accordées par l'utilisateur pour afficher et gérer les présentations Google Slides. Une fois que vous aurez vérifié et autorisé ces autorisations, l'application s'exécutera comme avant.
En plus de la feuille de calcul créée, vous devriez également obtenir une nouvelle présentation Slides avec trois diapositives (titre, tableau de données, graphique de données), comme indiqué ci-dessous:

7. Conclusion

Félicitations ! Vous venez de créer une application qui utilise les deux côtés de Google Cloud. Il exécute 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, puis crée une présentation Google Slides contenant les données et les résultats du graphique de la feuille de calcul.

Ces étapes correspondent à ce que vous avez fait techniquement. De manière générale, vous êtes passé d'une analyse de big data à un résultat que vous pouvez présenter aux personnes concernées, le tout automatisé à l'aide de code. Nous espérons que cet échantillon vous donnera envie de le personnaliser pour vos propres projets. À la fin de cet atelier de programmation, nous vous proposerons quelques suggestions pour améliorer davantage cette application exemple.

Avec les modifications apportées à la tâche finale (à l'exception de PROJECT_ID), votre bq-sheets-slides.js devrait maintenant se présenter comme suit:

/**
 * 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 sujets abordés dans cet atelier de programmation et de découvrir d'autres façons d'accéder aux outils Google pour les développeurs de manière programmatique.

Ressources pour cette application

Documentation

Site de documentation sur 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 sur l'analyse de données Google Cloud
Blog Google Developers
Twitter: Google Developers (@googledevs)
Blog des développeurs Google Workspace
Twitter: Développeurs Google Workspace (@workspacedevs)

Autres ateliers de programmation

Introduction

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

Intermédiaire

[Apps Script] Outil de ligne de commande Apps Script CLASP
[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 liés au code

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

Application : vous ne voulez pas vous limiter à l'utilisation de JavaScript ni aux restrictions imposées par Apps Script ? Portez cette application dans votre langage de programmation favori, qui utilise les API REST pour Google BigQuery, Sheets et Slides.
BigQuery : testez une autre requête sur l'ensemble de données Shakespeare qui vous intéresse. Vous trouverez un autre exemple de requête dans l'application exemple BigQuery Apps Script d'origine.
BigQuery : testez d'autres ensembles de données publics de BigQuery pour en trouver un plus pertinent pour vous.
BigQuery : plus tôt, nous avons mentionné d'autres requêtes que vous pouvez créer à partir des œuvres de Shakespeare ou d'autres tables de données publiques. Ils sont disponibles 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 grand ensemble de données de feuille de calcul. En 2016, l'équipe BigQuery a lancé une fonctionnalité permettant aux développeurs d'utiliser une feuille de calcul Sheets comme source de données. Pour en savoir plus, consultez Google BigQuery s'intègre à Google Drive.
Diapositives : ajoutez d'autres diapositives à la présentation générée, par exemple des images ou d'autres éléments liés à votre analyse de big data. Voici la documentation de référence sur 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 des références concernant les services intégrés et avancés.