好きなモノは酒と女とラーメン、LIFULLの千葉(@rechiba3)です。
弊社にはふたりの千葉がいますが、私のことは“人妻の方の千葉”で覚えて下さい！

はじめに

この記事はLIFULL Advent Calender2017　その2の13日目の記事です。

今回はBigQueryでGoogleスプレッドシートに出力してもらったデータをどう見せるか、について書きます。

BigQueryとは？

Google が提供するデータ解析サービスです。
初めて説明を聞いたときは、
「データベース専用のストレージが俺のために高速で欲しいデータを抽出してくれる…、ほーーーん、いいべっちゃ」 と思っておりましたが、
BigQueryで150万円溶かした人の顔を読んで怖くなりました。

BigQueryについては14日担当の@poyonex が書いてくれるので割愛。
今回は出力後のデータをどうする、のお話をします。

スプレッドシートを整える

1. スプレッドシートの設計＜出力シート＞

1-1. 指定されたスプレッドシートにデータを挿入する

BigQueryで出力してもらったデータを挿入する列を決めます。
今回は、日々の数値をウォッチしたいので、日付とイコールになっていなければなりません。

シート内のタイムスタンプを検索して、その列に挿入するというGASを書きます。

function find_row_date(sheet, dateTime) {
  var arr = sheet.getRange(1, 1, sheet.getLastRow(), 1).getValues();
  for (var i=1;i<arr.length;i++) {
    if(arr[i] == dateTime){
      return i+1;
    }
  }
  return i+1;
}

次に、Tableの存在を確認してクエリを実行するのですが、今回はコードは見せられないものなのではぶきます。ごめんね

1-2. スクリプトの実行タイマーセット

毎朝出社したら最新の数値が見たいですよね。
自動で指定した数値がスプレッドシートへ反映されるようにタイマーをセットします。

■参考

Google Apps Scriptの日毎のトリガーで時間をもっと細かく設定する

1-3. シートに出力される

指定したTableから呼び出された数値が、cellに表示されます。
今回はTable毎にシートのタブを分け、id別に入ってください、といったクエリを実行しているので下記のようになります。

2. スプレッドシートの設計＜KPIシート＞

上述したシートはあくまでデータを吐き出すところ。
チームで閲覧するシートは別で作成して使い分けます。

2-1. シートの編集権限をつける

自動更新できるシートなので、主要なメンバー以外に編集権限は必要ありません。

シートタブの上で右クリックシートを保護...を選択。

「保護されているシートと範囲」にて保護したいシートを選択。
プルダウンで対象のシートを選択（塗りつぶしたのは任意のシート名です）、からの権限を設定を選択。

これで触ることのできるメンバーが制限できました。

シートを分けること
シートごとに編集/閲覧権限を分けること

をすることで、管理メンバーが明らかとなり野良スプレッドシートを生まないことにも繋がります。
データを表すセンシティブなスプレッドシートはこまめに制限をかけましょう。

２−２.出力シートからKPIシートへの参照の仕方

ここまでの登場キャラクターは以下の３つ！

1. Google BigQueryさん
2. Googleスプレッドシート＜出力用＞さん
3. Googleスプレッドシート＜KPI見る用＞さん

BigQueryからの出力は＜出力用＞さんがやってくれたので、チームで閲覧する参照用として＜KPI見る用＞さんを作ります。

関数を駆使して＜出力用＞さんから＜KPI見る用＞さんへ出力しましょう。
VLOOKUPを用いて式を書きます。

=VLOOKUP(【参照するタイムスタンプのセル】,IMPORTRANGE("【出力元となるシートのID】", "【出力元となるセル番号】"),2,FALSE)

しかしこれだとエラーが出てしまいます。

「VLOOKUPは境界外の範囲を求めています」じゃあ、境界内にすればいいんです。

IMPORTRANGEで＜出力用シート＞を呼び出します。

=VLOOKUP($A2,IMPORTRANGE("【出力用シートのシートID】", "【シートタブ名】！【範囲の始点】:【範囲の終点】"), 2,FALSE)

出力用シートのシートIDはスプレッドシートのURLでいうところのd/以降/以前になります。

以上を書くと、図のようにリンクを求められるのでアクセスを許可を選択します。

すると、無事にインポートできました。

再度VLOOKUPを書いたセルを編集して見ると、あれ、なんでだろ、またエラーです。

「VLOOKUPの評価で「20171201」が見つかりませんでした」じゃあ、「20171201」が評価されればいいんです。
はて、評価とは？？
どうやら表示形式が文字列以外だとこのようなエラーが起こる、とのこと。
表示形式を書式なしテキストにします。

読み込めました。

関数IMPORTRANGEは消しても大丈夫なので、VLOOKUPを列にコピーします。

以上で完了です。
これでBigQueryを活用して出力した粒度の細かいデータが集まるシートが出来上がりました。
好きに整形して、いくつものミクロのデータをつなぎ合わせてマクロな分析につなぎ合わせて見ることもできます。
KPIに合わせて、好みにゴニョゴニョできますね。ステキステキ〜〜！

運用シートの保守運用は属人化しがち

「俺の俺による俺のための業務効率化」が世の中横行しすぎなため、野良スプレッドシートや捨てスプレッドシートが生まれるんです。
ヨシヨシ、うちで飼ってあげようね…

個人の能力の差によって属人化する作業なのであれば、参照すれば誰にでも状況がすぐにわかる手段で作られるべきなんです。
マニュアルに残すことはもちろん、担当となったメンバーは絶対的な情熱を持ってそれを運用させることが大事だと思っています。
もちろん、試運転フェーズを入れることも大切です。
最中のトラブルシューティングはすべて記録しておき、アップデートを繰り返せるように活かしましょう。
いつも開きたくなる、触りたくなるようなアウトプットであることが必要です。

共感できる思想を持ったチームと出逢えてまじ感謝。

Googleスプレッドシート×GAS×BigQueryでKPIを可視化する