コネクテッド シートを使用すると、Google スプレッドシート内でペタバイト単位のデータを直接分析できます。スプレッドシートを BigQuery データ ウェアハウスまたは Looker に接続し、ピボット テーブル、グラフ、数式などの使い慣れたスプレッドシートのツールを使用して分析を行うことができます。
BigQuery データソースを管理する
このセクションでは、BigQuery の一般公開データセット Shakespeare を使用して、コネクテッド シートの使用方法を説明します。このデータセットには、次の情報が含まれています。
| フィールド | タイプ | 説明 |
|---|---|---|
| word | STRING |
コーパスから抽出された 1 つの一意の単語（空白が区切り文字）。 |
| word_count | INTEGER |
この単語がこのコーパスに出現する回数。 |
| corpus | STRING |
この単語が抽出された作品。 |
| corpus_date | INTEGER |
このコーパスが公開された年。 |
アプリケーションが BigQuery コネクテッド シートのデータをリクエストする場合は、通常の Google Sheets API リクエストに必要な他のスコープに加えて、bigquery.readonly スコープを付与する OAuth 2.0 トークンを提供する必要があります。詳細については、Google Sheets API スコープを選択するをご覧ください。
データソースは、データが見つかる外部の場所を指定します。データソースがスプレッドシートに接続されます。
BigQuery データソースを追加する
データソースを追加するには、spreadsheets.batchUpdate メソッドを使用して AddDataSourceRequest を指定します。リクエストの本文では、DataSource オブジェクト型の dataSource フィールドを指定する必要があります。
"addDataSource":{
"dataSource":{
"spec":{
"bigQuery":{
"projectId":"PROJECT_ID",
"tableSpec":{
"tableProjectId":"bigquery-public-data",
"datasetId":"samples",
"tableId":"shakespeare"
}
}
}
}
}
PROJECT_ID は、有効な Google Cloud プロジェクト ID に置き換えます。
データソースを作成すると、関連する DATA_SOURCE シートが作成され、最大 500 行のプレビューが表示されます。プレビューはすぐに利用できるわけではありません。実行が非同期でトリガーされ、BigQuery データがインポートされます。
AddDataSourceResponse には次のフィールドが含まれています。
dataSource: 作成されたDataSourceオブジェクト。dataSourceIdは、スプレッドシート スコープの一意の ID です。データソースから各DataSourceオブジェクトを作成するために、このオブジェクトが入力され、参照されます。dataExecutionStatus: BigQuery データをプレビュー シートにインポートする実行のステータス。詳細については、データ実行ステータスのセクションをご覧ください。
BigQuery データソースを更新または削除する
spreadsheets.batchUpdate メソッドを使用して、UpdateDataSourceRequest リクエストまたは DeleteDataSourceRequest リクエストを適宜指定します。
BigQuery データソース オブジェクトを管理する
データソースがスプレッドシートに追加されると、そこからデータソース オブジェクトを作成できます。データソース オブジェクトは、コネクテッド シートと統合されてデータ分析を強化する、ピボット テーブル、グラフ、数式などの通常のスプレッドシート ツールです。
オブジェクトには次の 4 種類があります。
DataSource個のテーブルDataSourcepivotTableDataSourceのグラフDataSourceの数式
BigQuery データソース テーブルを追加する
スプレッドシート エディタでは「抽出」と呼ばれるテーブル オブジェクトは、データソースからスプレッドシートにデータの静的ダンプをインポートします。ピボット テーブルと同様に、テーブルは指定され、左上のセルに固定されます。
次のコードサンプルは、spreadsheets.batchUpdate メソッドと UpdateCellsRequest を使用して、2 つの列（word と word_count）の最大 1,000 行のデータソース テーブルを作成する方法を示しています。
"updateCells":{
"rows":{
"values":[
{
"dataSourceTable":{
"dataSourceId":"DATA_SOURCE_ID",
"columns":[
{
"name":"word"
},
{
"name":"word_count"
}
],
"rowLimit":{
"value":1000
},
"columnSelectionType":"SELECTED"
}
}
]
},
"fields":"dataSourceTable"
}
DATA_SOURCE_ID は、データソースを識別するスプレッドシート スコープの一意の ID に置き換えます。
データソース テーブルの作成後、データはすぐに利用可能になりません。スプレッドシート エディタでは、プレビューとして表示されます。BigQuery データを取得するには、データソース テーブルを更新する必要があります。同じ batchUpdate 内で RefreshDataSourceRequest を指定できます。すべてのデータソース オブジェクトは同様に機能します。詳細については、データソース オブジェクトを更新するをご覧ください。
更新が完了して BigQuery データが取得されると、データソース テーブルは次のように入力されます。
BigQuery データソースのピボット テーブルを追加する
従来のピボット テーブルとは異なり、データソース ピボット テーブルはデータソースによってバックアップされ、列名でデータを参照します。次のコードサンプルは、spreadsheets.batchUpdate メソッドと UpdateCellsRequest を使用して、コーパス別の単語数の合計を示すピボット テーブルを作成する方法を示しています。
"updateCells":{
"rows":{
"values":[
{
"pivotTable":{
"dataSourceId":"DATA_SOURCE_ID",
"rows":{
"dataSourceColumnReference":{
"name":"corpus"
},
"sortOrder":"ASCENDING"
},
"values":{
"summarizeFunction":"SUM",
"dataSourceColumnReference":{
"name":"word_count"
}
}
}
}
]
},
"fields":"pivotTable"
}
DATA_SOURCE_ID は、データソースを識別するスプレッドシート スコープの一意の ID に置き換えます。
BigQuery データを取得すると、データソースのピボット テーブルは次のように入力されます。
BigQuery データソースのグラフを追加する
次のコードサンプルは、spreadsheets.batchUpdate メソッドと AddChartRequest を使用して、chartType が COLUMN のデータソース チャートを作成し、コーパス別の単語の合計数を示す方法を示しています。
"addChart":{
"chart":{
"spec":{
"title":"Corpus by word count",
"basicChart":{
"chartType":"COLUMN",
"domains":[
{
"domain":{
"columnReference":{
"name":"corpus"
}
}
}
],
"series":[
{
"series":{
"columnReference":{
"name":"word_count"
},
"aggregateType":"SUM"
}
}
]
}
},
"dataSourceChartProperties":{
"dataSourceId":"DATA_SOURCE_ID"
}
}
}
DATA_SOURCE_ID は、データソースを識別するスプレッドシート スコープの一意の ID に置き換えます。
BigQuery データの取得後、データソースのグラフは次のようにレンダリングされます。
BigQuery データソースの数式を追加する
次のコードサンプルは、spreadsheets.batchUpdate メソッドと UpdateCellsRequest を使用して、単語数の平均を計算するデータソースの数式を作成する方法を示しています。
"updateCells":{
"rows":[
{
"values":[
{
"userEnteredValue":{
"formulaValue":"=AVERAGE(shakespeare!word_count)"
}
}
]
}
],
"fields":"userEnteredValue"
}
BigQuery データを取得すると、データソースの数式が次のように入力されます。
BigQuery データソース オブジェクトを更新する
データソース オブジェクトを更新して、現在のデータソース仕様とオブジェクト構成に基づいて BigQuery から最新のデータを取得できます。spreadsheets.batchUpdate メソッドを使用して RefreshDataSourceRequest を呼び出すことができます。次に、DataSourceObjectReferences オブジェクトを使用して、更新するオブジェクト参照を 1 つ以上指定します。
1 つの batchUpdate リクエスト内でデータソース オブジェクトの作成と更新の両方を行うことができます。
Looker データソースを管理する
このガイドでは、Looker データソースを追加、更新、削除する方法、Looker データソースでピボット テーブルを作成して更新する方法について説明します。
Looker コネクテッド シートのデータをリクエストするアプリケーションは、Looker との既存の Google アカウント リンクを再利用します。
Looker データソースを追加する
データソースを追加するには、spreadsheets.batchUpdate メソッドを使用して AddDataSourceRequest を指定します。リクエストの本文では、DataSource オブジェクト型の dataSource フィールドを指定する必要があります。
"addDataSource":{
"dataSource":{
"spec":{
"looker":{
"instance_uri":"INSTANCE_URI",
"model":"MODEL",
"explore":"EXPLORE"
}
}
}
}
INSTANCE_URI、MODEL、EXPLORE は、それぞれ有効な Looker インスタンス URI、モデル名、エクスプローラの名前に置き換えます。
データソースを作成すると、関連付けられた DATA_SOURCE シートが作成され、選択した Explore の構造（ビュー、ディメンション、メジャー、フィールドの説明など）のプレビューが表示されます。
AddDataSourceResponse には次のフィールドが含まれています。
dataSource: 作成されたDataSourceオブジェクト。dataSourceIdは、スプレッドシート スコープの一意の ID です。データソースから各DataSourceオブジェクトを作成するために、このオブジェクトにデータが入力され、参照されます。dataExecutionStatus: BigQuery データをプレビュー シートにインポートする実行のステータス。詳細については、データ実行ステータスのセクションをご覧ください。
Looker データソースを更新または削除する
spreadsheets.batchUpdate メソッドを使用して、UpdateDataSourceRequest リクエストまたは DeleteDataSourceRequest リクエストを適宜指定します。
Looker データソース オブジェクトを管理する
データソースがスプレッドシートに追加されると、そこからデータソース オブジェクトを作成できます。Looker データソースの場合、そこから作成できるのは DataSource pivotTable オブジェクトのみです。
Looker データソースから DataSource の数式、抽出、グラフを作成することはできません。
Looker データソース オブジェクトを更新する
データソース オブジェクトを更新して、現在のデータソースの仕様とオブジェクト構成に基づいて Looker から最新のデータを取得できます。spreadsheets.batchUpdate メソッドを使用して RefreshDataSourceRequest を呼び出すことができます。次に、DataSourceObjectReferences オブジェクトを使用して、更新するオブジェクト参照を 1 つ以上指定します。
1 つの batchUpdate リクエスト内でデータソース オブジェクトの作成と更新の両方を行うことができます。
データ実行ステータス
データソースを作成するか、データソース オブジェクトを更新すると、BigQuery または Looker からデータを取得し、DataExecutionStatus を含むレスポンスを返すバックグラウンド実行が作成されます。実行が正常に開始されると、通常、DataExecutionState は RUNNING 状態になります。
このプロセスは非同期であるため、アプリケーションはポーリング モデルを実装して、データソース オブジェクトのステータスを定期的に取得する必要があります。ステータスが SUCCEEDED または FAILED の状態を返すまで、spreadsheets.get メソッドを使用します。ほとんどの場合、実行はすぐに完了しますが、データソースの複雑さによって異なります。通常、実行時間は 10 分を超えません。
関連トピック
- Google Sheets API のスコープを選択する
- Google スプレッドシートで BigQuery データを使ってみる
- BigQuery ドキュメント
- BigQuery: コネクテッド シートの使用
- コネクテッド シートの動画チュートリアル
- Looker 向けコネクテッド シートの使用
- Looker の概要