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

Barusu

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

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

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

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：そして伝説へ...

喫煙所にて (2020/8/某日)

Barusu
「ひひーん」

法務部長「あーBarusuくんおつかれ」

Barusu
「ういっすおつかれっす」

法務部長
「例のアレできた感じ？」

Barusu
「ういっすバッチリっす」

法務部長
「まじで！ありがとう」

Barusu
「こんな感じになりました」

▼機能実装したもの
1. 打刻機能
　a.  出勤、退勤、中抜け に反応する仕組みを作る
　b. 出勤打刻を受け取ってスプレッドシートに今の時刻を記載する
　　例外：出勤済ならば「出勤してます」と返す
　c. 中抜けを受け取ってスプレッドシートに記載する
　　例外：未出勤ならば「出勤してません」と返す
　　例外：時刻データが不正の場合は「形式が正しくありません」と返す
　d. 退勤打刻を受け取ってスプレッドシートに記載する
　　例外：未出勤ならば「出勤してません」と返す
　　例外：退勤済ならば「退勤済ですが上書きしますか？」と返す
2.ユーザー管理機能
　a. 初めて投稿したユーザーは勤怠ユーザーリストに追加する
　b. Gsuiteからメールアドレスを取得し、Slackユーザーリストと紐付ける
3. リマインド機能
　a. 今日が平日ならば→指定時間帯に未打刻のユーザーにDMを送る
　b. 退勤をしていないユーザーにDMを送る
4. 勤怠修正機能
　a. Googleフォームで修正申請を受付
　b. 指定の承認者のみが承認できるようにする
　b. 別の勤怠修正フォームから承認された勤怠データとデータを合流させる
5. アラート機能
　a. 月末残業予測値を算出し、しきい値を超えた者はアラートを出す 

Barusu
「データモデルはこんな感じっす」

画像1

Barusu
「実際に動かすとこんな感じっす」

画像2

法務部長
「おお」

Barusu「んで、月ごとの勤怠表はこんな感じっす」

画像3

法務部長「おおおおおおおお！！すげー」

Barusu
「Doya...」

法務部長「すごいね。ありがとう。
じゃこれを少人数で使ってみてテストしてみようか」

Barusu
「そうですね、負荷とかデータ量増えたときにちゃんと動くのか知りたいですし、まずはバックオフィスの数人で運用テストしましょう」

法務部長
「ユーザー向けマニュアルは作ってないよね？
こっちで作っとくわ」

Barusu
「ありがてぇっす。とはいえ、Botに話しかけるのと、申請フォーム使うだけですけどね」

法務部長「まぁ伝わるよね」

2週間後...

Barusu
「ひひーん」

法務部長「あーBarusuくん。勤怠のことなんだけどさ」

Barusu「あいっす。なんかありました？」

法務部長「いや、特に動きは問題なさそうなんだけど」

Barusu「けど？」

法務部長
「勤怠表からCSV出力の機能ってないよね...？」

Barusu
「ん？スプレッドシートならシートを選択してCSV出力すれば出ますけど...？」

法務部長
「それさ、試してみたんだけど文字化けしちゃってたんだよね」

Barusu
「なん...............だと...............」

(試してみた)

Barusu
「アッほんまや！日本語だけ文字化けしとる！！！！」

Barusu
「つまりは文字コードか...」

再び、喫煙所にて(2020/09/某日)

Barusu
「法務部長さぁん」

法務部長
「なんでしょ」

Barusu
「勤怠表の件ですけど、CSV出力したあと、ファイルの文字コードを変更してくれれば解決できるんですがそれでも良いですかね？」

法務部長
「うーん、それしか方法がないなら仕方ないなぁ。
けど、最終的には40人くらいの業務委託が勤務表を出力することになるから、手間はなるべく減らしてあげたいとこだよね」

Barusu
「そっすよねぇー。わかりました、ちっと頑張って解決します」

法務部長「！？」

CSV出力する機能を作る

Barusu
「さて、お得意の問題解決でござる」

Barusu
「[Googleスプレッドシート CSV 文字化け] でググってみたところ、↓の記事がHitする」

Barusu
「[Googleスプレッドシート CSV 文字化け GAS]で検索すると↓がHitする。岩澤さんのNoteやな。素敵な方よね」

Barusu
「ふむふむ。やはりGASでファイル出力するのが良いな。
実装イメージは、シートのボタンをクリックしたらGASが起動してファイルDLされる的な感じだな。せっかくだし、最近勉強してるHtmlサービス使ってみよっと」

▼ExportCSV.gs
function onOpen() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const entries = [{
   name : 'CSV (Shift JIS)',
   functionName : 'downloadSJIS_CSV'
 }];
 spreadsheet.addMenu('ダウンロード', entries);
}

function downloadSJIS_CSV() {
 // 開いてるシートのデータを取得
 const sheet = SpreadsheetApp.getActiveSheet();
 const sheetName = sheet.getName();
 const values = sheet.getDataRange().getValues();
 var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();

 var returnData =[];

 for(var k = 0; k < data.length; k++) {
   Logger.log('Before:' + data[k][0]);
   try {
     data[k][0] = dateToFormatString(data[k][0],'%YYYY%/%MM%/%DD%');
   }
   catch(e){}
   try {
     data[k][1] = dateToFormatString(data[k][1],'%HH%:%mm%');
   }
   catch(e){}
   try {
     data[k][2] = dateToFormatString(data[k][2],'%HH%:%mm%');
   }
   catch(e){}
   try {
     data[k][3] = dateToFormatString(data[k][3],'%HH%:%mm%');
   }
   catch(e){}
   try {
     data[k][4] = dateToFormatString(data[k][4],'%HH%:%mm%');
   }
   catch(e){}
   try {
     data[k][5] = dateToFormatString(data[k][5],'%HH%:%mm%');
   }
   catch(e){}
   Logger.log('After:' + data[0][k]);
 }
 
 // 文字列のままテンプレートに渡して html を表示
 const template = HtmlService.createTemplateFromFile('download_dialog');
 template.values = JSON.stringify(data)
 template.name = setFileName();
 SpreadsheetApp.getUi().showModalDialog(template.evaluate(), 'CSV (Shift JIS)');
}

function setFileName(){
 var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
 var fileName = dateToFormatString(data[0][0],'%YYYY%/%MM%') + '_'  + data[0][2];
 return fileName;
} 

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);
}
<!DOCTYPE html>
<html>
 <head>
   <base target="_top">
 </head>
 <body>
   <a id="download" href="#" download="<?= name ?>.csv" target="_blank">ダウンロード</a>
   <script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/4.3.7/papaparse.js" integrity="sha256-QvijOmxLUGxTuoxamQikzFRUJ5hFoDOG71tl2M+LULw=" crossorigin="anonymous"></script>
   <script src="https://cdnjs.cloudflare.com/ajax/libs/encoding-japanese/1.0.28/encoding.min.js" integrity="sha256-vvOjQJ89zzmmngTqmXugSMR1JFKbDZuOxHMP3Ia8EdA=" crossorigin="anonymous"></script>
   <script>
     const values = JSON.parse(<?= values ?>)

     // CSV 文字列にする
     const csv = Papa.unparse(values)

     // encoding.js を使って csv 文字列を sjis array に変換
     const sjisCodeArray = Encoding.convert(csv, {
       from: 'UNICODE',
       to: 'SJIS',
       type: 'array'
     })

     // sjis array を blob に変換
     const uint8Array = new Uint8Array(sjisCodeArray)
     const blob = new Blob([ uint8Array ], { type: 'text/csv' })

     // URL を a タグにセット
     window.URL = window.URL || window.webkitURL
     document.getElementById("download").href = window.URL.createObjectURL(blob)
   </script>
 </body>
</html>

※こちらのソースは以下記事より拝借し、手を加えております

Barusu
「あとは適当にボタン配置して」

画像5

Barusu
「クリックすればポップアップが出て、リンクをクリックすればDLできる」

画像4

Barusu
「できましたー」

法務部長
「おーありがとう。これ大変だったでしょ」

Barusu
「あーわかります？」

法務部長
「Barusuくんがドヤ顔してるときって、大変なもの終わらせたあとが多いからねぇ」

Barusu
「さすがですね🐴」

法務部長
「じゃとりあえずこれでテスト継続してみるよー」

Barusu「あいーす。またバグか問題が出て来たら教えてください」

法務部長「毎度たすかるね」

Barusu「まぁ、テストっていうのはバグを見つけるためにあるんすよね。
むしろデバッグあんまりやってなくって申し訳ないす」

法務部長「まぁまぁ。まだこれから先もあるし、今後ともよろしく」

Barusu「ひひーん」

次回予告

テストも進んで、割と思ったよりは動いているみたいだ！
CSV出力以外にも問題がいくつか出てきたんだが、事実ベースで書くと技術的な解説が散らばるのでかなり端折ったぞ！
いよいよ次回がラスト！
次回、「やっぱ、SaaS契約しません？」。デュエルスタンバイ！

画像6


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