GSP240

Überblick

Bei der Big-Data-Analyse können Data Scientists zwischen vielen Tools wählen. Doch welches davon eignet sich am besten, um die Analyseergebnisse der Geschäftsleitung und anderen beteiligten Gruppen und Personen zu erklären und vor diesen zu rechtfertigen? Blanke Zahlenreihen auf Papier oder in einer Datenbank sind denkbar ungeeignet. In diesem Lab zu Google Apps Script nutzen Sie zwei Entwicklungsplattformen von Google, mit denen Sie Ihre Daten in ein geeignetes Format bringen: Workspace und Google Cloud.

Mithilfe der Google Cloud-Entwicklertools können Sie Daten zunächst einmal erfassen und analysieren. Im zweiten Schritt stellen Sie Ihre Analyse samt Schlussfolgerungen in einer Präsentation vor – verständlich aufbereitet für Vorgesetzte sowie andere Beteiligte.

In diesem Lab arbeiten Sie mit der BigQuery API von Google Cloud (als erweiterter Dienst von Apps Script) und mit den integrierten Apps Script-Diensten für Google Sheets und Google Präsentationen.

Dargestellt wird ein sehr praxisnahes Szenario. Zusammen mit der verwendeten Anwendung setzen Sie Funktionen und APIs der gesamten Google Cloud ein. Ziel des Labs ist, zu zeigen, wie Sie Herausforderungen für Ihr Unternehmen oder Ihre Kundinnen und Kunden mit Google Cloud und Workspace meistern können.

Lerninhalte

• Google Apps Script mit mehreren Google-Diensten verwenden
• Big-Data-Analyse mit BigQuery durchführen
• Daten in einer Google-Tabelle darstellen und die Tabellendaten in Diagrammform bringen
• Diagramm und Daten aus der Tabelle in einzelne Folien einer Google-Präsentation übertragen

Einrichtung

Vor dem Klick auf „Start Lab“ (Lab starten)

Lesen Sie diese Anleitung. Labs sind zeitlich begrenzt und können nicht pausiert werden. Der Timer beginnt zu laufen, wenn Sie auf Lab starten klicken, und zeigt Ihnen, wie lange Google Cloud-Ressourcen für das Lab verfügbar sind.

In diesem praxisorientierten Lab können Sie die Lab-Aktivitäten in einer echten Cloud-Umgebung durchführen – nicht in einer Simulations- oder Demo-Umgebung. Dazu erhalten Sie neue, temporäre Anmeldedaten, mit denen Sie für die Dauer des Labs auf Google Cloud zugreifen können.

Für dieses Lab benötigen Sie Folgendes:

• Einen Standardbrowser (empfohlen wird Chrome)

Hinweis: Nutzen Sie den privaten oder Inkognitomodus (empfohlen), um dieses Lab durchzuführen. So wird verhindert, dass es zu Konflikten zwischen Ihrem persönlichen Konto und dem Teilnehmerkonto kommt und zusätzliche Gebühren für Ihr persönliches Konto erhoben werden.

• Zeit für die Durchführung des Labs – denken Sie daran, dass Sie ein begonnenes Lab nicht unterbrechen können.

Hinweis: Verwenden Sie für dieses Lab nur das Teilnehmerkonto. Wenn Sie ein anderes Google Cloud-Konto verwenden, fallen dafür möglicherweise Kosten an.

Einführung

Google Apps Script und BigQuery

Google Apps Script ist eine Entwicklungsplattform für 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. Programmiert wird in JavaScript. Damit lassen sich Workspace-Anwendungen einfach automatisieren, erweitern und in andere Anwendungen einbinden.

Ä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 Workspace und andere Google-Dienste. Deshalb unterscheidet sich die Entwicklungsumgebung grundlegend von den meisten anderen. Apps Script bietet folgende Vorteile:

• Entwickelt wird in einem browserbasierten Code-Editor, aber auch lokale Entwicklung ist möglich, wenn Sie clasp nutzen, das Befehlszeilen-Deployment-Tool für Apps Script.
• Code wird in einer speziellen Version von JavaScript geschrieben, die für den Zugriff auf Workspace und andere Google- oder externe Dienste (über die Apps Script-Dienste URLfetch oder JDBC) angepasst ist.
• Schreiben von Autorisierungscode entfällt – das übernimmt Apps Script.
• Hosten entfällt – Ihre Anwendung läuft auf den Google-Servern in der Cloud.

Hinweis: Weitere Informationen zu Apps Script finden Sie in der offiziellen Dokumentation, die eine Übersicht mit Kurzanleitungen, Tutorials und Videos enthält.

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

• Integrierte Dienste
• Erweiterte Dienste

Ein integrierter Dienst bietet Top-Level-Methoden für den Zugriff auf Workspace- oder Google-Produktdaten und andere nützliche Hilfsmethoden. Ein erweiterter Dienst ist lediglich ein Thin Wrapper um eine Workspace 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. Normalerweise sind die integrierten Dienste zu bevorzugen, weil sie 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. Ein Beispiel dafür ist BigQuery: Es gibt keinen integrierten, aber einen erweiterten BigQuery-Dienst. (Und das ist allemal besser als gar kein Dienst.)

Hinweis: BigQuery ist ein Google Cloud-Dienst, mit dem Sie einfache oder komplexe Abfragen von Datasets mit einer Größe von mehreren Terabyte durchführen können. Die Ergebnisse sind innerhalb weniger Sekunden verfügbar und nicht erst nach mehreren Stunden oder Tagen.

Aus Apps Script auf Google Sheets und Präsentationen zugreifen

BigQuery ist nur als erweiterter Apps Script-Dienst verfügbar. Google Sheets und Google Präsentationen sind hingegen mit beiden Apps Script-Dienstarten ausgestattet: den integrierten und den erweiterten. So können die Anwendungen auch auf Funktionen zugreifen, die nur in der API, aber nicht integriert vorhanden sind. Wählen Sie nach Möglichkeit immer einen integrierten Dienst statt der erweiterten Variante. Das erleichtert die Entwicklung, da mit dem integrierten Dienst auf höherer Ebene Dienste erstellt und Convenience-Aufrufe ausgeführt werden können.

Hinweis: Wenn Sie tiefer in das Thema einsteigen möchten, bevor Sie mit dem Code arbeiten, lesen Sie die Artikel zu Google Sheets und Google Präsentationen.

Aufgabe 1: BigQuery abfragen und die Ergebnisse in Google Sheets protokollieren

Die erste Aufgabe nimmt viel Platz in diesem Lab ein. Wenn Sie den Abschnitt abgeschlossen haben, sind Sie mit dem gesamten Lab etwa zur Hälfte fertig.

In diesem Abschnitt haben Sie folgende Aufgaben:

• Ein neues Google Apps Script-Projekt beginnen
• Zugriff auf den erweiterten BigQuery-Dienst aktivieren
• Entwicklungseditor aufrufen und den Quellcode der Anwendung eingeben
• Autorisierungsprozess (OAuth2) der Anwendung durchlaufen
• Anwendung ausführen, die eine Anfrage an BigQuery sendet
• Neue Google Tabelle aufrufen, die aus den BigQuery-Ergebnissen erstellt wurde

Neues Apps Script-Projekt erstellen

1. Erstellen Sie ein neues Apps Script-Projekt. Rufen Sie dazu script.google.com auf. Klicken Sie für dieses Lab auf den Link Script mit Apps Script erstellen.

2. Der Apps Script-Code-Editor wird geöffnet:

3. Klicken Sie oben auf den Projektnamen (in der Abbildung oben „Unbenanntes Projekt“), um Ihr Projekt zu benennen.

4. Geben Sie im Dialogfeld Projekt umbenennen einen Namen für das Projekt ein (zum Beispiel „BigQuery“, „Sheets“, „Präsentationen-Demo“) und klicken Sie dann auf Umbenennen.

Den erweiterten BigQuery-Dienst aktivieren

Aktivieren Sie den erweiterten BigQuery-Dienst für das neue Projekt und aktivieren Sie die BigQuery API.

1. Klicken Sie neben Dienste auf das Symbol Dienst hinzufügen.

2. Wählen Sie im Dialogfeld Dienst hinzufügen alle zutreffenden Dienste und APIs aus.

3. Rufen Sie die Cloud Console auf und wählen Sie Navigationsmenü > APIs und Dienste > Bibliothek aus.

4. Geben Sie in das Suchfeld BigQuery API ein und wählen Sie BigQuery API aus.

5. Klicken Sie bei Bedarf auf Aktivieren, um die BigQuery API zu aktivieren.

6. Kehren Sie zu Ihrem Projekt zurück. Das Dialogfeld Dienst hinzufügen sollte noch geöffnet sein.

7. Wählen Sie BigQuery API aus und klicken Sie auf Hinzufügen, um das Dialogfeld zu schließen.

Anwendungscode eingeben und ausführen

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

1. Kopieren Sie den Code im folgenden Feld und ersetzen Sie damit sämtlichen Code im Code-Editor:

/** * Copyright 2018 Google LLC * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0. * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ // Filename for data results var QUERY_NAME = "Most common words in all of Shakespeare's works"; // Replace this value with your Google Cloud API project ID var PROJECT_ID = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup'); /** * Runs a BigQuery query; puts results into Sheet. You must enable * the BigQuery advanced service before you can run this code. * @see http://developers.google.com/apps-script/advanced/bigquery#run_query * @see http://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs * * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results * @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet */ function runQuery() { // Replace sample with your own BigQuery query. var request = { query: 'SELECT ' + 'LOWER(word) AS word, ' + 'SUM(word_count) AS count ' + 'FROM [bigquery-public-data:samples.shakespeare] ' + 'GROUP BY word ' + 'ORDER BY count ' + 'DESC LIMIT 10' }; var queryResults = BigQuery.Jobs.query(request, PROJECT_ID); var jobId = queryResults.jobReference.jobId; // Wait for BQ job completion (with exponential backoff). var sleepTimeMs = 500; while (!queryResults.jobComplete) { Utilities.sleep(sleepTimeMs); sleepTimeMs *= 2; queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId); } // Get all results from BigQuery. var rows = queryResults.rows; while (queryResults.pageToken) { queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, { pageToken: queryResults.pageToken }); rows = rows.concat(queryResults.rows); } // Return null if no data returned. if (!rows) { return Logger.log('No rows returned.'); } // Create the new results spreadsheet. var spreadsheet = SpreadsheetApp.create(QUERY_NAME); var sheet = spreadsheet.getActiveSheet(); // Add headers to Sheet. var headers = queryResults.schema.fields.map(function(field) { return field.name.toUpperCase(); }); sheet.appendRow(headers); // Append the results. var data = new Array(rows.length); for (var i = 0; i < rows.length; i++) { var cols = rows[i].f; data[i] = new Array(cols.length); for (var j = 0; j < cols.length; j++) { data[i][j] = cols[j].v; } } // Start storing data in row 2, col 1 var START_ROW = 2; // skip header row var START_COL = 1; sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); }

2. Speichern Sie die Datei, die Sie gerade erstellt haben. Klicken Sie dazu in der Menüleiste auf das Symbol zum Speichern des Projekts oder drücken Sie Strg + S.

3. Klicken Sie auf das Dreipunkt-Menü neben dem Dateinamen und dann auf Umbenennen, um den Namen der Datei zu ändern.

4. Ändern Sie den Dateinamen in bq-sheets-slides.gs und drücken Sie die Eingabetaste.

Welche Funktionen stecken nun in dem Code? Sie wissen bereits, dass damit eine BigQuery-Abfrage durchgeführt wird und die Ergebnisse in eine neue Google-Tabelle geschrieben werden. Aber was genau wird abgefragt?

5. Sehen Sie sich den Abfragecode in der Funktion runQuery() an:

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. Stellen Sie sich vor, Sie müssten diese Aufgabe manuell erledigen! Das gibt Ihnen eine Vorstellung davon, wie nützlich BigQuery ist.

Fast fertig! Sie brauchen eine gültige Projekt-ID, um die Variable PROJECT_ID oben in bq-sheets-slides.gs festzulegen.

6. Ersetzen Sie <YOUR_PROJECT_ID> durch Ihre Projekt-ID, die im linken Bereich angezeigt wird.

So sieht der Code mit einer Beispiel-Projekt-ID aus. Der tatsächliche Wert für „PROJECT_ID“ lautet anders.

Beispielcode:

// Filename for data results var QUERY_NAME = "Most common words in all of Shakespeare's works"; // Replace this value with your Google Cloud API project ID var PROJECT_ID = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup'); Hinweis: In diesem Code-Snippet wird mit der if-Anweisung verhindert, dass die Anwendung ohne Projekt-ID fortgesetzt wird. Hinweis: Wenn sich die Menüauswahl nicht mehr bewegen oder bedienen lässt, aktualisieren Sie die Seite.

7. Speichern Sie die Datei und führen Sie den Code aus, indem Sie in der Menüleiste auf Ausführen klicken.

8. Klicken Sie dann auf Berechtigungen ansehen.

Hinweis: Wenn der Fehler Exception: Service BigQuery API has not been enabled for your Apps. angezeigt wird, entfernen Sie den BigQuery API-Dienst und fügen Sie ihn noch einmal hinzu.

9. Klicken Sie im Dialogfeld „Wählen Sie ein Konto aus qwiklabs.net aus“ auf Ihren Nutzernamen und dann auf Zulassen.

Hinweis: Wenn Sie die Anwendung einmal autorisiert haben, brauchen Sie den Vorgang bei den folgenden Ausführungen nicht mehr zu wiederholen. Das Dialogfeld wird erst weiter unten im Lab-Abschnitt „Ergebnisdaten in Präsentation einfügen“ wieder geöffnet. Dann erteilen Sie die Berechtigung, dass die Anwendung Google-Präsentationen erstellen und verwalten darf.

10. Wenn die Funktion ausgeführt wird, werden Sie darüber in einem kleinen Feld oben benachrichtigt.

Sobald die Ausführung beendet ist, wird das Feld geschlossen. Wenn Sie es also nicht sehen, wurde die Funktion bereits ausgeführt.

11. Rufen Sie Google Drive auf und suchen Sie die neue Google-Tabelle mit dem Namen Most common words in all of Shakespeare's works beziehungsweise dem Namen, den Sie für die Variable QUERY_NAME festgelegt haben:

12. Öffnen Sie die Tabelle. Sie sollten darin zehn Zeilen mit in absteigender Reihenfolge nach der Häufigkeit ihres Vorkommens sortierten Wörtern vorfinden:

Klicken Sie auf Fortschritt prüfen. BigQuery abfragen und die Ergebnisse in Google Sheets protokollieren

Zusammenfassung

Was ist gerade geschehen? Sie haben Code ausgeführt, der alle Werke Shakespeares abgefragt hat. Es gibt sicherlich noch größere Datenmengen, aber selbst bei dieser Menge Text wäre es schwierig für Sie geworden, alle Wörter aller Werke zu erfassen, ihre jeweilige Anzahl zu notieren und ihre Häufigkeit dann noch in absteigender Reihenfolge zu sortieren. Sie haben nicht nur BigQuery angewiesen, diese Aufgabe für Sie zu erledigen, sondern auch den integrierten Dienst in Apps Script genutzt, um die Daten in Google Sheets in ein leicht verständliches Format zu bringen.

Sie können die Abfrage jederzeit in der BigQuery-Konsole testen, bevor Sie sie in Apps Script ausführen. Die Benutzeroberfläche von BigQuery ist für Entwickler verfügbar.

Die BigQuery Console öffnen

1. Klicken Sie in der Google Cloud Console im Navigationsmenü auf BigQuery.

Zuerst wird das Fenster Willkommen bei BigQuery in der Cloud Console geöffnet, das neben allgemeinen Informationen auch einen Link zur Kurzanleitung und zu den Versionshinweisen enthält.

2. Klicken Sie auf Fertig.

Die BigQuery Console wird geöffnet.

3. Geben Sie den Code in den Abfrageeditor ein und klicken Sie auf Ausführen:

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

Aufgabe 2: Diagramm in Google Sheets erstellen

Wechseln Sie zurück in den Skripteditor. Bisher haben Sie eine Anwendung programmiert, die Shakespeares Werke abfragt, die Daten sortiert und sie dann in einer Google-Tabelle darstellt. Im Code kommuniziert die Funktion runQuery() mit BigQuery und sendet die Ergebnisse an eine Tabelle. Jetzt fügen Sie ein weiteres Stück Code hinzu, mit dem die Daten in Diagrammform gebracht werden. Im folgenden Abschnitt erstellen Sie die Funktion createColumnChart(). Diese ruft die Methode newChart() der Tabelle auf, mit der die Daten als Diagramm dargestellt werden.

Die Funktion createColumnChart() ruft die Tabelle mit den Daten ab und fordert ein Säulendiagramm mit allen Daten an. Der Datenbereich beginnt bei Zelle A2, weil in der ersten Zeile keine Daten vorhanden sind, sondern dort nur die Spaltenüberschriften stehen.

1. Diagramm erstellen: Fügen Sie in der Datei bq-sheets-slides.gs die Funktion createColumnChart() direkt nach der Funktion runQuery() ein (nach der letzten Codezeile):

/** * Uses spreadsheet data to create columnar chart. * @param {Spreadsheet} Spreadsheet containing results data * @returns {EmbeddedChart} visualizing the results * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart */ function createColumnChart(spreadsheet) { // Retrieve the populated (first and only) Sheet. var sheet = spreadsheet.getSheets()[0]; // Data range in Sheet is from cell A2 to B11 var START_CELL = 'A2'; // skip header row var END_CELL = 'B11'; // Place chart on Sheet starting on cell E5. var START_ROW = 5; // row 5 var START_COL = 5; // col E var OFFSET = 0; // Create & place chart on the Sheet using above params. var chart = sheet.newChart() .setChartType(Charts.ChartType.COLUMN) .addRange(sheet.getRange(START_CELL + ':' + END_CELL)) .setPosition(START_ROW, START_COL, OFFSET, OFFSET) .build(); sheet.insertChart(chart); }

2. Tabelle zurückgeben: Im Code oben braucht die Funktion createColumnChart() das Tabellenobjekt. Ändern Sie den Code deshalb so, dass die Anwendung das Objekt spreadsheet zurückgibt, damit es an die Funktion createColumnChart() weitergegeben werden kann. Nachdem Sie protokolliert haben, dass die Google-Tabelle erfolgreich erstellt wurde, geben Sie das Objekt am Ende der Funktion runQuery() zurück.

3. Ersetzen Sie die letzte Zeile (beginnt mit Logger.log) durch folgenden Code:

Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // Return the spreadsheet object for later use. return spreadsheet; }

4. Startfunktion für createBigQueryPresentation(): Es ist sinnvoll, die Funktionen für die BigQuery-Abfrage und für die Diagrammerstellung getrennt voneinander aufzurufen. Erstellen Sie die Funktion createBigQueryPresentation(), um die Anwendung zu starten und danach die beiden Funktionen aufzurufen: "runQuery()" und createColumnChart(). Der Code, den Sie einfügen, sollte so aussehen:

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

5. Setzen Sie die Funktion createBigQueryPresentation() direkt unter diesen Codeblock:

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

6. Code für andere nutzbar machen: Sie haben eben zwei wichtige Schritte ausgeführt: das Tabellenobjekt zurückgegeben und eine Startfunktion erstellt. Was ist nun, wenn auch ein Kollege die Funktion runQuery() verwenden möchte, aber nicht will, dass der Link protokolliert wird?

Wenn Sie runQuery() für allgemeinere Zwecke nutzbar machen möchten, verschieben Sie einfach die Log-Zeile. Wohin am besten? Wenn Ihre Antwort createBigQueryPresentation() ist, dann liegen Sie richtig.

Nach dem Verschieben der Log-Zeile sollte der Code so aussehen:

/** * Runs a BigQuery query, adds data and a chart in a Sheet. */ function createBigQueryPresentation() { var spreadsheet = runQuery(); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); createColumnChart(spreadsheet); }

Mit all diesen Änderungen sollte die Datei bq-sheets-slides.js jetzt so aussehen (mit Ausnahme von PROJECT_ID):

/** * Copyright 2018 Google LLC * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0. * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ // Filename for data results var QUERY_NAME = "Most common words in all of Shakespeare's works"; // Replace this value with your Google Cloud API project ID var PROJECT_ID = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup'); /** * Runs a BigQuery query, adds data and a chart in a Sheet. */ function createBigQueryPresentation() { var spreadsheet = runQuery(); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); createColumnChart(spreadsheet); } /** * Runs a BigQuery query; puts results into Sheet. You must enable * the BigQuery advanced service before you can run this code. * @see http://developers.google.com/apps-script/advanced/bigquery#run_query * @see http://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs * * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results * @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet */ function runQuery() { // Replace sample with your own BigQuery query. var request = { query: 'SELECT ' + 'LOWER(word) AS word, ' + 'SUM(word_count) AS count ' + 'FROM [bigquery-public-data:samples.shakespeare] ' + 'GROUP BY word ' + 'ORDER BY count ' + 'DESC LIMIT 10' }; var queryResults = BigQuery.Jobs.query(request, PROJECT_ID); var jobId = queryResults.jobReference.jobId; // Wait for BQ job completion (with exponential backoff). var sleepTimeMs = 500; while (!queryResults.jobComplete) { Utilities.sleep(sleepTimeMs); sleepTimeMs *= 2; queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId); } // Get all results from BigQuery. var rows = queryResults.rows; while (queryResults.pageToken) { queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, { pageToken: queryResults.pageToken }); rows = rows.concat(queryResults.rows); } // Return null if no data returned. if (!rows) { return Logger.log('No rows returned.'); } // Create the new results spreadsheet. var spreadsheet = SpreadsheetApp.create(QUERY_NAME); var sheet = spreadsheet.getActiveSheet(); // Add headers to Sheet. var headers = queryResults.schema.fields.map(function(field) { return field.name.toUpperCase(); }); sheet.appendRow(headers); // Append the results. var data = new Array(rows.length); for (var i = 0; i < rows.length; i++) { var cols = rows[i].f; data[i] = new Array(cols.length); for (var j = 0; j < cols.length; j++) { data[i][j] = cols[j].v; } } // Start storing data in row 2, col 1 var START_ROW = 2; // skip header row var START_COL = 1; sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // Return the spreadsheet object for later use. return spreadsheet; } /** * Uses spreadsheet data to create columnar chart. * @param {Spreadsheet} Spreadsheet containing results data * @returns {EmbeddedChart} visualizing the results * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart */ function createColumnChart(spreadsheet) { // Retrieve the populated (first and only) Sheet. var sheet = spreadsheet.getSheets()[0]; // Data range in Sheet is from cell A2 to B11 var START_CELL = 'A2'; // skip header row var END_CELL = 'B11'; // Place chart on Sheet starting on cell E5. var START_ROW = 5; // row 5 var START_COL = 5; // col E var OFFSET = 0; // Create & place chart on the Sheet using above params. var chart = sheet.newChart() .setChartType(Charts.ChartType.COLUMN) .addRange(sheet.getRange(START_CELL + ':' + END_CELL)) .setPosition(START_ROW, START_COL, OFFSET, OFFSET) .build(); sheet.insertChart(chart); }

7. Speichern Sie die Datei.

8. Klicken Sie in der Menüleiste auf runQuery und wählen Sie im Drop-down-Menü createBigQueryPresentation aus.

9. Klicken Sie dann 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.

Aufgabe 3: Ergebnisdaten in Präsentation einfügen

Im letzten Teil des Labs erstellen Sie eine neue Google-Präsentation, tragen in der Titelfolie einen Titel und Untertitel ein und fügen zwei neue Folien hinzu: eine für alle Datenzellen und eine weitere für das Diagramm.

1. Präsentation erstellen: Beginnen Sie mit dem Erstellen einer Präsentation und fügen Sie auf der Titelfolie, die bei allen neuen Präsentationen automatisch erzeugt wird, Titel und Untertitel ein. Alle Arbeitsschritte, die Sie an der Präsentation vornehmen, finden in der Funktion createSlidePresentation() statt. Diese fügen Sie in der Datei bq-sheets-slides.gs direkt nach dem Funktionscode createColumnChart() ein:

/** * Create presentation with spreadsheet data & chart * @param {Spreadsheet} Spreadsheet with results data * @param {EmbeddedChart} Sheets chart to embed on slide * @returns {Presentation} Slide deck with results */ function createSlidePresentation(spreadsheet, chart) { // Create the new presentation. var deck = SlidesApp.create(QUERY_NAME); // Populate the title slide. var [title, subtitle] = deck.getSlides()[0].getPageElements(); title.asShape().getText().setText(QUERY_NAME); subtitle.asShape().getText().setText('via GCP and G Suite APIs:\n' + 'Google Apps Script, BigQuery, Sheets, Slides');

2. Datentabelle hinzufügen: 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 createSlidePresentation() 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 of // the dimensions of the data range; fails if Sheet empty. var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK); var sheetValues = spreadsheet.getSheets()[0].getRange( START_CELL + ':' + END_CELL).getValues(); var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length); // Populate the table with spreadsheet data. for (var i = 0; i < sheetValues.length; i++) { for (var j = 0; j < sheetValues[0].length; j++) { table.getCell(i, j).getText().setText(String(sheetValues[i][j])); } }

3. Diagramm importieren: Im letzten Schritt in createSlidePresentation() erstellen Sie eine weitere Folie, importieren das Diagramm aus der Tabelle und geben das Objekt Presentation zurück. Fügen Sie dieses letzte 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; }

4. Diagramm zurückgeben: Da die letzte Funktion jetzt vollständig ist, lohnt sich ein genauerer Blick auf ihre Signatur. Die Funktion createSlidePresentation() erfordert sowohl ein Tabellen- als auch ein Diagrammobjekt. Sie haben runQuery() bereits so angepasst, dass das Objekt Spreadsheet zurückgegeben wird. Jetzt nehmen Sie eine ähnliche Änderung in der Funktion createColumnChart() vor, damit das Diagrammobjekt (EmbeddedChart) zurückgegeben wird. Gehen Sie dazu in Ihrer Anwendung zurück und fügen Sie am Ende der Funktion createColumnChart() eine letzte Zeile ein:

// Return chart object for later use return chart; }

5. Funktion createBigQueryPresentation() anpassen: Die Funktion createColumnChart() gibt das Diagramm zurück. Dieses müssen Sie in einer Variable speichern und dann das Tabellenblatt und das Diagramm an die Funktion createSlidePresentation() weitergeben. Da Sie die URL des neu erstellten Tabellenblatts protokollieren, können Sie auch die URL der neuen Folienpräsentation protokollieren. Ersetzen Sie den Codeblock:

/** * Runs a BigQuery query, adds data and a chart in a Sheet. */ function createBigQueryPresentation() { var spreadsheet = runQuery(); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); createColumnChart(spreadsheet); }

durch diesen Codeblock:

/** * Runs a BigQuery query, adds data and a chart in a Sheet, * and adds the data and chart to a new slide presentation. */ function createBigQueryPresentation() { var spreadsheet = runQuery(); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); var chart = createColumnChart(spreadsheet); var deck = createSlidePresentation(spreadsheet, chart); Logger.log('Results slide deck created: %s', deck.getUrl()); }

Nach allen Anpassungen sollte die Datei bq-sheets-slides.gs jetzt so aussehen (mit Ausnahme von PROJECT_ID):

bq-sheets-slides.gs - final version

// Filename for data results var QUERY_NAME = "Most common words in all of Shakespeare's works"; // Replace this value with your Google Cloud API project ID var PROJECT_ID = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup'); /** * Runs a BigQuery query; puts results into Sheet. You must enable * the BigQuery advanced service before you can run this code. * @see http://developers.google.com/apps-script/advanced/bigquery#run_query * @see http://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs * * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results * @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet */ function runQuery() { // Replace sample with your own BigQuery query. var request = { query: 'SELECT ' + 'LOWER(word) AS word, ' + 'SUM(word_count) AS count ' + 'FROM [bigquery-public-data:samples.shakespeare] ' + 'GROUP BY word ' + 'ORDER BY count ' + 'DESC LIMIT 10' }; var queryResults = BigQuery.Jobs.query(request, PROJECT_ID); var jobId = queryResults.jobReference.jobId; // Wait for BQ job completion (with exponential backoff). var sleepTimeMs = 500; while (!queryResults.jobComplete) { Utilities.sleep(sleepTimeMs); sleepTimeMs *= 2; queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId); } // Get all results from BigQuery. var rows = queryResults.rows; while (queryResults.pageToken) { queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, { pageToken: queryResults.pageToken }); rows = rows.concat(queryResults.rows); } // Return null if no data returned. if (!rows) { return Logger.log('No rows returned.'); } // Create the new results spreadsheet. var spreadsheet = SpreadsheetApp.create(QUERY_NAME); var sheet = spreadsheet.getActiveSheet(); // Add headers to Sheet. var headers = queryResults.schema.fields.map(function(field) { return field.name.toUpperCase(); }); sheet.appendRow(headers); // Append the results. var data = new Array(rows.length); for (var i = 0; i < rows.length; i++) { var cols = rows[i].f; data[i] = new Array(cols.length); for (var j = 0; j < cols.length; j++) { data[i][j] = cols[j].v; } } // Start storing data in row 2, col 1 var START_ROW = 2; // skip header row var START_COL = 1; sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data); // Return the spreadsheet object for later use. return spreadsheet; } /** * Uses spreadsheet data to create columnar chart. * @param {Spreadsheet} Spreadsheet containing results data * @returns {EmbeddedChart} visualizing the results * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart */ function createColumnChart(spreadsheet) { // Retrieve the populated (first and only) Sheet. var sheet = spreadsheet.getSheets()[0]; // Data range in Sheet is from cell A2 to B11 var START_CELL = 'A2'; // skip header row var END_CELL = 'B11'; // Place chart on Sheet starting on cell E5. var START_ROW = 5; // row 5 var START_COL = 5; // col E var OFFSET = 0; // Create & place chart on the Sheet using above params. var chart = sheet.newChart() .setChartType(Charts.ChartType.COLUMN) .addRange(sheet.getRange(START_CELL + ':' + END_CELL)) .setPosition(START_ROW, START_COL, OFFSET, OFFSET) .build(); sheet.insertChart(chart); // Return the chart object for later use. return chart; } /** * Create presentation with spreadsheet data & chart * @param {Spreadsheet} Spreadsheet with results data * @param {EmbeddedChart} Sheets chart to embed on slide * @returns {Presentation} Returns a slide deck with results * @see http://developers.google.com/apps-script/reference/slides/presentation */ function createSlidePresentation(spreadsheet, chart) { // Create the new presentation. var deck = SlidesApp.create(QUERY_NAME); // Populate the title slide. var [title, subtitle] = deck.getSlides()[0].getPageElements(); title.asShape().getText().setText(QUERY_NAME); subtitle.asShape().getText().setText('via GCP and G Suite APIs:\n' + 'Google Apps Script, BigQuery, Sheets, Slides'); // Datenbereich to copy is from cell A1 to B11 var START_CELL = 'A1'; // include header row var END_CELL = 'B11'; // Add the table slide and insert an empty table on it of // the dimensions of the data range; fails if Sheet empty. var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK); var sheetValues = spreadsheet.getSheets()[0].getRange( START_CELL + ':' + END_CELL).getValues(); var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length); // Populate the table with spreadsheet data. for (var i = 0; i < sheetValues.length; i++) { for (var j = 0; j < sheetValues[0].length; j++) { table.getCell(i, j).getText().setText(String(sheetValues[i][j])); } } // Add a chart slide and insert the chart on it. var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK); chartSlide.insertSheetsChart(chart); // Return the presentation object for later use. return deck; } /** * Runs a BigQuery query, adds data and a chart in a Sheet, * and adds the data and chart to a new slide presentation. */ function createBigQueryPresentation() { var spreadsheet = runQuery(); Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); var chart = createColumnChart(spreadsheet); var deck = createSlidePresentation(spreadsheet, chart); Logger.log('Results slide deck created: %s', deck.getUrl()); }

6. Speichern Sie die Datei und führen Sie die Funktion createBigQueryPresentation() noch einmal aus. Bevor die Funktion ausgeführt wird, werden Sie im Autorisierungsdialog gefragt, ob die Anwendung Google-Präsentationen aufrufen und verwalten darf.
7. Gehen Sie zum Ordner „Meine Ablage“. 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:

Glückwunsch!

Sie haben eine Anwendung erstellt, die beide Seiten von Google Cloud nutzt: Es wird eine BigQuery-Anfrage ausgeführt, die ein öffentliches BigQuery-Dataset abfragt. Dann wird eine Google-Tabelle erstellt, um die abgefragten Ergebnisse zu speichern, und es wird ein Diagramm basierend auf den abgerufenen Daten hinzugefügt. Schließlich wird eine Google-Präsentation erstellt, die alle Ergebnisse und das Diagramm der Tabelle enthält.

Das ist die technische Beschreibung. Allgemeiner formuliert, haben Sie eine Big-Data-Analyse in eine nachvollziehbare Form gebracht, die Sie anderen an Ihrem Projekt Interessierten vorstellen können, und zwar in Codeform und völlig automatisiert. Sie können dieses Lab für Ihre eigenen Projekte als Vorlage verwenden und entsprechend anpassen.

Weitere Informationen

Der Code aus diesem Lab ist auch in GitHub verfügbar. Wir versuchen, Lab und Repository auf dem gleichen Stand zu halten. Unten finden Sie weitere Ressourcen, die detaillierte Informationen zu den Inhalten in diesem Lab sowie zu Möglichkeiten für einen programmatischen Zugriff auf die Google-Entwicklertools enthalten.

Dokumentation

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

Themenbezogene und allgemeine Videos

• Apps Script-Introvideo zum Thema: Ein weiteres Google (Apps)-Geheimnis
• Video zum Thema: Aus einer Tabelle auf Google Maps zugreifen
• Andere Videos der Videobibliothek für Google Apps Script
• Videoreihe „Launchpad Online“
• Videoreihe The Google Workspace Developer Show

Themenbezogene und allgemeine News und Updates

• BigQuery-Integration in Google Drive (2016: Link 1, Link 2)
• Google Developers-Blog
• Google Cloud-Blog
• Google Cloud-Blog zum Thema Big Data und maschinelles Lernen
• Google Developers auf X (@GoogleDevs)
• Blog zur Entwicklung mit Workspace
• X für Workspace-Entwicklungsteams (@GSuiteDevs)
• Newsletter zu Google Cloud Innovators und Google Workspace
• Google Workspace-Schulungscenter

Anleitung zuletzt am 28. Juli 2025 aktualisiert

Lab zuletzt am 28. Juli 2025 getestet

© 2026 Google LLC. Alle Rechte vorbehalten. Google und das Google-Logo sind Marken von Google LLC. Alle anderen Unternehmens- und Produktnamen können Marken der jeweils mit ihnen verbundenen Unternehmen sein.