Google Apps Scriptでチャットワークからの出勤・退勤の打刻データを様々な条件分岐を駆使して処理する

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

Googleスプレッドシート上に勤怠管理集計システムを作るのを目標にシリーズ連載をしています。

今回はこちらの記事の続きです。

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

Googleスプレッドシート上に勤怠管理集計システムを作っています。今回はGoogle Apps Scriptでチャットワークからの打刻情報をappendRowでシートの最終行に追加する方法です。

tonari-it.com

チャットワークから取得した出勤に関するデータをデータシートに追加するというところをお伝えしました。

さて、システムとしてはチャットワークから新たなデータを受け取った際に、同日、同社員のレコードが既に存在しているかを判断する必要があります。

その結果いかんでその後の処理が変わってきます。

その準備として、以下の記事で判定用の関数を作成しました。

Google Apps Scriptでスプレッドシート内を検索して行番号を返す関数（高速版）

Google Apps Scriptで「スプレッドシート内に特定の値が存在しているかどうかを判定したい、または取り出したい」ということがよくあります。頻度が高いので、汎用的に使える関数を作っていきます。

tonari-it.com

今回は、この関数を活用して、新たなデータを受け取ったときの処理の分岐について組み立てつつ、出勤・退勤打刻データの入力処理を一気に作ってていきます。

これまでのおさらい

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

function recordTime(){
  
  /* シートの設定 */
  var wsData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("データ"); //データシートを設定
  var wsStaff = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("社員マスタ"); //社員マスタを設定

  /* チャットワークとURLリクエストの設定 */
  var params = {
    headers : {"X-ChatWorkToken" : '---------------------'},
    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){

      /* JSON内オブジェクトから日付、時刻、アカウントIDを取り出す */
      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(/おはよう/)){

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

     /* データが「退勤」であった場合の処理 */
      }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);
  }
}

全体のアルゴリズムとしては

• スプレッドシートの設定
• チャットワークとURLリクエストの設定
• try
  • JSON内オブジェクトから日付、時刻、アカウントIDを取り出す
  • 取得した全てのメッセージについて
    • JSON内オブジェクトから日付、時刻、アカウントIDを取り出す
    • データが「出勤」であった場合（★ここから）
      • シートにデータを一行追加
    • データが「退勤」であった場合
      • ログ出力（★ここまで）
• tryの中でエラーが発生した場合
  • エラー内容をログ出力

という流れになります。

今回は、「★ここから」～「★ここまで」の条件分岐を含む処理を変更していきます。

条件分岐アルゴリズム

上記、条件分岐部分のアルゴリズムはこうなります。

• データが「出勤」であった場合
  • 同日同社員のレコードが存在していない
    • レコードを一行追加
• データが「退勤」であった場合
  • 同日同社員のレコードが存在している
    • 退勤フィールドがnull
      • 退勤時刻を入力

ちょっと複雑な感じもしますね…。でも、頑張りましょう。

同日同社員のレコードが存在していないか判定する

同日同社員のレコードが「データ」シートに存在しているかどうかの判定ですが、シートではこのように

A列「打刻ID」が日付（シリアル値）と社員IDを結合したものになっており、これがシート上では一意の値となっています。

従って、この列の中に、同日同社員のレコードが存在していないか判定すれば良さそうです。

Date型オブジェクトをシリアル値に変換

まず、日付を格納したDate型のオブジェクトdate_Dをシリアル値に変換するためには、以下関数dateToSnを使います。

var MILLIS_DIFFERENCE = 9 * 60 * 60 * 1000; //UTCとJSTの時差 (ミリ秒)
var COEFFICIENT = 24 * 60 * 60 * 1000; //日数とミリ秒を変換する係数
var DATES_OFFSET = 70 * 365 + 17 + 1 + 1; //「1900/1/0」～「1970/1/1」 (日数)

function dateToSn(date){ // Date→シリアル値
  return convertUt2Sn(date.getTime());
}

function convertUt2Sn(unixTimeMillis){ // UNIX時間(ミリ秒)→シリアル値
  return (unixTimeMillis + MILLIS_DIFFERENCE) / COEFFICIENT + DATES_OFFSET;
}

こちらは以下記事から引用させて頂きました。

Excelの日付(シリアル値)についてと、UNIX時間との変換 (JavaScript, VBA) - Qiita

Excelの日付についてExcelは「1900年1月0日」を0とする数値を「9999年12月31日」までの各日付に割り振っている。これを「 シリアル値(Serial Number) 」と呼ぶ。…

qiita.com

社員マスタから社員IDを「VLOOKUP」する

次に社員IDが必要なのですが、これは「社員マスタ」シートからチャットワークのアカウントIDをキーとして抽出してきます。

スプレッドシート関数でいうとVLOOKUPですね。

この処理は、前回作った関数findRowを使います。

function findRow(sheet,val,col){

  var dat = sheet.getDataRange().getValues(); //受け取ったシートのデータを二次元配列に取得

  for(var i=1;i<dat.length;i++){
    if(dat[i][col-1] === val){
      return i+1;
    }
  }
  return 0;
}

解説はこちらをご覧ください。

Google Apps Scriptでスプレッドシート内を検索して行番号を返す関数（高速版）

Google Apps Scriptで「スプレッドシート内に特定の値が存在しているかどうかを判定したい、または取り出したい」ということがよくあります。頻度が高いので、汎用的に使える関数を作っていきます。

tonari-it.com

これを使って社員IDを取得するには、「社員マスタ」シートをwsStaffとした場合

var staffID = wsStaff.getRange(findRow(wsStaff,obj.account.account_id,1),2).getValue(); //社員ID

ということで取得できます。

数値と文字列の連結について

上記から日付のシリアル値と社員IDが求められましたので、これらを文字列として連結したものを「データ」シートでfindRowすれば、同日同社員のデータが存在するかどうかを判定することができます。

var dataRow=findRow(wsData,dateToSn(date_D)+staffID.toString(),1);

これで存在していればdataRowには発見した行数が、存在していない場合は0が格納されます。

ここで第二引数を単純に

としてしまうと、数値＋数値で加算されてしまいます。staffIDにtoStringメソッドをかまして文字列に変換してあげることで、数値＋文字列の演算になるので、文字列の連結となります。

覚えておくと良いですよ。

条件分岐箇所のスクリプト

これをもとに冒頭のスクリプトの26行目～43行目までを以下のように修正をしました。

/* 社員IDと同日同社員の判定 */
var staffID = wsStaff.getRange(findRow(wsStaff,obj.account.account_id,1),2).getValue(); //社員ID
var dataRow=findRow(wsData,dateToSn(date_D)+staffID.toString(),1); //同日同社員の判定 True:行数,False:0

if(obj.body.match(/出勤/) || obj.body.match(/おはよう/)){ 
  if(!dataRow){
    /* レコードを一行追加 */
    wsData.appendRow(
      [
        "=INDIRECT("RC[1]",FALSE) & INDIRECT("RC[2]",FALSE)",
        date_D,
        "=VLOOKUP(" + obj.account.account_id + ",'社員マスタ'!A:B,2,FALSE)",
        "出勤",
        date_T
      ]
    );
  }
}else if(obj.body.match(/退勤/) || obj.body.match(/お疲れ/)){
  if(dataRow){
    var rng = wsData.getRange(dataRow,6);
    if(!rng.getValue()){
      rng.setValue(date_T);
    }
  }
}

if文が大量に増えましたね（汗）。

条件式に比較演算子を使っていない場合がたくさんあるように見えますが、JavaScriptの場合はnull,0はfalseとして扱われるので、それを利用しています。

これも覚えておくと良いですね。

まとめ

チャットワークから受け取った出勤・退勤の打刻データについて条件に応じてシートに追加する処理を一気に作っていきました。

システムとしては、簡易ではありながら最低限の機能は実装された状態となりました。

しかし、相手は人間…色々な操作をしますし、システムに受け入れられない操作をしているということもなかなか気づかない場合もあります。

そのような場合はちゃんとお知らせしてあげる必要がありますね。

次回は、処理の結果についてメッセージを返してあげるという処理を作っていきたいと思います。

どうぞお楽しみに！

連載目次：チャットワークのメッセージ送信で出勤・退勤の打刻をする

本シリーズはチャットワークの特定のグループチャットで出勤や退勤の打刻が可能になる勤怠システムをGoogle Apps Scriptとスプレッドシートを用いて作っていきます。これからはチャットというインターフェースがトレンドになってきますので、良い練習になると思いますよ！

1. Google Apps Scriptでチャットワークでのチャット打刻システムを作る
2. Google Apps ScriptでのDateオブジェクトの各生成方法、日付と時刻を分ける
3. Google Apps Scriptでチャットワークからの打刻情報をシートの最終行に追加する方法
4. Google Apps Scriptでスプレッドシート内を検索して行番号を返す関数（高速版）
5. Google Apps Scriptでチャットワークからの出勤・退勤の打刻データを様々な条件分岐を駆使して処理する