Googleスプレッドシートを使ったChatWorkBotを作った話

Adways Advent Calendar 10日目の記事です。

http://blog.engineer.adways.net/entry/advent_calendar/archive

目次

  1. 作るにあたって必要なもの
  2. あると良いもの
  3. 作業フロー
    3.1 ChatWork API取得
    3.2 Google スプレッドシート(以下スプレッドシート)作成
    3.3 Google App Script(以下GAS)作成

作るにあたって必要なもの

  • ChatWork API Token
  • Google スプレッドシート(以下スプレッドシート)
  • Google App Script(以下GAS)
  • 情熱
  • 愛

Chatwork API Tokenがないと、メッセージを送れなかったりするので必要です。
スプレッドシートをDBのように使います。
GASで実行します。GAS様のお陰でサーバーレスですね。
情熱でコードを書きます。燃えよ情熱！
愛でコードを書きます。あふれる愛！メリークリスマス！！

あると良いもの

  • JavaScriptの知識
  • Excel(スプレッドシート)の知識
  • ピザ
  • コーラ

GASはJSなのでJSの知識があると開発が楽になります。(私には無いので苦労しかありません)
Excel(スプレッドシート)の知識があると、データの作成が楽になります。(私には無いのでry)
プログラマーはピザとカフェインをソフトウェアに変換する生物だとアメリカでは言われてます。
Progammer: an organism that turns caffeine and pizza into software.
プログラマーを捕まえたかったら、潤沢なピザとコーラを用意しましょう。
ノコノコやってきますよ。

早速作っていきましょう

一番最初にすることは、ChatWork API取得とGASの追加です。
ChatWork APIやGASについては佐藤さんが書いた記事に詳しく載っているので省略します。

今回私は社内清掃通知用のボットを作成しました。
弊社では平日の朝と夜に担当の班が社内を清掃します。
最初に、スプレッドシートで掃除のスケジュールを作成します。
このスプレッドシートをDBのように使い、GASで通知をする考えです。
なので

スプレッドシート作ります

掃除当番表のイメージは以下の写真のようなものになります。

f:id:AdwaysEngineerBlog:20161214111644p:plain

掃除当番表作成に必要なシート

  1. 掃除班一覧
  2. 休日一覧
  3. 掃除班インデックス

はじめに、掃除班シートを作成します。

f:id:AdwaysEngineerBlog:20161214111741p:plain

弊社の掃除大臣達です。
うそです。
実名はNGなので歴代総理大臣の名前を拝借いたしました。
弊社の掃除班は18班あります。
一番左のカラムに班の番号を入れます。
この値を当番表シートでVLOOKUPを使って参照するので、必ず一番左に入れる必要があります。

次に休日一覧シートを作成します。

f:id:AdwaysEngineerBlog:20161214111757p:plain

祝日と盆暮れ正月のデータを入れます。
またその他、休日がある場合このシートに追加します。
一番左のカラムに日付を入れ、その横に休日と入れます。
このシートは、当番表シートを作る際に祝日と盆暮れ正月など土日以外の休日に対応するためのものです。

掃除班インデックスシートを作ります。

f:id:AdwaysEngineerBlog:20161214111808p:plain

掃除班インデックスシートは、朝掃除と夜掃除の班の組み合わせのデータです。
データを見ていただけると、少々不規則な組み合わせであることに気づくと思います。(赤字の部分)
これはもともと、1日計3班で掃除をしていたのですが、1日計2班に変更になった名残です。
それに対応するために、掃除班インデックスというシートを作りました。
一番左のカラムに索引用の数字を振り、朝掃除と夜掃除の班の組み合わせを作ります。

役者は揃ったので掃除当番表作成します。

A列には日付を入れます。

A2に日付を入れて下にドラッグすれば簡単に出来ますね。

f:id:AdwaysEngineerBlog:20161214111841p:plain

B列にはWeekday関数を使って、曜日を入れます。

=WEEKDAY(A2)とすると、A2の日付から曜日を返してくれます。

f:id:AdwaysEngineerBlog:20161214111910p:plain

C列には平日か休日かを入れます。

f:id:AdwaysEngineerBlog:20161214111921p:plain

ここが難所です。
曜日だけのデータでは、祝日や盆暮れ正月の休日に対応が出来ません。
ですから、C列は平日か休日かのデータを保持させます。
=IFERROR(VLOOKUP(A2,'休日一覧'!$A$1:$B$1700,2,0),IF(OR(B2=1,B2=7),"休日","平日"))
IFERRORとVLOOKUPとIFとORの4つの関数を使ってます。
VLOOKUPでA2の値をキーとして休日一覧の値を参照して、もしエラーならば、もしB2の値が1か7ならば休日・そうでないならば平日を、返せ。

ややこしい式ですね。一つ一つ見ていきましょう。

VLOOKUPは

  • 第一引数を検索キー
  • 第二引数は検索範囲
  • 第三引数は検索範囲の何列目の値を返すのか
  • 第四引数は検索キーの完全一致か近似値か

の四つの引数を必要とします。
VLOOKUPは選択された範囲の一番左の列の値を検索キーとしてVerticalにLookupします。
VLOOKUP(A2,'休日一覧'!$A$1:$B$1700,2,0)
A2の日付を検索キーとして、休日一覧のA1からB1700までの範囲を検索し、該当範囲の2番目の値を返します。0は完全一致という意味です。
休日一覧シートを検索して、値があった場合(その日が祝日盆暮れ正月だった場合)"休日"という値を返します。
参照した値が休日一覧シートにない場合、エラーになります。
IFERROR関数でエラーを回収しましょう。
IFERRORは

  • 第一引数がエラーでない場合、第一引数を返し
  • 第二引数をエラーだった場合返す

関数です。
エラーだった場合、返す値をIF関数を使って処理します。
IFは

  • 第一引数に条件
  • 第二引数にTRUEだった場合返し
  • 第三引数にFALSEだった場合に返す

関数です。

複数の条件を指定する場合はOR関数を使います。
ORは

  • 引数に論理式
  • ひとつでもTRUEだったらTRUEを返し
  • すべてFALSEだったらFALSEを返す

関数です。
B2の値が1または7ならば(日曜は1、土曜は7です)、休日を返す、そうでないなら平日を返す。
毎日が休日ならこんな苦労しなくてすむのになぁ。

さて、難所は通り過ぎました。

D列はC列の平日をカウントします。

f:id:AdwaysEngineerBlog:20161214112026p:plain

COUNTIF($C$2:C2,"=平日")
$C$2と、＄を使って固定して、該当行までの平日の数をカウントしています。
平日の数と掃除インデックスをE列で結びつけちゃいましょう。

E列は掃除インデックスというカラムです。

f:id:AdwaysEngineerBlog:20161214112040p:plain

先ほど作った掃除インデックスシートと、掃除当番表をE列で繋ぎます。
IF(C2="平日", IF(MOD(D2,18)=0,18,MOD(D2,18)),"") MOD関数は、第一引数÷第二引数をして、その余りを返す関数です。
D列の平日カウントを18で割り、余りが掃除インデックスの検索キーとなります。
平日カウント数が18の倍数の場合は、18としています。
最初のIF関数で、C列が平日の時だけに掃除インデックスを返す処理にしました。
そうすることで、休日の行にF以降の列を空にしています。

E列の掃除インデックスの値を検索キーとして、F列とL列を埋めます。

f:id:AdwaysEngineerBlog:20161214112103p:plain

F列は朝掃除班、L列は夜掃除班の班番号を入れます。
=IFERROR(VLOOKUP(E2,'掃除班インデックス'!$A$1:$C$19,2,0),"")
=IFERROR(VLOOKUP(E2,'掃除班インデックス'!$A$1:$C$19,3,0),"")

さてさて、だいぶ当番表出来上がってきましたね。

最後に班のメンバーの名前を入れます。

f:id:AdwaysEngineerBlog:20161214112157p:plain

G列H列I列J列K列は朝掃除担当班メンバーです。
M列N列O列P列Q列は夜掃除担当班メンバーです。
G列に=IFERROR(VLOOKUP($F2,'掃除班一覧'!$A$2:$F$19,COLUMN(B1),0),"")
F列の値をもとに、掃除班一覧シートからメンバー名を取得します。
VLOOKUP($F2,'掃除班一覧'!$A$2:$F$19,COLUMN(B1),0)
VLOOKUPの第三引数にCOLUMN関数を使いました。
この関数は、COLUMN(B1)とすると2を返します。G2を選択して右にドラッグすると
=IFERROR(VLOOKUP($F2,'掃除班'!$A$2:$F$19,COLUMN(C1),0),"")
となり、COLUMNの引数がC1、つまり3を返します。
VLOOKUPを横にドラッグする際に、少し楽になる小技です。
M列にも同じように式を入れます。

あとは、これを一気に下まで反映させるだけ！

f:id:AdwaysEngineerBlog:20161214112218p:plain

これで当番表は完成です。

続いて、通知システムの作成に参りましょう。

f:id:AdwaysEngineerBlog:20161214112250p:plain

スクリプトエディタを開きます。

この時点で、スクリプトエディタないぜ！という方はGASをGoogle Driveに追加する必要があります。
詳しくは佐藤さんの記事に載っています。
追加すると、スクリプトエディタがツールに入ります。

この当番表をもちいて、通知・・・しません。

メンバー名をただ羅列して通知するのもいいですが、ChatWorkにはToという機能を使って強調しようと思います。
"To:"にはアカウントIDを使う必要があります。
そのために、名前とIDを紐づけたシートを作成します。
ただ、一人ひとりアカウントID紐付けるのめんどくさいですよね。
あれこれ考えた挙句、"users"というシートを作りました。
ChatWorkAPIを使って、コンタクトに入ってるユーザー全件取得して、アカウントIDをVLOOKUPで取り出すことにしました。

以下のスクリプトはChatWorkAPIを使ってコンタクトに入っているユーザーの情報を取得します。
headers : {"X-ChatWorkToken" : TOKEN} のTOKENには、ご自分のTOKENを入れてください。

function getUsers() {
  var params = {
    headers : {"X-ChatWorkToken" : TOKEN},
    method : "get"
  };
  var url = "https://api.chatwork.com/v1/contacts";
  var response = UrlFetchApp.fetch(url, params); //データの取得
  var hoge = JSON.parse(response.getContentText()); //取得したデータをJSON形式として返す


  var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var objSheet = objSpreadsheet.getSheetByName("users");
  SpreadsheetApp.setActiveSheet(objSheet);
  var maxRow = objSheet.getDataRange().getLastRow();//spreadsheetの最大行の数を取得
  var range= SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1);//セルのはじっこを選択
  range.offset(1, 0, maxRow, 9).clear();//データを消す

  for(var i=0; i < hoge.length; i++) {//JSON化した取得したデータは配列に入っているのでループでまわす。
    var item = hoge[i];
    // A列はVLOOKUP用に名前とする
    range.offset(i+1, 0).setValue(item.name);
    var j = 0;//列を移動するための変数を宣言
    for (key in item){//一列一列追加していく
       range.offset(i+1, 1+j).setValue(item[key]);
       j++;
    }
  };
};

このgetUsers関数をGASで実行すると、スプレッドシートのusersにデータが入ります。
実行するごとにデータを消して入れているので、気をつけてくださいね。 A列を名前にしているので、VLOOKUPが使えますね。
掃除班一覧シートに追加しちゃいましょう。
=IFERROR(VLOOKUP(B2,users!$A$1:$B$999999,2,0),"")

f:id:AdwaysEngineerBlog:20161214112331p:plain

※写真のアカウントIDは出鱈目です。rand関数とchar関数を使って、適当に作成しましたので実際には使えません。

おそらく、VLOOKUP上手く行かないメンバーが数名いるかと思います。
なぜなら、ChatWorkの名前に伊藤博文＠内線777などついている場合があるからです。
その場合は、手で入れるしかありません。
かといって、メンバーいっぱいいるなかで探すのはめんどくさい。
そんなアナタにQUERY関数。
適当なセルに=QUERY(A:I,"SELECT * WHERE A LIKE'%佐%'")
と打ち込んでみましょう。名前に佐のある人が全件取得できちゃいます。
%%の間に、見つからなかった人の名前を入れてあげれば、取得できますよ。
(また、Hirohumi Itohという名前の可能性もあります。)

朝掃除表と夜掃除表というシートを作ります。
またスプレッドシートです。 f:id:AdwaysEngineerBlog:20161214112400p:plain

朝掃除表と夜掃除表を通知する際に使います。
朝掃除表のA2に='掃除当番表'!A2
と掃除当番表のA2を参照させます。
B2に朝清掃と手入力します。
C2に='掃除当番表'!C2
D2に='掃除当番表'!F2
E2に=IFERROR(VLOOKUP($D2,'掃除班'!$A$2:$J$19,COLUMN(G1),0),"")
EからIまで右にドラッグして、アカウントIDを突っ込みます。
そして、下にドラッグして完成です。

朝掃除表をコピーして、夜掃除表とシートの名前を変えます。
B2の朝清掃を夜清掃に書き換えます。
D1の朝掃除担当班を夜掃除担当班に変えます。
D2を='掃除当番表'!L2に変えます。
そして、下にドラッグして完成です。

さてさて、だいぶスプレッドシートの話でしたね。

ChatWorkBotの実装コーディングに参りましょう。

今回は、ChatWorkClientというライブラリを利用します。
ChatWork社の渋谷さんという方が作ったライブラリです。
※"チャットワーク社 公式ライブラリではないので使用上起きた問題についての責任は負いかねます。 自己責任での利用をお願いします"
ただただ脱帽ですね。
コピペエンジニアの私には到底出来ない事です。
詳しい使い方などは、ChatWork社のCreators Noteに詳しく載っています。

通知までの流れ

作業に入る前に、ざっくりとどういう流れで通知に至るかを説明すると以下のようになります。
* GASの時間主導型トリガーを使って発火
* GASでスプレッドシートを参照して通知内容と通知対象を取得
* ChatWorkに投稿

GASには時間主導型トリガーという便利な機能があります。
時間がきたら発火してくれる機能です。
しかし欠点があります。
細かい時間の設定が出来ません。
なので、一分おきのトリガーをセットします。
そして、該当時間になったら動作するように処理を書きます。
sojiTypeという変数には、参照するシート名と、行のOFFSET値を入れます。
行のオフセットとは、シートからデータを取り出す際に、行を指定します。
そのときに、ループをまわしているので、0+1します。
朝掃除の通知は、翌日の通知をするので0+2になる了見です。

//一分おきのトリガーで動作する関数
function checkTime (){
  if (new Date().getHours() === 19 && new Date().getMinutes() === 00) {//毎日19時20分に通知するようにトリガーを設定すること
    var rowOffset = 1; //データを取り出す際に、行を一列動かす。当日だから1としている。
    var sheetName = "夜掃除表";
    var sojiType = [sheetName,rowOffset];//該当スプレッドシート名と、行からデータを取る際に使う数字を配列に入れる。
  };

  if (new Date().getHours() === 20 && new Date().getMinutes() === 30) {//毎日20時30分に通知するようにトリガーを設定すること
    var rowOffset = 2; //データを取り出す際に、行を一列動かす。翌日だから2としている。
    var sheetName = "朝掃除表";
    var sojiType = [sheetName,rowOffset];//該当スプレッドシート名と、行からデータを取る際に使う数字を配列に入れる。
  };
  if(sojiType !== undefined){
    notifyClearning (sojiType)
  };
};

必要な値を受け取ったnotifyClearning関数はgetShift関数とcreateMsg関数とsendMsg関数の三つを動かします。

//checkTimeからのデータを受け取って、掃除通知の一連の流れをつかさどる関数
function notifyClearning(sojiType) {
  if (sojiType === undefined) return;
  var shiftData = getShift(sojiType); //配列を渡し、スプレッドシートから該当行の配列を受け取る
  var msg = createMsg(shiftData); //該当行の配列を渡して、メッセージを成形する
  sendMsg(msg); //成形されたメッセージを送信する
};

getShit関数は、sojiTypeを受け取って、シートを参照し、該当行をまるごと返します。
GASでシートを毎回参照すると遅くなるので、シートの日付の行のデータを丸ごと変数に入れます。
そして日付がマッチしたら、その行を丸ごと取り出してます。
こうすることで、早くしてます。
GASでのスプレッドシートの扱い方はちょっと癖がありますね。
朝掃除の通知は該当日の前日に行います。
なので、たとえば水曜日が休みだった場合、木曜日の朝掃除の通知は水曜日の夜に行います。
rowOffsetを使った理由はこのためです。

function getShift(sojiType) {
  if(sojiType === undefined) return; //掃除タイプがundefinedだったら、returnする
  var today = new Date(); //今の日付を取得する
  today.setHours(0, 0, 0, 0); //時間を取る
  var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); //スプレッドシートを取得する
  var objSheet = objSpreadsheet.getSheetByName(sojiType[0]); //シートを指定する
  SpreadsheetApp.setActiveSheet(objSheet); //アクティブシートにする
  var maxRow = objSheet.getDataRange().getLastRow(); //行数を取得する
  var dateIndex = objSheet.getRange("A1:A").getValues(); //アクティブシートのA列を全て取得してインデックスに使う


  for (var i = 0; i < maxRow; i++) { //アクティブシートのA列の行数だけループをまわす
    var tmpDate = new Date(dateIndex[i][0]); //シートから取得した日付を変数に格納
    if( tmpDate.getTime() === today.getTime()) { //行の日付が、今の日付とマッチしたときに行のデータをシートから取得する処理
      var range = objSheet.getRange(i + sojiType[1], 1, 1, 9); //getRange関数を使って、アクティブシートの範囲を指定する。ここでrowOffsetを使う。
      var rowData = range.getValues(); //指定した範囲から値を取り出す
      if(rowData[0][2] === "平日") {//取得した行の値から平日かどうかを判定する
        return rowData[0]; //平日ならば値をReturnする
      }
      else {//平日でないならば、なにもしない。
        break;
      }
    }
  };
};

getShift関数からの配列のデータをcreateMsg関数に渡してメッセージを作ります。
班によっては三人だったり四人だったりするので、if (data[i] > 0)と邪道な形で確認してます。
実際のアカウントIDは数字なので0より大きいで問題ありません。
完全に苦し紛れです。

メッセージにはinfoタグを使ってます。
ChatWork APIをつかったメッセージで、infoタグ内で改行する方法はダブルクォートの中で\n\を使うと出来ます。 formatDateという関数は日付をフォーマットしてくれる関数です。
ネットに転がっていたので拝借致しました

//通知メッセージを作成する
function createMsg(data) {
  if (data === undefined) return;//データが入った配列がundefinedだったら、returnする。
  var text = "";
  for (var i = 4; i <= data.length; i++){
    if (data[i] > 0) { //account_idが0より大きいものを見ることで、ない場合を除外する
      text += "[To:" + data[i] +"]";
    };
  };
  //infoタグの中での改行は、""の中で\n\を使う
  text += "[info][title]"+formatDate(data[0])+"の"+data[1]+"[/title]"+formatDate(data[0])+"の"+data[1]+"担当は"+parseInt(data[3])+"班です。\n\
代理を頼んでいる方はその方へのリマインドをお願いします。\n\
よろしくお願いします。[/info]";
  return text;
};

さてメッセージ文も出来たので、送信しましょう。
ChatWorkClient.factoryというのは、追加したライブラリですね。
これにTokenを渡してあげて、clientという変数に格納。
clientのメソッドにsendMessageというのがあるので、そこでroom_idを指定します。
そして、bodyに送りたいメッセージを渡してあげましょう。

//メッセージを送信する
function sendMsg(msg) {
  if(msg === undefined) return;
  var client = ChatWorkClient.factory({token: TOKEN}); //チャットワークAPI
  client.sendMessage({
    room_id: ROOMID, //ルームID
    body: msg
  });
};

トリガーにcheckTime関数を実行するように指定して、完成です。
f:id:AdwaysEngineerBlog:20161214112506p:plain

一つにまとめるとこんな感じになります。
コピペして使う場合はTOKENとROOMIDを指定してください。

//メッセージを送信する
function sendMsg(msg) {
  if(msg === undefined) return;
  var client = ChatWorkClient.factory({token: TOKEN}); //チャットワークAPI
  client.sendMessage({
    room_id: ROOMID, //ルームID
    body: msg
  });
};

var formatDate = function (date, format) {//日付フォーマット用
  if (!format) format = 'YYYY-MM-DD';
  format = format.replace(/YYYY/g, date.getFullYear());
  format = format.replace(/MM/g, ('0' + (date.getMonth() + 1)).slice(-2));
  format = format.replace(/DD/g, ('0' + date.getDate()).slice(-2));
  format = format.replace(/hh/g, ('0' + date.getHours()).slice(-2));
  format = format.replace(/mm/g, ('0' + date.getMinutes()).slice(-2));
  format = format.replace(/ss/g, ('0' + date.getSeconds()).slice(-2));
  return format;
};

//通知メッセージを作成する
function createMsg(data) {
  if (data === undefined) return;//データが入った配列がundefinedだったら、returnする。
  var text = "";
  Logger.log(data)    
  for (var i = 4; i <= data.length; i++){//account_idが0より大きいものを見ることで、ない場合を除外する
    if (data[i] > 0) {
      text += "[To:" + data[i] +"]";
    };
  };
  //infoタグの中での改行は、""の中で\n\を使う
  text += "[info][title]"+formatDate(data[0])+"の"+data[1]+"[/title]"+formatDate(data[0])+"の"+data[1]+"担当は"+parseInt(data[3])+"班です。\n\
代理を頼んでいる方はその方へのリマインドをお願いします。\n\
よろしくお願いします。[/info]";
  return text;
};

//掃除のシフトを取得する
function getShift(sojiType) {
  if(sojiType === undefined) return;  //朝掃除・夜掃除の掃除タイプがundefinedだったら、returnする
  var today = new Date();
  today.setHours(0, 0, 0, 0);
  var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var objSheet = objSpreadsheet.getSheetByName(sojiType[0]);
  SpreadsheetApp.setActiveSheet(objSheet);
  var maxRow = objSheet.getDataRange().getLastRow();
  var dateIndex = objSheet.getRange("A1:A").getValues();
  for (var i = 0; i < maxRow; i++) {
    var tmpDate = new Date(dateIndex[i][0]);
    if( tmpDate.getTime() === today.getTime()) {
      var range = objSheet.getRange(i + sojiType[1], 1, 1, 9);
      var rowData = range.getValues();
      if(rowData[0][2] === "平日") {//平日ならばReturnする
        return rowData[0];
      }
      else {//平日でないならば、なにもしない。
        break;
      }
    }
  };
};


//checkTimeからのデータを受け取って、掃除通知の一連の流れをつかさどる関数
function notifyClearning (sojiType){
  if (sojiType === undefined) return;  
  var shiftData = getShift(sojiType);//配列を渡す。
  var msg = createMsg(shiftData);
  sendMsg(msg);
};

//一分おきのトリガーで動作する関数
function checkTime (){
  if (new Date().getHours() === 19 && new Date().getMinutes() === 00) {//毎日19時20分に通知するようにトリガーを設定すること
    var rowOffset = 1; //データを取り出す際に、行を一列動かす。当日だから1としている。
    var sheetName = "夜掃除表";
    var sojiType = [sheetName,rowOffset];//該当スプレッドシート名と、行からデータを取る際に使う数字を配列に入れる。
  };

  if (new Date().getHours() === 20 && new Date().getMinutes() === 30) {//毎日20時30分に通知するようにトリガーを設定すること
    var rowOffset = 2; //データを取り出す際に、行を一列動かす。翌日だから2としている。
    var sheetName = "朝掃除表";
    var sojiType = [sheetName,rowOffset];//該当スプレッドシート名と、行からデータを取る際に使う数字を配列に入れる。
  };
  if(sojiType !== undefined){
    notifyClearning (sojiType)
  };
};

あとがき

何故動かん？
こりゃ至極複雑無理難解
れい外処理ばかりで
すんなり出来ず
ゲンナリゲンメツ！
エンジニア魂が燃え上がる
こりゃ、ムリだと諦めて
んなわけあるか
なにか方法あるべや、と
のう味噌しぼって
はん死半生
じかんにして、6時間
めん倒な作業を
てき当にChatWorkBot化した話でした。

完
以上、湯浅でした。

次は清水さんの記事です。

http://blog.engineer.adways.net/entry/advent_calendar/11