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


append-row

photo credit: row of boats via photopin (license)

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

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

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

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

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

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

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

では行ってみましょう。

前回までのスクリプト

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

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
    ]
);

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

INDIRECT関数でセル参照をR1C1形式に

3行目ですが

=INDIRECT(“RC[1]”,FALSE) & INDIRECT(“RC[2]”,FALSE)

という数式です。

appendRowで数式を指定する場合は数式全体をダブルクォーテーションで囲む必要があるのですが、INDIRECT関数内でもダブルクォーテーションを使っているので、そちらのほうは「\”」でエスケープしています。

INDIRECT関数は指定したセルの参照を返すスプレッドシート関数で

INDIRECT(セル参照の文字列, [A1形式にする])

という指定の仕方をします。[A1形式にする]は

  • TRUE:A1形式にする
  • FALSE:R1C1形式にする

のどちらかを指定をします。今回、例えばA2セルであれば「=B2&C2」といったシンプルな数式なのですが、数式を記述する行数によって参照を変更したいのでR1C1形式で指定をしたかったのです。

VLOOKUPで社員番号を取得

5行目はVLOOKUPで「社員マスタ」シートから社員番号を取得してきます。

=VLOOKUP({アカウントID},’社員マスタ’!A:B,2,FALSE)

キーとしてはチャットワークAPIのレスポンスとして受け取ったjsonオブジェクト内のaccount_idですから

obj.account.account_id

で取得できますね。

こちらの数式は、数式を出力する行数によって参照の変更はありませんのでINDIRECT関数は使わなくても大丈夫です。

実行結果

では上記のスクリプトを前回のスクリプトの21行目のログ出力文とゴソっと入れ替えます。

チャットワークの打刻チャットで打刻して実行をしてみます。

スプレッドシートにappendRowで行を追加

このように無事に最終行にデータが追加されました。

まとめ

Google Apps Scriptでチャットワークからの打刻情報をシートの最終行に追加する方法についてお伝えしました。

Google Apps Scriptの場合、最終行へのデータ追加はSheetオブジェクトに対するappendRowメソッドが便利です。

  • 最終行数を調べる必要がない
  • 配列で一気にデータを追加できる
  • 値も数式も入力可能

などのメリットがあります。

次回ですが、次のダンドリの事前準備としてスプレッドシートから特定の値を検索する関数を作っていきます。

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

どうぞお楽しみに!

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

本シリーズはチャットワークの特定のグループチャットで出勤や退勤の打刻が可能になる勤怠システムをGoogle Apps Scriptとスプレッドシートを用いて作っていきます。これからはチャットというインターフェースがトレンドになってきますので、良い練習になると思いますよ!
  1. Google Apps Scriptでチャットワークでのチャット打刻システムを作る
  2. Google Apps ScriptでのDateオブジェクトの各生成方法、日付と時刻を分ける
  3. Google Apps Scriptでチャットワークからの打刻情報をシートの最終行に追加する方法
  4. Google Apps Scriptでスプレッドシート内を検索して行番号を返す関数(高速版)
  5. Google Apps Scriptでチャットワークからの出勤・退勤の打刻データを様々な条件分岐を駆使して処理する

  投稿者プロフィール

タカハシノリアキ株式会社プランノーツ 代表取締役
株式会社プランノーツ代表、コミュニティ「ノンプロ研」主宰。1976年こどもの日生まれ。東京板橋区在住。「ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、執筆などをしております。→詳しいプロフィールはコチラ
★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ
★フォロー頂ければ嬉しいです。

タイトルとURLをコピーしました