Diese Seite wurde von der Cloud Translation API übersetzt.

Verbundene Tabellenblätter verwenden

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

Gängige Aktionen für verbundene Tabellenblätter

Mit den Klassen und Objekten von DataSource können Sie eine Verbindung zu BigQuery oder Looker herstellen und Daten analysieren. In der folgenden Tabelle sind die gängigsten DataSource-Aktionen aufgeführt und beschrieben, wie sie in Apps Script erstellt werden:

Aktion	Google Apps Script-Klasse	Zu verwendende Methode
Tabellen mit unterstützten Datenquellen verknüpfen	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
Datenquelle auswählen	`DataSource`	`Spreadsheet.insertDataSourceSheet().getDataSource()`
Datenquellentabellenblatt hinzufügen	`DataSourceSheet`	`Spreadsheet.insertDataSourceSheet()`
Pivot-Tabellen hinzufügen	`DataSourcePivotTable`	`Range.insertDataSourcePivotTable()`
Daten in einen 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, müssen Sie die Methode enableBigQueryExecution() in Ihren Google Apps Script-Code einfügen. Mit dieser Methode wird Ihrem Google Apps Script-Projekt der erforderliche OAuth-Bereich bigquery.readonly hinzugefügt.

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

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

Wenn Sie auf Looker-Daten zugreifen möchten, müssen Sie die enableLookerExecution()-Methode in Ihren Google Apps Script-Code aufnehmen. Wenn Sie in Apps Script auf Looker zugreifen, wird Ihre vorhandene Google-Kontoverknüpfung mit Looker wiederverwendet.

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

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

Manifestdatei weitere OAuth-Bereiche hinzufügen

Wenn Sie eine Verbindung zu BigQuery herstellen, werden die meisten OAuth-Bereiche basierend auf den in Ihrem Code verwendeten Funktionen automatisch der Manifestdatei hinzugefügt. Wenn Sie für den Zugriff auf bestimmte BigQuery-Daten zusätzliche Bereiche benötigen, 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.

Im folgenden Beispiel wird der oauthScopes-Teil einer Manifestdatei gezeigt. Zusätzlich zu den erforderlichen OAuth-Bereichen spreadsheet und bigquery.readonly wird ein OAuth-Bereich für Google Drive 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, aus der Datenquelle ein Datenquellenobjekt erstellen, das Datenquellenobjekt aktualisieren und den Ausführungsstatus abrufen.

Datenquelle hinzufügen

Die folgenden Beispiele zeigen, wie Sie eine BigQuery- und eine Looker-Datenquelle hinzufügen.

BigQuery

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

Ersetzen Sie <INSTANCE_URL>, <MODEL_NAME> und <EXPLORE_NAME> im folgenden Beispiel durch eine gültige Looker-Instanz-URL, einen Modellnamen und 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

Nachdem die Datenquelle der Tabelle hinzugefügt wurde, können Datenquellenobjekte aus der Datenquelle erstellt werden. In diesem Beispiel wird eine Pivot-Tabelle mit DataSourcePivotTable auf der BigQuery-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 Zellenindex oder A1-Notationen verwiesen wird, werden Daten aus Datenquellen in der Regel über Spaltennamen referenziert. Daher wird bei den meisten Eigenschaftsfestlegungen 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

Sie können Datenquellenobjekte aktualisieren, um die neuesten Daten aus BigQuery abzurufen. Dabei werden die Datenquellenspezifikationen und Objektkonfigurationen berücksichtigt.

Das Aktualisieren der Daten erfolgt asynchron. So aktualisieren Sie ein Datenquellenobjekt:

refreshData() startet die Ausführung der Datenaktualisierung.
waitForCompletion() gibt den Endstatus zurück, sobald die Datenausführung abgeschlossen ist. So müssen Sie den Ausführungsstatus nicht ständig abfragen.
DataExecutionStatus.getErrorCode() ruft den Fehlercode ab, wenn die Datenausführung 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 Funktionen der verbundenen Google Tabellen-Datenquelle mit Triggern und Ereignissen automatisieren. Sie können beispielsweise zeitgesteuerte Trigger verwenden, um Datenquellenobjekte zu einer bestimmten Zeit wiederholt zu aktualisieren, und Ereignistrigger in Tabellen, um die Datenausführung bei einem vordefinierten Ereignis auszulösen.

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

Ersetzen Sie unten <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 obigen Beispiel wird der Tabelle mit der Funktion addDataSource() eine Datenquelle hinzugefügt. Nachdem Sie addDataSource() ausgeführt haben, erstellen Sie einen Ereignistrigger im Apps Script-Editor. Informationen zum Erstellen eines Ereignistriggers finden Sie unter Installierbare Trigger.

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

Ereignisquelle: Aus Tabelle
Ereignistyp: Beim Bearbeiten
Auszuführende Funktion: refreshOnParameterEdit

Nachdem der Trigger erstellt wurde, wird die Datenquellentabelle jedes Mal automatisch 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() vor dem Abrufen von BigQuery-Daten nicht aufgerufen wird.
Rufen Sie SpreadsheetApp.enableBigQueryExecution() in Funktionen auf, die Methoden zur Ausführung von BigQuery verwenden.
Zum Beispiel refreshData() auf Datenquellenobjekten, 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.
Wenden Sie sich an Ihren Administrator, um die Funktion zu aktivieren. Dieser Fehler weist darauf hin, dass in dem Konto keine verbundenen Tabellenblätter aktiviert sind.
Verbundene Tabellenblätter sind nur für Nutzer mit bestimmten Abos verfügbar. Google Workspace
Wenden Sie sich an Ihren Administrator, um die Funktion zu aktivieren.

Fehlermeldung	Auflösung
Mit `enableBigQuery()` können Sie Datenabfragen für BIGQUERY-Datenquellen ausführen.	Dieser Fehler weist darauf hin, dass `SpreadsheetApp.enableBigQueryExecution()` vor dem Abrufen von BigQuery-Daten nicht aufgerufen wird. Rufen Sie `SpreadsheetApp.enableBigQueryExecution()` in Funktionen auf, die Methoden zur Ausführung von BigQuery verwenden. Zum Beispiel `refreshData()` auf Datenquellenobjekten, `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. Wenden Sie sich an Ihren Administrator, um die Funktion zu aktivieren.	Dieser Fehler weist darauf hin, dass in dem Konto keine verbundenen Tabellenblätter aktiviert sind. Verbundene Tabellenblätter sind nur für Nutzer mit bestimmten Abos verfügbar. Google Workspace Wenden Sie sich an Ihren Administrator, um die Funktion zu aktivieren.