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 użycia
Łą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 wywołanie metody SpreadsheetApp.enableBigQueryExecution() 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 wywołanie metody SpreadsheetApp.enableLookerExecution() w funkcji:

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

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

Podczas łączenia się 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 na Dysku Google, musisz dodać do pliku manifestu zakres OAuth Dysku.

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 fragment <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 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, 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, jeśli wykonanie danych się nie powiedzie.

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żyć 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 wstępnie zdefiniowanego zdarzenia.

Poniższy przykład dodaje źródło danych BigQuery z parametrem zapytania i odświeża arkusz źródła danych, gdy parametr zapytania jest edytowany.

Zastąp poniższy fragment <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 typu 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 typu 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.