Mit Google Tabellen und Google Präsentationen Erkenntnisse aus Ihren Big Data gewinnen

Es gibt viele Tools für Data Scientists, um Big-Data-Analysen durchzuführen. Letztendlich müssen Sie diese Ergebnisse jedoch dem Management rechtfertigen. Viele Zahlen auf Papier oder in einer Datenbank sind für wichtige Interessenvertreterinnen und -vertreter kaum zugänglich. In diesem Zwischen-Codelab mit Google Apps Script können Sie zwei Google-Entwicklerplattformen nutzen, die G Suite und die Google Cloud Platform (GCP).

Mit den Entwicklertools von Google Cloud können Sie die Deeplinks analysieren und die Ergebnisse in eine Tabelle einfügen. So wird eine Präsentation mit diesen Daten generiert, die Sie besser zur Verwaltung bereitstellen können. In diesem Codelab werden die BigQuery API (als Apps Script-erweiterter Dienst) und die integrierten Apps Script-Dienste für Google Tabellen und Google Präsentationen behandelt.

Motivation/frühere Kunst

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

Die Codelab-Beispiel-App für die BigQuery API enthält zwar auch BigQuery und Präsentationen, sie unterscheidet sich jedoch in einigen Punkten von dieser Codelab-App:

  • Node.js-App und unsere Apps Script-App
  • Verwendet REST APIs, während wir Apps Script-Dienste verwenden
  • Verwendet Google Drive, aber nicht Google Tabellen, die App nutzt Google Tabellen, aber nicht Google Drive

Für dieses Codelab wollten wir so viele Technologien in einer einzigen App zusammenführen, während wir gleichzeitig Funktionen und APIs von Google Cloud präsentieren, die einem realen Anwendungsfall ähneln. Ihr Ziel ist, Ihre Fantasie anzuregen und sowohl die GCP als auch G Suite zu verwenden, um anspruchsvolle Probleme für Ihre Organisation oder Ihre Kunden zu lösen.

Lerninhalte

  • Google Apps Script mit mehreren Google-Diensten (GCP und G Suite) verwenden
  • Mit Google BigQuery eine Big-Data-Analyse durchführen
  • Eine Google-Tabelle erstellen und Daten darin einfügen
  • Neues Diagramm in Google Tabellen erstellen
  • Diagramme und Daten aus Google Tabellen in eine Google-Präsentation übertragen

Voraussetzungen

  • Zugriff auf das Internet und einen Webbrowser
  • Ein Google-Konto (G Suite-Konten erfordern möglicherweise die Genehmigung des Administrators)
  • Grundlegende JavaScript-Kenntnisse
  • Kenntnisse der Apps Script-Entwicklung sind hilfreich, aber nicht erforderlich

Wofür möchten Sie dieses Codelab/diese Anleitung nutzen?

Zu Informationszwecken durchlesen und mit Technikkollegen teilen Begib dich so weit wie möglich und probiere so viele der Übungen wie möglich aus Komm zur Hölle oder zu viel Wasser – ich werde das Codelab abschließen

Wie würdest du deine Erfahrung mit den G Suite-Entwicklertools und -APIs bewerten?

Neuling Fortgeschritten Profi

Wie würdest du deine Erfahrung mit Apps Script bewerten?

Neuling Fortgeschritten Profi

Wie würden Sie Ihre Erfahrungen mit den GCP-Entwicklertools und APIs bewerten?

Fortgeschritten Fortgeschritten Profi

Sie wissen jetzt, worum es in diesem Codelab geht. Was genau möchten Sie tun?

  1. Vorhandenes Apps Script-BigQuery-Beispiel verwenden und verwenden
  2. Anhand dieses Beispiels erfahren Sie, wie Sie eine Abfrage an BigQuery senden und die Ergebnisse abrufen.
  3. Google-Tabelle erstellen und die Ergebnisse aus BigQuery einfügen
  4. Durch Änderung des Codes können Sie die zurückgegebenen Daten geringfügig ändern
  5. Mit dem Tabellendienst in Apps Script können Sie ein Diagramm für die Daten aus BigQuery erstellen.
  6. Mit Google Präsentationen eine neue Präsentation erstellen
  7. Fügen Sie der automatisch erstellten Standardfolie für alle neuen Folien einen Titel hinzu
  8. Eine neue Folie mit einer Datentabelle erstellen und die Datenzellen in Google Tabellen importieren
  9. Weitere neue Folie und Tabellediagramm hinzufügen

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

Google Apps Script und BigQuery

Google Apps Script ist eine G Suite-Entwicklungsplattform, die auf höherer Ebene als die Google REST APIs verwendet wird. Hier finden Entwickler mit den unterschiedlichsten Kenntnissen eine serverlose Umgebung für die Entwicklung und das Hosting von Anwendungen. Kodiert wird in JavaScript. Damit lassen sich G Suite-Apps einfach automatisieren, erweitern und in andere Anwendungen einbinden.

Ähnlich wie Node.js ist AppsScript eine serverseitige JavaScript-Plattform. Ihr Schwerpunkt liegt jedoch nicht beim asynchronen, ereignisgetriebenen Hosting von Anwendungen, sondern bei der Einbindung in die G Suite und andere Google-Dienste. Deshalb unterscheidet sich die Entwicklungsumgebung grundlegend von den meisten anderen. mit folgenden Vorteilen:

  • In einem browserbasierten Codeeditor entwickeln, aber lokal entwickeln mit clasp, dem Befehlszeilentool für Apps Script
  • Code in einer speziellen JavaScript-Version, die für den Zugriff auf die G Suite und andere Google- oder externe Dienste (über das Apps Script-Dienst URLfetch oder Jdbc) angepasst ist
  • Es lässt sich vermeiden, Autorisierungscode zu schreiben, da Apps Script diesen Code für Sie verarbeitet.
  • Hosten entfällt – Ihre Anwendung läuft auf den Google-Servern in der Cloud

HINWEIS: In diesem Codelab lernen Sie größtenteils Google Apps Script kennen. Hilfe dazu erhalten Sie in den umfassenden Online-Ressourcen. Die offizielle Dokumentation umfasst auch eine Übersicht mit Kurzanleitungen, Anleitungen und Videos. Und schließlich sollten Sie das Einführungs-Codelab von Apps Script beachten, das vor Abschluss dieses Moduls abgeschlossen werden sollte.

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

  • Integrierter/nativer Dienst
  • Erweiterte Dienste

Ein integrierter Dienst bietet allgemeine Methoden, mit denen Sie auf G Suite- oder Google-Produktdaten oder andere nützliche Methoden zugreifen können. Ein erweiterter Dienst ist lediglich ein schmaler Wrapper um eine G Suite- 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. Erweiterte Dienste müssen auch für ein Skriptprojekt aktiviert werden, bevor sie verwendet werden.

Wenn möglich, sollten Entwickler einen integrierten Dienst nutzen, da sie einfacher zu bedienen und schwerer zu bedienen sind als erweiterte Dienste. Einige Google APIs haben jedoch keine integrierten Dienste, weshalb erweiterte Dienste die einzige Option sein können. Google BigQuery ist ein Beispiel hierfür, kein integrierter Dienst verfügbar, aber erweiterter BigQuery-Dienst istvorhanden. (Und das ist allemal besser als gar kein Dienst.) Wenn Sie BigQuery noch nicht kennen, ist das ein GCP-Dienst, mit dem Sie einfache (oder komplexe) Abfragen für sehr große Datenkörper ausführen können. Sie können beispielsweise mehrere Terabyte angeben und trotzdem in Sekundenschnelle Ergebnisse erzielen.

Aus Apps Script auf Google Tabellen und Präsentationen zugreifen

Im Gegensatz zu BigQuery haben Google Tabellen und Google Präsentationen integrierte Dienste sowie erweiterte Dienste, mit denen Sie nur auf Funktionen zugreifen können, die nur in der API verfügbar sind. Weitere Informationen zu den integrierten Diensten für Google Tabellen und Google Präsentationen finden Sie in der Dokumentation. Und es gibt natürlich auch Dokumente für erweiterte Dienste, wie sie nur für Google Tabellen bzw. Google Präsentationen sind.

Einführung

Mit dieser ersten Aufgabe möchten wir einen großen Teil dieses Codelabs ausüben. Wenn du hier fertig bist, hast du mit dem gesamten Codelab etwa die Hälfte geschafft. Sie sind in mehrere Unterabschnitte unterteilt und tun Folgendes:

  • 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

Einrichtung

  1. a) Erstellen Sie ein neues Apps Script-Projekt. Klicken Sie dazu auf script.google.com. Es gibt verschiedene G Suite-Produktlinien. Die Erstellung neuer Projekte hängt davon ab, welche Version Sie verwenden. Wenn du gerade dein Gmail-Konto verwendest und noch nicht mit dem Erstellen von Projekten vertraut bist, wird ein leerer Bildschirm mit einer Schaltfläche angezeigt, über die du dein erstes Projekt erstellen kannst:


b) Andernfalls werden oben links alle Projekte und die große Schaltfläche +Neu angezeigt. Klicken Sie also darauf.



c) Wenn keiner der oben genannten Fälle angezeigt wird, sieht der Bildschirm unten aus. Wenn ja, klicken Sie links oben auf das Hamburger-Menüsymbol und wählen Sie +Neues Skript aus.



d) Alle Nutzer, die die Befehlszeile bevorzugen, werden benachrichtigt. Das Tool lautet clasp, insbesondere werden Sie den Befehl clasp create ausführen.

e) Zum Erstellen eines neuen Skriptprojekts klicken Sie einfach auf diesen Link: https://script.google.com/create.

  1. Unabhängig davon, mit welcher Technik Sie ein neues Projekt gestartet haben, sollten Sie in den Apps Script-Code-Editor verschoben werden. Der Bildschirm sieht so aus:


  2. Klicken Sie auf Speichern &speichern und geben Sie dem Projekt einen Namen.
  3. Als Nächstes müssen Sie ein Google Cloud Console-Projekt erstellen, um BigQuery-Abfragen auszuführen.
  1. Erstellen Sie ein neues Projekt, geben Sie ihm einen Namen, wählen Sie ein Rechnungskonto aus und klicken Sie auf ERSTELLEN.
  1. Wenn der Vorgang abgeschlossen ist, wird rechts oben auf der Seite eine Benachrichtigung angezeigt. Klicken Sie auf den Eintrag Projekt erstellen: <Projektname>, um das Projekt zu öffnen.
  2. Klicken Sie links oben auf das Dreistrich-Menü und gehen Sie zu APIs & Services > Anmeldedaten. Klicken Sie auf den Tab OAuth-Zustimmungsbildschirm (direkter Link).
  3. Geben Sie im Feld Anwendungsname "Big Data Codelab" ein und klicken Sie unten auf Speichern.
  4. Klicken Sie oben rechts auf das Dreipunkt-Menü, um das Menü zu maximieren, und wählen Sie Projekteinstellungen (direkter Link) aus.
  5. Kopieren Sie den Wert unter Projektnummer. (Es gibt ein separates Feld Product ID, das wir später im Codelab verwenden.)
  6. Klicken Sie im App Script-Editor auf Ressourcen &Cloud Platform-Projekt.
  7. Geben Sie die Projektnummer in das Textfeld ein und klicken Sie auf Projekt festlegen. Wenn Sie dazu aufgefordert werden, klicken Sie auf Bestätigen.
  8. Wenn Sie fertig sind, klicken Sie auf Schließen, um das Dialogfeld zu schließen.
  9. Da Sie ein neues Projekt eingerichtet haben, müssen Sie den erweiterten BigQuery-Dienst aktivieren. Rufen Sie Ressourcen -> Erweiterte Google-Dienste auf und drehen Sie die BigQuery API um.


  10. Hinweis unten: „Diese Dienste müssen auch im &Google Cloud Platform API-Dashboard aktiviert sein“. Klicken Sie daher auf diesen Link, um einen weiteren Browsertab zur Entwicklerkonsole oder kurz „devconsole“ zu öffnen.
  11. Klicken Sie oben in der Entwicklertools-Konsole auf die Schaltfläche + 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. Lassen Sie diesen Browsertab geöffnet.

    HINWEIS: Nach dem Aktivieren der API wird auf dieser Seite möglicherweise ein Hinweis wie „Diese API verwenden“ angezeigt, für die Sie Anmeldedaten erstellen müssen. Mach dir aber erst mal keine Gedanken darüber. Apps Script kümmert sich um diesen Schritt.


  12. Im Browser-Tab des Code-Editors befinden Sie sich noch im Menü „Erweiterte Google-Dienste“. Klicken Sie auf OK, um das Dialogfeld zu schließen. Klicken Sie oben auf den Namen des Projekts und nennen Sie es einen Namen, z. B. „BigQuery-Demo“ oder Ähnliches – wir haben unsere Projekt heißt.

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

  1. Kopieren Sie den Code im Feld unten und fügen Sie alles in den Code-Editor ein:
// 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 = '';
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/googleworkspace/apps-script-samples/blob/main/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());
}


Speichern Sie die soeben erstellte Datei und benennen Sie sie von Code.gs in bq-sheets-slides.js um. Welche Funktionen stecken nun in dem Code? Wir haben Ihnen bereits mitgeteilt, dass die Daten in BigQuery abgefragt und in einer neuen Google-Tabelle gespeichert werden. Aber was ist diese Abfrage? Sie sehen ihn oben in den 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 Datensatzs von BigQuery sind. Dabei werden die 10 häufigsten Wörter in allen seinen Werken in absteigender Reihenfolge nach ihrer Beliebtheit sortiert. Stellen Sie sich vor, Sie müssten diese Aufgabe manuell erledigen! Das gibt Ihnen eine Vorstellung davon, wie nützlich BigQuery ist.

  1. Wir sind dabei aber noch nicht ganz fertig. Wie oben in diesem Code-Snippet sehen, ist eine gültige Projekt-ID erforderlich. Wir müssen die ID des Anwendungscodes hinzufügen. Rufen Sie dazu das Browserfenster oder den Tab mit der Entwicklerkonsole auf. Wie gesagt, solltest du sie offen lassen.
  2. Oben links neben dem Avatar Ihres Google-Kontos befindet sich die Drop-down-Menüauswahl (). Klicken Sie darauf und wählen Sie Projekteinstellungen aus. Sie sehen den Namen, die ID und die Nummer des Projekts. Kopieren Sie die Projekt-ID und setzen Sie die Variable PROJECT_ID oben in bq-sheets-slides.js auf den Wert, den Sie von der Entwicklerkonsole erhalten haben. HINWEIS: Wenn die Menüauswahl fixiert und nicht funktionsfähig ist, aktualisieren Sie die Seite.
  3. Die if-Anweisung soll verhindern, dass die Anwendung ohne eine Projekt-ID weiter ausgeführt wird. Nachdem Sie Ihre eigene Datei hinzugefügt, die Datei gespeichert und den Code ausgeführt haben, klicken Sie in der Menüleiste auf Run > Run feature > runQuery. Klicken Sie anschließend auf Berechtigungen überprüfen. Die App ist ungeprüft. Unten sehen Sie ein animiertes GIF für eine andere App.
  4. Nachdem Sie die Berechtigungen angefordert haben, wird ein neues Dialogfeld angezeigt (siehe oben). Wählen Sie das richtige Google-Konto aus, in dem das Skript ausgeführt werden soll. Klicken Sie dann auf Erweitert, scrollen Sie nach unten und klicken Sie auf „[PROJEKTNAME EINFÜGEN“), um den OAuth-Bildschirm für die Autorisierung von Anwendungen aufzurufen. Weitere Informationen zum Überprüfungsprozess, warum sich dieser Bildschirm zwischen Ihnen und dem OAuth2-Autorisierungsdialog befindet.


    HINWEIS:Sobald Sie die App autorisiert haben, müssen Sie diesen Vorgang nicht bei jeder Ausführung wiederholen. Es wird erst wieder angezeigt, wenn Sie wieder zur Aufgabe 3 wechseln und den Dialogbildschirm mit der Berechtigung zum Erstellen und Verwalten von Google-Präsentationen aufrufen.
  5. Wenn Sie im OAuth2-Dialogfeld auf Zulassen klicken, wird das Skript ausgeführt... Sie sehen oben ein pastellgelbes Dialogfeld. Die Ausführung erfolgt relativ schnell, sodass Sie möglicherweise nicht bemerken, dass sie ausgeführt wird oder dass die Ausführung abgeschlossen wurde.

  6. Dieses Dialogfeld verschwindet, sobald es fertig ist. Wenn Sie es nicht sehen, können Sie es in Google Drive (drive.google.com) nach einer neuen Google-Tabelle mit dem Namen &häufig verwendete Wörter suchen.


  7. Wenn Sie die Tabelle öffnen, sollten Sie zehn Wortzeilen und die Gesamtanzahl der Einträge in absteigender Reihenfolge sortieren:

Zusammenfassung für Aufgabe 1

Erinnern Sie sich daran, was gerade passiert ist – Sie haben Code ausgeführt, der alle Shakespeare-Werke abgefragt hat, aber keine riesige Menge an Daten, aber sicherlich mehr Text, als Sie alleine nach jedem Wort bei jedem Spiel scannen können, eine Anzahl dieser Wörter verwalten und sie dann in absteigender Reihenfolge sortieren. Sie haben BigQuery nicht nur darum gebeten, dies für Sie zu erledigen, sondern auch den integrierten Dienst in Apps Script für Google Tabellen verwenden, mit dem sich diese Daten ganz einfach dort speichern lassen.

Den Code für bq-sheets-slides.js (den ausgewählten Dateinamen), den Sie oben eingefügt haben (mit Ausnahme von PROJECT_ID), der eine echte Projekt-ID haben sollte, finden Sie auch im Ordner step1 in diesem Codelab und im GitHub-Repository unter github.com/googlecodelabs/bigquery-sheets-slides. Der Code war inspiriert mit dem ursprünglichen Beispiel auf der Seite für die erweiterten BigQuery-Dienste, bei dem eine etwas andere Abfrage ausgeführt wurde. Welche Wörter wurden am häufigsten von Shakespeare mit mindestens zehn Zeichen verwendet? Sie können dieses Beispiel auch im GitHub-Repository sehen.

Wenn du an anderen Suchanfragen interessiert bist, die du für Shakespeare-Werke oder andere öffentliche Datentabellen ausprobieren kannst, schau dir diese Webseite und diese Seite an. Unabhängig davon, welche Abfrage Sie verwenden, können Sie die Abfrage immer einfach in der BigQuery-Konsole testen, bevor Sie sie in Apps Script ausführen. Die BigQuery-Benutzeroberfläche steht Entwicklern unter bigquery.cloud.google.com zur Verfügung. Zum Beispiel so sieht unsere Abfrage mit der BigQuery-UI aus:

Während der Anleitung oben wurde der Code-Editor von Apps Script verwendet, kann die Entwicklung lokal über die Befehlszeile erfolgen. Sie können auch ein Skript namens bq-sheets-slides.js erstellen, den obigen Code einfügen und mit dem Befehl clasp push auf Google hochladen. Falls Sie sie verpasst haben, sehen Sie sich hier den Link zu clasp und seine Verwendung an.

Der Zweck von runQuery() ist es, mit BigQuery zu sprechen und die Ergebnisse in eine Google-Tabelle zu senden. Jetzt müssen wir ein Diagramm mit den Daten erstellen. Dazu erstellen wir eine neue Funktion mit dem Namen createColumnChart(), die die newChart()-Methode für Google Tabellen aufruft.

  1. Diagramm erstellen: Der Text von createColumnChart() unten wird bq-sheets-slides.js direkt nach runQuery() hinzugefügt. Er ruft das Tabellenblatt mit den Daten ab und fordert ein Säulendiagramm mit allen Daten an. Der Datenbereich beginnt in Zelle A2, weil die erste Zeile die Spaltenüberschriften enthält, keine Daten.
/**
 * 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);
}
  1. Rückgabetabelle. Oben benötigt createColumnChart() das Tabellenobjekt. Wir müssen also runQuery() aktualisieren, um das spreadsheet-Objekt zurückzugeben, damit es an createColumnChart() weitergegeben werden kann. Nach der erfolgreichen Erstellung der Google-Tabelle geben Sie das spreadsheet-Objekt am Ende von runQuery() direkt nach der Protokollzeile zurück:
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // NEW: Return the spreadsheet object for later use.
  return spreadsheet;
}
  1. Funktion createBigQueryPresentation()mit dem Auto. Eine logische Trennung der BigQuery- und Diagrammerstellungsfunktion ist eine gute Idee. Erstellen Sie nun eine createBigQueryPresentation()-Funktion zum Ausführen der App. Dabei werden runQuery() und createColumnChart() aufgerufen. Der hinzugefügte Code sollte in etwa so aussehen:
/**
 * Runs a BigQuery query, adds data and a chart in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  createColumnChart(spreadsheet);
}
  1. Code wiederverwendbarer machen. Sie haben oben zwei wichtige Schritte ausgeführt: das Tabellenobjekt zurückzugeben und eine Fahrfunktion zu erstellen. Was passiert, wenn ein Kollege runQuery() wiederverwenden möchte und nicht möchte, dass die URL protokolliert wird? Damit runQuery() besser für die allgemeine Nutzung nutzbar ist, müssen wir die Logzeile verschieben. Wohin am besten? Wenn Sie createBigQueryPresentation() erraten haben, sind Sie richtig. Nach dem Verschieben der Logzeile sollte sie 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()); // MOVED HERE
  createColumnChart(spreadsheet);
}

Mit diesen Änderungen oben (abgesehen von PROJECT_ID) sollte bq-sheets-slides.js jetzt so aussehen (und im Ordner step2 des GitHub-Repositorys zu sehen sein):

// 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 = '';
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/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Sheet} Returns a sheet with results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/sheet
 */
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);
}

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

Speichern Sie die Datei, gehen Sie dann oben im Code-Editor nach oben und wechseln Sie zu createBigQueryPresentation() statt zu runQuery(). Nach dem Ausführen erhalten Sie eine weitere Google-Tabelle, aber diesmal wird in der Tabelle ein Diagramm neben den Daten angezeigt:

Im letzten Teil des Codelabs erstellen Sie eine neue Google-Präsentation, füllen den Titel und den Untertitel auf der Titelfolie aus und fügen dann zwei neue Folien hinzu – eine für jede Datenzelle und eine für das Diagramm.

  1. Erstellen Sie eine Präsentation. Die gesamte Arbeit an der Präsentation wird in createSlidePresentation() erledigt, was unmittelbar nach dem createColumnChart() in bq-sheets-slides.js aufgenommen wird. Beginnen wir mit der Erstellung einer neuen Präsentation und fügen Sie dann der Standardfolie für den Titel alle neuen Präsentationen hinzu.
/**
 * 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');
  1. Datentabelle hinzufügen Als Nächstes importieren Sie in createSlidePresentation() die Zellendaten aus der Google-Tabelle in die neue Präsentation. 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 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]));
    }
  }
  1. Diagramm importieren: Im letzten Schritt in createSlidePresentation() erstellen Sie eine weitere Folie, importieren das Diagramm aus unserer Tabelle und geben das Presentation-Objekt zurück. Fügen Sie der Funktion das folgende 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;
}
  1. Rückgabediagramm: Sehen Sie sich nun die Signatur der letzten Funktion an. Ja. Für createSlidePresentation() werden sowohl eine Tabelle als auch ein Diagrammobjekt benötigt. Wir haben runQuery() bereits angepasst, um das Objekt Spreadsheet zurückzugeben, aber jetzt müssen wir eine ähnliche Änderung an createColumnChart() vornehmen, um das Diagrammobjekt (EmbeddedChart) zurückzugeben. Geh in deine Anwendung zurück, um Code hinzuzufügen und am Ende von createColumnChart() eine letzte Zeile hinzuzufügen:
  // NEW: Return chart object for later use
  return chart;
}
  1. Aktualisieren Sie createBigQueryPresentation(). Da das Diagramm von createColumnChart() zurückgegeben wird, muss es in einer Variablen gespeichert und sowohl die Tabelle als auch das Diagramm an createSlidePresentation() übergeben werden. Da wir die URL der neu erstellten Tabelle protokollieren, wird auch die URL der neuen Folien aufgezeichnet. Aktualisieren Sie Ihre createBigQueryPresentation() so:
/**
 * 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); // UPDATED
  var deck = createSlidePresentation(spreadsheet, chart); // NEW
  Logger.log('Results slide deck created: %s', deck.getUrl()); // NEW
}
  1. Speichern und wiederholen Sie createBigQueryPresentation(). Bevor die App ausgeführt wird, benötigt sie jedoch noch eine weitere Reihe von Berechtigungen, damit Nutzer Google-Präsentationen ansehen und verwalten können. Sobald du diese Berechtigung erteilst, wird sie wie gewohnt ausgeführt.
  2. Zusätzlich zur Tabelle, die Sie erstellt haben, sollten Sie auch eine neue Präsentation mit drei Folien (Titel, Datentabelle, Datendiagramm) sehen:

Glückwunsch! Sie haben jetzt eine Anwendung erstellt, die beide Seiten von Google Cloud nutzt. Dazu wird eine Google BigQuery-Anfrage ausgeführt, die einen der öffentlichen Datensätze abfragt. Dann wird eine neue Google-Tabelle erstellt, in der die Ergebnisse gespeichert werden, ein Diagramm auf Grundlage der soeben abgerufenen Daten erstellt und schließlich eine Google-Präsentation mit den Ergebnissen sowie einem Diagramm in der Tabelle erstellt.

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. Wir hoffen, dass Sie mit diesem Beispiel davon profitieren, es für Ihre eigenen Projekte anzupassen. Am Ende dieses Codelabs erhältst du von uns einige Vorschläge dazu, wie du diese Beispiel-App weiter optimieren kannst.

Wenn Sie (abgesehen von PROJECT_ID) nach der letzten Aufgabe wieder Änderungen vornehmen möchten, sollte bq-sheets-slides.js jetzt so aussehen (auch im Ordner final im GitHub-Repository):

bq-sheets-slides.js

/**
 * 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 = '';
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/googleworkspace/apps-script-samples/blob/main/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');

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

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

In diesem Codelab spielt keine Rolle eine von der zweiten Datei in diesem Codelab aus, in diesem Fall die Apps Script-Manifestdatei appsscript.json. Sie können darauf zugreifen, indem Sie den Code-Editor-Tab und die Option View &Manifestdatei anzeigen aus dem Menü oben auswählen. Der Inhalt sollte in etwa so aussehen:

appsscript.json

{
  "timeZone": "America/Los_Angeles",
  "dependencies": {
    "enabledAdvancedServices": [{
      "userSymbol": "BigQuery",
      "serviceId": "bigquery",
      "version": "v2"
    }]
  },
  "exceptionLogging": "STACKDRIVER"
}

Die Manifestdatei ist eine Konfigurationsdatei auf Systemebene, mit der Apps Script weiß, welche Ausführungsumgebung für Ihre Anwendung verfügbar ist. In diesem Codelab wird der Inhalt einer Manifestdatei nicht behandelt. Du bekommst aber einen Eindruck davon, was passiert.

Im Folgenden erhalten Sie zusätzliche Ressourcen, mit denen Sie tiefer in das Thema dieses Codelabs eintauchen und andere Möglichkeiten des programmatischen Zugriffs auf die Google-Entwicklertools entdecken können. Wir versuchen, dieses Codelab mit dem Repository zu synchronisieren.

Ressourcen für diese Anwendung

Dokumentation

Andere Codelabs

Einführung

Fortgeschritten

Referenz-Apps

Im Folgenden stellen wir verschiedene Code-Challenges für die in diesem Codelab erstellten Beispiele vor. Diese Liste ist natürlich nicht vollständig, sollte Ihnen aber Anregungen für den nächsten Schritt geben.

  • Anwendung: Möchten Sie nicht durch den Einsatz von JavaScript oder Einschränkungen durch Apps Script eingeschränkt werden? Portieren Sie diese App in Ihre bevorzugte Programmiersprache, bei der die REST APIs für Google BigQuery, Google Tabellen und Google Präsentationen verwendet werden.
  • BigQuery. Experimentieren Sie mit einer anderen Abfrage für den Shakespeare-Datensatz. Vielleicht finden Sie eine Abfrage, die Sie interessiert. Eine andere Beispielabfrage finden Sie in der ursprünglichen Apps Script BigQuery-Beispielanwendung.
  • BigQuery. Experimentieren Sie mit einigen anderen öffentlichen Datensätzen..., die für Sie aussagekräftiger sind.
  • BigQuery. Wir haben bereits andere Suchanfragen erwähnt, die ihr für Shakespeare-Werke oder andere öffentliche Datentabellen ausprobieren könnt. Deshalb wollten wir diese Webseite und diese Seite noch einmal teilen.
  • Tabellen Experimentieren Sie mit anderen Diagrammtypen.
  • Google Tabellen &BigQuery Drehen Sie die Tabellen um... vielleicht haben Sie in einer Tabelle einen großen Datensatz. 2016 hat das BigQuery-Team die Funktion eingeführt, damit Entwickler eine Tabelle als Datenquelle verwenden können. Weitere Informationen finden Sie in diesem Blogpost und in diesem Blogpost.
  • Präsentationen: Fügen Sie der erstellten Präsentation andere Folien hinzu, z. B. Bilder oder andere Assets, die mit Ihrer Big-Data-Analyse verknüpft sind. Weitere Informationen zum integrierten Dienst in Google Präsentationen
  • G Suite Sie können die Nutzung anderer G Suite- oder Google-Dienste mit Apps Script wie Gmail, Google Drive, Google Kalender, Google Docs, Google Maps, Google Analytics, YouTube usw. sowie andere erweiterte Dienste. Weitere Informationen zu integrierten und erweiterten Diensten finden Sie in der Referenzübersicht.