Korzystanie z połączonych arkuszy

Połączone arkusze to funkcja Arkuszy Google, która umożliwia analizowanie danych BigQuery bezpośrednio w Arkuszach. Dostęp do połączonych arkuszy możesz uzyskać automatycznie za pomocą usługi Arkusze kalkulacyjne.

Typowe czynności wykonywane w połączonych arkuszach

Używaj klas i obiektów DataSource do łączenia się z BigQuery i analizowania danych. Tabela poniżej zawiera listę najczęstszych działań DataSource i sposobów ich tworzenia w Apps Script:

Działanie Zajęcia dotyczące Google Apps Script Zastosowana metoda
Łączenie arkusza z BigQuery DataSourceSpec SpreadsheetApp.newDataSourceSpec()
Wybierz źródło danych BigQuery DataSource Spreadsheet.insertDataSourceSheet().getDataSource()
Dodawanie arkusza źródła danych BigQuery 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()

Dodaj wymagane zakresy autoryzacji

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

Poniższy przykład przedstawia metodę SpreadsheetApp.enableBigQueryExecution() wywołaną w funkcji:

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

Dodaj dodatkowe zakresy OAuth do pliku manifestu

Większość zakresów protokołu 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 do danych BigQuery przechowywanych na Dysku Google, do pliku manifestu musisz dodać zakres protokołu OAuth Dysku.

Na przykładzie poniżej pokazano część pliku manifestu dla: oauthScopes. Oprócz minimalnej wymaganej liczby zakresów OAuth spreadsheet i bigquery.readonly – dodanie zakresu OAuth na 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 przykładu poniżej dowiesz się, jak dodać źródło danych BigQuery, utworzyć ze źródła danych obiekt źródła danych, odświeżyć obiekt źródła danych i uzyskać stan wykonania. W tym przykładzie fragmenty kodu są wykonywane po kolei.

Dodawanie źródła danych 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 zostanie automatycznie odświeżony w celu pobrania danych podglądu.

Zastąp <YOUR_PROJECT_ID> poniżej 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();

Dodawanie obiektu źródła danych

Po dodaniu źródła danych do arkusza kalkulacyjnego możesz z niego tworzyć obiekty źródła danych. W tym przykładzie tabela przestawna jest tworzona za pomocą właściwości DataSourcePivotTable.

W przeciwieństwie do zwykłych danych w arkuszach siatki, do których odwołują się indeksy komórek lub notacje A1, do danych ze źródeł danych odwołują się zwykle nazwy kolumn. Z tego powodu większość metod ustawiania 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żyć obiekty źródła danych, aby pobrać 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:

  1. refreshData() rozpoczyna odświeżanie danych.
  2. waitForCompletion() zwraca stan końcowy po zakończeniu wykonywania danych. Dzięki temu nie trzeba będzie sprawdzać stanu wykonania.
  3. DataExecutionStatus.getErrorCode() otrzymuje kod błędu w przypadku niepowodzenia wykonania danych.

Przykład poniżej ilustruje odświeżenie 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());
}

Używanie reguł w połączonych arkuszach

Zautomatyzuj funkcje źródła danych w połączonych arkuszach za pomocą reguł i zdarzeń. Możesz na przykład używać reguł zależnych od czasu, aby odświeżać obiekty źródła danych wielokrotnie w określonym czasie, a aktywatory zdarzeń w arkuszu kalkulacyjnym uruchamiają wykonywanie danych w przypadku wstępnie zdefiniowanego zdarzenia.

Przykład poniżej dodaje źródło danych z parametrem zapytania i odświeża arkusz źródła danych po zmianie parametru zapytania.

Zastąp <YOUR_PROJECT_ID> poniżej 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 aktywator zdarzenia w edytorze Apps Script. Informacje o tworzeniu reguł opartych na zdarzeniach znajdziesz w sekcji Reguły, które można zainstalować.

Wybierz te opcje reguły:

  • Źródło zdarzeń: Z arkusza kalkulacyjnego
  • Typ zdarzenia: Przy edycji
  • Funkcja, która ma zostać uruchomiona: refreshOnParameterEdit

Po utworzeniu reguły arkusz źródła danych jest odświeżany automatycznie za każdym razem, gdy komórka z parametrami jest edytowana.

Rozwiązywanie problemów

Komunikat o błędzie Rozdzielczość
Użyj metody enableBigQuery(), aby włączyć wykonywanie danych w przypadku źródeł danych BIGQUERY. Ten błąd oznacza, że przed pobraniem danych BigQuery funkcja SpreadsheetApp.enableBigQueryExecution() nie jest wywoływana.
Wywołaj SpreadsheetApp.enableBigQueryExecution() w funkcjach, które korzystają z metod wykonywania zadań w BigQuery.
Na przykład refreshData() w obiektach źródła danych: Spreadsheet.insertDataSourceTable() i DataSource.updateSpec().
Te metody wymagają do działania 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 Google Workspace użytkowników, którzy mają określone subskrypcje.
Aby włączyć tę funkcję, skontaktuj się z administratorem.