google spreadsheetの変更をhubotに通知する

スプレッドシートの値が変更されたら、その変更された内容をhubotに渡して投稿する仕組みを考えてみます。

hubot側

hubot scriptでは簡単にhttpリクエストを処理できるので、それを使って通知を受け取るようにします。

google docsやspreadsheet用のscriptはさすがになさそうだったので、以下のscriptを参考に書いてみました。

# Dependencies:
#   "url": ""
#   "querystring": ""
#
# Commands:
#   None
#
# URLS:
#   POST /hubot/google-spreadsheet?room=<room>&name=<name>&value=<value>
#
url = require 'url'
querystring = require 'querystring'

module.exports = (robot) ->
  robot.router.post "/hubot/google-spreadsheet", (req, res) ->
    query = querystring.parse (url.parse req.url).query
    res.end()

    return unless query.room

    user =
      room: query.room

    name = query.name or "未設定"
    value = query.value or "未設定"

    try
      message = "【#{name}】の値が#{value}に変更されました。"
      robot.send user, message
      console.log message
    catch error
      console.log "google spreadsheet notifier error: #{error}. Request: #{req.body}"

google spreadsheet側

まず、変更を検知したいspeadsheetを開き、メニューの「ツール > スクリプト エディタ」を選択します。

変更を処理するスクリプトgoogle apps script(GAS)というjs拡張言語で記述していきます。

以下の実装ではC列が変更されたとき、F列とC列の値をそれぞれnamevalueとしてhubotに送っています。

このスクリプトを使うときにはroomの値やhubotのホスト名を適切な値に変更してください。

function postToHubot(event) {
  if (!event) {
    return;
  }
  
  var range = event.source.getActiveRange();
  var col = range.getColumn();
  var row = range.getRow();

  // C列が変更されたとき
  if (col == 3.0) {
    try {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheets()[0];
      var room = 1234;
      var value = event.value;
      var name = sheet.getRange(row, 6.0).getValue();
      var params = '?room=' + room + '&name=' + name + '&value=' + value;

      var res = UrlFetchApp.fetch(
        'http://hubot.example.com:8080/hubot/google-spreadsheet' + params, {
        'method': 'POST'
      });
    } catch(err) {
      Logger.log(err);
    }
  }
}

後はスプレッドシートの値が変更されたときにスクリプトの関数が実行されるようにスクリプトエディタのメニューで「リソース > 現在のプロジェクトのトリガー」を選択し以下のように設定します。

これで編集するたびにこの関数が呼ばれるようになります。

ハマったところ

gasを使うのは初めてだったのですが、以下の2点でハマりました。

onEdit()のパーミッション

最初はonEdit()内に通知の処理を書いていたのですが、UrlFetchApp#fetchで例外が出て動きませんでした。

原因はonEdit()のパーミッションで、自作関数をトリガーに登録する場合はユーザ権限で実行になるため問題ないのですが、onEdit()はシステムの権限で実行されるようで例外が出ていました。

例外

そもそも最初はこの例外が出ていることに気がつかず(例外が出てもログになにも出ないため)、気づくのに時間がかかりました…。

感想とか

やっぱりhubot scriptは簡単に書けて良いですね。

gasはちょっと書くのがめんどくさいですが(coffeescriptじゃないしドキュメントもわかりにくい)、spreadsheet以外でも色々できそうなので通知書くと便利な場面は多そうだなと思いました。