Google Apps Scriptでチャットワークからの打刻情報をシートの最終行に追加する方法

photo credit: row of boats via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

シリーズでGoogleスプレッドシート上に勤怠管理集計システムを作っています。

前回の記事はこちらです。

Google Apps ScriptでのDateオブジェクトの各生成方法、日付と時刻を分ける

スプレッドシートの勤怠管理システムをお題としてGoogle Apps Scriptでの日時について解説します。Dateオブジェクトの生成のいくつかの種類、要素を取得するメソッド、日付と時刻の分解などです。

チャットワークの打刻用のグループチャットから取得した”打刻メッセージ”から「日付」と「時刻」を取得する方法についてお伝えしました。

今回は続いて、社員番号を取得しつつ、出力先をログではなくてシートの最終行に出力していくようにしていきたいと思います。

Google Apps Scriptでチャットワークからの打刻情報をシートの最終行に追加する方法です。

では行ってみましょう。

前回までのスクリプト
チャットワークのレスポンスから社員番号を割り出す
シートの準備
appendRowメソッドでシートの最終行にデータを追加する
1. INDIRECT関数でセル参照をR1C1形式に
2. VLOOKUPで社員番号を取得
実行結果
まとめ
連載目次：チャットワークのメッセージ送信で出勤・退勤の打刻をする

前回までのスクリプト

前回までで作成したスクリプトはこちらです。

function recordTime(){
  var params = {
    headers : {"X-ChatWorkToken" : '---------------------'}, //チャットワークAPIトークン
    method : "get"
  };
  var roomID = XXXXXXXX; //ルームID
  var url = "https://api.chatwork.com/v2/rooms/" + roomID + "/messages?force=0"; //指定のグループチャットからメッセージを取得

  try{
    var respons = UrlFetchApp.fetch(url, params); //チャットワークAPIエンドポイントからレスポンスを取得
    var json = JSON.parse(respons.getContentText()); //文字列をJSON形式として解析しJSONオブジェクトとして返す

    for each(var obj in json){

      var date = new Date(obj.send_time*1000); //レスポンスから送信日時を取り出す
      var date_D = new Date(date.getFullYear(),date.getMonth(),date.getDate(),0,0,0); //日付を取り出す
      var date_T = new Date(0,0,0,date.getHours(),date.getMinutes(),0); //時刻を取り出す

      if(obj.body.match(/出勤/) || obj.body.match(/おはよう/)){

        Logger.log("日付:" + date_D + ",時刻:" + date_T.getHours() + ":" + date_T.getMinutes() + ",出勤");

      }else if(obj.body.match(/退勤/) || obj.body.match(/お疲れ/)){

        Logger.log("日付:" + date_D + ",時刻:" + date_T.getHours() + ":" + date_T.getMinutes() + ",退勤");
      }
    }
  }catch(e){
    Logger.log('エラーが発生しました');
    Logger.log(e.message);
  }
}

チャットワークの打刻用のグループチャットからメッセージを取得して、日付、時刻、区分（出勤か退勤か）をログ出力できるようになりました。

打刻情報としてはあとは社員番号が必要になります。

チャットワークのレスポンスから社員番号を割り出す

チャットワークAPIからのレスポンスはこちらですから

[
  {
    "message_id": 5,
    "account": {
      "account_id": 123,
      "name": "Bob",
      "avatar_image_url": "https://example.com/ico_avatar.png"
    },
    "body": "Hello Chatwork!",
    "send_time": 1384242850,
    "update_time": 0
  }
]

社員ごとに一意な情報としてaccount_id（アカウントID）がありますので、この情報をキーに社員番号を取得するようなシステムにしていければ良さそうですね

ちょうど社員マスタをこのように作っておきました。

チャットワークIDと社員情報が紐づいていますから、このシートから引っ張て来ればOKですね。

シートの準備

今回は出力先をシートの最終行にしていきたいので、まずシートの準備をします。

スクリプトの冒頭に

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

として、情報を追加する「データ」という名前のシートをwsDataという変数で取り扱いできるようにしておきます。

appendRowメソッドでシートの最終行にデータを追加する

データを最終行に追加する方法として、シートの最終行を調べて、その行の該当セルに対してsetValueやsetFormulaなどのメソッドを使うという方法があります。

しかしGoogle Apps Scriptの場合、最終行にデータを追加するだけなら、appendRowメソッドを使うほうが手っ取り早いです。

書き方としては

Sheetオブジェクト.appendRow(配列)

となります。配列ですが角括弧([])内に、A列、B列、…のセルに入力したいデータをカンマ区切りで指定します。データとして入力可能なデータは数値、文字列、日付などの一般的なデータのほかに、数式も含まれます。

RangeオブジェクトへのsetValue、setFormula、setFormulaR1C1などですと、入力する内容が値なのか数式なのかで使うメソッドを変えないといけなかったのですが、appendRowならいっぺんにいけます。

今回の場合

A列「打刻ID」：B列とC列の結合の数式　例：”=B10&C10″
B列「日付」：日付の値
C列「社員ID]：社員マスタシートからアカウントIDをキーに社員番号をVLOOKUP　例：”=VLOOKUP(XXXXXXX,’社員マスタ’!A:B,2,FALSE)”
D列「区分」：「出勤」
E列「打刻（出社）：時刻の値

となります。

これからをappendRowで出力するスクリプトとしては

wsData.appendRow(
    [
        "=INDIRECT("RC[1]",FALSE) & INDIRECT("RC[2]",FALSE)",
        date_D,
        "=VLOOKUP(" + obj.account.account_id + ",'社員マスタ'!A:B,2,FALSE)",
        "出勤",
        date_T
    ]
);

以降、ポイント解説しますね。