Slack×GASで作った勤怠Botの話#8
UG Advent Calendar 2020 の8日目の記事です。
どうもbarusuです。知らない方は初めまして。
昨日の続きです。
フィクションと思って読んでいただければ幸いです。
Slack×GASで作った勤怠Botの話 #8
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
「ざっくり書くとこんな感じ」
Barusu
「この図作るのに1時間かかったわ…」
Barusu
「ユーザー→BotへのDMから打刻したデータは[打刻マスタ]の[当日打刻データ]シートに格納される。緑の枠で囲った範囲がそれ。
特に解説してなかったけど、当日打刻データは日付が変わったらGASが起動して打刻マスタに転記し、当日打刻データを初期化するようにしている。
当日打刻状況はリマインド機能で使うシート、当月勤怠状況シートは今回実装する、勤怠予測アラートのためのシート」
勤怠修正機能
Barusu
「さて、勤怠修正機能についてだけど。
はーこんなん作りたくねぇわ…
全員が正しく勤怠入れてくれればこんなものいらないのに」
Barusu
「気を取り直して…
今回実装する承認フォームは別のスプレッドシートに格納する。
一緒のスプレッドシートにすると重くなるというのと、承認者がアクセスしてくるのでアクセス権的にも分けた方が望ましい。
入力のインターフェイスについてはGoogleフォームを採用した。
ホントならSlackBotかSlackワークフローからやりたかったんだけど…
その場合の処理を追加で書くには時間が足りんかったので、一旦こうした。
承認されたデータは[打刻マスタ]と合算する必要があるから、それ用のGASを書いて、別のスプレッドシートに転記するようにした。
緑で囲った部分がそれ」
Googleフォームで修正申請を受付
Barusu
「仕組みとしては簡単。
↓みたいなGoogleフォームを作って、スプレッドシートに記録する」
Barusu
「フォーム回答を受け付けるスプレッドシートは↓のようにしている。
スプレッドシート:[打刻マスタ]からユーザーリスト:[[参照]UserInfo]をImportrange関数で読み込み、フォーム受信時にGASを用いて、データを[承認用シート]に転記する仕組みにした。
で、承認されたデータを[承認済]のシートでQuery関数を使って表示しているので、これを別のGASで値取り込みをし、スプレッドシート:[打刻マスタ]からも値を取得、また別のスプレッドシート:[勤怠マスタ]に転記処理をしている」
Barusu
「フォーム申請時とかのソースは共有出来るので、雑に記載しときますわ」
function getLastRowWithValue() {
var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var sheetMaster = spreadSheet.getSheetByName('勤怠マスタ');
const columnBVals = sheetMaster.getRange('A:A').getValues();
const LastRow = Number(columnBVals.filter(String).length); //空白を除き、配列の数を取得
}
function getFormData(){
var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var sheetMaster = spreadSheet.getSheetByName('勤怠マスタ');
const columnBVals = sheetMaster.getRange('A:A').getValues();
const LastRow = Number(columnBVals.filter(String).length); //空白を除き、配列の数を取得
var arraySheet = sheetMaster.getRange(LastRow,1,1,8).getValues()[0];
Logger.log(arraySheet);
var attendDate = Utilities.formatDate(arraySheet[0],"JST", "yyyy'/'MM'/'dd"); //タイムスタンプから日付を取りたい
var attendTime = Utilities.formatDate(arraySheet[0],"JST", "HH':'mm':'ss");
var attendType = arraySheet[2];//種別
switch (attendType){
case '退勤_現在時刻':
case '[出勤_現在時刻]':
var attendDate = array[0].split(",");//タイムスタンプから取得した時刻 もしかしたらDate型の変換が必要かも
var attendTime = array[2].split(",");//勤怠日付
var hogetime = Utilities.formatDate(attendDate, "JST", "yyyy'/'MM'/'dd'/' HH'時'mm'分'ss'秒'");
Logger.log(array);
Logger.log(attendDate);
Logger.log(hogetime);
break;
case "出勤(過去分修正)":
case "退勤(過去分修正)":
var attendDate = array[3].split(",");//タイムスタンプから取得した時刻 もしかしたらDate型の変換が必要かも
var attendTime = array[4].split(",");//勤怠日付
break;
case "セット打刻":
break;
default :
Logger.log("default");
}
}
function onFormSubmit(e){
Logger.log(e);
var range = e.range;
var sheetApprove = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('承認用シート');
//例えばこんな処理
range.setVerticalAlignment("top"); //挿入されたセルを全て上揃えにする
var hoge = range.getRow();
Logger.log(hoge);
var array = e.values;
var attendDataId = e.values[0].split(",") + " " + array[2].split(",");//タイムスタンプから日付を取りたい
var attendType = array[2];//種別
//attendType = attendType.replace('[','');
//attendType = attendType.replace(']','');
Logger.log('----array---');
Logger.log(array[0]);//タイムスタンプ
Logger.log(array[1]);//メールアドレス
Logger.log(array[2]);//勤怠種別
Logger.log(array[3]);//日付(出勤)
Logger.log(array[4]);//時刻(出勤)
Logger.log(array[5]);//日付(退勤)
Logger.log(array[6]);//時刻(退勤)
Logger.log(array[7]);//出勤日
Logger.log(array[8]);//出勤時刻
Logger.log(array[9]);//退勤時刻
Logger.log(array[10]);//対象日
Logger.log(array[11]);//休憩時間
Logger.log('----');
Logger.log(attendType);
// 種別に応じてDate,Timeの取る場所を変更する
switch (attendType){
case "出勤(過去分修正)":
var appendDate = array[0];
var mail = array[1];
var date = array[3];//日付(出勤)
var signIn = array[3] +' '+ array[4];//時刻(出勤)
var signOut = '';
var interval = '';
cloneValueAppend(appendDate,mail,attendType,date,signIn,signOut,interval);
Logger.log(mail + attendType + date + signIn + signOut + interval);
break;
case "退勤(過去分修正)":
var appendDate = array[0];
var mail = array[1];
var date = array[5];//日付(退勤)
var signIn = '';
var signOut = array[5] +' '+ array[6];//時刻(退勤)
var interval = '';
cloneValueAppend(appendDate,mail,attendType,date,signIn,signOut,interval);
break;
case "セット打刻":
var appendDate = array[0];
var mail = array[1];
var date = array[7];//出勤日
var signIn = array[7] +' '+ array[8];//出勤時刻
var signOut = array[7] +' '+ array[9];//退勤時刻
var interval = '';
cloneValueAppend(appendDate,mail,attendType,date,signIn,signOut,interval);
break;
case "休憩時間(過去分修正)":
var appendDate = array[0];
var mail = array[1];
var date = array[10];//出勤日
var signIn = '';
var signOut = '';
var interval = array[10] +' '+ array[11];//休憩時間
cloneValueAppend(appendDate,mail,attendType,date,signIn,signOut,interval);
break;
default :
Logger.log("default");
}
}
function testMethod(){
var testData = "";
onFormSubmit(testData);
}
function testForUpdate(){
var sheetApprove = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('承認用シート');
var updateValueMail = 'hoge@domain.com';
var updateValueKind = 'test';
var updateValueDate = '2020/08/17';
var updateValueSignIn = '1:00'
var updateValueSignout = '12:00';
var lastRow = sheetApprove.getLastRow()+1;
var formula = '=vlookup(D' + lastRow + ','+ "'[参照]UserInfo'!B:J,9,false)";
sheetApprove.appendRow(['','','メールアドレス','勤怠種別','日付(出勤)','時刻(出勤)','時刻(退勤)']);
sheetApprove.appendRow(['',formula,updateValueMail,updateValueKind,updateValueDate,updateValueSignIn,updateValueSignout]);
}
function cloneValueAppend(appendDate,mail,attendType,date,signIn,signOut,interval){
var sheetApprove = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('承認用シート');
// var updateValueMail = 'hoge@domain.com';
// var updateValueKind = 'test';
// var updateValueDate = '2020/08/17';
// var updateValueSignIn = '1:00'
// var updateValueSignout = '12:00';
var lastRow = sheetApprove.getLastRow()+1;
var formulaSlackID = '=vlookup(F' + lastRow + ','+ "'[参照]UserInfo'!B:J,3,false)";
var formulaApprover = '=vlookup(F' + lastRow + ','+ "'[参照]UserInfo'!B:J,9,false)";
var formulaUser = '=vlookup(F' + lastRow + ','+ "'[参照]UserInfo'!B:J,2,false)";
//sheetApprove.appendRow(['','','メールアドレス','勤怠種別','日付','出勤時刻','退勤時刻','休憩時間']);
sheetApprove.appendRow(['',formulaApprover,formulaUser,formulaSlackID,appendDate,mail,attendType,date,signIn,signOut,interval]);
}
function dateToFormatString(date, fmt, locale, pad) {
// %fmt% を日付時刻表記に。
// 引数
// date: Dateオブジェクト
// fmt: フォーマット文字列、%YYYY%年%MM%月%DD%日、など。
// locale:地域指定。デフォルト（入力なし）の場合はja-JP（日本）。現在他に対応しているのはen-US（英語）のみ。
// pad: パディング（桁数を埋める）文字列。デフォルト（入力なし）の場合は0。
// 例：2016年03月02日15時24分09秒
// %YYYY%:4桁年（2016）
// %YY%:2桁年（16）
// %MMMM%:月の長い表記、日本語では数字のみ、英語ではMarchなど（3）
// %MMM%:月の短い表記、日本語では数字のみ、英語ではMar.など（3）
// %MM%:2桁月（03）
// %M%:月（3）
// %DD%:2桁日（02）
// %D%:日（2）
// %HH%:2桁で表した24時間表記の時（15）
// %H%:24時間表記の時（15）
// %h%:2桁で表した12時間表記の時（03）
// %h%:12時間表記の時（3）
// %A%:AM/PM表記（PM）
// %A%:午前/午後表記（午後）
// %mm%:2桁分（24）
// %m%:分（24）
// %ss%:2桁秒（09）
// %s%:秒（9）
// %W%:曜日の長い表記（水曜日）
// %w%:曜日の短い表記（水）
var padding = function(n, d, p) {
p = p || '0';
return (p.repeat(d) + n).slice(-d);
};
var DEFAULT_LOCALE = 'ja-JP';
var getDataByLocale = function(locale, obj, param) {
var array = obj[locale] || obj[DEFAULT_LOCALE];
return array[param];
};
var format = {
'YYYY': function() { return padding(date.getFullYear(), 4, pad); },
'YY': function() { return padding(date.getFullYear() % 100, 2, pad); },
'MMMM': function(locale) {
var month = {
'ja-JP': ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12'],
'en-US': ['January', 'February', 'March', 'April', 'May', 'June',
'July', 'August', 'September', 'October', 'November', 'December'],
};
return getDataByLocale(locale, month, date.getMonth());
},
'MMM': function(locale) {
var month = {
'ja-JP': ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12'],
'en-US': ['Jan.', 'Feb.', 'Mar.', 'Apr.', 'May', 'June',
'July', 'Aug.', 'Sept.', 'Oct.', 'Nov.', 'Dec.'],
};
return getDataByLocale(locale, month, date.getMonth());
},
'MM': function() { return padding(date.getMonth()+1, 2, pad); },
'M': function() { return date.getMonth()+1; },
'DD': function() { return padding(date.getDate(), 2, pad); },
'D': function() { return date.getDate(); },
'HH': function() { return padding(date.getHours(), 2, pad); },
'H': function() { return date.getHours(); },
'hh': function() { return padding(date.getHours() % 12, 2, pad); },
'h': function() { return date.getHours() % 12; },
'mm': function() { return padding(date.getMinutes(), 2, pad); },
'm': function() { return date.getMinutes(); },
'ss': function() { return padding(date.getSeconds(), 2, pad); },
's': function() { return date.getSeconds(); },
'A': function() {
return date.getHours() < 12 ? 'AM' : 'PM';
},
'a': function(locale) {
var ampm = {
'ja-JP': ['午前', '午後'],
'en-US': ['am', 'pm'],
};
return getDataByLocale(locale, ampm, date.getHours() < 12 ? 0 : 1);
},
'W': function(locale) {
var weekday = {
'ja-JP': ['日曜日', '月曜日', '火曜日', '水曜日', '木曜日', '金曜日', '土曜日'],
'en-US': ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'],
};
return getDataByLocale(locale, weekday, date.getDay());
},
'w': function(locale) {
var weekday = {
'ja-JP': ['日', '月', '火', '水', '木', '金', '土'],
'en-US': ['Sun', 'Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat'],
};
return getDataByLocale(locale, weekday, date.getDay());
},
};
var fmtstr = ['']; // %%（%として出力される）用に空文字をセット。
Object.keys(format).forEach(function(key) {
fmtstr.push(key); // ['', 'YYYY', 'YY', 'MMMM',... 'W', 'w']のような配列が生成される。
})
var re = new RegExp('%(' + fmtstr.join('|') + ')%', 'g');
// /%(|YYYY|YY|MMMM|...W|w)%/g のような正規表現が生成される。
var replaceFn = function(match, fmt) {
// match には%YYYY%などのマッチした文字列が、fmtにはYYYYなどの%を除くフォーマット文字列が入る。
if(fmt === '') {
return '%';
}
var func = format[fmt];
// fmtがYYYYなら、format['YYYY']がfuncに代入される。つまり、
// function() { return padding(date.getFullYear(), 4, pad); }という関数が代入される。
if(func === undefined) {
//存在しないフォーマット
return match;
}
return func(locale);
};
return fmt.replace(re, replaceFn);
}Barusu
「きたねぇソースだ」
アラート機能
Barusu
「勤怠実績から、月末着地時点の勤怠時間を予測するシートを作る。
これはほとんど関数で出来ている」
Barusu
「このシートを読み込んで、しきい値を超えているものがあれば管理者にメールを飛ばすGASを日次で起動している。
ここで工夫したのは、通常の時刻型でデータを持っていると計算がやりづらいので、一旦UNIXTIMEに変更、差分を数値で取得し、分に変更することで数値化した」
Barusu
「はい、ということでね。
だいぶ駆け足だったけどこれで実装は出来た。やー疲れた…
んじゃ週明けにでも法務部長に報告するかな…さーて寝るか」
―――――――2020/08/15 未明,機能実装完了!!
次回予告
諸々のデータモデルを整理して、とりあえず動くものは出来た！！
死ぬ気で作ったからどうせ粗があるだろうけど、これからテストだから細けぇこたぁいいんだ！
次回、「テストっていうのはバグを見つけるためにあるんすよ」。デュエルスタンバイ！
この記事が気に入ったらサポートをしてみませんか？