Verbundene Tabellenblätter

Mit verbundenen Tabellenblättern können Sie Petabyte an Daten direkt in Google Sheets analysieren. Sie können Ihre Tabellen mit einem BigQuery Data Warehouse oder Looker verbinden und die Analyse mit vertrauten Sheets-Tools wie Pivot-Tabellen, Diagrammen und Formeln durchführen.

BigQuery-Datenquelle verwalten

In diesem Abschnitt wird anhand des öffentlichen BigQuery Shakespeare Datasets gezeigt, wie Sie verbundene Tabellenblätter verwenden. Das Dataset enthält die folgenden Informationen:

Feld Typ Beschreibung
Wort STRING Ein einzelnes eindeutiges Wort (Leerzeichen als Trennzeichen), das aus einem Korpus extrahiert wurde.
word_count INTEGER Die Anzahl der Vorkommen dieses Wortes in diesem Korpus.
Korpus STRING Das Werk, aus dem dieses Wort extrahiert wurde.
corpus_date INTEGER Das Jahr, in dem dieser Korpus veröffentlicht wurde.

Wenn Ihre Anwendung Daten aus verbundenen Tabellenblättern in BigQuery anfordert, muss sie zusätzlich zu den anderen Bereichen, die für eine reguläre Google Sheets API-Anfrage erforderlich sind, ein OAuth 2.0-Token mit dem Bereich bigquery.readonly bereitstellen. Weitere Informationen finden Sie unter Google Sheets API Bereiche auswählen.

Eine Datenquelle gibt einen externen Speicherort an, an dem Daten gefunden werden. Die Datenquelle wird dann mit der Tabelle verbunden.

BigQuery-Datenquelle hinzufügen

Um eine Datenquelle hinzuzufügen, stellen Sie mit der spreadsheets.batchUpdate Methode eine AddDataSourceRequest bereit. Im Anfragetext muss ein dataSource-Feld vom Typ DataSource Objekt angegeben sein.

"addDataSource":{
   "dataSource":{
      "spec":{
         "bigQuery":{
            "projectId":"PROJECT_ID",
            "tableSpec":{
               "tableProjectId":"bigquery-public-data",
               "datasetId":"samples",
               "tableId":"shakespeare"
            }
         }
      }
   }
}

Ersetzen Sie PROJECT_ID durch eine gültige Google Cloud-Projekt-ID.

Nachdem eine Datenquelle erstellt wurde, wird ein zugehöriges DATA_SOURCE Tabellenblatt erstellt, um eine Vorschau von bis zu 500 Zeilen zu liefern. Die Vorschau ist nicht sofort verfügbar. Die Ausführung wird asynchron ausgelöst, um die BigQuery-Daten zu importieren.

Die AddDataSourceResponse enthält die folgenden Felder:

  • dataSource: Das erstellte DataSource-Objekt. Die dataSourceId ist eine eindeutige ID im Tabellenbereich. Sie wird ausgefüllt und referenziert, um jedes DataSource-Objekt aus der Datenquelle zu erstellen.

  • dataExecutionStatus: Der Status einer Ausführung, bei der BigQuery-Daten in das Vorschautabellenblatt importiert werden. Weitere Informationen finden Sie im Abschnitt Status der Datenausführung.

BigQuery-Datenquelle aktualisieren oder löschen

Verwenden Sie die spreadsheets.batchUpdate Methode und stellen Sie entsprechend eine UpdateDataSourceRequest oder DeleteDataSourceRequest bereit.

BigQuery-Datenquellenobjekte verwalten

Nachdem der Tabelle eine Datenquelle hinzugefügt wurde, kann daraus ein Datenquellenobjekt erstellt werden. Ein Datenquellenobjekt ist ein reguläres Sheets-Tool wie Pivot-Tabellen, Diagramme und Formeln, das in verbundene Tabellenblätter integriert ist, um Ihre Datenanalyse zu unterstützen.

Es gibt vier Arten von Objekten:

  • DataSource-Tabelle
  • DataSource pivotTable
  • DataSource-Diagramm
  • DataSource-Formel

BigQuery-Datenquellentabelle hinzufügen

Das Tabellenobjekt, das im Sheets-Editor als „Extrahieren“ bezeichnet wird, importiert einen statischen Dump von Daten aus der Datenquelle in Sheets. Ähnlich wie bei einer Pivot-Tabelle wird die Tabelle angegeben und an der oberen linken Zelle verankert.

Das folgende Codebeispiel zeigt, wie Sie mit der spreadsheets.batchUpdate Methode und einer UpdateCellsRequest eine Datenquellentabelle mit bis zu 1.000 Zeilen und zwei Spalten (word und word_count) erstellen.

"updateCells":{
   "rows":{
      "values":[
         {
            "dataSourceTable":{
               "dataSourceId":"DATA_SOURCE_ID",
               "columns":[
                  {
                     "name":"word"
                  },
                  {
                     "name":"word_count"
                  }
               ],
               "rowLimit":{
                  "value":1000
               },
               "columnSelectionType":"SELECTED"
            }
         }
      ]
   },
   "fields":"dataSourceTable"
}

Ersetzen Sie DATA_SOURCE_ID durch eine eindeutige ID im Tabellenbereich, die die Datenquelle identifiziert.

Nachdem eine Datenquellentabelle erstellt wurde, sind die Daten nicht sofort verfügbar. Im Sheets-Editor wird sie als Vorschau angezeigt. Sie müssen die Datenquellentabelle aktualisieren, um die BigQuery-Daten abzurufen. Sie können innerhalb derselben batchUpdate eine RefreshDataSourceRequest angeben. Alle Datenquellenobjekte funktionieren ähnlich. Weitere Informationen finden Sie unter Datenquellenobjekt aktualisieren.

Nachdem die Aktualisierung abgeschlossen und die BigQuery-Daten abgerufen wurden, wird die Datenquellentabelle wie folgt ausgefüllt:

Tabelle mit Daten aus dem öffentlichen Shakespeare-Dataset.

BigQuery-Datenquellen-Pivot-Tabelle hinzufügen

Im Gegensatz zu einer herkömmlichen Pivot-Tabelle basiert eine Datenquellen-Pivot-Tabelle auf einer Datenquelle und verweist anhand des Spaltennamens auf die Daten. Das folgende Codebeispiel zeigt, wie Sie mit der Methode spreadsheets.batchUpdate und einer UpdateCellsRequest eine Pivot-Tabelle erstellen, in der die Gesamtzahl der Wörter nach Korpus angezeigt wird.

"updateCells":{
   "rows":{
      "values":[
         {
            "pivotTable":{
               "dataSourceId":"DATA_SOURCE_ID",
               "rows":{
                  "dataSourceColumnReference":{
                     "name":"corpus"
                  },
                  "sortOrder":"ASCENDING"
               },
               "values":{
                  "summarizeFunction":"SUM",
                  "dataSourceColumnReference":{
                     "name":"word_count"
                  }
               }
            }
         }
      ]
   },
   "fields":"pivotTable"
    }

Ersetzen Sie DATA_SOURCE_ID durch eine eindeutige ID im Tabellenbereich, die die Datenquelle identifiziert.

Nachdem die BigQuery-Daten abgerufen wurden, wird die Datenquellen-Pivot-Tabelle wie folgt ausgefüllt:

Pivot-Tabelle für die Datenquelle mit Daten aus dem öffentlichen Shakespeare-Dataset.

BigQuery-Datenquellendiagramm hinzufügen

Das folgende Codebeispiel zeigt, wie Sie mit der spreadsheets.batchUpdate Methode und einer AddChartRequest ein Datenquellendiagramm mit dem chartType COLUMN erstellen, in dem die Gesamtzahl der Wörter nach Korpus angezeigt wird.

"addChart":{
   "chart":{
      "spec":{
         "title":"Corpus by word count",
         "basicChart":{
            "chartType":"COLUMN",
            "domains":[
               {
                  "domain":{
                     "columnReference":{
                        "name":"corpus"
                     }
                  }
               }
            ],
            "series":[
               {
                  "series":{
                     "columnReference":{
                        "name":"word_count"
                     },
                     "aggregateType":"SUM"
                  }
               }
            ]
         }
      },
      "dataSourceChartProperties":{
         "dataSourceId":"DATA_SOURCE_ID"
      }
   }
}

Ersetzen Sie DATA_SOURCE_ID durch eine eindeutige ID im Tabellenbereich, die die Datenquelle identifiziert.

Nachdem die BigQuery-Daten abgerufen wurden, wird das Datenquellendiagramm wie folgt gerendert:

Diagramm für die Datenquelle mit Daten aus dem öffentlichen Shakespeare-Dataset.

BigQuery-Datenquellenformel hinzufügen

Das folgende Codebeispiel zeigt, wie Sie mit der Methode spreadsheets.batchUpdate und einer UpdateCellsRequest eine Datenquellenformel erstellen, um die durchschnittliche Anzahl der Wörter zu berechnen.

"updateCells":{
   "rows":[
      {
         "values":[
            {
               "userEnteredValue":{
                  "formulaValue":"=AVERAGE(shakespeare!word_count)"
               }
            }
         ]
      }
   ],
   "fields":"userEnteredValue"
}

Nachdem die BigQuery-Daten abgerufen wurden, wird die Datenquellenformel wie folgt ausgefüllt:

Formel für die Datenquelle mit Daten aus dem öffentlichen Shakespeare-Dataset.

BigQuery-Datenquellenobjekt aktualisieren

Sie können ein Datenquellenobjekt aktualisieren, um die neuesten Daten aus BigQuery abzurufen. Dabei werden die aktuellen Datenquellenspezifikationen und Objektkonfigurationen verwendet. Mit der spreadsheets.batchUpdate Methode können Sie RefreshDataSourceRequestaufrufen. Geben Sie dann mit dem DataSourceObjectReferences Objekt eine oder mehrere Objektverweise an, die aktualisiert werden sollen.

Sie können Datenquellenobjekte sowohl erstellen als auch aktualisieren, und zwar mit einer einzigen batchUpdate-Anfrage.

Looker-Datenquelle verwalten

In dieser Anleitung wird gezeigt, wie Sie eine Looker-Datenquelle hinzufügen, aktualisieren oder löschen, eine Pivot-Tabelle darauf erstellen und sie aktualisieren.

Ihre Anwendung, die Daten aus verbundenen Tabellenblättern in Looker anfordert, verwendet Ihre vorhandene Google-Kontoverknüpfung mit Looker.

Looker-Datenquelle hinzufügen

Um eine Datenquelle hinzuzufügen, stellen Sie mit der spreadsheets.batchUpdate Methode eine AddDataSourceRequest bereit. Im Anfragetext muss ein dataSource-Feld vom Typ DataSource Objekt angegeben sein.

"addDataSource":{
   "dataSource":{
      "spec":{
         "looker":{
            "instance_uri":"INSTANCE_URI",
            "model":"MODEL",
            "explore":"EXPLORE"
         }
      }
   }
}

Ersetzen Sie INSTANCE_URI, MODEL und EXPLORE durch eine gültige Looker-Instanz-URI, einen gültigen Modellnamen und einen gültigen Namen für die explorative Datenanalyse.

Nachdem eine Datenquelle erstellt wurde, wird ein zugehöriges DATA_SOURCE Tabellenblatt erstellt, um eine Vorschau der Struktur der ausgewählten explorativen Datenanalyse zu liefern, einschließlich Ansichten, Dimensionen, Messwerten und Feld-Beschreibungen.

Die AddDataSourceResponse enthält die folgenden Felder:

  • dataSource: Das erstellte DataSource-Objekt. Die dataSourceId ist eine eindeutige ID im Tabellenbereich. Sie wird ausgefüllt und referenziert, um jedes DataSource-Objekt aus der Datenquelle zu erstellen.

  • dataExecutionStatus: Der Status einer Ausführung, bei der BigQuery-Daten in das Vorschautabellenblatt importiert werden. Weitere Informationen finden Sie im Abschnitt Status der Datenausführung.

Looker-Datenquelle aktualisieren oder löschen

Verwenden Sie die spreadsheets.batchUpdate Methode und stellen Sie entsprechend eine UpdateDataSourceRequest oder DeleteDataSourceRequest bereit.

Looker-Datenquellenobjekte verwalten

Nachdem der Tabelle eine Datenquelle hinzugefügt wurde, kann daraus ein Datenquellenobjekt erstellt werden. Für Looker-Datenquellen können Sie nur ein DataSource-Pivot-Tabellenobjekt erstellen.

Es ist nicht möglich, DataSource-Formeln, -Extrakte und -Diagramme aus Looker-Datenquellen zu erstellen.

Looker-Datenquellenobjekt aktualisieren

Sie können ein Datenquellenobjekt aktualisieren, um die neuesten Daten aus Looker abzurufen. Dabei werden die aktuellen Datenquellenspezifikationen und Objektkonfigurationen verwendet. Mit der spreadsheets.batchUpdate Methode können Sie RefreshDataSourceRequestaufrufen. Geben Sie dann mit dem DataSourceObjectReferences Objekt eine oder mehrere Objektverweise an, die aktualisiert werden sollen.

Sie können Datenquellenobjekte sowohl erstellen als auch aktualisieren, und zwar mit einer einzigen batchUpdate-Anfrage.

Status der Datenausführung

Wenn Sie Datenquellen erstellen oder Datenquellenobjekte aktualisieren, wird eine Hintergrund ausführung erstellt, um die Daten aus BigQuery oder Looker abzurufen und eine Antwort mit dem DataExecutionStatus zurückzugeben. Wenn die Ausführung erfolgreich gestartet wird, befindet sich der DataExecutionState normalerweise im Status RUNNING.

Da der Vorgang asynchron ist, sollte Ihre Anwendung ein Polling-Modell implementieren, um den Status für die Datenquellenobjekte regelmäßig abzurufen. Verwenden Sie die spreadsheets.get Methode, bis der Status entweder den Status SUCCEEDED oder FAILED zurückgibt. Die Ausführung ist in den meisten Fällen schnell abgeschlossen, hängt aber von der Komplexität Ihrer Datenquelle ab. In der Regel dauert die Ausführung nicht länger als 10 Minuten.