このページは Cloud Translation API によって翻訳されました。

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

コネクテッドシートは、Google スプレッドシート内で 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 から最新のデータを取得できます。

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

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

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

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

次の例では、クエリパラメータを含む 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 ユーザーのみが利用できます。
この機能を有効にするには、管理者にお問い合わせください。

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

コネクテッド シートを使用する コレクションでコンテンツを整理 必要に応じて、コンテンツの保存と分類を行います。

コネクテッド シートでよく使用する操作