コネクテッド シートを使用すると、ペタバイト規模のデータを Google スプレッドシート内で直接分析できます。スプレッドシートを BigQuery データ ウェアハウスに接続し、ピボット テーブル、グラフ、数式などの使い慣れたスプレッドシート ツールを使用して分析を行うことができます。
このガイドでは、一般公開データセット Shakespeare
を使用して、コネクテッド シートの使用方法を説明します。このデータセットには次の情報が含まれています。
項目 | タイプ | 説明 |
---|---|---|
word | STRING |
コーパスから抽出された単一の一意の単語(空白は区切り文字)。 |
word_count | INTEGER |
コーパス内でのこの単語の出現回数。 |
corpus | STRING |
この単語が抽出された作品。 |
corpus_date | INTEGER |
このコーパスが公開された年。 |
アプリケーションがコネクテッド シートのデータをリクエストする場合は、通常の 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 データをプレビュー シートにインポートする実行のステータス。詳細については、データ実行ステータスをご覧ください。
データソースを更新または削除する
spreadsheets.batchUpdate
メソッドを使用して、UpdateDataSourceRequest
または DeleteDataSourceRequest
リクエストを指定します。
データソース オブジェクトを管理する
スプレッドシートにデータソースを追加すると、そこからデータソース オブジェクトを作成できます。データソース オブジェクトは、データ分析を強化するためにコネクテッド シートと統合される、ピボット テーブル、グラフ、数式などの通常のスプレッドシートツールです。
オブジェクトには次の 4 種類があります。
DataSource
個のテーブルDataSource
pivotTableDataSource
のグラフDataSource
個の数式
データソースのテーブルを追加する
テーブル オブジェクトは、スプレッドシート エディタでは「抽出」と呼ばれ、データソースからスプレッドシートにデータの静的ダンプをインポートします。ピボット テーブルと同様に、テーブルは左上のセルにアンカーされて指定されます。
次のコードサンプルは、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 データが取得されると、次のようにデータソース テーブルにデータが入力されます。
データソースの PivotTable を追加する
従来のピボット テーブルとは異なり、データソースのピボット テーブルはデータソースに基づいており、列名でデータを参照します。次のコードサンプルは、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 データが取得されると、次のようにデータソースのピボット テーブルにデータが取り込まれます。
データソースのグラフを追加
次のコードサンプルは、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 データが取得されると、データソースのグラフが次のように表示されます。
データソースの数式を追加
次のコードサンプルは、spreadsheets.batchUpdate
メソッドと UpdateCellsRequest
を使用して、平均単語数を計算するデータソース式を作成する方法を示しています。
"updateCells":{
"rows":[
{
"values":[
{
"userEnteredValue":{
"formulaValue":"=AVERAGE(shakespeare!word_count)"
}
}
]
}
],
"fields":"userEnteredValue"
}
BigQuery データが取得されると、次のようにデータソースの数式にデータが入力されます。
データソース オブジェクトを更新する
データソース オブジェクトを更新すると、現在のデータソース仕様とオブジェクト構成に基づいて BigQuery から最新のデータを取得できます。spreadsheets.batchUpdate
メソッドを使用して、RefreshDataSourceRequest
を呼び出すことができます。次に、DataSourceObjectReferences
オブジェクトを使用して、更新するオブジェクト参照を 1 つ以上指定します。
1 回の batchUpdate
リクエスト内で、データソース オブジェクトの作成と更新の両方を行うことができます。
データ実行ステータス
データソースの作成時またはデータソース オブジェクトの更新時に、バックグラウンド実行が作成され、BigQuery からデータが取得され、DataExecutionStatus
を含むレスポンスが返されます。実行が正常に開始すると、DataExecutionState
は通常 RUNNING
状態になります。
このプロセスは非同期であるため、アプリにポーリング モデルを実装して、データソース オブジェクトのステータスを定期的に取得する必要があります。ステータスが SUCCEEDED
または FAILED
を返すまで spreadsheets.get
メソッドを使用します。ほとんどの場合、実行は迅速に完了しますが、データソースの複雑さによって異なります。通常、実行時間は 10 分を超えません。
関連トピック
- Google Sheets API のスコープを選択する
- Google スプレッドシートで BigQuery データを使ってみる
- BigQuery ドキュメント
- BigQuery: コネクテッド シートの使用
- コネクテッド シートの動画チュートリアル