コネクテッド シートを使用する

コネクテッド シートは、BigQuery と Looker のデータをスプレッドシート内で直接分析できる Google スプレッドシートの機能です。スプレッドシート サービスを使用して、プログラムでコネクテッド シートにアクセスできます。

コネクテッド シートの一般的な操作

DataSource クラスとオブジェクトを使用して BigQuery または Looker に接続し、データを分析します。次の表に、最も一般的な DataSource アクションと、Apps Script で作成する方法を示します。

アクション	Google Apps Script クラス	使用する方法
サポートされているデータソースにシートを接続する	DataSourceSpec	SpreadsheetApp.newDataSourceSpec()
データソースを選択	DataSource	Spreadsheet.insertDataSourceSheet().getDataSource()
データソース シートを追加	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();
  }

Looker データにアクセスするには、Google Apps Script コードに enableLookerExecution() メソッドを含める必要があります。Apps Script で Looker にアクセスすると、Looker との既存の Google アカウントのリンクが再利用されます。

次のサンプルは、関数内で呼び出される SpreadsheetApp.enableLookerExecution() メソッドを示しています。

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

マニフェスト ファイルに OAuth スコープを追加する

BigQuery に接続すると、コードで使用されている関数に基づいて、ほとんどの OAuth スコープがマニフェスト ファイルに自動的に追加されます。特定の BigQuery データにアクセスするために追加のスコープを必要とする場合は、明示的なスコープを設定できます。

たとえば、Google ドライブ内でホストされている BigQuery データをクエリするには、マニフェスト ファイルにドライブの OAuth スコープを追加する必要があります。

次のサンプルは、マニフェスト ファイルの oauthScopes 部分を示しています。必要な最小限の spreadsheet と bigquery.readonly OAuth スコープに加えて、ドライブ OAuth スコープが追加されます。

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

例: データソース オブジェクトを作成して更新する

次の例は、データソースを追加し、データソースからデータソース オブジェクトを作成し、データソース オブジェクトを更新して、実行ステータスを取得する方法を示しています。

データソースを追加する

次の例は、BigQuery データソースと Looker データソースをそれぞれ追加する方法を示しています。

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

Looker

Looker データソースをスプレッドシートに追加するには、データソース仕様を含むデータソースシートを挿入します。データソースシートは自動的に更新され、プレビュー データが取得されます。

次のサンプルの <INSTANCE_URL>、<MODEL_NAME>、<EXPLORE_NAME> は、それぞれ有効な Looker インスタンス URL、モデル名、データ探索名に置き換えてください。

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

データソース オブジェクトを追加する

データソースがスプレッドシートに追加されると、データソースからデータソース オブジェクトを作成できます。この例では、BigQuery データソースを追加するコードサンプルで作成された BigQuery dataSource で 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 から最新のデータを取得できます。

データを更新するプロセスは非同期です。データソース オブジェクトを更新するには、次の方法を使用します。

1. refreshData() はデータ更新の実行を開始します。
2. waitForCompletion() は、データ実行が完了すると終了状態を返します。これにより、実行ステータスをポーリングし続ける必要がなくなります。
3. 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());
}

コネクテッド シートでトリガーを使用する

トリガーとイベントを使用して、接続されたスプレッドシートのデータソース関数を自動化します。たとえば、時間ドリブン トリガーを使用してデータソース オブジェクトを特定の時刻に繰り返し更新し、スプレッドシートのイベント トリガーを使用して、事前定義されたイベントでデータ実行をトリガーします。

次のサンプルでは、クエリ パラメータを使用して BigQuery データソースを追加し、クエリ パラメータが編集されたときにデータソースシートを更新します。

以下の <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

トリガーを作成すると、パラメータ セルが編集されるたびにデータソース シートが自動的に更新されます。

トラブルシューティング

エラー メッセージ	解決策
データソース「BIGQUERY」でデータの実行を有効にするには、enableBigQuery() を使用します。	このエラーは、BigQuery データを取得する前に SpreadsheetApp.enableBigQueryExecution() が呼び出されていないことを示します。 BigQuery の実行にメソッドを使用する関数で SpreadsheetApp.enableBigQueryExecution() を呼び出します。 データソース オブジェクトの refreshData()、Spreadsheet.insertDataSourceTable()、DataSource.updateSpec() など。 これらのメソッドを機能させるには、追加の bigquery.readonly OAuth スコープが必要です。
データソースでの操作は許可されていません。 この機能を有効にするには、管理者にお問い合わせください。	このエラーは、アカウントでコネクテッド シートが有効になっていないことを示します。 コネクテッド シートは、特定のサブスクリプションをお持ちの Google Workspace ユーザーのみが利用できます。 この機能を有効にするには、管理者にお問い合わせください。