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

好きなモノは酒と女とラーメン、LIFULLの千葉(@rechiba3)です。
弊社にはふたりの千葉がいますが、私のことは“人妻の方の千葉”で覚えて下さい！
# はじめに
この記事は[LIFULL Advent Calender2017](https://qiita.com/advent-calendar/2017/lifull2)　その2の13日目の記事です。

今回は**BigQueryでGoogleスプレッドシートに出力してもらったデータをどう見せるか**、について書きます。
## BigQueryとは？
Google が提供するデータ解析サービスです。
初めて説明を聞いたときは、
**「データベース専用のストレージが俺のために高速で欲しいデータを抽出してくれる…、ほーーーん、いいべっちゃ」** と思っておりましたが、
[BigQueryで150万円溶かした人の顔](https://qiita.com/itkr/items/745d54c781badc148bb9)を読んで怖くなりました。

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の日毎のトリガーで時間をもっと細かく設定する](https://qiita.com/sumi-engraphia/items/465dd027e17f44da4d6a)

### 1-3. シートに出力される
指定したTableから呼び出された数値が、cellに表示されます。
今回はTable毎にシートのタブを分け、id別に入ってください、といったクエリを実行しているので下記のようになります。
![20171213_004.JPG](https://qiita-image-store.s3.amazonaws.com/0/86461/5d585787-9303-49da-1c82-a7f4ccef8ac9.jpeg)

### 2. スプレッドシートの設計＜KPIシート＞
上述したシートはあくまでデータを吐き出すところ。
チームで閲覧するシートは別で作成して使い分けます。

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

シートタブの上で右クリック**シートを保護...**を選択。
<img width="257" alt="20171213_003.png" src="https://qiita-image-store.s3.amazonaws.com/0/86461/cd3cda64-9677-da5a-45ee-6ade506cb477.png">
「保護されているシートと範囲」にて保護したいシートを選択。
プルダウンで対象のシートを選択（塗りつぶしたのは任意のシート名です）、からの**権限を設定**を選択。
<img width="225" alt="20171213_004.png" src="https://qiita-image-store.s3.amazonaws.com/0/86461/7d3be7d4-ff42-9f6c-eed1-9b8e1b9da798.png">

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

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

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

## ２−２.出力シートからKPIシートへの参照の仕方
ここまでの登場キャラクターは以下の３つ！
![20171213_001.JPG](https://qiita-image-store.s3.amazonaws.com/0/86461/010d08d3-a4ce-6901-4455-fc98a800adeb.jpeg)
1. Google BigQueryさん
2. Googleスプレッドシート＜出力用＞さん
3. Googleスプレッドシート＜KPI見る用＞さん

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

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

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

しかしこれだとエラーが出てしまいます。
<img width="455" alt="20171213_005.png" src="https://qiita-image-store.s3.amazonaws.com/0/86461/ed526c04-a5bb-cc54-86a8-bcc81e449b00.png">
**「VLOOKUPは境界外の範囲を求めています」**じゃあ、**境界内にすればいい**んです。

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

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

**出力用シートのシートID**はスプレッドシートのURLでいうところのd/以降/以前になります。
<img width="860" alt="20171213_007.png" src="https://qiita-image-store.s3.amazonaws.com/0/86461/5e2f50d2-304e-f999-054d-1afb05c05306.png">

以上を書くと、図のようにリンクを求められるので**アクセスを許可**を選択します。
<img width="308" alt="20171213_006.png" src="https://qiita-image-store.s3.amazonaws.com/0/86461/c80f6c79-f066-206d-2d0b-210ed62f136c.png">

すると、無事にインポートできました。
<img width="845" alt="20171213_008.png" src="https://qiita-image-store.s3.amazonaws.com/0/86461/6ef99e9d-f96c-abc9-70bf-b229cb0c0510.png">
再度VLOOKUPを書いたセルを編集して見ると、あれ、なんでだろ、またエラーです。
<img width="464" alt="20171213_009.png" src="https://qiita-image-store.s3.amazonaws.com/0/86461/a34500dd-7cb0-77af-7882-04dd68729e96.png">
**「VLOOKUPの評価で「20171201」が見つかりませんでした」**じゃあ、**「20171201」が評価されればいい**んです。
はて、評価とは？？
どうやら表示形式が文字列以外だとこのようなエラーが起こる、とのこと。
表示形式を**書式なしテキスト**にします。
<img width="295" alt="20171213_010.png" src="https://qiita-image-store.s3.amazonaws.com/0/86461/413f7bb1-7087-5b1e-619d-b7850cd67d12.png">
読み込めました。
<img width="991" alt="20171213_011.png" src="https://qiita-image-store.s3.amazonaws.com/0/86461/65288df5-4948-bb50-a01c-6c786943e1c0.png">

関数**IMPORTRANGE**は消しても大丈夫なので、**VLOOKUP**を列にコピーします。
<img width="1009" alt="20171213_012.png" src="https://qiita-image-store.s3.amazonaws.com/0/86461/582a6926-483c-e20b-8a3e-a8379c5ea9f6.png">

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

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

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

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