Google Spreadsheet, Google Drive, Google Apps Scriptでレポーティングの簡単自動化

この記事は CyberAgent エンジニア Advent Calendar 2014 12日目の投稿です。
昨日は yoheiMune さんのフロントエンドエンジニアにもできるMongoDBを使ったログ分析でした。
明日はT_2さんです。

はじめに

Webサービスを運営する上で、サービスは現在どういう状態なのか？サービスを改修したことで何が変わったか？ を知ることは非常に重要です。弊社ではHawkeyeという内製のレポーティング・分析システムを開発し、日々のPDCAに活かしています（参考: Amebaソシャゲ分析事例のご紹介）。

見るべきKPIや行うべき分析を定型化し、それに最適なデータとUIを設計し、システム化することは重要ですが、一方でそれをするには設計・開発に時間がかかってしまいます。また、実際に運用してみないと本当に有用なレポート・分析であるかどうか判断が付きにくい場合もありますし、運用しながら実情に合わせていろいろ変えたい場合もあるかと思います。そのような場合、とりあえず手動で集計して何日か様子を見てから判断という方法も取れますが、手動は面倒です。

そこで、本記事ではそれを補間するために Google Spreadsheet, Google Drive, Google Apps Scriptを利用した簡単な自動レポーティングの作成方法を紹介します。弊社ではGoogle Appsを導入しており、全員が利用可能なので非常に手軽に利用することができます（スピード経営の足かせになるな！：ID統合にインフラ強化、サイバーエージェントが挑んだ情報システム改革 (1/2) - ITmedia エンタープライズ）。また、導入していなくても通常のGoogleのアカウントでも可能です。

やること

本記事では

Google Drive にあるデイリーで追加されるTSVファイルを読み込んで、Google Spreadsheet に貼り付け、グラフを作成（または更新）する

ことを自動的に実施するために必要な Google Apps Scriptの使い方を紹介します。

ただし、本記事では Google Drive にファイルを配置するところは扱わないので、その部分についてこちらをご覧ください。curlコマンドで簡単にできます。

琴線探査: Google DriveにCURLでアップロードするには？

完成イメージ

f:id:swarm_of_trials:20141211145509j:plain

Google Appsを使うメリット・デメリット

Google Apps上でレポーティングすることは何が嬉しいかについて考えてみます。

閲覧・編集権限に関する制御やアクセス負荷などに関することは、Google Appsが全てやってくれるので気にしなくても良い。
- 特にレポートは売上など重要な情報を扱うことが多いため、閲覧権限を簡単に制御できる点は重要です。
ブラウザで閲覧可能なので閲覧者にとってアクセスが容易。
- レポーティングシステムは閲覧者に毎日継続して見てもらい、問題点把握・施策の効果確認をしてもらうこと目的です。したがって閲覧者がアクセスしにくいのは致命的です。
使い慣れたスプレッドシート形式でレポートが提供されるので、閲覧者がレポート上のデータを使って、即座に自分で分析することができる。
- レポートの対象サービスについて最もよく理解しているのは閲覧者（≒サービスの運営者）です。レポート作成者とは異なった観点で手軽に分析できることはサービスやそのためのレポートの品質を上げることに役立つはずです。
Google Appsが上から下までひと通りの機能を提供してくれてるので自動化が簡単。
- とはいえ手間がかかったら大変なので簡単なのは大事です。Google Apps ScriptはJavaScriptとほぼ同じなので学習コストもあまり高くありません（特に私は元フロントエンドエンジニアなので非常に楽でした）。

一方で、凝った動的なUIや分析などを提供することは難しいので、そういったものが必要な場合はTableauやShinyといった専用のツールを使ったほうがいいかと思います。

やってみる

Google Apps Script の開発環境の準備

Google Spreadsheet のファイルを新規作成し、メニューから [ツール] → [スクリプトエディタ] を押下します。するとエディタが起動し、「コード.gs」というファイルにプログラムを書き込むことができるようになります。この「コード.gs」にプログラムを書いていきます。

Google Driveからファイルを読み込む

まずGoogle Apps ScriptでGoogle Driveからファイルを読み込みます。ファイルにはHadoopで集計した結果やR/Pythonで分析した結果が書き込まれていることを想定しています。

//fname: 読み込むファイル名
function loadData(fname){
  //Spreadsheetとデータの置いてあるフォルダを取得
  //FolderIDはGoogle Driveで [共有] する際に表示される [共有URL] のGetパラメータのidから知ることができます。
  //当然、Google DriveのAPIからも知ることができるので、そこも含めて自動化したい場合はそこから取得したほうがいいでしょう。
  var folder = DriveApp.getFolderById('xxxxxxxxxxxxxxxxxxxxxxx');
  
  //TSVデータファイルの取得
  var files = folder.getFilesByName(fname)

  //folder以下には同じファイルはひとつしかないようにするので
  if(!files.hasNext()) throw(fname + ': not found');

  var file = files.next();
  var fileBlob = file.getBlob();
  
  //TSVをパースして二次元配列に変換
  d = Utilities.parseCsv(fileBlob.getDataAsString(), '\t')
  
  return d;
}

Google Apps ScriptはJDBCが使える（JDBC - Google Apps Script — Google Developers）ので、データベースに接続してデータを読み込むこともできそうです（未検証）。データベースを使えば動的なスライシングなどもある程度実現できると思うので、そのうち試してみたいと思います。

シートを選択（または新規作成）する

次にデータを書き込む場所（シート）を確保しましょう。

//sname: 対象のシート名
function getSheet(sname){
  //対象となるシート名は一個しかないと仮定して、シートの一覧を取得してその中から名前が一致するものを探す
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var sheets = spreadsheet.getSheets();
  var sheet = underscoreGS._filter(sheets, function(s){
    return s.getName() == sname;
  })[0];
  
  if(sheet){
    //対象のシートが存在している時はそれを使う
    return sheet;
  }else{
    //対象のシートが存在していない時はシートを作成しカラム名を設定する
    var s = spreadsheet.insertSheet(sname);
    s.getRange(1,1, 1, 5).setValues([['dt', 'DAU', 'ARPU', 'ARPPU', '課金率']]);
    return s;
}

シートにデータを書き込む

それでは読み込んだデータをシートに書き込んでみましょう。

//sheet: 書き込み対象のシート
//data: 書き込むデータ（2次元配列）
//c, r: 書き込む場所
function putData(sheet, data){
  var range = sheet.getRange(r, c, data[0].length, data.length);
  range.setValues(data);
}

グラフを新規作成する

いよいよシートに書き込んだデータを使ってグラフを作ります。

//sheet: グラフ作成対象のシート
function createChart(sheet){
  //グラフ範囲を求める
  var dataRange = sheet.getDataRange();
  var height = dataRange.getHeight();

  //グラフ作成
  var dau = sheet.newChart()
                    .setChartType(Charts.ChartType.LINE)
                    .addRange(sheet.getRange('A1:A' + height)) //グラフに使うデータ（X軸）
                    .addRange(sheet.getRange('B1:B' + height))  //グラフに使うデータ（Y軸）
                    .setPosition(1, 7, 0, 0)                   //グラフを配置する座標
                    .setOption('title', 'DAU')
                    .setOption('hAxis.title', '日')
                    .setOption('vAxis.title', 'ユーザ数')
                    .setOption('useFirstColumnAsDomain', true)//最初のカラムをX軸として使うことを指定
                    .setOption('vAxis.viewWindow.min', 0)
                    .setOption('height', 280)
                    .setOption('width', 480)
                    .build();

  //シートに貼り付け
  sheet.insertChart(dau);
}

グラフを更新する

すでに存在するグラフの場合、元になるデータの入っているセル群の下に1行追加すると自動的にグラフも更新されます。便利。

定期実行する

ここまでで、データを読み込んで、書き込むシートを作って、データをシートに書き込んで、それを使ってグラフを生成する、というレポート作成に最低限の機能を紹介してきました。
それを定期実行します。

まず以下の様な処理を開始する関数を定義します。

function main(){
  var data = loadData('20140101.tsv');
  var sheet = getSheet('DAU_201401');
  
  putData(sheet, data);
  createChart(sheet);
}

次にその関数を駆動するトリガーを設定します。

メニューから [リソース] → [現在のプロジェクトのトリガー] を押下します。すると今のプロジェクトで作成したトリガーが表示されます。
リンク [新しいトリガーを追加]（または [トリガーが設定されていません。今すぐ追加するにはここをクリックしてください。] ）を押下するとトリガーが新規作成されるのでトリガーの設定をします。

トリガーの内容を、例えば以下のように設定すると、「main関数を毎日午前8時〜9時に実行する」というトリガーを作成することができます。
f:id:swarm_of_trials:20141211160447p:plain

※ トリガーに [スプレッドシートから] → [フォーム送信時] というものがあります。これはスプレッドシートに紐づく Google フォームのリクエストに応じで関数が駆動されるというものです。弊社の環境だとちょっと試した範囲ではできなかった（権限とかの問題？）のですが、普通のGoogleのアカウントの方だとできたので、このトリガーが可能な環境ではフォーム送信のリクエストを真似て分析バッチなどからリクエストを送ることで、よりいい感じの自動化ができるようになるかもしれません。