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 BigQuery i Lookera bezpośrednio w Arkuszach. Do połączonych arkuszy możesz uzyskać dostęp programowy za pomocą usługi Arkusze.

Typowe działania w połączonych arkuszach

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

Działanie	Klasa Google Apps Script	Metoda korzystania
Łą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 w kodzie Google Apps Script uwzględnić metodę enableBigQueryExecution(). 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 funkcji:

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

Aby uzyskać dostęp do danych Lookera, musisz uwzględnić w kodzie Google Apps Script metodę enableLookerExecution(). Dostęp do Lookera w Apps Script będzie ponownie wykorzystywać istniejące połączenie konta Google z Lookerem.

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

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

Dodawanie dodatkowych zakresów protokołu OAuth do pliku manifestu

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

Jeśli na przykład chcesz wysyłać zapytania dotyczące danych BigQuery hostowanych w Google Drive, musisz dodać zakres OAuth Drive do pliku manifestu.

Poniższy przykład przedstawia fragment oauthScopes pliku manifestu. Dodaje zakres protokołu OAuth Dysku oprócz minimalnych wymaganych zakresów protokołu OAuth spreadsheet i bigquery.readonly:

{ ...
  "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

Poniższy przykład pokazuje, jak dodać źródło danych, utworzyć z niego obiekt źródła danych, odświeżyć ten obiekt i uzyskać stan wykonania.

Dodaj źródło danych

Poniższe przykłady pokazują, 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 pobrać dane podglądu.

Zastąp poniższy symbol <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ć do arkusza źródło danych Looker, wstaw arkusz źródła danych ze specyfikacją źródła danych. Arkusz źródła danych jest automatycznie odświeżany, aby pobrać dane podglądu.

W poniższym przykładzie zastąp <INSTANCE_URL>, <MODEL_NAME> i <EXPLORE_NAME> odpowiednio prawidłowym adresem URL instancji Lookera, 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żna z niego utworzyć obiekty źródła danych. W tym przykładzie tabela przestawna jest tworzona za pomocą funkcji DataSourcePivotTable w dataSource BigQuery utworzonym w przykładowym kodzie, który dodaje źródło danych BigQuery.

W przeciwieństwie do zwykłych danych w arkuszach siatki, do których odwołuje się indeks komórki lub notacja A1, do danych ze źródeł danych zwykle odwołuje się nazwa kolumny. Dlatego większość ustawień właściwości w obiektach źródła danych używa nazwy kolumny jako danych wejściowych.

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, skorzystaj z tych metod:

refreshData() rozpoczyna wykonywanie odświeżania danych.
waitForCompletion() zwraca stan końcowy po zakończeniu wykonywania danych. Eliminuje to konieczność ciągłego sprawdzania stanu wykonania.
DataExecutionStatus.getErrorCode() otrzymuje kod błędu w przypadku niepowodzenia wykonania danych.

Poniższy przykład ilustruje odświeżanie danych 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());
}

Używanie aktywatorów w połączonych arkuszach

Zautomatyzuj funkcje źródła danych połączonych arkuszy za pomocą aktywatorów i zdarzeń. Możesz na przykład używać wyzwalaczy opartych na czasie, aby wielokrotnie odświeżać obiekty źródła danych o określonej godzinie, oraz wyzwalaczy zdarzeń arkusza kalkulacyjnego, aby wywoływać wykonywanie danych w przypadku zdefiniowanego zdarzenia.

Poniższy przykład dodaje źródło danych BigQuery z parametrem zapytania i odświeża arkusz źródła danych po edycji parametru zapytania.

Zastąp poniższy symbol <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 wyzwalacz zdarzenia w edytorze Apps Script. Aby dowiedzieć się, jak utworzyć aktywator zdarzenia, przeczytaj artykuł Aktywatory instalowane.

Wybierz te opcje aktywatora:

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

Po utworzeniu wyzwalacza arkusz źródła danych będzie się automatycznie odświeżać za każdym razem, gdy edytowana będzie komórka 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 BIGQUERY. Ten błąd oznacza, że przed pobraniem danych BigQuery nie wywołano funkcji SpreadsheetApp.enableBigQueryExecution().
Wywołaj funkcję SpreadsheetApp.enableBigQueryExecution() w funkcjach, które używają metod do wykonywania BigQuery.
Na przykład refreshData() w przypadku obiektów źródła danychSpreadsheet.insertDataSourceTable() i DataSource.updateSpec().
Te metody wymagają dodatkowego zakresu protokołu 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 są włączone połączone arkusze.
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 BIGQUERY.	Ten błąd oznacza, że przed pobraniem danych BigQuery nie wywołano funkcji `SpreadsheetApp.enableBigQueryExecution()`. Wywołaj funkcję `SpreadsheetApp.enableBigQueryExecution()` w funkcjach, które używają metod do wykonywania BigQuery. Na przykład `refreshData()` w przypadku obiektów źródła danych`Spreadsheet.insertDataSourceTable()` i `DataSource.updateSpec()`. Te metody wymagają dodatkowego zakresu protokołu 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 są włączone połączone arkusze. 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.

Korzystanie z połączonych arkuszy Zadbaj o dobrą organizację dzięki kolekcji Zapisuj i kategoryzuj treści zgodnie ze swoimi preferencjami.