GoogleスプレッドシートでBigQueryのデータを眺める

この記事は株式会社ガラパゴス（有志）アドベントカレンダーの4日目の記事です。

皆さんはBigQueryのデータをスプレッドシートで眺めたいことは無いでしょうか？生データや分析結果のデータをエンジニア以外の人に見せたい場合、BigQueryのクエリ結果を見せるのは少し気が引けますし、共有することも難しいです。そんな時、データを楽にスプレッドシートから確認する方法があります。

1回限りで良い場合

BigQuery上でクエリを実行し、その結果をスプレッドシートに出力します。クエリ結果から「結果を保存」→「Google スプレッドシート」を選択すれば良いですね

クエリ結果をスプレッドシートに出力

継続的に確認したい場合

コネクテッドシートの機能を利用します。
https://cloud.google.com/bigquery/docs/connected-sheets?hl=ja

なお使用しているGoogleアカウントに、コネクテッドシートを利用したいスプレッドシートの編集権限とBigQueryのプロジェクトの権限がともにあることが条件です。

コネクテッドシートの作成手順

やり方１：BigQueryのコンソールから作成する方法

BigQueryのエクスプローラから出力したいテーブル名の右隣の3点縦リーダを選択し、「開始」→「コネクテッドシート」を選択すると新規のスプレッドシートとしてコネクテッドシートが作成されます。

コネクテッドシートの作成方法（１）

あるいは、テーブルをタブで開いた後に「エクスポート」→「シートを使って調べる」と選択しても同様に新規のスプレッドシートとしてコネクテッドシートが作成されます。

コネクテッドシートの作成方法（２）

やり方２：既存のスプレッドシートから作成する方法

スプレッドシートの「データ」→「データコネクタ」→「BigQuery に接続」を選択します。

スプレッドシートからコネクテッドシート挿入

「データ接続の追加」という画面が表示されるので、GCPの課金先プロジェクト、データセット、テーブル名を順番に選択して「接続」ボタンを押下します。すると開いているスプレッドシートの別シートにコネクテッドシートが作成されます。

プレビューからできること

コネクテッドシートが作成されるとBigQueryのデータがスプレッドシートに表示されます（この時作成されるシートがプレビューです）。データが見られるだけでも十分嬉しいのですが、以下のような機能もあります。

プレビュー

列の統計情報

選択した1つのカラムについて、簡単な集計やグラフの描画をしてくれます。集計した結果は別シートに挿入することもできます。

列の統計情報の表示例

計算された列の追加

スプレッドシートの関数を用いてプレビューに列を追加します。プレビュー内のセルはカラム名で指定します。

抽出

BigQueryから抽出した結果を別シートに吐き出します。自動で作成されたプレビューでは見た目を変えたりメモを追加したりすることができないため、別シートに抽出してから加工します。

関数

集計結果のスカラー値を別シートに出力します。

ピボットテーブル

集計テーブルを作成します。テーブルの行と列、値とフィルタを設定でき、表示のソートも可能です。

グラフ

集計結果を表ではなくグラフ形式で可視化します。折れ線グラフや円グラフ、散布図など基本的なグラフはほぼ揃っています。

データの更新

BigQueryからデータを更新する際はプレビューや上記で作成した表・グラフ等の左下にある緑色の↻ボタンを押します。なおプレビューと上記で作成した表やグラフのデータ更新はそれぞれ別々に行われるので、仮に一部分だけ更新するとデータが不整合しているように見えます。

カスタムクエリ

単純にBigQueryの1テーブルのデータと連携するのではなく、複数のテーブルを結合したりデータを加工してから参照したりする場合にはカスタムクエリにSQLを入力します。カスタムクエリはスプレッドシートからコネクテッドシートを作成する手順で入力します。

カスタムクエリの入力画面

カスタムクエリでは、既にスプレッドシートにある値をパラメータとして利用できます。例えば抽出条件を値から取得したい場合にフィルターをかけずに確認したり、値で絞り込みを行った結果をさらに集計したりする場合に使用します。

データの自動更新

BigQueryからのデータ更新を自動で実施することもできます。更新頻度は更新オプションから設定でき、日次や月次等で自動的にデータを更新します。

更新オプション

ちなみにBigQueryではデータソースとしてスプレッドシートを指定することもできるので、スプレッドシートのデータをBigQueryに取り込んでコネクテッドシートの機能で分析する、なんてことも可能です。ただしスプレッドシートをデータソースにした場合コネクテッドシートのデータ更新にかなり時間がかかるのでご注意を。

またあまりにも大きいサイズのデータが入っているとスプレッドシートの動きが遅くなりますが、それはコネクテッドシートでも同様です。