コネクテッド シートは、スプレッドシート内で BigQuery データを直接分析できる Google スプレッドシートの機能です。スプレッドシート サービスを使ってコネクテッド シートにプログラムでアクセスできます。
コネクテッド シートの一般的な操作
DataSource
クラスとオブジェクトを使用して BigQuery に接続し、データを分析します。次の表に、最も一般的な DataSource
アクションと、Apps Script でそのアクションを作成する方法を示します。
行動 | Google Apps Script のクラス | 使用する方法 |
---|---|---|
シートを BigQuery に接続する | DataSourceSpec |
SpreadsheetApp.newDataSourceSpec() |
BigQuery データソースを選択する | DataSource |
Spreadsheet.insertDataSourceSheet().getDataSource() |
BigQuery データソース シートを追加する | DataSourceSheet |
Spreadsheet.insertDataSourceSheet() |
ピボット テーブルを追加する | DataSourcePivotTable |
Range.insertDataSourcePivotTable() |
抽出データにデータを取り込む | DataSourceTable |
Range.insertDataSourceTable() |
数式を使用 | DataSourceFormula |
Range.setFormula() |
グラフを追加 | DataSourceChart |
Sheet.insertDataSourceChart() |
必要な認可スコープを追加する
BigQuery データにアクセスするには、Google Apps Script のコードに enableBigQueryExecution()
メソッドを含める必要があります。このメソッドは、必要な bigquery.readonly
OAuth スコープを Google Apps Script プロジェクトに追加します。
次のサンプルは、関数内で呼び出される SpreadsheetApp.enableBigQueryExecution()
メソッドを示しています。
function addDataSource() { SpreadsheetApp.enableBigQueryExecution(); var spreadsheet = SpreadsheetApp.getActive(); }
マニフェスト ファイルに OAuth スコープを追加する
ほとんどの OAuth スコープは、コードで使用している関数に基づいてマニフェスト ファイルに自動的に追加されます。特定の BigQuery データにアクセスするために追加のスコープが必要な場合は、明示的なスコープを設定できます。
たとえば、Google ドライブ内でホストされている BigQuery データに対してクエリを実行するには、ドライブの OAuth スコープをマニフェスト ファイルに追加する必要があります。
次のサンプルは、マニフェスト ファイルの oauthScopes
部分を示しています。必要最小限の OAuth スコープ spreadsheet
と bigquery.readonly
に加えて、ドライブの OAuth スコープを追加します。
{ ... "oauthScopes": [ "https://www.googleapis.com/auth/bigquery.readonly", "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive" ], ... }
例: データソース オブジェクトの作成と更新
次の例では、BigQuery データソースの追加、データソースからのデータソース オブジェクトの作成、データソース オブジェクトの更新、実行ステータスの取得を行います。この例では、コード スニペットが順番に実行されます。
BigQuery データソースを追加する
BigQuery データソースをスプレッドシートに追加するには、データソースの仕様を含むデータソース シートを挿入します。データソース シートは自動的に更新され、プレビュー データが取得されます。
以下の <YOUR_PROJECT_ID>
は、有効な Google Cloud プロジェクト 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();
データソース オブジェクトを追加する
データソースがスプレッドシートに追加されると、データソースからデータソース オブジェクトを作成できるようになります。この例では、DataSourcePivotTable
を使用してピボット テーブルを作成します。
セル インデックスや A1 表記で参照されるグリッド シートの通常のデータとは異なり、データソースのデータは通常、列名で参照されます。そのため、データソース オブジェクトのプロパティ セッターのほとんどは、列名を入力として使用します。
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);
データソース オブジェクトを更新する
データソース オブジェクトを更新すると、データソースの仕様とオブジェクト構成に基づいて BigQuery から最新のデータを取得できます。
データの更新プロセスは非同期で行われます。データソース オブジェクトを更新するには、次のメソッドを使用します。
refreshData()
は、データ更新の実行を開始します。waitForCompletion()
は、データ実行が完了すると終了状態を返します。これにより、実行ステータスをポーリングし続ける必要がなくなります。DataExecutionStatus.getErrorCode()
は、データの実行が失敗した場合にエラーコードを受け取ります。
次のサンプルは、ピボット テーブルのデータを更新する方法を示しています。
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());
}
コネクテッド シートでトリガーを使用する
トリガーとイベントを使用して、コネクテッド シートのデータソース関数を自動化します。たとえば、時間ドリブン トリガーを使用してデータソース オブジェクトを特定の時間に繰り返し更新し、スプレッドシートのイベント トリガーを使用して事前定義イベントでデータ実行をトリガーします。
以下のサンプルでは、クエリ パラメータを使用してデータソースを追加し、クエリ パラメータを編集するとデータソース シートを更新します。
以下の <YOUR_PROJECT_ID>
は、有効な Google Cloud プロジェクト 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();
}
上記のサンプルでは、addDataSource()
関数がデータソースをスプレッドシートに追加します。addDataSource()
を実行したら、Apps Script エディタでイベント トリガーを作成します。イベント トリガーの作成方法については、インストール可能なトリガーをご覧ください。
トリガーに次のオプションを選択します。
- イベントソース: スプレッドシートから
- イベントタイプ: 編集時
- 実行する関数:
refreshOnParameterEdit
トリガーが作成されると、パラメータ セルが編集されるたびにデータソース シートが自動的に更新されます。
トラブルシューティング
エラー メッセージ | 解像度 |
---|---|
enableBigQuery() を使用して、BigQuery データソースのデータ実行を有効にします。 |
このエラーは、BigQuery データを取得する前に SpreadsheetApp.enableBigQueryExecution() が呼び出されないことを示します。BigQuery の実行にメソッドを使用する関数で、 SpreadsheetApp.enableBigQueryExecution() を呼び出します。データソース オブジェクトの refreshData() 、Spreadsheet.insertDataSourceTable() 、DataSource.updateSpec() などです。これらのメソッドが機能するには、追加の bigquery.readonly OAuth スコープが必要です。 |
データソースを操作することはできません。 この機能を有効にするには、管理者にお問い合わせください。 |
このエラーは、アカウントでコネクテッド シートが有効になっていないことを示します。 コネクテッド シートは、特定のサブスクリプションをご利用のお客様のみが Google Workspace ご利用いただけます。 この機能を有効にするには、管理者にお問い合わせください。 |