Google Apps Scriptで出退勤の発報botを作った
こんばんは、葛の葉です。
ちょっと前に、GoogleAppsScriptでdiscordのbotを作ったのですが、なんか社長が偉く気に入ったらしくブログの記事に書いてちょんまげって言われて、まぁ、書くことにしました。
どういうbotを作ったのか
私の会社では下図のようにGoogleSpreadSheetsを使った出退勤の表がありまして…
私、葛の葉は置いといて、他の人たちがまぁインターン生さんです。桃太郎さんとかキャプテン鬼ヶ島さんがそうですね。インターン生さんたちに、この表に時刻を書き込ませて、シフトの決定をしてもらっています。また、シフトを組むのもインターン生さんが決めることになっています。そのため、インターン生さんが好きな時に好きに出勤日と日時を決められるということができます。
また、お仕事の連絡にチャットツールを用いておりまして、それがDiscordなんですね。
今回作ったbotは、インターン生さんがその出退勤表に一週間以内に出勤の予定を書き込んだ時に、DiscordBotが私宛に発報するというものになります。
10/22現在で、桃太郎さんが10/23 10:00-16:00として出社予定日時を記載した場合
「発報しているシーン」
「チャットツールDiscordに新着メッセージが来ているシーン」
ちなみに、セルをコピー＆ペーストで張り付けた場合は内容: [予定なし] → [予定なし]と表示されちゃいます。これはちょっと直し方わからんです。ざんねん。
botの作り方
GoogleSpreadsSheetsのGoogleAppsScriptとDiscordのWebhookの連携。
これは過去記事に書いているので、そちらを参照してほしいです。
そちらのコードのうち、function discord(message)の関数を使います。他三つの関数は使いません。(似たようなのはつくるんですけどね）
// Discordに送信する関数
function discord(message) {
const url = 'さっきコピーしたwebhooksのURL';
const token = 'さっきのURLの中のtokenの文字列';
const channel = '#general';
const text = message;
const username = 'bot';
const parse = 'full';
const method = 'post';
const payload = {
'token' : token,
'channel' : channel,
"content" : text,
'username' : username,
'parse' : parse,
};
const params = {
'method' : method,
'payload' : payload,
'muteHttpExceptions': true
};
response = UrlFetchApp.fetch(url, params);
}
編集されたセルの行と列の番号を取得する。
GoogleSpreadSheetsにはfunction onEdit(e){}というものがあります。詳しくは以下のリンクを参照してください。この関数はそのスプレッドシート内(ExcelでいうBook内)で編集された様々な情報を取得できる関数です。
Event Objects | Apps Script | Google Developers
function onEdit(e){}の関数内でe.range.getRow();関数を使用すると行番号、e.range.getColumnIndex()関数を使用すると列番号を取得できます。
onEdit(e)関数はそのスプレッドシートのすべてのシートが対象になります。つまり、新しい月のシートを作成してもこのプログラムは動作します。ﾔｯﾀｾﾞ。
function onEdit(e){
//e.****は更新のシートやセルの情報
var change_row_num = e.range.getRow();
var change_colum_num = e.range.getColumnIndex();
}
編集された氏名と日付を取得する。
編集された行と列が取得できたので、それを元に編集された個所の日付と氏名を取得しましょう。まずは氏名から。
var change_name = SpreadsheetApp.getActiveSheet().getRange(2, change_colum_num).getValue();
SpreadsheetApp.getActiveSheet().getRange(行, 列).getValue()という関数を使用して、編集されたシートから氏名を取得します。.getRange(行, 列)の行は行数を、列は列番号を指定します。今回、行を2としているのは、該当のシートの二行目が名前欄となっているからです。今回のコードをそのまま使うなら、この辺りを変更しないとダメかも？
次に日付になります。
var unformat_change_date = SpreadsheetApp.getActiveSheet().getRange(change_row_num, 2).getValue();
var change_date = Utilities.formatDate(unformat_change_date, "Asia/Tokyo", "yyyy/MM/dd");
また、編集されたシートから日付を取得します。列を2としているのは、該当のシートの二列目が日付欄となっているからです。ただ、このまま値を取得しても、それをdate型として扱うことができません。編集された日付と一週間以内を区別するためにdate型にしてあげる必要があります。そのため、Utilities.formatDate(unformat_change_date, "Asia/Tokyo", "yyyy/MM/dd")という関数を使用します。以下も参照してみて下さい。
日時の設定 | AdWords scripts | Google Developers
当日を含めた一週間の日付データを取得しリストに格納する。
新しい関数を作ります。関数にしなくてもよいですが、とりあえず機能としては分けたかったので別関数にしました。
書き込み時点でのnew Date()を取得します。それにより、書き込み時点での当日を取得しています。それをloop文回し、当日を含めた8日分取得します。ちなみにloop文でvar i = 0にしようとしたらエラー吐きました。なんでだろう？
function this_week_days_list() {
var today = new Date();
var this_week = new Array();
for(var i = 1; i < 9; i++){ /* var i = 0 ではだめらしい */
var one_day = new Date(today.getYear(), today.getMonth(), today.getDate() + i - 1);
var format_today = Utilities.formatDate( one_day, "Asia/Tokyo", "yyyy/MM/dd");
this_week.push(format_today);
};
return this_week
};
この関数をfunction onEdit(e){}内で呼び出してあげてます。
var this_week = this_week_days_list();
日付が当日を含めた一週間(8日間)の中に存在しているかを確認する。
this_week(list)に当日を含めた一週間(8日間)が格納されています。この中にchange_date(日付)が入っているかを確認します。リスト.indexOf(date型)とすると、引数の値がリスト内にあるかどうかを判定し、あれば0,なければ-1を返します。詳しくは以下のリンクを参照してください。
indexOf メソッド (Array) (JavaScript)
リスト内にその日付がないなら-1が返されます。ですので、条件分岐として-1の時は何もしない、という命令をします。何もしない命令文は;らしいです。
if(this_week.indexOf(change_date) == -1){
; // 何もしない
}else{
.............-1じゃなかった時の実行文...............
}
セルの編集前の値と編集後の値を取得する。
前述のif文のelse以降は、編集された日付が一週間以内のものであるということになり、本格的な動作はここからになります。
e.oldValueとe.valueを使うことで、セルの編集前の値と編集後の値を取得することができます。詳しくは以下のリンクを参照してください。セルの中身が空の場合はundefinedになりますので、その場合は[予定なし]という値で変数を書き換えます。
Event Objects | Apps Script | Google Developers
//編集前の値を取得
var old_value = e.oldValue;
if (old_value != undefined){;}else{
old_value = "[予定なし]"
};
//編集後の値を取得
var change_value = e.value;
if (change_value != undefined){;}else{
change_value = "[予定なし]"
};
Discordのwebhookを使って送信する。
必要な条件や情報は揃ったのであとはstr型としてメッセージを作ります。
var message = "";
message += "名前: " + change_name + " ";
message += "日付: " + change_date + " ";
message += "内容: " + old_value + " → " + change_value;
discord_bot(message);
GASを通して特定のユーザーにメンションを送りたい場合。
APIを通してdiscordで送信するとき、特定の人にメンションとして送りたい場合は開発者モード（デベロッパモード）になる必要があります。やり方は以下のリンクの通りです。開発者モードにて、メンションを送りたい相手のユーザーIDを取得します。数字で18桁のようです。
messageにこのような形で入れてあげてください。<@xxxxxxxxxxxxxxxxxx>(xxxxxxxxxxxxxxxxxxは18桁のユーザーID)
var message = "";
message += "<@xxxxxxxxxxxxxxxxxx> ";
message += "名前: " + change_name + " ";
message += "日付: " + change_date + " ";
message += "内容: " + old_value + " → " + change_value;
discord_bot(message);
ソースコードはこんな感じです。
function onEdit(e) {
//e.****は更新時のシートやセルの情報
var change_row_num = e.range.getRow();
var change_colum_num = e.range.getColumnIndex();
try{
var change_name = SpreadsheetApp.getActiveSheet().getRange(2, change_colum_num).getValue();
}catch(e){
//log用日付の取得
var today = Utilities.formatDate( new Date(), "Asia/Tokyo", "yyyy/MM/dd HH:mm:ss");
Logger.log("名前の参照できない箇所での操作を検知" + today + "\n");
return
}
//GoogleSpreadSheetの日付が特殊な記載方法ため、フォーマット例（Tue Jul 17 00:00:00 GMT+09:00 2018）
try{
var unformat_change_date = SpreadsheetApp.getActiveSheet().getRange(change_row_num, 2).getValue();
var change_date = Utilities.formatDate(unformat_change_date, "Asia/Tokyo", "yyyy/MM/dd");
}catch(e){
var today = Utilities.formatDate( new Date(), "Asia/Tokyo", "yyyy/MM/dd HH:mm:ss");
Logger.log("日付の参照できない箇所での操作を検知" + today + "\n");
return
};
//今週の日付８つ分の日付（当日を含むため８日）が入ったリストを取得する。
var this_week = this_week_days_dict();
//もし日付内に存在しなければ何もしない。
if(this_week.indexOf(change_date) == -1){
;
}else{
//編集前と編集後のセルの値を取得
var old_value = e.oldValue;
if (old_value != undefined){;}else{
old_value = "[予定なし]"
};
var change_value = e.value;
if (change_value != undefined){;}else{
change_value = "[予定なし]"
};
var message = ""
message += "<@xxxxxxxxxxxxxxxxxx> ";
message += "名前: " + change_name + " ";
message += "日付: " + change_date + " ";
message += "内容: " + old_value + " → " + change_value;
discord_bot(message);
};
};
function this_week_days_dict() {
var today = new Date();
var this_week = new Array();
for(var i = 1; i < 9; i++){
var one_day = new Date(today.getYear(), today.getMonth(), today.getDate() + i - 1);
var format_today = Utilities.formatDate( one_day, "Asia/Tokyo", "yyyy/MM/dd");
this_week.push(format_today);
};
return this_week
};
function discord_bot(message) {
省略
}
なんかtryとcatchが増えてますが、実際はそういう感じで使ってる感じです。Logger.logをdiscord_botに変更してもらってもいいのかも知れないです。そうしたらDiscordにエラーのログが出力されます。
あとがき
botを作ったきっかけ
前述の通り、出勤の日付は好きな時にインターン生さんが自分で決めることが出来ます。そこで少し困ったのが当日のAM01:00やAM02:00に出勤の予定を記入するという人がいました。当日の朝に更新されても、こちらも準備とかがあるので急な決定は困ったところがありました。
また、会社の事務所がスペースが狭くインターン生さんは少人数しかいれられないのですが、前日までは人数的にスペースの問題はないと思っていたら、当日の朝確認をしてみたら予定が結構増えていてビックリすることがありました。
そのため、インターン生さんにチャットツールを使って「出勤予定日を早めに連絡してほしい」旨をお伝えしていたのですが、伝えた当日から連絡がないということがあり、ホトホト困りました。コミュニケーションがとても難しいということ実感した瞬間でもありました。
であるならば、botを使って、シートに更新があったタイミングでデータを取得して、発報してもらうような仕組みを考えました。これで少なくとも私の確認が漏れるということはなくなります。
また、Discordというチャットツールとして出力されるので、編集されたタイミングがわかりやすいです。最もGoogleSpreadSheetsは編集履歴が残っているのですが、編集があったタイミングで確認できるというのはちょっといいかもと思いました。
さいごに
本当に必要なのは人間と人間のやり取りであって、botはきっかけレベルでいいと思うんです。そもそも、私とインターン生さんとの間のコミュニケーションがうまく行ってないのが問題だから。とはいえ、最近は気を使ってくれてるのか、それとも願いが届いたのか、インターン生さんもちゃんと連絡してきてくださるようになって、このbotそのものもそこまで重要な存在にならなくなってるのかなぁとおもってたりします。
唐突にうちの会社のあっぴるが始まる
なんとうちの会社では社員さんを募集中です。募集の内容は以下の通り。
