saga

csvをGoogle スプレッドシートに定期的に反映させる

サーバーの負荷状況を調査するため、cronでApache Benchを実行し定期的にcsvを出力していました。
このcsvが勝手にスプレッドシートに取り込まれてくれればなぁ、と思い Google Apps Script(以下GAS)を使って自動化してみました。

csvをGoogle ドライブへ直接出力→GASでcsvファイルの内容取得してする
という流れをcronとGASのトリガーを使って定期的に実行する流れをご紹介いたします。

■もくじ

  1. csvをGoogle ドライブのディレクトリに出力する
  2. Google スプレッドシートを作成
  3. スクリプト エディタを起動
  4. コードを書く
  5. トリガー設定

csvをGoogle ドライブのディレクトリに出力する

15分ごとにApache Benchを実行→ Googleドライブにcsv出力。
毎回同じファイル名で出力し、過去のファイルは上書きします。
cron設定はこんな感じにしました。

0,15,30,45 * * * * cd /Users/namae/Google\ ドライブ/abdist; /usr/sbin/ab -n 10 -c 10 -A digest:ninsyo -e ab.csv http://example.com/

出力されるcsvはこんな感じです。2列目のみが必要なデータです。

Percentage served,Time in ms
0,60.640
1,60.640
2,60.640
3,61.978
4,61.978
5,61.978

Google スプレッドシートを作成

新規作成し、見出し列と平均値が表示される列を用意してみました。
C列以降にcsvのデータが反映されていく予定です。

スクリプト エディタを起動

メニューの「ツール」-「スクリプト エディタ」をクリック。
「無題のプロジェクト」と書いてあるエディタ画面が起動してくれます。

コードを書く

  1. Googleドライブからcsv取得して配列化
  2. まだ空っぽの列にcsvの2列目だけ書き込む
  3. 見出し部分は実行日時で上書きしておく
  4. エラーが発生したらログをメールで投げる
var ss = SpreadsheetApp.openById("シートIDをここに書く");
var sheets = ss.getSheets();
var targetSheet = ss.setActiveSheet(sheets[0]);
var date = new Date();

function import() {
  //取得ファイルとディレクトリ
  var dirName = "abdist";
  var filename = "ab.csv";

  try {
    //csvファイルを取得+配列化
    var dir = DriveApp.getFoldersByName(dirName);  
    var file = DriveApp.getFilesByName(filename).next();
    var data = file.getBlob().getDataAsString();
    var csv = Utilities.parseCsv(data);

    //まだ書き込まれていない列を探す
    var col = targetSheet.getLastColumn()+1;

    //ループさせて2列目だけ書き込む
    for (var i = 0; i < csv.length; i++) {
      targetSheet.getRange(i+2,col).setValue(csv[i][1]);
    }

    //いつの実行分なのか見出しに書いとく
    targetSheet.getRange(2,col).setValue(date);

    } catch(e) {
    //失敗したらログをメールで送りつける
      MailApp.sendEmail('xxx@example.com', 'csvインポート失敗しました。',e.message);
    } 
}

書けたら三角のボタンで実行してみて問題ないか確認。

実行中、「承認が必要です」と確認画面が表示されるので許可しましょう。

トリガー設定

時計のマークからトリガーを設定します。
今のところ、何時何分ぴったりに繰り返し実行ということはできないようです。
(1回だけの実行であれば、YYYY-MM-DD HH:MM 形式で指定できるようです。)

15分ごとに実行する設定を保存。
cronで15分ごとに同じ名前でファイルが出力されるので上書きされる前に1回実行される、という目論見です。

しばし待つ。


自動的に反映できました!トリガー設定前にテスト実行した列と合わせて2列増えています。

終わりに

特定の時間ぴったりに繰り返し実行させたい場合はGASをAPI化してcronで実行する方がいいのかな?と思いました。
GASをAPI化して外部から実行させるには認証まわりを設定する必要がありそうなので、機会があれば試してご紹介します!

参考

GAS公式リファレンス
https://developers.google.com/apps-script/reference/spreadsheet/

お問い合わせはこちら