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. Sie können programmatisch mit dem Tabellen-Dienst auf verbundene Tabellenblätter zugreifen.

Häufige Aktionen mit verbundenen Tabellenblättern

Mit den Klassen und Objekten DataSource können Sie eine Verbindung zu BigQuery oder Looker herstellen und Daten analysieren. In der folgenden Tabelle sind die häufigsten DataSource-Aktionen und die entsprechenden Schritte zum Erstellen in Google Apps Script aufgeführt:

Aktion	Apps Script-Klasse	Zu verwendende Methode
Tabellenblatt mit einer unterstützten Datenquelle verbinden	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
Datenquelle auswählen	`DataSource`	`Spreadsheet.insertDataSourceSheet().getDataSource()`
Datenquellentabelle hinzufügen	`DataSourceSheet`	`Spreadsheet.insertDataSourceSheet()`
Pivot-Tabelle hinzufügen	`DataSourcePivotTable`	`Range.insertDataSourcePivotTable()`
Daten in einen Extrakt ziehen	`DataSourceTable`	`Range.insertDataSourceTable()`
Formel verwenden	`DataSourceFormula`	`Range.setFormula()`
Diagramm hinzufügen	`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.

Im folgenden Beispiel wird die Methode SpreadsheetApp.enableBigQueryExecution() innerhalb einer Funktion aufgerufen:

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-Kontoverknüpfung mit Looker wiederverwendet.

Im folgenden Beispiel wird die Methode SpreadsheetApp.enableLookerExecution() innerhalb einer Funktion aufgerufen:

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

Weitere OAuth-Bereiche zur Manifestdatei hinzufügen

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

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

Im folgenden Beispiel sehen Sie den Abschnitt oauthScopes einer Manifestdatei. Neben den mindestens erforderlichen OAuth-Bereichen spreadsheet und bigquery.readonly wird 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

In den folgenden Beispielen wird gezeigt, 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 jeweils eine BigQuery- und eine Looker-Datenquelle hinzufügen.

BigQuery

Wenn Sie einer Tabelle eine BigQuery-Datenquelle hinzufügen möchten, fügen Sie ein Datenquellentabellenblatt mit einer Datenquellenspezifikation ein. Das Datenquellentabellenblatt wird automatisch aktualisiert, um Vorschaudaten 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 Datenquellentabellenblatt mit einer Datenquellenspezifikation ein. Das Datenquellentabellenblatt wird automatisch aktualisiert, um Vorschaudaten abzurufen.

Ersetzen Sie im folgenden Beispiel <INSTANCE_URL>,<MODEL_NAME> und <EXPLORE_NAME> durch eine gültige Looker-Instanz-URL, einen gültigen Modellnamen und einen gültigen Namen für die Datenexploration.

// 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

Nachdem die Datenquelle der Tabelle hinzugefügt wurde, können Datenquellenobjekte aus der Datenquelle erstellt werden. In diesem Beispiel wird mit DataSourcePivotTable eine Pivot-Tabelle für die BigQuery-dataSource erstellt, die in dem Codebeispiel erstellt wurde, in dem eine BigQuery-Datenquelle hinzugefügt wird.

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

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 basierend auf den Datenquellenspezifikationen und Objektkonfigurationen abzurufen.

Der Prozess zum Aktualisieren von Daten ist asynchron. Verwenden Sie die folgenden Methoden, um ein Datenquellenobjekt zu aktualisieren:

refreshData() startet die Aktualisierung der Daten.
waitForCompletion() gibt den Endstatus zurück, sobald die Datenaktualisierung abgeschlossen ist. So müssen Sie den Ausführungsstatus nicht mehr abfragen.
DataExecutionStatus.getErrorCode() ruft den Fehlercode ab, falls die Datenaktualisierung fehlschlägt.

Im folgenden Beispiel wird die Aktualisierung der Pivot-Tabellendaten veranschaulicht:

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 Datenquellenfunktionen für verbundene Tabellenblätter mit Triggern und Ereignissen automatisieren. Verwenden Sie beispielsweise zeitgesteuerte Trigger , um Datenquellenobjekte zu einem bestimmten Zeitpunkt wiederholt zu aktualisieren, und verwenden Sie Tabellen ereignistrigger , um die Datenaktualisierung bei einem vordefinierten Ereignis auszulösen.

Im folgenden Beispiel wird eine BigQuery-Datenquelle mit einem Abfrageparameter hinzugefügt und das Datenquellentabellenblatt 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 fügt die Funktion addDataSource() der Tabelle eine Datenquelle hinzu. Nachdem Sie addDataSource() ausgeführt haben, erstellen Sie im Apps Script-Editor einen Ereignistrigger. Informationen zum Erstellen eines Ereignistriggers, siehe Installierbare Trigger.

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

Ereignisquelle: Aus Tabelle
Ereignistyp: Bei Bearbeitung
Auszuführende Funktion: refreshOnParameterEdit

Sobald der Trigger erstellt wurde, wird die Datenquellentabelle automatisch aktualisiert, wenn die Parameterzelle bearbeitet wird.

Fehlerbehebung

Fehlermeldung Auflösung

Verwenden Sie enableBigQuery(), um die Datenaktualisierung für BIGQUERY-Datenquellen zu aktivieren. Dieser Fehler gibt an, 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 sind refreshData() für Datenquellenobjekte, Spreadsheet.insertDataSourceTable(), und DataSource.updateSpec().
Für diese Methoden ist der zusätzliche 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 gibt an, dass für das Konto keine verbundenen Tabellenblätter aktiviert sind.
Verbundene Tabellenblätter sind nur für Google Workspace-Nutzer mit bestimmten Abos verfügbar.
Bitten Sie Ihren Administrator, die Funktion zu aktivieren.

Fehlermeldung	Auflösung
Verwenden Sie `enableBigQuery()`, um die Datenaktualisierung für BIGQUERY-Datenquellen zu aktivieren.	Dieser Fehler gibt an, 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 sind `refreshData()` für Datenquellenobjekte, `Spreadsheet.insertDataSourceTable()`, und `DataSource.updateSpec()`. Für diese Methoden ist der zusätzliche 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 gibt an, dass für das Konto keine verbundenen Tabellenblätter aktiviert sind. Verbundene Tabellenblätter sind nur für Google Workspace-Nutzer mit bestimmten Abos verfügbar. Bitten Sie Ihren Administrator, die Funktion zu aktivieren.

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