chatworkで出退勤情報をgoogleスプレッドシートに保存

背景

chatworkで、出退勤の投稿から自動的に集計したいという話が持ち上がったので、google apps scriptで動作確認のためのスクリプトを作ってみました。chatworkでwebhookを登録して、投稿したときに、googleの公開URLを呼び出す仕組みは経験がありましたので、簡単にできるだろうとお思いましたが、思わぬところで躓きました。その話は後で。ネット検索して
Google Apps Scriptでチャットワークからの打刻情報をシートの最終行に追加する方法 という記事を参考にさせて頂きました。

準備

社員マスタを以下のようにgoogleスプレッドシートで作ります。これは、chatworkのアカウントIDと氏名のリストです。シート名は、user_listにしています。なぜ、社員マスタが必要かと言いますと、chatworkから得られるのは、アカウントIDという数字情報なので、それをスプレッドシートに吐き出してもなんのこっちゃになりますので、スプレッドシートには、VLOOKUP関数で氏名が表示されるように、社員マスタを作っておきます。

chatworkでAPIトークンを取得しておきます。

テストスクリプトを作ってみる

新規に勤怠情報を保存するgoogleスプレッドシートを作ります。そして、ツール->スクリプトエディタを選んで、スクリプトを書いていきます。
chatworkで投稿した文字列に"出勤"または"出社"が含まれていたら、時刻と氏名をgoogle スプレッドシートに保存するようにします。

var API_TOKEN = '*************************************';　//チャットワークAPIトークン

function doPost(e) {

  var json = JSON.parse(e.postData.contents);
  /* リクエスト用パラメータ・URLの準備 */
  var params = {
    headers : {"X-ChatWorkToken" : API_TOKEN},
    method : "post"
  };

  var roomId = json.webhook_event.room_id;

  url = "https://api.chatwork.com/v2/rooms/" + roomId + "/messages";

  var jsonBody = json.webhook_event.body;


  var wsData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1"); //シートを設定

  var sheet_key = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
  var range = "user_list!A2:B100" ;

  if(jsonBody.match(/出勤/) || jsonBody.match(/出社/)){
        var accountId = json.webhook_event.account_id;
        var messageId = json.webhook_event.message_id;

        var body = ''
        body += '[rp aid=' + accountId;
        body += ' to=' + roomId + '-' + messageId + '] '

        var date = new Date();
        var time = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy/MM/dd/HH:mm:ss');
        replyMessage = time + '  ' + ' アカウントID:' + accountId;
        body += '[info]打刻時刻：' + replyMessage  + '[/info]'

        params.payload = {body :body};
        UrlFetchApp.fetch(url, params);

        var convert_name = "=VLOOKUP(" + accountId + ",IMPORTRANGE(" + "\"" +  sheet_key + "\"" +  "," + "\"" +  range + "\"" +  "),2,FALSE)" ;
        wsData.appendRow([convert_name,time,"出勤"]);


      }else if(jsonBody.match(/退勤/) || jsonBody.match(/退社/)){
　　　　　　　　上と同じような処理を入れる
  }

}

上のスクリプトは、スプレッドシートにVLOOKUP関数を埋め込んでまして、IMPORTRANGEで社員マスタを指定するようになっています。
sheet_key がそれに相当します。ここは、URLを入れるのではなくて、
https://docs.google.com/spreadsheets/d/　
の後に続く文字列をセットします。古いネット記事ですと、URL全体を埋め込むようになっていたので、ここで若干ハマりました。
一番ハマったのは、IMPORTRANGE(に続くところは、文字列を直接書いていたのですが、ダブルクオーテーションがうまく処理されずに、スプレッドシートに保存したときに、半角のところが全角のダブルクオーテーションになりエラーとなっていました。対策として、事前にsheet_key,rangeを変数として定義しました。
google apps scriptが書けたら、以下の２つの処理をします。
Google Apps Script のWeb URLを公開します。

ChatWork のwebhook で上記URLを設定します。

実行してみる

chatworkで投稿する。例えば、
出社しました。
と投稿すると、chatworには自動で
打刻時刻：2020/07/16/09:33:16 アカウントID:XXXXX
と表示されます。

googleスプレッドシートには以下のように表示されます。

今後

とりあえず、目的の動作をすることが確認できましたので、今後は、本番運用できるようにカスタマイズして行きます。