Ta strona została przetłumaczona przez Cloud Translation API.

Korzystanie z połączonych arkuszy

Połączone arkusze to funkcja Arkuszy Google, która umożliwia analizowanie danych z BigQuery i Lookera bezpośrednio w Arkuszach. Do połączonych arkuszy możesz uzyskać dostęp programowo za pomocą usługi Arkuszy.

Typowe działania w połączonych arkuszach

Używaj klas i obiektów DataSource, aby łączyć się z BigQuery lub Lookerem i analizować dane. W tabeli poniżej znajdziesz najczęstsze działania DataSource oraz instrukcje ich tworzenia w usłudze Apps Script:

Działanie	Klasa Google Apps Script	Metoda
Łączenie arkusza z obsługiwanym źródłem danych	`DataSourceSpec`	`SpreadsheetApp.newDataSourceSpec()`
Wybierz źródło danych	`DataSource`	`Spreadsheet.insertDataSourceSheet().getDataSource()`
Dodawanie arkusza źródła danych	`DataSourceSheet`	`Spreadsheet.insertDataSourceSheet()`
Dodawanie tabeli przestawnej	`DataSourcePivotTable`	`Range.insertDataSourcePivotTable()`
Pobieranie danych do fragmentu	`DataSourceTable`	`Range.insertDataSourceTable()`
Używanie formuły	`DataSourceFormula`	`Range.setFormula()`
Dodaj wykres	`DataSourceChart`	`Sheet.insertDataSourceChart()`

Dodawanie wymaganych zakresów autoryzacji

Aby uzyskać dostęp do danych BigQuery, musisz uwzględnić metodę enableBigQueryExecution() w kodzie Google Apps Script. Ta metoda dodaje do projektu Google Apps Script wymagany zakres protokołu OAuth bigquery.readonly.

Poniższy przykład pokazuje metodę SpreadsheetApp.enableBigQueryExecution() wywoływaną w ramach funkcji:

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

Aby uzyskać dostęp do danych Lookera, musisz uwzględnić metodę enableLookerExecution() w kodzie Google Apps Script. Otwieranie Lookera w Google Apps Script spowoduje ponowne użycie istniejącego połączenia konta Google z Lookerem.

Ten przykład pokazuje metodę SpreadsheetApp.enableLookerExecution() wywoływaną w funkcji:

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

Dodawanie do pliku manifestu dodatkowych zakresów OAuth

Podczas łączenia z BigQuery większość zakresów OAuth jest automatycznie dodawana do pliku manifestu na podstawie funkcji używanych w kodzie. Jeśli potrzebujesz dodatkowych zakresów, aby uzyskać dostęp do określonych danych BigQuery, możesz ustawić zakresy jawne.

Aby na przykład wysyłać zapytania o dane BigQuery przechowywane na Dysku Google, musisz dodać do pliku manifestu zakres protokołu OAuth na Dysku.

Poniższy przykład pokazuje część oauthScopes pliku manifestu. Oprócz minimalnych wymaganych zakresów protokołu OAuth spreadsheet i bigquery.readonly dodaje zakres protokołu OAuth Dysku:

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

Przykład: tworzenie i odświeżanie obiektu źródła danych

Z podanych niżej przykładów dowiesz się, jak dodać źródło danych, utworzyć ze źródła danych obiekt źródła danych, odświeżyć obiekt źródła danych i sprawdzić stan wykonania.

Dodaj źródło danych

W poniższych przykładach pokazano, jak dodać źródło danych BigQuery i Looker.

BigQuery

Aby dodać źródło danych BigQuery do arkusza kalkulacyjnego, wstaw arkusz źródła danych ze specyfikacją źródła danych. Arkusz źródła danych jest automatycznie odświeżany, aby pobierać dane podglądu.

Zastąp <YOUR_PROJECT_ID> prawidłowym identyfikatorem projektu Google Cloud.

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

Aby dodać źródło danych Looker do arkusza kalkulacyjnego, wstaw arkusz źródła danych ze specyfikacją źródła danych. Arkusz źródła danych jest automatycznie odświeżany, aby pobierać dane podglądu.

W tym przykładzie zamień <INSTANCE_URL>, <MODEL_NAME> i <EXPLORE_NAME> na prawidłowe adresy URL instancji Looker, nazwę modelu i nazwę eksploracji.

// 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();

Dodawanie obiektu źródła danych

Po dodaniu źródła danych do arkusza kalkulacyjnego możesz tworzyć z niego obiekty źródeł danych. W tym przykładzie tabela przestawna jest tworzona za pomocą DataSourcePivotTable w BigQuery dataSource utworzonym w przykładowym kodzie, który dodaje źródło danych BigQuery.

W przeciwieństwie do zwykłych danych w arkuszach siatki, które odwołują się do komórek za pomocą indeksu komórki lub notacji A1, dane ze źródeł danych odwołują się zwykle do nazw kolumn. Dlatego większość kreatorów właściwości w obiektach źródła danych jako danych wejściowych używa nazwy kolumny.

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);

Odświeżanie obiektu źródła danych

Możesz odświeżać obiekty źródła danych, aby pobierać najnowsze dane z BigQuery na podstawie specyfikacji źródła danych i konfiguracji obiektów.

Proces odświeżania danych jest asynchroniczny. Aby odświeżyć obiekt źródła danych, użyj tych metod:

refreshData() uruchamia odświeżanie danych.
waitForCompletion() zwraca stan końcowy po zakończeniu wykonywania danych. Dzięki temu nie trzeba stale sprawdzać stanu wykonania.
DataExecutionStatus.getErrorCode() otrzymuje kod błędu, jeśli wykonanie danych zakończy się niepowodzeniem.

Poniższy przykład pokazuje odświeżanie danych w tabeli przestawnej:

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());
}

Korzystanie z reguł w połączonych arkuszach

Zautomatyzuj funkcje źródeł danych w połączonych arkuszach za pomocą reguł i zdarzeń. Możesz na przykład używać wyzwalaczy czasowych, aby wielokrotnie odświeżać obiekty źródeł danych w określonym czasie, oraz wyzwalaczy zdarzeń w arkuszu kalkulacyjnym, aby uruchamiać wykonywanie danych po wystąpieniu zdefiniowanego zdarzenia.

W tym przykładzie dodajemy źródło danych BigQuery z parametrem zapytania i odświeżamy arkusz źródła danych po jego zmodyfikowaniu.

Zastąp <YOUR_PROJECT_ID> prawidłowym identyfikatorem projektu Google Cloud.

// 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();
}

W powyższym przykładzie funkcja addDataSource() dodaje źródło danych do arkusza kalkulacyjnego. Po wykonaniu addDataSource() utwórz w edytorze Apps Script wyzwalacz zdarzenia. Aby dowiedzieć się, jak utworzyć aktywator zdarzenia, przeczytaj artykuł Instalowalne aktywatory.

Wybierz te opcje reguły:

Źródło zdarzenia: Z arkusza kalkulacyjnego
Typ zdarzenia: przy edycji
Funkcja do uruchomienia: refreshOnParameterEdit

Po utworzeniu reguły arkusz źródła danych jest automatycznie odświeżany za każdym razem, gdy edytujesz komórkę parametru.

Rozwiązywanie problemów

Komunikat o błędzie Rozdzielczość

Użyj metody enableBigQuery(), aby umożliwić wykonywanie zapytań do bazy danych w przypadku źródeł danych typu BIGQUERY. Ten błąd wskazuje, że funkcja SpreadsheetApp.enableBigQueryExecution() nie jest wywoływana przed pobieraniem danych z BigQuery.
Wywołuj funkcję SpreadsheetApp.enableBigQueryExecution() w funkcjach, które używają metod do wykonywania zapytań do BigQuery.
Na przykład refreshData() w przypadku obiektów źródeł danych, Spreadsheet.insertDataSourceTable() i DataSource.updateSpec().
Te metody wymagają dodatkowego zakresu OAuth bigquery.readonly.

Niedozwolone działanie na źródłach danych.
Aby włączyć tę funkcję, skontaktuj się z administratorem. Ten błąd oznacza, że na koncie nie włączono połączonych arkuszy.
Połączone arkusze są dostępne tylko dla użytkowników Google Workspace z określonymi subskrypcjami.
Aby włączyć tę funkcję, skontaktuj się z administratorem.

Komunikat o błędzie	Rozdzielczość
Użyj metody `enableBigQuery()`, aby umożliwić wykonywanie zapytań do bazy danych w przypadku źródeł danych typu BIGQUERY.	Ten błąd wskazuje, że funkcja `SpreadsheetApp.enableBigQueryExecution()` nie jest wywoływana przed pobieraniem danych z BigQuery. Wywołuj funkcję `SpreadsheetApp.enableBigQueryExecution()` w funkcjach, które używają metod do wykonywania zapytań do BigQuery. Na przykład `refreshData()` w przypadku obiektów źródeł danych, `Spreadsheet.insertDataSourceTable()` i `DataSource.updateSpec()`. Te metody wymagają dodatkowego zakresu OAuth bigquery.readonly.
Niedozwolone działanie na źródłach danych. Aby włączyć tę funkcję, skontaktuj się z administratorem.	Ten błąd oznacza, że na koncie nie włączono połączonych arkuszy. Połączone arkusze są dostępne tylko dla użytkowników Google Workspace z określonymi subskrypcjami. Aby włączyć tę funkcję, skontaktuj się z administratorem.