Diese Seite wurde von der Cloud Translation API übersetzt.

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 Tabellendienst 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 Apps Script aufgeführt:

Aktion	Google 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

Für den Zugriff auf BigQuery-Daten 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 SpreadsheetApp.enableBigQueryExecution()-Methode, die in einer Funktion aufgerufen wird:

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

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

Das folgende Beispiel zeigt die SpreadsheetApp.enableLookerExecution()-Methode, 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 Vorschaudaten 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-Tabelle 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 den Zellenindex oder die A1-Notation verwiesen wird, wird auf Daten aus Datenquellen in der Regel über 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

Sie können Datenquellenobjekte aktualisieren, um die neuesten Daten aus BigQuery abzurufen. Die Daten werden auf Grundlage der Datenquellenspezifikationen und Objektkonfigurationen abgerufen.

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. So muss der Ausführungsstatus nicht mehr ständig abgefragt werden.
DataExecutionStatus.getErrorCode() ruft den Fehlercode ab, falls 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 Ihrer Connected Sheets-Datenquelle mit Triggern und Ereignissen automatisieren. Verwenden Sie beispielsweise zeitgesteuerte Trigger, 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> 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 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.
, z. B. 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. , z. B. `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.