Big Data in Erkenntnisse verwandeln – mit Google Tabellen und Google Präsentationen

1. Von der Big-Data-Analyse zur Darstellung in Google Präsentationen

Es gibt viele Tools, mit denen Data Scientists Big-Data-Analysen durchführen können. Letztendlich müssen sie die Ergebnisse jedoch der Geschäftsleitung gegenüber rechtfertigen. Blanke Zahlenreihen auf Papier oder in einer Datenbank sind denkbar ungeeignet, um sie wichtigen Stakeholdern zu präsentieren. In diesem Codelab für Fortgeschrittene zu Google Apps Script werden zwei Google-Plattformen für Entwickler (Google Workspace und Google Cloud Console) verwendet, um Ihnen bei der Automatisierung des letzten Schritts zu helfen.

Mit den Entwicklertools von Google Cloud können Sie detaillierte Datenanalysen durchführen. Anschließend können Sie die Ergebnisse in eine Tabelle einfügen und mit den Daten eine Präsentation erstellen. So lassen sich Daten besser an das Management weitergeben. In diesem Codelab arbeiten Sie mit der BigQuery der Cloud Console (als erweiterter Dienst von Apps Script) und mit den integrierten Apps Script-Diensten für Google Sheets und Google Präsentationen.

Motivation

Die Beispiel-App in diesem Codelab wurde von den folgenden Codebeispielen inspiriert:

Die Google Apps Script BigQuery Service-Beispielanwendung, die auf GitHub als Open Source zur Verfügung gestellt wird.
Die Beispiel-App, die im Entwicklervideo Folien aus Tabellendaten erstellen vorgestellt und in diesem Blogpost veröffentlicht wurde.
Die Beispiel-App, die im Google Slides API-Codelab vorgestellt wird.

Die Beispiel-App im Slides API-Codelab enthält zwar auch BigQuery und Google Präsentationen, unterscheidet sich aber in einigen Punkten von der Beispiel-App in diesem Codelab:

Ihre Node.js-App im Vergleich zu unserer Apps Script-App.
Sie verwenden REST APIs, während wir Apps Script-Dienste verwenden.
Sie verwenden Google Drive, aber nicht Google Sheets, während diese App Google Sheets, aber nicht Google Drive verwendet.

In diesem Codelab wollten wir mehrere Technologien in einer einzigen App zusammenführen und gleichzeitig Funktionen und APIs aus der gesamten Google Cloud in einer Weise präsentieren, die einem realen Anwendungsfall ähnelt. Ziel des Labs ist, Sie zu inspirieren, Ihrer Fantasie freien Lauf zu lassen und sowohl die Cloud Console als auch Google Workspace zu nutzen, um schwierige Probleme für Ihr Unternehmen und Ihre Kunden zu lösen.

Lerninhalte

Google Apps Script mit mehreren Google-Diensten verwenden
Big Data mit Google BigQuery analysieren
Daten in einer Google-Tabelle darstellen
Diagramm in Google Sheets erstellen
Daten und Diagramme aus Google Sheets in eine Google-Präsentation übertragen

Voraussetzungen

Einen Webbrowser mit Internetzugriff
Ein Google-Konto (für Google Workspace-Konten ist möglicherweise die Genehmigung durch den Administrator erforderlich)
Grundlegende Kenntnisse von Google Sheets
Kann die A1-Notation von Google Sheets lesen
Grundkenntnisse in JavaScript
Kenntnisse in der Apps Script-Entwicklung sind hilfreich, aber nicht zwingend erforderlich.

2. Umfrage

Wie werden Sie dieses Codelab/diese Anleitung verwenden?

Ich lese es mir zur Information durch und leite es gegebenenfalls an technische Kollegen weiter.

Ich arbeite es so weit wie möglich durch und versuche, so viele Übungen wie möglich zu machen.

Ich werde das gesamte Codelab auf jeden Fall durcharbeiten.

Wie würden Sie Ihre Erfahrungen mit den Google Workspace-Entwicklertools und ‑APIs bewerten?

Anfänger

Mittelstufe

Fortgeschrittene

Wie würden Sie Ihre Erfahrungen mit Apps Script bewerten?

Anfänger

Mittelstufe

Fortgeschrittene

Wie würden Sie Ihre Erfahrungen mit den Entwicklertools und APIs der Cloud Console bewerten?

Anfänger

Mittelstufe

Fortgeschritten

3. Übersicht

Nachdem Sie nun wissen, worum es in diesem Codelab geht, sehen Sie hier, was Sie tun werden:

Ein vorhandenes Apps Script-BigQuery-Beispiel verwenden und zum Laufen bringen
Im Beispiel erfahren Sie, wie Sie eine Abfrage an BigQuery senden und die Ergebnisse abrufen.
Erstellen Sie eine Google-Tabelle und fügen Sie die Ergebnisse aus BigQuery ein.
Ändern Sie den Code, um die zurückgegebenen und in das Tabellenblatt eingefügten Daten leicht zu ändern.
Mit dem Sheets-Dienst in Apps Script ein Diagramm für die BigQuery-Daten erstellen
Mit dem Google Präsentationen-Dienst können Sie eine Google-Präsentation erstellen.
Fügen Sie der Standardtitelfolie einen Titel und Untertitel hinzu.
Erstellen Sie eine Folie mit einer Datentabelle und exportieren Sie die Datenzellen der Tabelle dorthin.
Erstellen Sie eine weitere Folie und fügen Sie das Tabellendiagramm ein.

Zuerst einige Hintergrundinformationen zu Apps Script, BigQuery, Google Sheets und Google Präsentationen.

Google Apps Script und BigQuery

Google Apps Script ist eine Entwicklungsplattform für Google Workspace, die auf höherer Ebene als Google REST APIs funktioniert. Hier finden Entwickler mit den unterschiedlichsten Kenntnissen eine serverlose Umgebung für die Entwicklung und das Hosting von Anwendungen. Apps Script ist eine serverlose JavaScript-Laufzeitumgebung für die Automatisierung, Erweiterung und Integration von Google Workspace.

Ähnlich wie Node.js ist Apps Script eine serverseitige JavaScript-Plattform. Ihr Schwerpunkt liegt jedoch nicht beim asynchronen, ereignisgesteuerten Hosting von Anwendungen, sondern bei der Einbindung in Google Workspace und andere Google-Dienste. Deshalb unterscheidet sich die Entwicklungsumgebung grundlegend von den meisten anderen. Apps Script bietet folgende Vorteile:

Entwickeln Sie Skripts mit einem browserbasierten Code-Editor. Sie können aber auch lokal entwickeln, wenn Sie clasp verwenden, das Befehlszeilen-Deployment-Tool für Apps Script.
Code in einer speziellen Version von JavaScript schreiben, die für den Zugriff auf Google Workspace und andere Google- oder externe Dienste (über die Apps Script-Dienste URL Fetch oder JDBC) angepasst ist.
Sie müssen keinen Autorisierungscode schreiben, da Apps Script das für Sie übernimmt.
Sie müssen das Hosting nicht selbst organisieren. Ihre Anwendung wird auf Google-Servern in der Cloud gehostet und ausgeführt.

Es gibt zwei Möglichkeiten, wie Apps Script mit anderen Google-Technologien interagieren kann:

Als integrierter Dienst
Als erweiterter Dienst

Ein integrierter Dienst bietet Top-Level-Methoden für die Interaktion mit Nutzerdaten, anderen Google-Systemen und externen Systemen. Ein erweiterter Dienst ist im Grunde ein Thin Wrapper um eine Google Workspace API oder Google REST API. Erweiterte Dienste bieten eine uneingeschränkte Nutzung der REST API und können oft mehr als integrierte Dienste. Ihr Code ist etwas komplexer, aber immer noch einfacher zu handhaben als die REST API selbst. Um überhaupt nutzbar zu sein, müssen erweiterte Dienste für ein Skriptprojekt aktiviert werden.

Nach Möglichkeit sollten Entwickler einen integrierten Dienst verwenden, da diese einfacher zu verwenden sind und mehr können als erweiterte Dienste. Einige Google APIs haben jedoch keine integrierten Dienste. Dann ist die einzige Option ein erweiterter Dienst. Für Google BigQuery gibt es beispielsweise keinen integrierten Dienst, aber den BigQuery-Dienst. Der BigQuery-Dienst ist ein Cloud Console-Dienst, mit dem Sie die Google BigQuery API verwenden können, um Abfragen für große Datasets (z. B. mehrere Terabyte) durchzuführen. Die Ergebnisse sind innerhalb weniger Sekunden verfügbar.

Aus Apps Script auf Google Sheets und Präsentationen zugreifen

Im Gegensatz zu BigQuery haben sowohl Sheets als auch Präsentationen integrierte Dienste. Außerdem haben sie erweiterte Dienste, mit denen auf Funktionen zugegriffen werden kann, die nur in der API verfügbar sind. Sehen Sie sich die Dokumentation für die integrierten Dienste Sheets und Slides an, bevor Sie mit dem Code arbeiten. Es gibt auch Dokumentationen für die erweiterten Dienste von Google Sheets und Google Präsentationen.

4. Aufgabe 1: BigQuery-Abfrage ausführen und Ergebnisse in Google Sheets protokollieren

Einführung

Wir werden einen Großteil dieses Codelabs mit dieser ersten Aufgabe durchführen. Wenn Sie diesen Abschnitt abgeschlossen haben, sind Sie mit dem gesamten Codelab etwa zur Hälfte fertig. In mehreren Unterabschnitten erfahren Sie Folgendes:

Erstellen Sie sowohl ein Google Apps Script- als auch ein Cloud Console-Projekt.
Aktivieren Sie den Zugriff auf den erweiterten BigQuery-Dienst.
Öffnen Sie den Script-Editor und geben Sie den Quellcode der Anwendung ein.
Autorisierungsprozess (OAuth2) der App durchlaufen
Führen Sie die Anwendung aus, die eine Anfrage an BigQuery sendet.
Sehen Sie sich die neue Google-Tabelle an, die mit den BigQuery-Ergebnissen erstellt wurde.

Einrichtung

Rufen Sie script.google.com auf und klicken Sie auf Neues Projekt, um ein Apps Script-Projekt zu erstellen.
Wenn Sie Ihr Apps Script-Projekt umbenennen möchten, klicken Sie auf Unbenanntes Projekt, geben Sie einen Titel für Ihr Projekt ein und klicken Sie auf Umbenennen.

Als Nächstes müssen Sie ein Cloud Console-Projekt erstellen, um Daten in BigQuery abzufragen.

Verwenden Sie zum Erstellen eines Cloud Console-Projekts diesen Shortcut-Link, um ein Projekt zu erstellen, geben Sie dem Projekt einen Namen und klicken Sie auf Erstellen.

Wenn das Projekt erstellt wurde, wird auf der Seite eine Benachrichtigung angezeigt. Prüfen Sie, ob Ihr neues Projekt in der Projektliste oben auf der Seite ausgewählt ist.
Klicken Sie auf das Menü und rufen Sie APIs und Dienste > OAuth-Zustimmungsbildschirm auf (Direktlink).
Klicken Sie auf Intern > Erstellen, um eine App für Google Workspace-Nutzer in Ihrer Organisation zu erstellen.
Geben Sie im Feld App-Name „Big Data Codelab“ ein.
Geben Sie Kontakt-E‑Mail-Adressen für die Felder Nutzersupport und Kontaktdaten des Entwicklers ein.
Klicken Sie auf Speichern und fortfahren > Speichern und fortfahren.
Klicken Sie in der Navigationsleiste auf das Dreipunkt-Menü und wählen Sie Projekteinstellungen aus (Direktlink).
Kopieren Sie den Wert, der unter Projektnummer aufgeführt ist. (Ein separates Feld für die Projekt-ID wird später im Codelab verwendet.)

Als Nächstes verknüpfen Sie Ihr Apps Script-Projekt mit dem Cloud Console-Projekt.

Wechseln Sie zum App Script-Editor und klicken Sie auf Projekteinstellungen .
Klicken Sie unter „Google Cloud Platform-Projekt (GCP)“ auf Projekt ändern.
Geben Sie die Projektnummer ein und klicken Sie auf Projekt festlegen.
Klicken Sie als Nächstes auf Editor , um den erweiterten BigQuery-Dienst hinzuzufügen.
Klicken Sie neben Dienste auf „Dienst hinzufügen“ .
Wählen Sie im Dialogfeld „Dienst hinzufügen“ die Option BigQuery API aus und klicken Sie auf Hinzufügen.

Im letzten Schritt müssen Sie die BigQuery API in der Cloud Console aktivieren.

Wechseln Sie dazu zur Cloud Console und klicken Sie auf APIs & Dienste > Dashboard. Achten Sie darauf, dass Sie sich noch im selben Projekt befinden, das Sie in Schritt 3 erstellt haben.
Klicken Sie auf APIs und Dienste aktivieren.
Suchen Sie nach „BigQuery“, wählen Sie die BigQuery API (nicht die BigQuery Data Transfer API) aus und klicken Sie auf Aktivieren, um sie zu aktivieren.

Jetzt ist alles vorbereitet. Sie können den Anwendungscode eingeben, den Autorisierungsprozess durchlaufen und die Anwendung zum ersten Mal in Aktion sehen.

Anwendung hochladen und ausführen

Ersetzen Sie im Skript-Editor den Standardcodeblock myFunction() durch den folgenden Code:

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

Klicken Sie auf „Speichern“ .
Klicken Sie neben Code.gs auf das Dreipunkt-Menü > Umbenennen. Ändern Sie den Titel von Code.gs in bq-sheets-slides.js.
Sehen wir uns nun den Code an, mit dem BigQuery abgefragt und die Ergebnisse in eine Google-Tabelle geschrieben werden. Sie finden sie oben auf der Seite 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

Diese Abfrage durchsucht die Werke von Shakespeare, die Teil des öffentlichen BigQuery-Datasets sind, und gibt die zehn meist genutzten Wörter in all seinen Werken zurück, absteigend sortiert nach ihrer Häufigkeit. Sie bekommen eine Vorstellung davon, wie nützlich BigQuery sein kann, wenn Sie sich vorstellen, wie viel Arbeit es wäre, diese Zusammenstellung manuell zu erstellen.

Die Funktion deklariert auch eine PROJECT_ID-Variable, für die eine gültige Cloud Console-Projekt-ID erforderlich ist. Die if-Anweisung unter der Variablen soll verhindern, dass die Anwendung ohne die Projekt-ID fortgesetzt wird.

Rufen Sie Ihr Cloud Console-Projekt auf, klicken Sie in der Navigationsleiste auf „Mehr“ und wählen Sie Projekteinstellungen aus.
Kopieren Sie den Wert, der unter Projekt-ID aufgeführt ist.
Wechseln Sie zurück zum App Script-Editor, suchen Sie die Variable PROJECT_ID in bq-sheets-slides.js und fügen Sie den Wert hinzu.
Klicken Sie auf Speichern > Ausführen.
Klicken Sie auf Berechtigungen ansehen, um fortzufahren.

Hinweis:Wenn Sie ein gmail.com-Konto verwenden, wird beim ersten Ausführen des Skripts möglicherweise das Dialogfeld „Diese App ist nicht bestätigt“ angezeigt. Google verwendet diese Informationen, um Nutzer zu warnen, die möglicherweise Code von unbekannten oder nicht vertrauenswürdigen Autoren verwenden. Wenn dieses Dialogfeld angezeigt wird, können Sie fortfahren, da Sie der Autor des Skripts sind. Folgen Sie der Anleitung auf dem Bildschirm, um das Skript weiter zu autorisieren:

Klicken Sie im Dialogfeld für nicht bestätigte Apps auf Erweitert.
Klicken Sie auf Zu your-project-name (unsicher).
Klicken Sie auf Zulassen.

Während dieses Codelabs werden Sie möglicherweise mehrmals aufgefordert, Berechtigungen zu erteilen. Weitere Informationen zu diesem Prozess finden Sie unter Autorisierung für Google-Dienste.

Sobald das Skript ausgeführt wird, wird das integrierte Ausführungsprotokoll geöffnet und die Skriptaktionen werden in Echtzeit protokolliert.

Sobald im Ausführungsprotokoll „Execution completed“ (Ausführung abgeschlossen) angezeigt wird, rufen Sie Google Drive (drive.google.com) auf und suchen Sie die Google-Tabelle mit dem Namen „Most common words in all of Shakespeare's works“ (Die häufigsten Wörter in den Werken Shakespeares) bzw. dem Namen, den Sie der Variablen QUERY_NAME zugewiesen haben, falls Sie sie aktualisiert haben:
Öffnen Sie die Tabelle, um die zehn häufigsten Wörter und ihre Gesamtzahlen in absteigender Reihenfolge zu sehen:

Zusammenfassung von Aufgabe 1

Sie haben Code ausgeführt, der alle Werke von Shakespeare abgefragt und jedes Wort in jedem Werk untersucht hat. Die Wörter wurden gezählt und in absteigender Reihenfolge ihrer Häufigkeit sortiert. Außerdem haben Sie den integrierten Apps Script-Dienst für Google Sheets verwendet, um diese Daten darzustellen.

Der Code, den Sie für bq-sheets-slides.js verwendet haben, befindet sich auch im Ordner step1 des GitHub-Repositorys dieses Codelabs unter github.com/googlecodelabs/bigquery-sheets-slides. Der Code wurde von diesem Originalbeispiel auf der Seite „BigQuery Advanced Services“ inspiriert, in dem eine etwas andere Abfrage ausgeführt wurde, um die beliebtesten Wörter mit mindestens 10 Zeichen von Shakespeare abzurufen. Ein Beispiel finden Sie auch im GitHub-Repository.

Wenn Sie sich für andere Abfragen interessieren, die Sie mit den Werken von Shakespeare oder anderen öffentlichen Datentabellen erstellen können, lesen Sie die Informationen unter BigQuery-Beispieltabellen abfragen und in diesem GitHub-Repository.

Sie können Abfragen auch über die BigQuery-Seite in der Cloud Console ausführen, bevor Sie sie in Apps Script ausführen. Klicken Sie dazu auf das Menü und rufen Sie die BigQuery-UI > SQL-Arbeitsbereich auf (Direktlink). So sieht unsere Abfrage beispielsweise in der grafischen Benutzeroberfläche von BigQuery aus:

BigQueryUI

5. Aufgabe 2: Diagramm in Google Sheets erstellen

Der Zweck von runQuery() besteht darin, BigQuery zu verwenden und die Datenresultate an eine Google-Tabelle zu senden. Als Nächstes müssen wir ein Diagramm mit den Daten erstellen. Erstellen wir eine neue Funktion namens createColumnChart(), die die Methode newChart() von Google Tabellen aufruft.

Fügen Sie im Apps Script-Editor die Funktion createColumnChart() in bq-sheets-slides.js nach runQuery() ein. Der Code ruft die Tabelle ab und fordert ein Säulendiagramm mit allen Daten an. Der Datenbereich beginnt bei Zelle A2, weil in der ersten Zeile die Spaltenüberschriften stehen.

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

Für die Funktion createColumnChart() ist ein Tabellenkalkulationsobjektparameter erforderlich. Daher müssen wir runQuery() so aktualisieren, dass ein spreadsheet-Objekt zurückgegeben wird, das wir an createColumnChart() übergeben können. Geben Sie am Ende von runQuery() das Objekt spreadsheet zurück, nachdem Sie protokolliert haben, dass die Tabelle erfolgreich erstellt wurde:

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

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

Erstellen Sie eine createBigQueryPresentation()-Funktion, um sowohl runQuery() als auch createColumnChart() aufzurufen. Es ist sinnvoll, die Funktionen für die BigQuery-Abfrage und für die Diagrammerstellung getrennt voneinander aufzurufen:

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

Sie haben eben zwei wichtige Schritte ausgeführt: das Tabellenobjekt zurückgegeben und die Eingangsfunktion erstellt. Damit runQuery() besser nutzbar ist, müssen wir die Log-Zeile von runQuery() nach createBigQueryPresentation() verschieben. Die Methode sollte nun so aussehen:

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

Mit den oben genannten Änderungen (mit Ausnahme von PROJECT_ID) sollte bq-sheets-slides.js jetzt so aussehen. Diesen Code finden Sie auch in step2 des GitHub-Repositorys.

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

Speichern Sie Ihr Skriptprojekt im Script-Editor. Wählen Sie dann createBigQueryPresentation aus der Funktionsliste aus und klicken Sie auf Ausführen. Wenn die Ausführung abgeschlossen ist, sehen Sie eine weitere Google-Tabelle in Ihrer Google Drive-Ablage. Doch diesmal wird neben den Daten ein Diagramm angezeigt.

Tabelle mit Diagramm

6. Aufgabe 3: Ergebnisdaten in Präsentation einfügen

Im letzten Teil des Codelabs erstellen Sie eine Google-Präsentation, fügen der Titelfolie den Titel und Untertitel hinzu und erstellen dann Folien für die Datenzellen und das Diagramm.

Fügen Sie im Apps Script-Editor die Funktion createSlidePresentation() in bq-sheets-slides.js nach createColumnChart() ein. Alle Arbeiten am Foliensatz finden in dieser Funktion statt. Zuerst erstellen wir eine Präsentation und fügen dann auf der Titelfolie, die bei allen neuen Präsentationen automatisch erzeugt wird, Titel und Untertitel ein.

/**
 * 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');

Der nächste Schritt in createSlidePresentation() besteht darin, die Zellendaten aus der Google-Tabelle in die neue Präsentation zu importieren. Fügen Sie dieses Code-Snippet in die Funktion ein:

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

Der letzte Schritt in createSlidePresentation() besteht darin, eine weitere Folie hinzuzufügen, das Diagramm aus der Tabelle zu importieren und das Objekt Presentation zurückzugeben. Fügen Sie dieses Code-Snippet in die Funktion ein:

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

Da die Funktion jetzt vollständig ist, lohnt sich ein genauerer Blick auf ihre Signatur. Für die Funktion createSlidePresentation() sind sowohl Tabellen- als auch Diagrammobjektparameter erforderlich. Wir haben runQuery() bereits so angepasst, dass das Objekt Spreadsheet zurückgegeben wird. Jetzt nehmen wir eine ähnliche Änderung in der Funktion createColumnChart() vor, damit ein Diagrammobjekt (EmbeddedChart) zurückgegeben wird. Kehren Sie zu createColumnChart() zurück und fügen Sie das folgende Code-Snippet am Ende der Funktion ein:

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

Da createColumnChart() jetzt ein Diagrammobjekt zurückgibt, müssen wir das Diagramm in einer Variablen speichern. Wir übergeben dann sowohl die Tabellen- als auch die Diagrammvariablen an createSlidePresentation(). Da wir den Link des neu erstellten Tabellenblatts protokollieren, protokollieren wir auch den Link zur neuen Folienpräsentation. Aktualisieren Sie Ihre createBigQueryPresentation() so, dass sie so aussieht:

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

Speichern Sie die Datei und führen Sie createBigQueryPresentation() noch einmal aus. Bevor die Funktion ausgeführt wird, benötigt Ihre App jedoch noch eine weitere Reihe von Berechtigungen von Ihrem Nutzer, um Google-Präsentationen aufrufen und verwalten zu können. Nachdem Sie diese Berechtigungen geprüft und zugelassen haben, wird die Funktion wie gewohnt ausgeführt.
Neben der erstellten Tabelle ist hier auch eine neue Google-Präsentation mit drei Folien vorhanden (Titel, Datentabelle, Datendiagramm), wie in den folgenden Abbildungen zu sehen:

7. Fazit

Herzlichen Glückwunsch! Sie haben eine Anwendung erstellt, die beide Seiten von Google Cloud nutzt. Es wird eine Google BigQuery-Anfrage ausgeführt, mit der eines der öffentlichen Datasets abgefragt wird. Anschließend wird eine Google-Tabelle zum Speichern der Ergebnisse erstellt, ein Diagramm auf Grundlage der Daten hinzugefügt und schließlich eine Google-Präsentation mit den Daten und Diagrammergebnissen aus der Tabelle erstellt.

Das ist die technische Beschreibung. Allgemeiner formuliert, haben Sie eine Big-Data-Analyse in ein Ergebnis umgewandelt, das Sie anderen an Ihrem Projekt Interessierten präsentieren können, und zwar völlig automatisiert mithilfe von Code. Wir hoffen, dass dieses Beispiel Sie dazu anregt, es für Ihre eigenen Projekte anzupassen. Am Ende dieses Codelabs geben wir Ihnen einige Vorschläge, wie Sie diese Beispiel-App weiter verbessern können.

Mit den Änderungen aus der letzten Aufgabe (mit Ausnahme von PROJECT_ID) sollte Ihr bq-sheets-slides.js jetzt so aussehen:

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

Dieses Codebeispiel finden Sie auch im Ordner final im GitHub-Repository.

8. Zusätzliche Ressourcen

Unten finden Sie weitere Ressourcen, die detaillierte Informationen zu den Inhalten in diesem Codelab sowie zu Möglichkeiten für einen programmatischen Zugriff auf die Google-Entwicklertools enthalten.

Ressourcen für diese Anwendung

Dokumentation

Videos

Neuigkeiten & Updates

Google Cloud-Blog
Google Cloud Data Analytics-Blog
Google Developers-Blog
X: Google Developers (@googledevs)
Google Workspace Developers-Blog
X: Google Workspace Developers (@workspacedevs)

Weitere Codelabs

Einführung

[Google Sheets] Grundlagen von Apps Script mit Google Sheets
[REST APIs] Google Workspace- und Google-APIs verwenden, um auf Dateien und Ordner in Google Drive zuzugreifen

Fortgeschritten

[Apps Script] CLASP-Befehlszeilentool für Apps Script
[Apps Script] Google Workspace-Add-ons für Gmail
[Apps Script] Benutzerdefinierte Bots für Hangouts Chat
[REST APIs] Google Sheets als Berichtstool für Ihre Anwendung verwenden
[REST APIs] Google-Präsentationen mit der BigQuery API erstellen

9. Nächster Schritt: Code-Herausforderungen

Nachfolgend finden Sie verschiedene Möglichkeiten, das in diesem Codelab erstellte Beispiel zu erweitern. Diese Liste ist nicht vollständig, enthält aber einige Ideen, wie Sie den nächsten Schritt gehen können.

Anwendung: Sie möchten nicht auf JavaScript beschränkt sein oder die Einschränkungen von Apps Script umgehen? Portieren Sie diese Anwendung in Ihre bevorzugte Programmiersprache, die die REST APIs für Google BigQuery, Google Sheets und Google Präsentationen verwendet.
BigQuery: Führen Sie einen Test mit einer anderen Abfrage für das Shakespeare-Dataset durch, das Sie interessiert. Eine weitere Beispielabfrage finden Sie in der ursprünglichen Apps Script-Beispielanwendung für BigQuery.
BigQuery: Testen Sie einige der anderen öffentlichen Datasets von BigQuery, um ein für Sie aussagekräftigeres Dataset zu finden.
BigQuery: Wir haben bereits andere Abfragen erwähnt, die Sie mit den Werken von Shakespeare oder anderen öffentlichen Datentabellen erstellen können. Sie finden sie auf dieser Webseite und in diesem GitHub-Repository.
Google Tabellen: Experimentieren Sie mit anderen Diagrammtypen in der Diagrammgalerie.
Google Sheets und BigQuery: Verwenden Sie Ihr eigenes großes Tabellendataset. 2016 führte das BigQuery-Team eine Funktion ein, mit der Entwickler ein Google-Tabellenblatt als Datenquelle verwenden können. Weitere Informationen finden Sie unter Google BigQuery-Integration in Google Drive.
Folien: Sie können der generierten Präsentation weitere Folien hinzufügen, z. B. Bilder oder andere Assets, die mit Ihrer Big Data-Analyse verknüpft sind. Referenzdokumentation für den integrierten Google-Dienst „Google-Präsentationen“
Google Workspace: Verwenden Sie andere Google Workspace- oder integrierte Google-Dienste über Apps Script. Dazu gehören beispielsweise Gmail, Google Kalender, Google Docs, Google Drive, Google Maps, Google Analytics und YouTube sowie andere erweiterte Dienste. Weitere Informationen finden Sie in der Referenzübersicht für integrierte und erweiterte Dienste.