Googleのスプレットシートで変更した内容をGoogle Apps Script経由でチャットワークに通知するライフハック的なものを作った話。

Googleのスプレットシートを使ってタスク管理する場面がちょくちょくあります。
タスクを割り振る人がどんどんスプレットシートにタスクを追加していくのですが、そのタスク追加されたものをチャットワーク側に通知出来ないかなぁと思ったので作ってみました。

##先に作ったものの紹介

例えば、こんな感じで

新しいタスクが追加されると↓

こんな感じでチャットワークに通知がきます。

##変更検知のonEdit()
GASではスクリプト実行のトリガーを設定することができます。
デフォルトでは一定時間毎に実行させることなどが可能ですが、今回はスプレットシートの変更を検知するonEdit()という関数が用意されていたので、これを使って変更検知してみます。

e.rangeで変更したセルの内容を取得したりできます。

参考: Understanding Events - Google Apps Script — Google Developers -

###onChange() ?
changeっていうイベントがあるみたいだけどこれの使い方がイマイチ分かりませんでした汗
参考: Google Apps Event changeType undefined in Google Sheet - Stack Overflow -

###onEdit()でのHTTP POSTは出来ないらしい
実装上遠回りになってしまった仕様(?)。

変更内容をもとに変更が行われたタイミングで(条件式とかもいれつつ)http postしてchatworkに通知しようとしましたが、onEdit()ではHTTP POSTは出来ないみたいでした。　
参考: google apps script - URLfetch in an event failing - Stack Overflow -

##Google Apps Scriptで使えるデータベース Properties Service

###onEdit()で変更内容をDB保存しておき、時間をトリガーにしてHTTP POSTすることに

onEdit()のタイミングでは変更内容をそのままHTTP POSTできないみたいだったので変更内容をDBに保存しておき、指定時間ごとにDBの値をポストする形で実装しました。

ScriptDBはもうすぐ使えなくなるらしい。

少し前に書かれたブログ記事などを見てると、Google Apps Script内で利用出来るDBとしてはScriptDBというものが主流だったみたいですが、公式ドキュメントを見ると2014年11月で使えなくなるみたいです。

参考: ScriptDB: Saving and Loading Objects - Google Apps Script — Google Developers -

参考: Class PropertiesService - Google Apps Script — Google Developers -
参考: Class Properties - Google Apps Script — Google Developers -

Properties Service

基本的にKey Value Storeな作りなのでLocalStrageなどと同じイメージで使えます。今回使ったメソッドは以下の3つです。現時点ではkeyを１つしか使ってないですが、拡張性を持たせようかと思って全てのkeyを取得する感じにしておきました。

• setProperties() [keyを指定して値を保存]

• deleteAllProperties() [全てのKeyとvalueを破棄]

• getProperties() [全てのkeyとvalueを取得]

##Rangeクラス
セルの内容を取得するにはRangeクラスを多用するので覚えておくと便利です。
参考: Class Range - Google Apps Script — Google Developers -

• getRow / getColumn [セルの行と列を取得]
  getRow()で編集したセルが存在する行数を取得出来ます。同じようにgetColumn()で列数も取得できますが、スプレットシート上ではA,B,C...という感じでアルファベット表記ですがgetColumn()では1,2,3...という感じで数字で取得されるので注意しましょう。

• getRange() [指定したセル情報を取得]

• getValue() [セルの中身を取得]

##完成したコード

流れとしては編集が行われるとonEdit()が動作して、5列目(作業者)にのびすけが記入された時に処理をする形にしています。

同じ行のNoの値や記入者の値をmessageに保存しておきます。複数あった場合は上書きになります。

getAndPostMessage()ではmessageの値をサーバーにpostします。サーバー側では前のNode.js+ExpressでChatworkにpostするプログラムを設置しておきます。(※前回はgetアクセスでサンプルを書いていましたが今回はpostに書き換えました。) ポストしたらmessageの値は削除しています。

あとはトリガー設定で数分毎にgetAndPostMessage()を実行すれば、前にポストしたときから変更があった場合だけchatworkにポストするプログラムの完成です。

参考: ChatworkAPIでmessageをPOSTしてみる。 - Qiita -

main.gs

var my_name = 'のびすけ';
var userProperties = PropertiesService.getUserProperties(); //データベース利用

//サーバーへpostする関数
function sendHttpPost(message) {
  var payload = {"message" : message};
  var options = {"method" : "post", "payload" : payload};
  UrlFetchApp.fetch("http://exsample.com", options);
}

//編集イベントのトリガー
function onEdit(event){
  var ss = event.source.getActiveSheet(); //編集したシート
  var r = event.source.getActiveRange(); //編集したセル
  
  if(r.getColumn() == 5 && event.value == my_name){
    //記入者
    var new_range = 'D' + String(r.getRow());
    var leftCell = ss.getRange(new_range);
    var issue_user = leftCell.getValue();
    
    //No.
    new_range = 'A' + String(r.getRow());
    var NoCell = ss.getRange(new_range);
    var issue_no = NoCell.getValue();
    
    //重要度
    new_range = 'B' + String(r.getRow());
    var LevelCell = ss.getRange(new_range);
    var issue_level = LevelCell.getValue();
    
    //ステータス
    new_range = 'F' + String(r.getRow());
    var StCell = ss.getRange(new_range);
    var issue_st = StCell.getValue();
    
    //メッセージ生成
    var message = 'No.'+ issue_no + ':' + issue_user + 'が' + my_name + 'に優先度'+ issue_level +'のチケットを発行しました。';
    if(issue_level == '高' && issue_st == '未'){
      //Browser.msgBox(message);
      var newProperties = {message: message};
      userProperties.setProperties(newProperties);
    }
  }
}

//時間毎に実行
function getAndPostMessage(){
 var message_list = userProperties.getProperties();

 for (var key in message_list) {
   Logger.log('A %s goes %s!', key, message_list[key]);
   if(message_list[key] != ''){
     sendHttpPost(message_list[key]);
   }
 }
 
 userProperties.deleteAllProperties(); //DBのメッセージを全て削除
}

##まとめ
割とカンタンに実装できると思いきや、色々と回り道してしまいました。
初めてちゃんと触ったけどGASはJavascriptをベースにしてるので基本的には作り易いなぁとい印象でした。
でもGASは仕様変更などが多いみたいなので調査しながら開発が必要そうですね。

参考: Apps Script Sunset Schedule - Google Apps Script — Google Developers -