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-Daten direkt in Google Tabellen analysieren können. Über den Tabellendienst können Sie programmatisch auf verbundene Tabellenblätter zugreifen.

Häufige Aktionen für verbundene Tabellenblätter

Verwenden Sie die Klassen und Objekte von DataSource, um eine Verbindung zu BigQuery herzustellen und Daten zu analysieren. In der folgenden Tabelle finden Sie die häufigsten DataSource-Aktionen und wie sie in Apps Script erstellt werden:

Aktion	Google Apps Script-Kurs	Zu verwendende Methode
Tabelle mit BigQuery verbinden	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
BigQuery-Datenquelle auswählen	`DataSource`	`Spreadsheet.insertDataSourceSheet().getDataSource()`
BigQuery-Datenquellentabelle 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

Für den Zugriff auf BigQuery-Daten müssen Sie die Methode enableBigQueryExecution() in den 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();
  }

Der Manifestdatei zusätzliche OAuth-Bereiche hinzufügen

Die meisten OAuth-Bereiche werden anhand der in Ihrem Code verwendeten Funktionen automatisch zur 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 BigQuery-Daten abfragen möchten, die in Google Drive gehostet werden, müssen Sie Ihrer Manifestdatei einen Drive-OAuth-Bereich hinzufügen.

Das folgende Beispiel zeigt den oauthScopes-Teil einer Manifestdatei. Zusätzlich zu den mindestens erforderlichen spreadsheet- und bigquery.readonly-OAuth-Bereichen 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

Im folgenden Beispiel wird gezeigt, wie Sie eine BigQuery-Datenquelle hinzufügen, ein Datenquellenobjekt aus der Datenquelle erstellen, das Datenquellenobjekt aktualisieren und den Ausführungsstatus abrufen. In diesem Beispiel werden die Code-Snippets nacheinander ausgeführt.

BigQuery-Datenquelle hinzufügen

Wenn Sie einer Tabelle eine BigQuery-Datenquelle hinzufügen möchten, fügen Sie eine Datenquellentabelle mit einer Datenquellenspezifikation ein. Die Datenquellentabelle 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();

Datenquellenobjekt hinzufügen

Sobald die Datenquelle der Tabelle hinzugefügt wurde, können aus ihr Datenquellenobjekte erstellt werden. In diesem Beispiel wird eine Pivot-Tabelle mit DataSourcePivotTable erstellt.

Im Gegensatz zu normalen Daten in Rastertabellen, auf die durch den Zellenindex oder durch A1-Notationen verwiesen wird, werden Daten aus Datenquellen in der Regel durch Spaltennamen referenziert. 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

Sie können Datenquellenobjekte aktualisieren, um anhand der Spezifikationen der Datenquelle und der Objektkonfigurationen die neuesten Daten aus BigQuery abzurufen.

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

refreshData() startet die Datenaktualisierung.
waitForCompletion() gibt den Endstatus zurück, sobald die Datenausführung abgeschlossen ist. So müssen Sie den Ausführungsstatus nicht mehr abfragen.
DataExecutionStatus.getErrorCode() ruft den Fehlercode für den Fall ab, dass die Datenausführung fehlschlägt.

Das folgende Beispiel zeigt eine Aktualisierung der Daten in der Pivot-Tabelle:

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

Automatisieren Sie die Funktionen Ihrer verbundenen Tabellenblätter mit Triggern und Ereignissen. Beispielsweise können Sie zeitgesteuerte Trigger verwenden, um Datenquellenobjekte zu einem bestimmten Zeitpunkt wiederholt zu aktualisieren, und Tabellenereignistrigger verwenden, um die Datenausführung für ein vordefiniertes Ereignis auszulösen.

Im folgenden Beispiel wird eine Datenquelle mit einem Abfrageparameter hinzugefügt. Das Datenquellenblatt wird aktualisiert, wenn der Abfrageparameter bearbeitet wird.

Ersetzen Sie <YOUR_PROJECT_ID> unten 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 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 Ereignistrigger 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 das Datenquellenblatt bei jeder Bearbeitung der Parameterzelle automatisch aktualisiert.

Fehlerbehebung

Fehlermeldung Auflösung

Verwenden Sie enableBigQuery(), um die Datenausführung für BIGQUERY-Datenquellen zu aktivieren. 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 in BigQuery verwenden.
Zum Beispiel refreshData() für Datenquellenobjekte, Spreadsheet.insertDataSourceTable() und DataSource.updateSpec().
Diese Methoden erfordern einen zusätzlichen OAuth-Bereich vom Typ „bigquery.readonly“.

Sie sind nicht berechtigt, Änderungen an Datenquellen vorzunehmen.
Bitten Sie Ihren Administrator, die Funktion zu aktivieren. Dieser Fehler weist darauf hin, dass verbundene Tabellenblätter für das Konto nicht aktiviert sind.
Verbundene Tabellenblätter sind nur für Google Workspace Nutzer mit bestimmten Abos verfügbar.
Wenden Sie sich an Ihren Administrator, um die Funktion zu aktivieren.

Fehlermeldung	Auflösung
Verwenden Sie `enableBigQuery()`, um die Datenausführung für BIGQUERY-Datenquellen zu aktivieren.	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 in BigQuery verwenden. Zum Beispiel `refreshData()` für Datenquellenobjekte, `Spreadsheet.insertDataSourceTable()` und `DataSource.updateSpec()`. Diese Methoden erfordern einen zusätzlichen OAuth-Bereich vom Typ „bigquery.readonly“.
Sie sind nicht berechtigt, Änderungen an Datenquellen vorzunehmen. Bitten Sie Ihren Administrator, die Funktion zu aktivieren.	Dieser Fehler weist darauf hin, dass verbundene Tabellenblätter für das Konto nicht aktiviert sind. Verbundene Tabellenblätter sind nur für Google Workspace Nutzer mit bestimmten Abos verfügbar. Wenden Sie sich an Ihren Administrator, um die Funktion zu aktivieren.