BigQueryとスプレッドシートを連携する方法(無料プラグイン使用)

この記事では、Googleが提供しているBigQueryというデータベースサービスと、Googleスプレッドシートとのデータ連携の方法について説明します。今回は、BigQueryにあるデータをスプレッドシート上で使用することを目的としています。

BigQueryとスプレッドシートの接続方法(王道)

GoogleスプレッドシートからBigQueryにあるデータを直接参照するのは、実はとても簡単だったりします。Googleの接続方法を説明したサポートページが存在しますし、YouTubeでの動画チュートリアルも用意されています。

このチュートリアルだけでなく、やり方を解説したブログもたくさん存在しますし、作業自体はとても簡単そうに見えます。ただ多くの方が、以下の最初のステップで躓くことがあります。

まず最初の手順として、スプレッドシートから[データ] 次に [データコネクタ] 次に [BigQuery に接続]を選択、、、というのがあります。しかしながら、

そうなんです。実のところ、この機能自体、Google Workspace(旧G Suite)の「Enterprise Plus」以上のプランを使っている組織所属者のみが使えるようになっているのです。

プラン自体は、特定の個人のみの変更が出来ず、組織全体(多くの場合は会社全体)でバージョンアップする必要があり、人数が多い会社であれば割と大きなコスト増になります。(実際の金額は会社によって異なるので、Google担当者か購入している代理店に聞いてみてください)

経験上、メンバー一人が単一の機能を使いたいという理由で全社コストを上げる、という要望がすんなり通る組織はほとんどありません。実現するために、情シス部門を巻き込んで、経営層に上程して、、、とかやっているうちに軽く数ヶ月が過ぎていくでしょうし、そこまでの行動に移すこと自体、通常業務をやりながらでは現実的ではありません。

Google Workspaceのプラン変更が出来ない場合

Google WorkSpaceのプラン変更が出来ない場合、上記の王道以外の方法を取る必要があります。やり方として次にベーシックなのが、AppsScriptを使うやり方です。

他の方の記事ですが、やり方が順を追って説明されています。

astamuse Lab

BigQueryテーブルのレコードを直にスプレッドシートに出力する - astamuse Lab BigQueryで集計した結果を、GoogleDrive上のスプレッドシートに直にエクスポートする環境を整えたので、実装例含めて紹介します。

このやり方で実現するのも、AppsScript自体をそれなりにマスターしている必要がありますし、ちょっとした修正を行う場合、条件を変更したい場合など、直接コードを書き換える必要があります。
(そもそも、BigQueryとスプレッドシートを連携させたい、ということを思いつく時点で、AppsScript自体は難なく弄れる方も多いとは思いますが、、、)

AppsScriptもよく分からないし、分かっていても面倒くさい(実際に自分もそうでしたw)という場合に使えるもう一つの代替案が、スプレッドシートの無料アドオンを使って接続するという方法です。

アドオン OWOX BI BigQuery Reportsを使う

結論からいうと、OWOX BI BigQuery Reportsというスプレッドシートのアドオンを使います。OWOXはデータ連携のためのハブ的なサービス(データパイプ)を提供している会社です。

このアドオンを使うメリットは以下の通りです。

唯一のデメリットしては、SQLをアドオン上で編集する必要がある、という点ですが、これも先程のお話と一緒で、このニーズがある時点でSQLは書ける方がほとんど、という前提にしておきます。(なので、SQLの書き方とかはこの記事では割愛させていただきます)

アドオンの導入

アドオンの導入はスプレッドシートの画面上から行えます。上部のメニューの[アドオン] > [アドインを取得]を選択します。

次に出てくるポップアップ画面の検索フォーム内に、「OWOX BI BigQuery Reports」と入力すると、今回導入したいアドインが出てきますので、それをクリックします。

アドオンの紹介ページ内にある[インストール]ボタンをクリックすれば、インストールが開始され、ガイダンスに従って権限付与を行っていけば、導入は完了です。

設定方法

導入が成功すると、上部メニューの[アドオン]から[OWOX BI BigQuery Reports]が選択出来るようになりますので、そのサブメニューのうち[Add as new report]を選択します。

次に、ログインしているGoogleアカウントに権限が付与されているBigQueryの対象プロジェクトを選択し、新しいクエリを発行するために、下部の説明文の中の[create]をクリックします。

次に、クエリの編集画面が出てきますので、そこに抽出したい条件のクエリを入力していきます。ここで注意が必要な点としては、出力先がスプレッドシートになりますので、500万セルの上限の壁があるということです。全てのデータを出力するのは難しい場合も多いと思いますので、ここのクエリで使いたいカラムと粒度を設定し、その先の処理に必要な分のデータだけ出すようにしましょう。

あとは [Save & Run]をクリックすると、上部の「Query Title」で指定した名前のシートが作成され、そのシートにクエリの結果が返されます。

検索条件を指定出来るようにする

OWOX BIのアドインでは、クエリ内に指定されたフォーマットでコードを追加すると、UI上で動的にパラメーター指定が出来るようになります。

仕様は以下のページより確認いただけますが、ここでは実際に日付の指定が出来るようにしてみます。
https://support.owox.com/hc/en-us/articles/217491007?utm_source=drive.google.com&utm_medium=spreadsheet&utm_campaign=owox_bi_bigquery_reports&utm_content=no_parameters

クエリの編集画面を開いて、Where句内で動的に設定したいカラムの設定値にコードを書きます。

注意点としては、{}を更に「”」ダブルクオーテーションで囲む必要があるという点です。(自分はここでハマりました)

上記の状態でSaveした後、アドオンを開き直すと、、

「reportDate」という入力フォームが現れ、ここで出力する日付の指定が出来るようになりました。

この機能を使うことで、SQLを直接編集できない(したくない)非エンジニアのメンバーであっても、慣れ親しんだスプレッドシートから、自分が好きな条件でデータが出力出来るようになります。
引いては、ちょっとしたクエリ変更で、都度都度依頼されることもなくなります。

まとめ

今回のまとめです。

スプレッドシートのデータ容量上限に耐えつつ、BigQueryのデータを使って、手元で集計、見える化が出来るようになると、提出するレポートの品質が上がったり、そもそものインサイトが増えたりします。皆様のお仕事のお役に立てれば幸いです。

スプレッドシートの関数やGoogle Apps Scriptに関する記事は他にもアップしていますので、よろしければ御覧ください。