Verbundene Tabellenblätter verwenden

Verbundene Tabellenblätter ist eine Google Sheets-Funktion, mit der Sie BigQuery- und Looker-Daten direkt in Google Sheets analysieren können. Mit dem Tabellendienst programmatisch auf Verbundene Tabellenblätter zugreifen

Häufig verwendete Aktionen in „Verbundene Tabellenblätter“

Verwenden Sie die DataSource-Klassen und -Objekte, um eine Verbindung zu BigQuery oder Looker herzustellen und Daten zu analysieren. In der folgenden Tabelle sind die häufigsten DataSource-Aktionen und die Vorgehensweise zum Erstellen dieser Aktionen in Google Apps Script aufgeführt:

Aktion	Apps Script-Klasse	Verfahren
Tabelle mit einer unterstützten Datenquelle verbinden	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
Datenquelle auswählen	`DataSource`	`Spreadsheet.insertDataSourceSheet().getDataSource()`
Datenquellenblatt hinzufügen	`DataSourceSheet`	`Spreadsheet.insertDataSourceSheet()`
Pivot-Tabelle hinzufügen	`DataSourcePivotTable`	`Range.insertDataSourcePivotTable()`
Daten in einem Auszug abrufen	`DataSourceTable`	`Range.insertDataSourceTable()`
Formel verwenden	`DataSourceFormula`	`Range.setFormula()`
Fügen Sie ein Diagramm hinzu	`DataSourceChart`	`Sheet.insertDataSourceChart()`

Erforderliche Autorisierungsbereiche hinzufügen

Wenn Sie auf BigQuery-Daten zugreifen möchten, fügen Sie die Methode enableBigQueryExecution() in Ihren Apps Script-Code ein. Mit dieser Methode wird Ihrem Apps Script-Projekt der erforderliche OAuth-Bereich bigquery.readonly hinzugefügt.

Das folgende Beispiel zeigt die Methode SpreadsheetApp.enableBigQueryExecution(), die in einer Funktion aufgerufen wird:

function addDataSource() {
  SpreadsheetApp.enableBigQueryExecution();
  var spreadsheet = SpreadsheetApp.getActive();
  }

Wenn Sie auf Looker-Daten zugreifen möchten, fügen Sie die Methode enableLookerExecution() in Ihren Apps Script-Code ein. Wenn Sie in Apps Script auf Looker zugreifen, wird Ihre vorhandene Google-Konto-Verknüpfung mit Looker wiederverwendet.

Das folgende Beispiel zeigt die Methode SpreadsheetApp.enableLookerExecution(), die in einer Funktion aufgerufen wird:

function addDataSource() {
  SpreadsheetApp.enableLookerExecution();
  var spreadsheet = SpreadsheetApp.getActive();
  }

Der Manifestdatei zusätzliche OAuth-Bereiche hinzufügen

Wenn Sie eine Verbindung zu BigQuery herstellen, werden die meisten OAuth-Bereiche automatisch der Manifestdatei hinzugefügt, basierend auf den Funktionen, die in Ihrem Code verwendet werden. Wenn Sie zusätzliche Bereiche benötigen, um auf bestimmte BigQuery-Daten zuzugreifen, können Sie explizite Bereiche festlegen.

Wenn Sie beispielsweise in Google Drive gehostete BigQuery-Daten abfragen möchten, müssen Sie Ihrer Manifestdatei einen Drive-OAuth-Bereich hinzufügen.

Das folgende Beispiel zeigt den oauthScopes-Teil einer Manifestdatei. Es wird zusätzlich zu den mindestens erforderlichen OAuth-Bereichen spreadsheet und bigquery.readonly ein Drive-OAuth-Bereich hinzugefügt:

{ ...
  "oauthScopes": [
    "https://www.googleapis.com/auth/bigquery.readonly",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive" ],
... }

Beispiel: Datenquellenobjekt erstellen und aktualisieren

Die folgenden Beispiele zeigen, wie Sie eine Datenquelle hinzufügen, ein Datenquellenobjekt aus der Datenquelle erstellen, das Datenquellenobjekt aktualisieren und den Ausführungsstatus abrufen.

Datenquelle hinzufügen

In den folgenden Beispielen wird gezeigt, wie Sie eine BigQuery- bzw. eine Looker-Datenquelle hinzufügen.

BigQuery

Wenn Sie einer Tabelle eine BigQuery-Datenquelle hinzufügen möchten, fügen Sie ein Datenquellenblatt mit einer Datenquellenspezifikation ein. Das Datenquellenblatt wird automatisch aktualisiert, um Vorschau-Daten abzurufen.

Ersetzen Sie <YOUR_PROJECT_ID> unten durch eine gültige Google Cloud-Projekt-ID.

// For operations that fetch data from BigQuery, enableBigQueryExecution() must be called.
SpreadsheetApp.enableBigQueryExecution();
var spreadsheet = SpreadsheetApp.create('Test connected sheets');
Logger.log('New test spreadsheet: %s', spreadsheet.getUrl());

// Build data source spec by selecting a table.
var dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
    .asBigQuery()
    .setProjectId('<YOUR_PROJECT_ID>')
    .setTableProjectId('bigquery-public-data')
    .setDatasetId('ncaa_basketball')
    .setTableId('mbb_historical_tournament_games')
    .build();
// Add data source and its associated data source sheet.
var dataSourceSheet = spreadsheet.insertDataSourceSheet(dataSourceSpec);
var dataSource = dataSourceSheet.getDataSource();

Looker

Wenn Sie einer Tabelle eine Looker-Datenquelle hinzufügen möchten, fügen Sie ein Datenquellenblatt mit einer Datenquellenspezifikation ein. Das Datenquellenblatt wird automatisch aktualisiert, um Vorschau-Daten abzurufen.

Ersetzen Sie <INSTANCE_URL>, <MODEL_NAME> und <EXPLORE_NAME> im folgenden Beispiel durch eine gültige Looker-Instanz-URL, einen Modellnamen bzw. einen Explore-Namen.

// For operations that fetch data from Looker, enableLookerExecution() must be called.
SpreadsheetApp.enableLookerExecution();
var spreadsheet = SpreadsheetApp.create('Test connected sheets');
Logger.log('New test spreadsheet: %s', spreadsheet.getUrl());

// Build data source spec by selecting a table.
var dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
    .asLooker()
    .setInstanceUrl('<INSTANCE_URL>')
    .setModelName('<MODEL_NAME>')
    .setExploreName('<EXPLORE_NAME>')
    .build();
// Add data source and its associated data source sheet.
var dataSourceSheet = spreadsheet.insertDataSourceSheet(dataSourceSpec);
var dataSource = dataSourceSheet.getDataSource();

Datenquellenobjekt hinzufügen

Sobald die Datenquelle der Tabelle hinzugefügt wurde, können Datenquellenobjekte aus der Datenquelle erstellt werden. In diesem Beispiel wird eine Pivot-Tabelle mit DataSourcePivotTable für die BigQuery-Datenquelle dataSource erstellt, die im Codebeispiel zum Hinzufügen einer BigQuery-Datenquelle erstellt wurde.

Im Gegensatz zu regulären Daten in Tabellenblättern, auf die über Zellindex oder A1-Notation verwiesen wird, wird bei Daten aus Datenquellen in der Regel auf Spaltennamen verwiesen. Daher wird bei den meisten Property-Settern für Datenquellenobjekte der Spaltenname als Eingabe verwendet.

var rootCell = spreadsheet.insertSheet('pivotTableSheet').getRange('A1');

// Add data source pivot table and set data source specific configurations.
var dataSourcePivotTable = rootCell.createDataSourcePivotTable(dataSource);
var rowGroup = dataSourcePivotTable.addRowGroup('season');
rowGroup.sortDescending().setGroupLimit(5);
dataSourcePivotTable.addColumnGroup('win_school_ncaa');
dataSourcePivotTable.addPivotValue('win_pts',
SpreadsheetApp.PivotTableSummarizeFunction.AVERAGE);
dataSourcePivotTable.addPivotValue('game_date',
SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
var filterCriteria = SpreadsheetApp.newFilterCriteria()
    .whenTextEqualToAny(['Duke', 'North Carolina'])
    .build();
dataSourcePivotTable.addFilter('win_school_ncaa', filterCriteria);

// Get a regular pivot table instance and set shared configurations.
var pivotTable = dataSourcePivotTable.asPivotTable();
pivotTable.setValuesDisplayOrientation(SpreadsheetApp.Dimension.ROWS);

Datenquellenobjekt aktualisieren

Aktualisieren Sie Datenquellenobjekte, um die neuesten Daten aus BigQuery abzurufen. Die Datenquelle wird anhand der Spezifikationen und Objektkonfigurationen aktualisiert.

Das Aktualisieren von Daten erfolgt asynchron. Verwenden Sie die folgenden Methoden, um ein Datenquellenobjekt zu aktualisieren:

Mit refreshData() wird die Ausführung der Datenaktualisierung gestartet.
waitForCompletion() gibt den Endstatus zurück, sobald die Datenausführung abgeschlossen ist. Dadurch ist es nicht mehr erforderlich, den Ausführungsstatus ständig abzufragen.
DataExecutionStatus.getErrorCode() ruft den Fehlercode ab, falls die Datenausführung fehlschlägt.

Im folgenden Beispiel werden die Daten der Pivot-Tabelle aktualisiert:

var status = dataSourcePivotTable.getStatus();
Logger.log('Initial state: %s', status.getExecutionState());

dataSourcePivotTable.refreshData();

status = dataSourcePivotTable.waitForCompletion(/* timeoutInSeconds= */ 60);
Logger.log('Ending state: %s', status.getExecutionState());
if (status.getExecutionState() == SpreadsheetApp.DataExecutionState.ERROR) {
  Logger.log('Error: %s (%s)', status.getErrorCode(),
  status.getErrorMessage());
}

Trigger mit verbundenen Tabellenblättern verwenden

Sie können die Funktionen Ihrer Connected Sheets-Datenquelle mit Triggern und Ereignissen automatisieren. Sie können beispielsweise zeitgesteuerte Trigger verwenden, um Datenquellenobjekte zu einem bestimmten Zeitpunkt wiederholt zu aktualisieren, und Tabellenkalkulations-Ereignistrigger, um die Datenausführung bei einem vordefinierten Ereignis auszulösen.

Im folgenden Beispiel wird eine BigQuery-Datenquelle mit einem Abfrageparameter hinzugefügt und das Datenquellenblatt wird aktualisiert, wenn der Abfrageparameter bearbeitet wird.

Ersetzen Sie <YOUR_PROJECT_ID> durch eine gültige Google Cloud-Projekt-ID.

// Add data source with query parameter.
function addDataSource() {
  SpreadsheetApp.enableBigQueryExecution();
  var spreadsheet = SpreadsheetApp.getActive();

  // Add a new sheet and use A1 cell as the parameter cell.
  var parameterCell = spreadsheet.insertSheet('parameterSheet').getRange('A1');
  parameterCell.setValue('Duke');

  // Add data source with query parameter.
  var dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
      .asBigQuery()
      .setProjectId('<YOUR_PROJECT_ID>')
      .setRawQuery('select * from `bigquery-public-data`.`ncaa_basketball`.`mbb_historical_tournament_games` WHERE win_school_ncaa = @SCHOOL')
      .setParameterFromCell('SCHOOL', 'parameterSheet!A1')
      .build();
  var dataSourceSheet = spreadsheet.insertDataSourceSheet(dataSourceSpec);
  dataSourceSheet.asSheet().setName('ncaa_data');
}

// Function used to configure event trigger to refresh data source sheet.
function refreshOnParameterEdit(e) {
  var editedRange = e.range;
  if (editedRange.getSheet().getName() != 'parameterSheet') {
    return;
  }
  // Check that the edited range includes A1.
  if (editedRange.getRow() > 1 || editedRange.getColumn() > 1) {
     return;
  }

  var spreadsheet = e.source;
  SpreadsheetApp.enableBigQueryExecution();
  spreadsheet.getSheetByName('ncaa_data').asDataSourceSheet().refreshData();
}

Im vorherigen Beispiel wird mit der Funktion addDataSource() eine Datenquelle der Tabelle hinzugefügt. Nachdem Sie addDataSource() ausgeführt haben, erstellen Sie im Apps Script-Editor einen Ereignistrigger. Informationen zum Erstellen eines Ereignistriggers finden Sie unter Installierbare Trigger.

Wählen Sie die folgenden Optionen für Ihren Trigger aus:

Ereignisquelle: Aus Tabelle
Ereignistyp: Beim Bearbeiten
Ausführungsfunktion: refreshOnParameterEdit

Nachdem der Trigger erstellt wurde, wird das Datenquellenblatt automatisch jedes Mal aktualisiert, wenn die Parameterzelle bearbeitet wird.

Fehlerbehebung

Fehlermeldung Auflösung

Mit enableBigQuery() können Sie Datenabfragen für BIGQUERY-Datenquellen ausführen. Dieser Fehler weist darauf hin, dass SpreadsheetApp.enableBigQueryExecution() nicht aufgerufen wird, bevor BigQuery-Daten abgerufen werden.
Rufen Sie SpreadsheetApp.enableBigQueryExecution() in Funktionen auf, die Methoden für die BigQuery-Ausführung verwenden.
Beispiele: refreshData() für Datenquellenobjekte, Spreadsheet.insertDataSourceTable() und DataSource.updateSpec().
Für diese Methoden ist ein zusätzlicher OAuth-Bereich „bigquery.readonly“ erforderlich.

Sie sind nicht berechtigt, diese Funktion für Datenquellen zu verwenden.
Bitten Sie Ihren Administrator, die Funktion zu aktivieren. Dieser Fehler weist darauf hin, dass für das Konto keine Verbindung zu Google Tabellen aktiviert ist.
Connected Sheets ist nur für Google Workspace-Nutzer mit bestimmten Abos verfügbar.
Wenden Sie sich an Ihren Administrator, um die Funktion aktivieren zu lassen.

Fehlermeldung	Auflösung
Mit `enableBigQuery()` können Sie Datenabfragen für BIGQUERY-Datenquellen ausführen.	Dieser Fehler weist darauf hin, dass `SpreadsheetApp.enableBigQueryExecution()` nicht aufgerufen wird, bevor BigQuery-Daten abgerufen werden. Rufen Sie `SpreadsheetApp.enableBigQueryExecution()` in Funktionen auf, die Methoden für die BigQuery-Ausführung verwenden. Beispiele: `refreshData()` für Datenquellenobjekte, `Spreadsheet.insertDataSourceTable()` und `DataSource.updateSpec()`. Für diese Methoden ist ein zusätzlicher OAuth-Bereich „bigquery.readonly“ erforderlich.
Sie sind nicht berechtigt, diese Funktion für Datenquellen zu verwenden. Bitten Sie Ihren Administrator, die Funktion zu aktivieren.	Dieser Fehler weist darauf hin, dass für das Konto keine Verbindung zu Google Tabellen aktiviert ist. Connected Sheets ist nur für Google Workspace-Nutzer mit bestimmten Abos verfügbar. Wenden Sie sich an Ihren Administrator, um die Funktion aktivieren zu lassen.

Verbundene Tabellenblätter verwenden Mit Sammlungen den Überblick behalten Sie können Inhalte basierend auf Ihren Einstellungen speichern und kategorisieren.