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

1. Von der Big-Data-Analyse zur Folienpräsentation

Es gibt viele Tools für Data Scientists, um Big-Data-Analysen durchzuführen, aber am Ende müssen Fachkräfte für Datenanalyse die Ergebnisse dem Management gegenüber rechtfertigen. Viele Zahlen auf Papier oder in einer Datenbank können den wichtigsten Stakeholdern kaum vorgelegt werden. In diesem Google Apps Script-Codelab für Fortgeschrittene werden zwei Google-Entwicklerplattformen (Google Workspace und Google Cloud Console) verwendet, um diesen letzten Schritt zu automatisieren.

Mit den Entwicklertools von Google Cloud können Sie eine umfassende Datenanalyse durchführen. Sie können die Ergebnisse dann in eine Tabellenkalkulation einfügen und eine Folienpräsentation mit den Daten erstellen. Dies bietet eine geeignetere Methode, um Daten an das Management zu liefern. Dieses Codelab behandelt die BigQuery der Cloud Console (als erweiterter Dienst von Apps Script) und die integrierten Apps Script-Dienste für Google Tabellen und Google Präsentationen.

Motivation

Die Beispiel-App in diesem Codelab wurde von diesen anderen Codebeispielen inspiriert:

• Die Beispiel-App für den BigQuery-Dienst von Google Apps Script, die als Open Source auf GitHub zur Verfügung steht.
• Die Beispiel-App, die im Entwicklervideo zum Erstellen von Folien aus Tabellendaten vorgestellt und in diesem Blogpost veröffentlicht wurde.
• Die Beispiel-App im Codelab für die Google Slides API.

Die Präsentationen API-Codelab-Beispiel-App unterstützt zwar auch BigQuery und Präsentationen, unterscheidet sich jedoch in mehreren Punkten von der Beispielanwendung dieses Codelabs:

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

In diesem Codelab wollten wir mehrere Technologien in einer einzigen Anwendung bündeln und gleichzeitig Funktionen und APIs aus Google Cloud auf eine reale Weise präsentieren. Das Ziel ist, Sie dazu anzuregen, Ihrer Fantasie freien Lauf zu lassen und sowohl die Cloud Console als auch Google Workspace zu verwenden, um herausfordernde Probleme für Ihr Unternehmen und Ihre Kunden zu lösen.

Lerninhalte

• Google Apps Script mit mehreren Google-Diensten verwenden
• So verwenden Sie Google BigQuery zur Analyse von Big Data
• Eine Google-Tabelle erstellen und Daten einfügen
• Diagramme in Google Tabellen erstellen
• Daten und Diagramme aus Google Tabellen in eine Google-Präsentation übertragen

Voraussetzungen

• Einen Webbrowser mit Zugriff auf das Internet
• Ein Google-Konto (Google Workspace-Konten erfordern möglicherweise die Genehmigung eines Administrators)
• Grundkenntnisse in Google Tabellen
• Lesezugriff auf Google Tabellen A1-Notation
• Grundlegende JavaScript-Kenntnisse
• Kenntnisse in der Apps Script-Entwicklung sind hilfreich, aber nicht erforderlich

2. Umfrage

Wie werden Sie dieses Codelab/Tutorial verwenden?

<ph type="x-smartling-placeholder"></ph> Lies sie zu Informationszwecken durch und leite sie möglicherweise an technische Kollegen weiter. Besprich sie so gut wie möglich und probiere so viele Übungen wie möglich aus. Ich werde auch das gesamte Codelab durcharbeiten.

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

<ph type="x-smartling-placeholder"></ph> Neuling Mittel Kompetent

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

<ph type="x-smartling-placeholder"></ph> Neuling Mittel Kompetent

Wie würden Sie Ihre Erfahrung mit den Cloud Console-Entwicklertools und APIs?

<ph type="x-smartling-placeholder"></ph> Neuling Mittel Kompetent

3. Übersicht

Nachdem Sie nun wissen, worum es in diesem Codelab geht, werden Sie Folgendes tun:

1. Nehmen Sie ein vorhandenes Apps Script-BigQuery-Beispiel und machen Sie es zum Laufen.
2. Anhand des Beispiels lernen Sie, wie Sie eine Abfrage an BigQuery senden und die Ergebnisse abrufen.
3. Erstellen Sie eine Google-Tabelle und fügen Sie die Ergebnisse aus BigQuery ein.
4. Ändern Sie den Code so, dass die zurückgegebenen und in das Tabellenblatt eingefügten Daten leicht abgeändert werden.
5. Verwenden Sie Google Tabellen in Apps Script, um ein Diagramm für die BigQuery-Daten zu erstellen.
6. Mit Google Präsentationen können Sie eine Google-Präsentation erstellen.
7. Fügen Sie der Standard-Titelfolie einen Titel und eine Unterüberschrift hinzu.
8. Erstellen Sie eine Folie mit einer Datentabelle und exportieren Sie die Datenzellen der Tabelle in diese Tabelle.
9. Erstellen Sie eine weitere Folie und fügen Sie das Tabellenkalkulationsdiagramm in diese ein.

Beginnen wir mit einigen Hintergrundinformationen zu Apps Script, BigQuery, Tabellen und Präsentationen.

Google Apps Script und BigQuery

Google Apps Script ist eine Google Workspace-Entwicklungsplattform, die auf einer höheren Ebene als Google REST APIs betrieben wird. Es ist eine serverlose Umgebung für Entwicklung und Anwendungshosting, auf die alle Entwicklerkenntnisse zugreifen können. Apps Script ist eine serverlose JavaScript-Laufzeit für die Automatisierung, Erweiterung und Einbindung von Google Workspace.

Dabei wird ähnlich wie Node.js serverseitiges JavaScript verwendet, der Schwerpunkt liegt jedoch auf einer engen Einbindung in Google Workspace und andere Google-Dienste statt auf schnellem, asynchronem ereignisgesteuertem Anwendungshosting. Außerdem ist eine Entwicklungsumgebung vorhanden, die sich von der gewohnten Umgebung unterscheidet. mit folgenden Vorteilen:

• Sie können Skripts mit einem browserbasierten Codeeditor oder lokal entwickeln, wenn Sie clasp verwenden, das Bereitstellungstool für Apps Script, das in der Befehlszeile ausgeführt wird.
• Schreiben Sie Code in einer speziellen Version von JavaScript, die für den Zugriff auf Google Workspace und andere Google- oder externe Dienste (mit den Apps Script-Diensten URL Fetch oder JDBC) angepasst ist.
• Sie müssen keinen Autorisierungscode schreiben, weil Apps Script die Verarbeitung übernimmt.
• Sie müssen Ihre Anwendung nicht selbst hosten – sie wird auf den Servern von Google in der Cloud ausgeführt.

HINWEIS: In diesem Codelab lernen Sie nicht, wie Sie Apps Script verwenden. Hilfe dazu erhalten Sie in den umfassenden Online-Ressourcen. Die offizielle Dokumentation enthält auch eine Übersicht mit Kurzanleitungen, Anleitungen und Videos. Bevor Sie mit diesem beginnen, sollten Sie die beiden einführenden Codelabs absolvieren: Grundlagen zu Apps Script mit Google Tabellen und Praktische Übung mit Google Apps Script.

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

• Als integrierten Dienst
• Als erweiterter Dienst

Ein integrierter Dienst bietet allgemeine Methoden für die Interaktion mit Nutzerdaten, anderen Google-Systemen und externen Systemen. Ein erweiterter Dienst ist im Wesentlichen ein Thin Wrapper um eine Google Workspace API oder eine Google REST API. Erweiterte Dienste bieten eine vollständige Abdeckung der REST API und können oft mehr als integrierte Dienste. Sie erfordern jedoch eine höhere Codekomplexität und sind gleichzeitig einfacher zu verwenden als die vollständige REST API. Erweiterte Dienste müssen außerdem für ein Skriptprojekt aktiviert werden, bevor sie verwendet werden können.

Entwickler sollten nach Möglichkeit einen integrierten Dienst verwenden, weil dieser einfacher zu nutzen ist und mehr kann als erweiterte Dienste. Einige Google APIs haben jedoch keine integrierten Dienste, sodass ein erweiterter Dienst die einzige Option ist. Google BigQuery hat beispielsweise keinen integrierten Dienst, während der BigQuery-Dienst existiert. Der BigQuery-Dienst ist ein Cloud Console-Dienst, mit dem Sie die Google BigQuery API verwenden können, um Abfragen mit großen Datenkorpus (z. B. mehrere Terabyte) durchzuführen und trotzdem innerhalb von Sekunden Ergebnisse bereitzustellen.

Auf Google Tabellen und Folien aus Apps Script

Im Gegensatz zu BigQuery haben sowohl Google Tabellen als auch Google Präsentationen integrierte Dienste. Außerdem bieten sie erweiterte Dienste für den Zugriff auf Funktionen, die nur in der API verfügbar sind. Sehen Sie sich die Dokumente für die integrierten Dienste von Google Tabellen und Google Präsentationen an, bevor Sie mit dem Code fortfahren. Außerdem stehen Dokumente zu den erweiterten Diensten von Google Tabellen und Google Präsentationen zur Verfügung.

4. Aufgabe 1: BigQuery ausführen und Ergebnisse in Google Tabellen protokollieren

Einführung

Bei dieser ersten Aufgabe befassen wir uns mit einem großen Teil dieses Codelabs. Sobald Sie sie abgeschlossen haben, sind Sie mit dem gesamten Codelab etwa zur Hälfte fertig. In mehreren Unterabschnitten können Sie:

• Erstellen Sie sowohl ein Google Apps Script-Projekt 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.
• Führen Sie den Autorisierungsprozess für Anwendungen (OAuth2) durch.
• 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

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

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

Hinweis: Wenn Sie dieses Codelab über einen Kiosk bei einer von Google gehosteten Veranstaltung ausführen, wurde bereits ein Cloud Console-Projekt für Sie erstellt. Öffnen Sie das Projekt und fahren Sie mit Schritt 5 unten fort.

3. Wenn Sie ein Cloud Console-Projekt erstellen möchten, verwenden Sie diesen Verknüpfungslink, um ein Projekt zu erstellen, geben Sie dem Projekt einen Namen und klicken Sie auf Erstellen.

Hinweis: BigQuery ist ein kostenpflichtiger Dienst, dessen Kosten sowohl für das Speichern als auch für das Abfragen von Daten anfallen. In diesem Codelab wird eine kleine Datenmenge abgefragt, die unter die kostenlose Nutzungsstufe fallen sollte. Weitere Informationen finden Sie in der BigQuery-Preisübersicht.

4. Wenn die Projekterstellung abgeschlossen ist, wird eine Benachrichtigung auf der Seite angezeigt. Prüfen Sie, ob das neue Projekt in der Projektliste oben auf der Seite ausgewählt ist.
5. Klicken Sie auf das Dreistrich-Menü und gehen Sie zu APIs und Dienste > OAuth-Zustimmungsbildschirm (direkter Link)
6. Klicken Sie auf Intern > Erstellen, um eine App für Google Workspace-Nutzer in Ihrer Organisation zu erstellen.
7. Geben Sie im Feld App name (App-Name) „Big Data Codelab“ ein.
8. Geben Sie die Kontakt-E-Mail-Adressen in die Felder Nutzersupport und Kontaktdaten des Entwicklers ein.
9. Klicken Sie auf Speichern und fortfahren > Speichern und fortfahren.
10. Klicken Sie in der Navigationsleiste auf das Dreipunkt-Menü und wählen Sie Projekteinstellungen (direkter Link) aus.
11. Kopieren Sie den Wert unter Projektnummer. Im Codelab wird später ein separates Feld Projekt-ID verwendet.

Hinweis: Die Details zu Ihrem Projekt finden Sie auch in der Cloud Console auf der Dashboard-Seite.

Als Nächstes verbinden Sie Ihr Apps Script-Projekt mit dem Cloud Console-Projekt.

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

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

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

HINWEIS: Nach dem Aktivieren der API wird auf dieser Seite möglicherweise der Hinweis „Um diese API zu verwenden, müssen Sie Anmeldedaten erstellen...“ sehen. Das ist erst einmal kein Problem – Apps Script übernimmt diesen Schritt für Sie.

Jetzt können Sie den Anwendungscode eingeben, den Autorisierungsprozess durchlaufen und die erste Ausführung dieser Anwendung starten.

Anwendung hochladen und ausführen

1. Ersetzen Sie im Script-Editor den standardmäßigen myFunction()-Codeblock 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());
}

2. Klicken Sie auf „Speichern“ .
3. Klicken Sie neben Code.gs auf das Dreipunkt-Menü > Umbenennen. Ändere den Titel von „Code.gs“ zu „bq-sheets-slides.js“.
4. Sehen wir uns als Nächstes den Code an, mit dem BigQuery abgefragt und die Ergebnisse in eine Google-Tabelle geschrieben werden. Sie können sie im oberen Bereich von runQuery() ansehen:

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 am häufigsten vorkommenden Wörter in allen seinen Werken zurück, in absteigender Reihenfolge nach Beliebtheit sortiert. Sie erhalten eine Vorstellung davon, wie nützlich BigQuery sein kann, wenn Sie sich vorstellen, wie viel Arbeit es wäre, diese Kompilierung von Hand durchzuführen.

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

5. Wechseln Sie zu Ihrem Cloud Console-Projekt, klicken Sie in der Navigationsleiste auf das Dreipunkt-Menü und wählen Sie Projekteinstellungen aus.
6. Kopieren Sie den Wert unter Projekt-ID.
7. Wechseln Sie zurück zum App Script-Editor, suchen Sie in bq-sheets-slides.js nach der Variablen PROJECT_ID und fügen Sie den Wert hinzu.
8. Klicken Sie auf Speichern . Ausführen.
9. Klicken Sie auf Berechtigungen prüfen, um fortzufahren.

Hinweis:Wenn Sie ein gmail.com-Konto verwenden, wird möglicherweise die Meldung „Diese App wurde nicht bestätigt“ angezeigt. wenn Sie Ihr Skript zum ersten Mal verwenden. So warnt Google Nutzer, die möglicherweise Code von unbekannten oder nicht vertrauenswürdigen Autoren verwenden. Wenn dieses Dialogfeld angezeigt wird, können Sie fortfahren, da Sie der Skriptautor sind. Folgen Sie der Anleitung auf dem Bildschirm, um mit der Autorisierung des Skripts fortzufahren:

1. Klicken Sie im Dialogfeld für die nicht verifizierte App auf Erweitert.
2. Klicken Sie auf Zu Ihren Projektnamen wechseln (unsicher).
3. Klicken Sie auf Zulassen.

In diesem Codelab werden Sie möglicherweise mehrere Berechtigungsaufforderungen erhalten. Weitere Informationen zu diesem Vorgang finden Sie unter Autorisierung für Google-Dienste.

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

Wenn Sie das Ausführungsprotokoll ein- oder ausblenden möchten, klicken Sie oben im Editor auf Ausführungsprotokoll.

11. Sobald im Ausführungsprotokoll „ExecutionCompleted“ steht, rufen Sie Google Drive (drive.google.com) auf und suchen Sie die Google-Tabelle mit dem Namen „Die häufigsten Wörter in allen Werken von Shakespeare“. (oder den Namen, den Sie der Variablen QUERY_NAME zugewiesen haben, falls Sie sie aktualisiert haben):
12. Öffnen Sie die Tabelle, um sich die zehn häufigsten Wörter und ihre Gesamtzahl in absteigender Reihenfolge anzusehen:

Zusammenfassung Aufgabe 1

Sie haben Code ausgeführt, mit dem alle Werke Shakespeares nach jedem Wort in jedem Werk von Shakespeare durchsucht wurden. Die Wörter wurden gezählt und in absteigender Reihenfolge sortiert. Sie haben außerdem den integrierten Apps Script-Dienst für Google Tabellen verwendet, um diese Daten anzuzeigen.

Den Code, den Sie für bq-sheets-slides.js verwendet haben, finden Sie auch im Ordner step1 des GitHub-Repositorys dieses Codelabs unter github.com/googlecodelabs/bigquery-sheets-slides. Der Code basierte auf diesem ursprünglichen Beispiel auf der Seite mit den erweiterten BigQuery-Diensten, bei dem mit einer etwas anderen Abfrage die beliebtesten Wörter mit mindestens zehn von Shakespeare verwendeten Zeichen abgerufen wurden. Ein Beispiel finden Sie auch im GitHub-Repository.

Wenn Sie sich für andere Abfragen interessieren, die Sie mit Shakespeares Werken oder anderen öffentlichen Datentabellen erstellen können, lesen Sie BigQuery-Beispieltabellen abfragen und dieses 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 Dreistrich-Menü und rufen Sie die BigQuery-Benutzeroberfläche > SQL-Arbeitsbereich (direkter Link). Ihre Abfrage wird beispielsweise in der grafischen Benutzeroberfläche von BigQuery so dargestellt:

5. Aufgabe 2: Ein Diagramm in Google Tabellen erstellen

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

1. Fügen Sie im Apps Script-Editor die Funktion createColumnChart() nach runQuery() zu bq-sheets-slides.js hinzu. Der Code ruft die Tabelle ab und fordert ein Säulendiagramm mit allen Daten an. Der Datenbereich beginnt bei Zelle A2, da die erste Zeile die Spaltenüberschriften enthält.

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

2. Für die createColumnChart()-Funktion ist ein Tabellenobjektparameter erforderlich. Deshalb müssen wir runQuery() so aktualisieren, dass ein spreadsheet-Objekt zurückgegeben wird, das an createColumnChart() übergeben werden kann. Geben Sie am Ende von runQuery() das Objekt spreadsheet zurück, nachdem Sie die Erstellung des Tabellenblatts protokolliert haben:

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

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

3. Erstellen Sie eine createBigQueryPresentation()-Funktion, um sowohl runQuery() als auch createColumnChart() aufzurufen. Es hat sich bewährt, BigQuery und die Funktion zur Diagrammerstellung logisch voneinander zu trennen:

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

4. Sie haben oben zwei wichtige Schritte ausgeführt: das Tabellenobjekt zurückgegeben und die Eintragsfunktion erstellt. Um runQuery() nutzungsfreundlicher zu machen, müssen wir die Logzeile von runQuery() nach createBigQueryPresentation() verschieben. Ihre 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 (außer PROJECT_ID) sollte bq-sheets-slides.js jetzt so aussehen. Dieser Code befindet sich 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 in der Liste der Funktionen createBigQueryPresentation aus und klicken Sie auf Ausführen. Nach Abschluss des Vorgangs wird eine weitere Tabelle in Google Drive erstellt. Dieses Mal enthält das Tabellenblatt jedoch ein Diagramm neben den Daten:

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

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

1. Fügen Sie im Apps Script-Editor die Funktion createSlidePresentation() nach createColumnChart() zu bq-sheets-slides.js hinzu. Die gesamte Arbeit an der Präsentation findet in dieser Funktion statt. Beginnen wir mit der Erstellung einer Präsentation und fügen dann der Standard-Titelfolie einen Titel und eine Unterüberschrift hinzu.

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

2. Im nächsten Schritt in createSlidePresentation() importieren Sie die Zellendaten aus der Google-Tabelle in unsere neue Präsentation. Fügen Sie der Funktion dieses Code-Snippet hinzu:

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

3. Im letzten Schritt in createSlidePresentation() fügen Sie eine weitere Folie hinzu, importieren das Diagramm aus unserer Tabelle und geben das Presentation-Objekt zurück. Fügen Sie der Funktion dieses Code-Snippet hinzu:

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

4. Nachdem die Funktion nun vollständig ist, schauen Sie sich noch einmal ihre Signatur an. Für createSlidePresentation() sind sowohl Tabellen- als auch Diagrammobjektparameter erforderlich. Wir haben runQuery() bereits so angepasst, dass das Spreadsheet-Objekt zurückgegeben wird. Wir müssen aber eine ähnliche Änderung an createColumnChart() vornehmen, damit ein Diagrammobjekt (EmbeddedChart) zurückgegeben wird. Kehren Sie zu createColumnChart() zurück und fügen Sie am Ende der Funktion das folgende Code-Snippet hinzu:

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

5. Da createColumnChart() jetzt ein Diagrammobjekt zurückgibt, müssen Sie das Diagramm in einer Variablen speichern. Anschließend übergeben wir sowohl die Variablen in der Tabelle als auch die Diagrammvariablen an createSlidePresentation(). Da wir die URL der neu erstellten Tabelle protokollieren, protokollieren wir auch die URL der neuen Folienpräsentation. Aktualisieren Sie Ihre createBigQueryPresentation() so, dass sie wie folgt 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
}

6. Speichern Sie die Datei und führen Sie createBigQueryPresentation() noch einmal aus. Bevor sie ausgeführt wird, benötigt Ihre App jedoch noch weitere Berechtigungen von Ihrem Nutzer, um Google-Präsentationen aufrufen und verwalten zu können. Nachdem Sie diese Berechtigungen überprüft und gewährt haben, wird der Test wie zuvor ausgeführt.
7. Neben der erstellten Tabelle sollten Sie jetzt auch eine neue Google-Präsentation mit drei Folien erhalten (Titel, Datentabelle, Datendiagramm), wie unten dargestellt:

7. Fazit

Herzlichen Glückwunsch! Sie haben eine Anwendung erstellt, die beide Seiten von Google Cloud nutzt. Es führt eine Google BigQuery-Anfrage aus, die einen seiner öffentlichen Datasets abfragt, eine Google Tabellen-Tabelle zum Speichern der Ergebnisse erstellt, ein Diagramm auf der Grundlage der Daten hinzufügt und schließlich eine Google-Präsentation mit den Daten- und Diagrammergebnissen aus der Tabelle erstellt.

Diese Schritte sind die technischen Schritte, die Sie durchgeführt haben. Im Grunde haben Sie von der Big-Data-Analyse zu einem Ergebnis übergegangen, das Sie den Stakeholdern präsentieren können – alles automatisch mit Code. Wir hoffen, dass dieses Beispiel Sie dazu inspiriert, es für Ihre eigenen Projekte anzupassen. Am Ende dieses Codelabs erhältst du einige Vorschläge, wie du diese Beispiel-App weiter verbessern kannst.

Mit den Änderungen aus der letzten Aufgabe (außer PROJECT_ID) sollte 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 befindet sich auch im Ordner final im GitHub-Repository.

8. Zusätzliche Ressourcen

Unten finden Sie weitere Ressourcen mit weiteren Informationen zu den Inhalten in diesem Codelab und zu anderen Möglichkeiten, programmatisch auf die Google-Entwicklertools zuzugreifen.

Ressourcen für diese Anwendung

• Quellcode-Repository
• Entwicklervideo (und) Blogpost
• Google Cloud NEXT '18-Sitzung

Dokumentation

• Dokumentationswebsite für Google Apps Script
• Apps Script: Tabellenkalkulation
• Apps Script: Google Präsentationen-Dienst
• Apps Script: Erweiterter BigQuery-Dienst

Videos

• Ein weiteres Google Apps Script-Secret
• Über eine Tabelle auf Google Maps zugreifen
• Komplett ohne Script
• Google Workspace Developer Show

Neuigkeiten & Updates

• Google Cloud Platform-Blog
• Google Cloud Data Analytics-Blog
• Google Developers-Blog
• Twitter: Google Developers (@googledevs)
• Blog für Google Workspace-Entwickler
• Twitter: Google Workspace-Entwickler (@workspacedevs)

Weitere Codelabs

Einführung

• [Google Tabellen] Apps Scripts mit Google Tabellen: Grundlagen
• [REST APIs] Google Workspace nutzen und Google APIs für den Zugriff auf Dateien und Ordner in Google Drive

Fortgeschritten

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

9. Der nächste Schritt: Code-Challenges

Im Folgenden sind verschiedene Möglichkeiten aufgeführt, wie Sie das Beispiel, das wir in diesem Codelab erstellt haben, verbessern oder erweitern können. Diese Liste ist nicht vollständig, enthält aber einige inspirierende Ideen für den nächsten Schritt.

• Anwendung: Sie möchten nicht auf die Verwendung von JavaScript oder die Einschränkungen von Apps Script beschränkt sein? Portieren Sie diese Anwendung in Ihre bevorzugte Programmiersprache, die die REST APIs für Google BigQuery, Google Tabellen und Google Präsentationen verwendet.
• BigQuery: Experimentieren Sie mit einer anderen Abfrage für das Shakespeare-Dataset, die Sie interessieren. Eine weitere Beispielabfrage finden Sie in der ursprünglichen BigQuery-Beispiel-App für Apps Script.
• BigQuery: Experimentieren Sie mit einigen 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 Shakespeares Werken oder anderen öffentlichen Datentabellen erstellen können. Sie finden sie auf dieser Webseite und in diesem GitHub-Repository.
• Tabellen: In der Diagrammgalerie können Sie mit anderen Diagrammtypen experimentieren.
• Tabellen und BigQuery: Verwenden Sie Ihr eigenes großes Tabellen-Dataset. 2016 hat das BigQuery-Team eine Funktion eingeführt, mit der Entwickler eine Tabelle als Datenquelle verwenden können. Weitere Informationen finden Sie unter Google BigQuery kann in Google Drive integriert werden.
• Google Präsentationen: 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. Hier finden Sie die Referenzdokumentation für den integrierten Dienst in Google Präsentationen.
• Google Workspace: Sie können andere in Google Workspace oder Google integrierte Dienste über Apps Script verwenden. z. B. Gmail, Kalender, Docs, Drive, Maps, Analytics, YouTube und andere erweiterte Dienste. Weitere Informationen finden Sie in der Referenzübersicht für integrierte und erweiterte Dienste.