Slack×GASで作った勤怠Botの話#6

2020年12月6日 20:06

UG Advent Calendar 2020 の6日目の記事です。

どうもbarusuです。知らない方は初めまして。
昨日の続きです。
フィクションと思って読んでいただければ幸いです。

Slack×GASで作った勤怠Botの話 #6

2020年5月。
良くも悪くもコロナ禍による新しい生活様式が定着しはじめた頃のお話。

―――――――ちょっとさ、勤怠システム作れない？

すべては法務部長の一言から始まった…

全10話分の5話目となります。
　2020/12/01：導入,初期の要望
　2020/12/02：設計
　2020/12/03：構築その1 ：SlackBotを作る
　2020/12/04：構築その2 ：GASを書く(基本メソッド編)
　2020/12/05：構築その3 ：GASを書く(ユーティリティ編)
　2020/12/06：構築その4 ：GASを書く(機能実装編：打刻処理) ← 今日はココ
　2020/12/07：構築その5 ：GASを書く(機能実装編：ユーザー管理機能,リマインド機能)
　2020/12/08：構築その6 ：色々書く(機能実装編：,勤怠修正機能)
　2020/12/09：単体,ユーザーテスト
　2020/12/10：そして伝説へ...

Barusu
「ひひーん」

Barusu
「前回はユーティリティを追加したところまでだったな」

▼簡易設計
下準備：SlackAccessTokenを設定しておく
基礎
　投稿を受け取り、スプレッドシートに記載してSlackに返す
　投稿したユーザー名をSlackAPIを用いて取得
　ユーザーにDMを送る
　投稿内容に応じて異なる処理を実行する

各種ユーティリティ
　日付のフォーマットを変更する
　スプレッドシートの範囲から連想配列を作成する
　連想配列を二次元配列に変換する
　祝日一覧を取得してシートに転記
　GoogleWorkspaceのユーザー一覧を取得してシートに転記　
---(今日はココから)---
機能実装予定のもの
1. 打刻機能
　a. 出勤、退勤、中抜けに反応する仕組みを作る
　b. 出勤打刻を受け取ってスプレッドシートに今の時刻を記載する
　　例外：出勤済ならば「出勤してます」と返す
　c. 中抜けを受け取ってスプレッドシートに記載する
　　例外：未出勤ならば「出勤してません」と返す
　　例外：時刻データが不正の場合は「形式が正しくありません」と返す
　d. 退勤打刻を受け取ってスプレッドシートに記載する
　　例外：未出勤ならば「出勤してません」と返す
　　例外：退勤済ならば「退勤済ですが上書きしますか？」と返す
---(今日はココまで)---

2.ユーザー管理機能
　a. 初めて投稿したユーザーは勤怠ユーザーリストに追加する
　b. Gsuiteからメールアドレスを取得し、Slackユーザーリストと紐付ける
3. リマインド機能
　a. 今日が平日ならば→指定時間帯に未打刻のユーザーにDMを送る
　b. 退勤をしていないユーザーにDMを送る
4. 勤怠修正機能
　a. Googleフォームで修正申請を受付
　b. 指定の承認者のみが承認できるようにする
　b. 別の勤怠修正フォームから承認された勤怠データとデータを合流させる
5. アラート機能
　a. 月末残業予測値を算出し、しきい値を超えた者はアラートを出す

Barusu
「まだこれから機能増えるんだろうな...」

Barusu
「とりあえず今日は打刻機能を追加する」

Barusu
「っと、その前に解説入れとこか」

打刻機能

Barusu
「打刻機能はFuctionにして、出勤、退勤、中抜けの命令を引数にすることで分岐するようにした」

// 完成したソース
function appendToSheet(userId,userName,type,timeInterval){
 
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Append');
 var lastRow = sheet.getLastRow();
 var lastCol = sheet.getLastColumn();

 
 //シートをまるごと変数にぶち込む
 var array = sheet.getRange(1,1,lastRow,lastCol).getValues();
 var now = new Date(); //現在日時を取得
 var convertedDate = now.getFullYear() + "/" + (now.getMonth() + 1) + "/"+ now.getDate();
 var arrayNum = getArrayNum(userName,userId,now,array);
 var userDataRowNum = arrayNum + 1;

 switch (type){
   case 'SignIn' :

     if (arrayNum == null ){
     sheet.appendRow([convertedDate,userName,userId,now]);
       return "出勤打刻が完了しました。\n 本日の打刻時間： " + convertedDate +" " + dateToFormatString(now, '%HH%:%mm%:%ss%');
     }
     else{
         return "出勤済です。\n 本日の打刻時間： " + convertDate(signedIndate)+" "+ dateToFormatString(signedIndate, '%HH%:%mm%:%ss%');
     }
     
     break
     case 'SignOut' :
     
     if (arrayNum != null ){
       
       if( sheet.getRange(userDataRowNum, 5).getValue() == '' ){
     sheet.getRange(userDataRowNum, 5).setValue(now);
     sheet.getRange(userDataRowNum, 5).setNumberFormat('YYYY/MM/DD HH:mm')     
     sheet.getRange(userDataRowNum, 8).setValue('=(E'+userDataRowNum+'-D'+userDataRowNum +')-F'+userDataRowNum);
     sheet.getRange(userDataRowNum, 8).setNumberFormat('[hh]:mm')
     sheet.getRange(userDataRowNum, 9).setValue('=minute(H' + userDataRowNum + ')+(hour(H' + userDataRowNum + ')*60)')
    
     return "退勤打刻が完了しました。\n 本日の打刻時間： " + convertedDate + " " + dateToFormatString(now, '%HH%:%mm%:%ss%');
       }
       else{
         return "Not";
       }
       }
     else {
     return "今日はまだ出勤打刻をしていません。";
     }
     break
     case 'Interval' :
     //中抜け時間を登録する
     if (arrayNum != null ){
        if( sheet.getRange(userDataRowNum, 6).getValue() == '' ){
     sheet.getRange(userDataRowNum, 6).setValue(timeInterval);
     sheet.getRange(userDataRowNum, 6).setNumberFormat('H:mm')
     
     return "中抜け時間を登録しました。本日の中抜け時間： " + timeInterval;
        }
       else{
       return "Not";   
        }
     }
     else {
     return "今日はまだ打刻していません。打刻をしてください。";
     }
     
     break
     
 }
 
 
}

Barusu
「以下に細かいとこの解説を記載していく」

a. 出勤、退勤、中抜けに反応する仕組みを作る

Barusu
「BotへのDMの内容に応じて、出勤なのか退勤なのか中抜け時間の登録なのかを判別→各処理を起動の流れで、内容を判別する処理が以下。
前回書いたやつそのまんまなんだけど、まぁソースはこんな感じ」

var json = JSON.parse(e.postData.getDataAsString());
var text = json.event.text;
～中略～
    // 出勤
    var commandSignInSent =/(モ[ー〜]+ニン|も[ー〜]+にん|おっは|おは|へろ|はろ|ヘロ|ハロ|hi|hello|morning|出勤)/.test(json.event.text);
    
    // 退勤
    var commandSignOutSent = /(バ[ー〜ァ]*イ|ば[ー〜ぁ]*い|おやすみ|お[つっ]ー|おつ|さらば|お先|お疲|帰|乙|bye|night|(c|see)\s*(u|you)|退勤|ごきげんよ|グ[ッ]?バイ)/.test(json.event.text);
    
    // 中抜け時間指定 想定: 中抜け 2:00
    var commandIntervalSent = /(中抜|(ぬ|なか|休憩))/.test(json.event.text);
    
    var userCheck = userId == "BotUserIdを入力する";
         
    if(userCheck == false ){

      if(commandSignInSent == true){
        var messagePost = "";
        var command = "SignIn";
        var intervalTime = "";
      }
      else if(commandSignOutSent == true){
        var messagePost = "";
        var command = "SignOut";
        var intervalTime = "";
      }  
      else if(commandIntervalSent == true){
        var messagePost = "";
        var intervalTime = text.match(/.[0-9]:[0-9]{2}/g);
        var command = "Interval";
        SpreadsheetApp.getActiveSheet().getRange(9, 6).setValue(intervalTime);
      }
      else {
        var messagePost = "すみません。よくわかりません。\n私ができることは以下となります。\n\n「おはよう」→ 今の時刻で出勤打刻\n「おつかれ」→今の時刻で退勤打刻\n「中抜け hh:mm」→ 今日の打刻にhh:mm の中抜け時間を追加";// 投稿するメッセージ
      }

Barusu
「ここでやっているのは、正規表現に一致しているか？の結果(True/False)を各変数に格納している。次のIf文でそれぞれのTrue/Falseを判別している感じ。
ここで重要なのは、各変数において一つだけがTrueになるようにすること。
出勤打刻(commandSignInSent)と退勤打刻(commandSignOutSent )が同時にTrueにならないように気をつける」

b. 出勤打刻を受け取ってスプレッドシートに今の時刻を記載する

 ~中略~
 
 var arrayNum = getArrayNum(userName,userId,now,array);
 var userDataRowNum = arrayNum + 1;

 switch (type){
   case 'SignIn' :

     if (arrayNum == null ){
     sheet.appendRow([convertedDate,userName,userId,now]);
       return "出勤打刻が完了しました。\n 本日の打刻時間： " + convertedDate +" " + dateToFormatString(now, '%HH%:%mm%:%ss%');
     }
     else{
         return "出勤済です。\n 本日の打刻時間： " + convertDate(signedIndate)+" "+ dateToFormatString(signedIndate, '%HH%:%mm%:%ss%');
     }

例外：出勤済ならば「出勤してます」と返す

     else{
         return "出勤済です。\n 本日の打刻時間： " + convertDate(signedIndate)+" "+ dateToFormatString(signedIndate, '%HH%:%mm%:%ss%');
     }

Barusu
「 function getArrayNum(){} で重複打刻をしていないか？とか判別しているのだけど、これのソースはチョット秘密。
sheet.appendRow([convertedDate,userName,userId,now]);
出勤打刻のロジックはシンプルで、appendRowメソッドで行を追加してる。
値は(日付,Slackのユーザー名,ユーザーId,今の時間(hh:mm:ss))」

c. 中抜けを受け取ってスプレッドシートに記載する

     case 'Interval' :
     //中抜け時間を登録する
     if (arrayNum != null ){
        if( sheet.getRange(userDataRowNum, 6).getValue() == '' ){
     sheet.getRange(userDataRowNum, 6).setValue(timeInterval);
     sheet.getRange(userDataRowNum, 6).setNumberFormat('H:mm')
     
     return "中抜け時間を登録しました。本日の中抜け時間： " + timeInterval;
        }
       else{
       return "Not";   
        }
     }
     else {
     return "今日はまだ打刻していません。打刻をしてください。";
     }

例外：未出勤ならば「出勤してません」と返す

     else {
     return "今日はまだ打刻していません。打刻をしてください。";
     }

Barusu
「 sheet.getRange(userDataRowNum, 6).setValue(timeInterval);
sheet.getRange(userDataRowNum, 6).setNumberFormat('H:mm')
この2つで中抜け時間を追加している。
わざわざgetRangeでスプレッドシートの範囲を指定しているのは、勤怠データの持ち方を↓画像のように1レコードにしたかったから」

例外：時刻データが不正の場合は「形式が正しくありません」と返す

Barusu
「チョットこれも秘密で。申し訳ない」

d. 退勤打刻を受け取ってスプレッドシートに記載する

     case 'SignOut' :
     
     if (arrayNum != null ){
       
       if( sheet.getRange(userDataRowNum, 5).getValue() == '' ){
     sheet.getRange(userDataRowNum, 5).setValue(now);
     sheet.getRange(userDataRowNum, 5).setNumberFormat('YYYY/MM/DD HH:mm')     
     sheet.getRange(userDataRowNum, 8).setValue('=(E'+userDataRowNum+'-D'+userDataRowNum +')-F'+userDataRowNum);
     sheet.getRange(userDataRowNum, 8).setNumberFormat('[hh]:mm')
     sheet.getRange(userDataRowNum, 9).setValue('=minute(H' + userDataRowNum + ')+(hour(H' + userDataRowNum + ')*60)')
    
     return "退勤打刻が完了しました。\n 本日の打刻時間： " + convertedDate + " " + dateToFormatString(now, '%HH%:%mm%:%ss%');
       }
       else{
         return "Not";
       }
       }
     else {
     return "今日はまだ出勤打刻をしていません。";
     }
     break

Barusu
「sheet.getRange(userDataRowNum, 5).setValue(now);
sheet.getRange(userDataRowNum, 5).setNumberFormat('YYYY/MM/DD HH:mm')
sheet.getRange(userDataRowNum, 8).setValue('=(E'+userDataRowNum+'-D'+userDataRowNum +')-F'+userDataRowNum);
sheet.getRange(userDataRowNum, 8).setNumberFormat('[hh]:mm')
sheet.getRange(userDataRowNum, 9).setValue('=minute(H' + userDataRowNum + ')+(hour(H' + userDataRowNum + ')*60)')

ここでやっていることは
退勤を打刻→表示形式を変更→横のセルに勤務時間計算用の関数を追加→表示形式を変更→全体時間計算用の数式を追加
の処理。
シート側に数式を予め置いておいたり、Arrayformula関数を使っても良いんだけど、それだとシートがすっごく重くなるのでおすすめしない。
また、GASで計算して処理しても良いんだけど、それだとGASの処理が重くなるのがデメリット。
まーどっちでも良いんだけどね！めんどくさくて横着した！」

例外：未出勤ならば「出勤してません」と返す

     else {
     return "今日はまだ出勤打刻をしていません。";
     }

例外：退勤済ならば「退勤済ですが上書きしますか？」と返す

Barusu「これもねー。チョットお見せできませんのです。申し訳ない」

実際にBotへ打刻してみる

Barusu
「GASを保存して、再度APIとして公開して...
SlackでBotにチャット送れば動くはず」

Barusu「おー動いた。さてさて今回はココまでかな」

Barusu
「さて次回はユーザー管理機能だー。ここもあんまりソース見せられないのが残念である」

次回予告

今回のソースは一部お見せできないやつがあるのでやっぱ分かりづらいよなーと反省してます！
でもこれ以上書くと多分怒られるからな...今回で一番頑張ったとこだけど、故に価値があるのでシェアできないというジレンマがあるんですよね。
参考までに、実際に作ったとき、今日の内容に至るまでの実稼働時間は24h,2週間くらい。
他のPJと平行して手探りしつつ、このSlackBotを作ったぞ！まじで誰か褒めて！
次回、「ユーザー管理なんていちいちやってられっか」。デュエルスタンバイ！

この記事が気に入ったらサポートをしてみませんか？