【保存版】SlackとGoogleスプレッドシートで本格的な勤怠管理システムをつくる

こんにちは。ディレクターの池原です。

最近は仕事も大学も在宅でこもりっきりのため、運動不足を解消すべく先月ステッパーを購入しました。
ステッパーは左右のペダル(?)に足を置いて交互に踏み込む運動器具です。本を読みながら30分程度の運動するだけで約200キロカロリーは燃焼されるので非常に効率よく有酸素運動ができます。
今月からは更なる効率化を追求すべく、デスクの上に高めのPC台を設置しステッパーを踏みながら仕事や勉強ができる環境を整えました。リズミカルな動作で集中力もUP、血行も良くなり、下半身の筋肉も鍛えられ大満足です。

さて効率化といえば、テレワークの普及により打刻や出退勤管理でお困りの企業さんも多いのではないでしょうか。
今回はSlackとGoogleスプレッドシートを活用して、テレワークでも簡単に打刻ができるシステムと勤怠管理表のつくりかたを紹介いたします。

システムの流れ
SlackとGoogleスプレッドシートの連携
勤怠管理シートをつくる
おわりに

システムの流れ

今回つくりたいシステムの流れは以下のとおりです。

Slackで「出勤」「退勤」などのコマンドを送信
GASを経由してGoogleスプレッドシートに打刻データを記録
社員それぞれの勤怠管理シートに打刻データを抽出して表示し、月ごとの終業時間や残業時間などを算出する

SlackとGoogleスプレッドシートの連携

まずは、Slackで「出勤」「退勤」等のコマンドを使ってスプレッドシートに打刻内容を送信する仕組みをつくります。以下の記事を参考にSlackの無料アプリとGoogle Apps Script（GAS）を使用します。
Slackで簡単に勤怠管理！【GAS】

はじめに、Slackのワークスペースに打刻用のチャンネルを作成しておきましょう。

SlackのワークスペースへOutgoing Webhookのアプリを追加します。
Outgoing Webhookを使用すると、Slackの指定したチャンネルに投稿された内容をGoogleスプレッドシートへ簡単に飛ばすことができます。

左上のワークスペース名 > その他管理項目 > アプリを管理する　をクリックします。

アプリ管理画面に移動したらOutgoing Webhookを検索し、Slackに追加してください。

Outgoing WebhookがSlackに追加できたら、設定タブを開き以下のとおり設定してください。
トークンは後ほど使いますので、メモに控えておきましょう。

チャンネル	先ほど作成した出退勤用のチャンネル
引き金となる言葉	出勤,退勤,離席,再開
URL	後ほど設定します
トークン	後ほど使用するので控えておきましょう
説明ラベル	出退勤をスプレッドシートに記録する
名前をカスタマイズ	タイムカード
アイコンをカスタマイズする	任意のアイコンを設定
ユーザー ID を移動する	「グローバルエンタープライズ ID をローカルワークスペース ID に変換する」にチェック

次に記録用のスプレッドシートを作成します。

まずSlackとスプレッドシートを連携させたいため、橋渡し役となるGASを設定していきましょう。
ツール > スクリプトエディタ　をクリックします。

エディタがひらいたら任意のタイトルを設定し、デフォルトで入っているコードを削除した上で以下のコードを入力します。

function doPost(e) {
  //'シート名'を変更する
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート名');

  //Outgoing Webhookのトークン
  var token = '先ほど控えたOutgoing Webhookのトークン'

  //送られてきたトークンが正しければ勤怠を記録する
  //parameterは必要に応じて変更してください
  if (token == e.parameter.token){
    var datetime     = new Date();
    var date         = (datetime.getFullYear() + '/' + ('0' + (datetime.getMonth() + 1)).slice(-2) + '/' + ('0' + datetime.getDate()).slice(-2))
    var time         = (('0' + datetime.getHours()).slice(-2) + ':' + ('0' + datetime.getMinutes()).slice(-2));
    var user_name    = e.parameter.user_name;
    var trigger_word = e.parameter.trigger_word;
    var text         = e.parameter.text;

    //追加する配列を作成
    array = [date,time,user_name,trigger_word,text];

    //シートの最下行に配列を記述
    sheet.appendRow(array);
  }

  return
}

'シート名'の箇所については適宜変更してください。
今回は打刻を記録するためにdataというシートを作成しているので'data'と入力します。

先ほどメモに控えたOutgoing Webhookのトークンは、'先ほど控えたOutgoing Webhookのトークン'の箇所へ入力します。

すべての入力が完了したら、公開 > ウェブアプリケーションとして導入　をクリックします。

表示されたウィンドウで以下のとおり設定してください。

Project version	New + 任意のタイトル
Execute the app as	Me(メールアドレス)
Who has access to the app	Anyone, even anonymous

Deployをクリックすると権限許可のウィンドウが出るので「許可を確認」とし、Googleアカウントの承認を進めてください。

承認完了後、web app URLが生成されます。出てきたURLをコピーしてください。

SlackのOutgoing Webhook設定へ移動します。
先ほど空白にしていた URL の箇所へweb app URLを貼り付け、設定を保存してください。

これでSlackとスプレッドシートの連携は完了です。
ためしにSlackの #出退勤チャンネルで「出勤」と入力してみてください。
スプレッドシートのシート：data へ打刻が記録されていれば成功です。

現状のままではSlackでコマンドを送信したときのレスポンスがないため、botから記録成功の結果が返されるように設定します。

まず、Googleドライブから新しいGoogle App Scriptを作成してください。

エディタが表示されたら、任意のタイトルを設定し、デフォルトで入っているコードを削除した上で以下のコードを入力します。

function doPost(e) {

  var message = "スプレッドシートに記録しました";

  var options =
  {
    "method" : "post",
    "contentType" : "application/json",
    "payload" : JSON.stringify(
      {
        "text" : message
      }
    )
  };

  UrlFetchApp.fetch("Webhook URL", options);
}

一旦Slackへ移動し、左上のワークスペース名 > その他管理項目 > アプリを管理する　でアプリ管理画面へ移動し「Incoming Webhooks」を検索、Slackへ追加してください。

設定タブをひらき「Webhook URL」をコピーしてください。

Google App Scriptのエディタに戻り、"Webhook URL"の箇所へURLを貼り付けます。

先程と同じ手順（公開 > ウェブアプリケーションとして導入）でGASを公開してください。
生成されたURLをコピーします。

SlackのOutgoing Webhook設定へ移動し、URLの箇所の既にあるweb app URLの下の行にコピーしたweb app URLを貼り付けて設定を保存してください。

Slackからコマンドを送信してみましょう。botからのレスポンスがあれば成功です。

勤怠管理シートをつくる

次に以下の記事を参考にしながら、スプレッドシートに記録された打刻データを集計して残業時間などを算出するためのシートを作成していきます。
Googleスプレッドシートでクラウド上の勤怠管理集計システムを作る

手順は以下のとおりです。

社員マスタを作成する
法定労働時間などを設定する
非稼働日を設定する
社員ごとにデータを集計するための関数を設定する
集計用のシートを作成する
フォーマットを整える

社員マスタを作成する

はじめに社員マスタを作成しましょう。
社員マスタ用の新しいシートを作成します。ここではシート名を’staff-master’としておきます。

ID、部署、性、名　などの項目を入力していきます。

IDはSlackのユーザー名を記入しましょう。（Slack > プロフィール > アカウント設定 > ユーザー名　から確認できます。）
「社員ごとにデータを集計するための関数を設定する」のステップでこちらのIDを使用するため、必ずSlackのユーザー名にしておいてください。

法定労働時間などを設定する

法定労働時間や休憩時間などは改定された場合を考慮するためシートを分けて設定します。
新たにシートを作成し、任意のシート名に変更します。ここでは’config’としておきます。

法定労働時間、休憩時間、深夜労働開始時間および終了時間などを設定しましょう。

始業〜終業の時刻が9時〜18時など、明確な時間が定められている場合、始業・終業時間もそれぞれ設定しておくことで、それ以外の時間に発生した労働を残業時間として集計できます。

フレックス制などの場合は始業・終業時間は設定せずに、法定労働時間の超過分を残業時間として集計します。

今回は、法定労働時間の超過分を残業時間とするケースを想定して解説します。

非稼働日を設定する

非稼働日の扱いとなる祝日や会社規定の休業日などを設定していきましょう。
こちらも法定労働時間などと同様に別のシートで設定します。

新たにシートを作成し、任意のシート名に変更します。ここでは’holiday’としておきます。
次に祝日や会社規定の休業日を入力していきましょう。

社員ごとにデータを集計するための関数を設定する

Slackから送信された全ての打刻データは一箇所に蓄積されるため、そこから社員それぞれの打刻データを各社員のシートへ取り出す必要があります。
ここでは日付とIDを結びつけて取り出す方法を紹介します。

シート：data の右端のカラムの1行目に以下の関数を入力しましょう。

=ARRAYFORMULA($A$1$A&$C$1:$C&$D$1:$D)

=ARRAYFORMULA(日付セル&ユーザー名セル&トリガーワードセル)

「日付セル」「ユーザー名セル」「トリガーワードセル」を & で連結させ、打刻データが記録される度に関数が自動で入力されるようにARRAYFORMULA関数で括ります。
ARRAYFORMULA関数は配列数式から返された値を複数行または複数列に表示させる関数のため、今回のように行数が多い場合に便利です。

ここでは、日付セル：A列、ユーザ名：C列、トリガーワード：D列なので次のようになります。

=ARRAYFORMULA($A$1$A&$C$1:$C&$D$1:$D)

関数を入力すると各セルの「日付&ユーザー名&トリガーワード」が「44409ikehara出勤」といったテキスト変換され返されます。日付が正しく反映されていないように見えますが、”2021/08/01″を数字に直した”44409″が表示されているためであり正常な値ですのでご安心ください。

これで社員ごとのデータを抽出する準備は完了です。

集計用のシートを作成する

全ての準備が整ったら社員ごとの集計用シートを作成してきましょう。

新しいシートを作成し、以下のとおり必要項目を入力します。シート名は集計対象の社員名などにしておきます。

それぞれのセルに関数を入力していきます。

氏名と部署
日付と曜日
区分
打刻時間
離席時間
就業時間
残業時間と深夜残業
休日労働
時間内・時間外
出勤日数、総就業時間などの合計

氏名と部署

まずは氏名の隣のカラム：B2に次の関数を入力します。

=VLOOKUP($B$1,'staff-master'!A:D,3,0) &" "& VLOOKUP($B$1,'staff-master'!A:D,4,0)

VLOOKUPは表を縦方向に検索し、指定した条件に一致する値を取り出し表示させる関数です。VLOOKUP(検索値,範囲,列番号,[検索方法])の配列で指定します。

上記の関数ではそれぞれの引数を以下のように設定しました。

検索値：B1に入力したID
範囲：’staff-master’のA列からD列
列番号：’staff-master’のA列を1番目として、3番目（C列）を指定
検索方法：0（「検索値」と完全一致するデータのみ検索）

性と名を分けて設定しているので、&" "& で名のセルを取り出してつなげます。

IDのセル（B1）に社員マスタで設定したIDを入力し、氏名が返されたら成功です。

同じ要領で所属の箇所にも関数をいれましょう。

=VLOOKUP($B$1,'staff-master'!A:D,2,0)

検索値：B1に入力したID
範囲：’staff-master’のA列からD列
列番号：’staff-master’のA列を1番目として、2番目（B列）を指定
検索方法：0（「検索値」と完全一致するデータのみ検索

日付と曜日

次にB列の年と月を入力すると、日が自動的に返されるように関数を入れます。

D3セルに

=DATE($B$4,$B$5,1)

と入力しましょう。

DATEは、指定された年、月、日を日付に変換する関数です。DATE(年, 月, 日)の配列となるので、ここでは年：B4、月：B5、日：1日、の引数になります。

関数を入れたD3セルに「2021/08/01」と表示されていると思うので「1」と日のみの表示となるように設定します。
表示形式 > 数字 > 表示形式の詳細設定 > その他の日付や時刻の形式　をクリック。

設定画面で「年」と「月」を削除、「日」のみの表示となるように設定してください。ここでは「先行ゼロなしの日」を選択しています。

次に、D4セルに

=D3+1

（D3の日＋1日）と入力します。
これをオートフィルでD32セルまでコピーしてください。

30日と31日の月があるため、D33セルには次の関数を入力します。

D33：

=IF(OR(B5=2,B5=4,B5=6,B5=9,B5=11),"–",D32+1)

「B5が2月、4月、6月、9月、11月なら – と表示する」「それ以外の月なら31（D32+1）と表示する」と指定しています。

曜日セルは、日付セルの表示を曜日に変換するだけでOKです。
E3セルに=D3 と入力します。次に、表示形式 > 数字 > 表示形式の詳細設定 > その他の日付や時刻をクリックし、設定画面で「年」と「月」を削除、「曜日の省略形」を選択してください。

オートフィルでD33セルまでコピーして完了です。

区分

さて、ここから社員ごとのデータを抽出していきます。
まずは出勤した日の区分列に「出勤」と表示されるように設定します。

F3セルにVLOOKUP関数でシート’data’のトリガーワードを取り出しましょう。

=VLOOKUP(D3&$B$1&$G$2,{data!F:F,data!A:E},5,0)

VLOOKUP(検索値,範囲,列番号,[検索方法])

検索値：日付セル & IDセル & 出勤(G2の見出し)
範囲：’data’のF列
列番号：’data’のF列を1番目として、5番目（D列）を指定
検索方法：0（「検索値」と完全一致するデータのみ検索）

VLOOKUPは左端の列から値を検索するため、範囲を{data!F:F,data!A:E}とすることによってF列を右端と認識するように指定しています。

列番号はA列の前にF列が挿入されたと考えて、F,A,B,C… と数えていきます。
（したがってD列は5番目となります。）

次に、ARRAYFORMULA で囲みD33セルまで表示させましょう。

=ARRAYFORMULA(VLOOKUP(D3:D33&$B$1&$G$2,{data!F:F,data!A:E},5,0))

空白行のエラー#N/Aを消したいので、IFERROR関数で括ります。
IFERROR関数は、エラーの場合に表示させる値を指定することができます。

=IFERROR(ARRAYFORMULA(VLOOKUP(D3:D33&$B$1&$G$2,{data!F:F,data!A:E},5,0)),"")

これで区分の列は完成です。

打刻時間

打刻時間も同じくVLOOKUP関数で取り出します。

=IFERROR(ARRAYFORMULA(VLOOKUP(D3:D33&$B$1&$G$2,{data!F:F,data!A:F},3,0)),"")

VLOOKUP(検索値,範囲,列番号,[検索方法])

検索値：日付セル & IDセル & 出勤(G2の見出し)
範囲：’data’のF列
列番号：’data’のF列を1番目として、3番目（B列）を指定
検索方法：0（「検索値」と完全一致するデータのみ検索）

区分と同じように、ARRAYFORMULA関数とIFERROR関数で括ります。

関数が入力できたら、表示形式 > 数字 > 表示形式の詳細設定 > その他の日付や時刻で時刻の表示形式を選択し、「秒」を削除、「13:30」の表示形式となるように設定しておきましょう。

同様の手順で、退勤、離席、再開の打刻時刻も表示させます。

退勤：

=IFERROR(ARRAYFORMULA(VLOOKUP(D3:D33&$B$1&$H$2,{data!F:F,data!A:F},3,0)),"")

離席：

=IFERROR(ARRAYFORMULA(VLOOKUP(D3:D33&$B$1&$I$2,{data!F:F,data!A:F},3,0)),"")

再開：

=IFERROR(ARRAYFORMULA(VLOOKUP(D3:D33&$B$1&$J$2,{data!F:F,data!A:F},3,0)),"")

離席時間

打刻時間から就業時間や残業時間などを算出していきましょう。

まずは離席時間です。O3セルに以下の関数を入力します。

=IFERROR(IF(OR(I3="",J3=""),"",J3-I3),"")

IF関数を使って「I3,J3のいずれかが空白の場合は空白の値を返す」「そうでなければ”J3-I3″の値を返す」と指定し、IFERROR関数で括ります。

就業時間

次に終業時間を算出しましょう。

就業時間は、退勤時間-出勤時間から、離席時間と休憩時間を除いて算出したいので、K3セルには以下のとおり関数を入力します。

=IFERROR(IF(OR(G3="",H3=""),"",H3-G3-O3-config!$B$3),"")

IF関数を用いて「G3,H3のいずれかが空白のときは空白の値を返す」「G3,H3どちらも空白でない場合は “H3-G3-O3-‘congic’!B3（休憩時間）”の値を返す」と指定します。IFERRORで囲みエラー表示を消しておきます。

表示形式 > 数字 > 経過時間　を選択すると 3:30:00 の表示に変更できます。

オートフィルでK33セルまでコピーしましょう。

残業時間

定められた就業時間を超過した分は、残業時間として集計します。

1日の就業時間が「9時間（休憩1時間含む）」とした場合、L3セルの関数は以下のようになります。

=IF(H3-G3-"9:00"<0,"",IF(OR(G3="",H3=""),"",H3-G3-"9:00"))

「"H3-G3-9時間"が0より小さい場合は空白の値を返す」「そうでなければ、G3,H3どちらも空白でない場合に"H3-G3-9時間"の値を返す」と指定しています。

次に深夜残業の算出ですが、午前と午後で分けて集計した後にそれらの合計をS列（深夜残業時間）に表示する、という手順で設定していきます。

まずはM3セルの深夜(AM)から。

=IFERROR(IF(OR(G3="",H3="",G3 > =config!$B$2),"",IF(H3 < config!$B$2,H3-G3,config!$B$2-G3)),"")

「G3,H3のいずれかが空白、もしくはG3がAM5:00よりも大きい場合、空白の値を返す」「そうでなければ、H3がAM5:00よりも小さい場合"H3-G3"の返し、H3がAM5:00よりも大きければ"AM5:00-G3"の値を返す」と指定しています。

同じ要領でN3セルの深夜(PM)を入力します。

=IFERROR(IF(OR(G3="",H3="",H3 < config!$B$1),"",IF(G3 < config!$B$1,H3-config!$B$1,H3-G3)),"")

「G3,H3のいずれかが空白、もしくはH3が22:00よりも大きい場合、空白の値を返す」「そうでなければ、G3が22:00よりも小さい場合"H3-22:00"の返し、G3が22:00よりも大きければ"H3-G3"の値を返す」と指定しています。

最後にS3セルでAMとPMの深夜残業時間の合計を表示させます。

=IF(OR(M3<>"",N3<>""),M3+N3,"")

「M3,N3いずれかが空白でない場合、"M3+N3"の値を返す」「そうでなければ空白の値を返す」と指定しています。

これで残業時間の設定は完了です。

休日労働

土日と'holiday'で指定した非稼働日を休日労働として表示させます。
R3セルに以下の関数を入れてください。

=IFERROR(IF(OR(WEEKDAY(D3,2)>=6,COUNTIF(holiday!A:A,D3)>0),K3,IF(OR(G3="",H3=""),"",0)),"")

WEEKDAYは日付に対応する曜日を返す関数です。WEEKDAY(シリアル値,[週の基準])の引数を指定します。
シリアル値は検索する日付を入れるため上記ではD3セルの日付を指定しています。

[週の基準]とは、戻り値の種類です。規定「1」にすると、日曜が 1 となり、2（月曜）、3（火曜）...と、7 (土曜) までの範囲の整数となります。上記では「2」と指定していますので、月曜を 1 とし 7（日曜）までの整数が戻り値となっています。したがってWEEKDAY(D3,2)>=6は、「D3セルの日付が、6（土曜）よりも小さい値（つまり1~5 月曜〜金曜）なら」という条件を示しています。

COUNTIFはひとつの検索条件に一致するセルの個数を返す関数です。COUNTIF(参照範囲, 検索値)となるので、COUNTIF(holiday!A:A,D3)とすると、holidayのA列にD3の日付に一致する値がないかを検索させることができます。

これらを OR でつなぎ、「D3セルが土日もしくは非稼働日であれば、K3（就業時間）の値を返す」「そうでなければ、G3,H3のいずれかが空白の場合は空白の値を返し、空白でなければ"0"を返す」と指定します。

時間内・時間外

最後に法定労働時間内(外)労働時間を算出しましょう。

法廷労働時間内の労働時間「P3セル」に以下の関数を入力します。

=IF(OR(R3="",R3>0),"",IF(K3 < config!$B$4,K3,config!$B$4))

休日労働の場合はすべて時間外労働の扱いになるため、IF関数で「休日労働のセルが空白が0より大きければ空白の値を返す」「そうでなければ、就業時間（K3）が8時間（config!B4）より小さい場合、K3の値を返し、大きい場合はconfig!B4の値を返す」と指定します。

時間外は、就業時間から法定時間内労働時間を引けば算出できますので、以下の関数となります。

=IF(OR(G3="",H3=""),"",K3-P3)

出勤日数、総就業時間などの合計

出勤日数は打刻されたセルを数えることで算出できますので、B3セルには

=COUNT(G3:G33)

と入力しましょう。

その他の項目は、SUM関数でセルの合計を表示してあげればOKです。

総就業時間：

=SUM(K3:K33)

時間内労働時間：

=SUM(P3:P33)

時間外労働時間：

=SUM(Q3:Q33)

休日労働時間：

=SUM(R3:R33)

深夜労働時間：

=SUM(S3:S33)

フォーマットを整える

ここまで大変おつかれさまでした。
あとは見やすいように色分けするなどしてフォーマットを整えて完成です。

土日と非稼働日のセルの色を変える

土日と非稼働日を区別しやすくするために、条件付き書式で設定してみましょう。

表示形式 > 条件付き書式　をクリックします。

D3:E33のセル範囲を指定してください。
条件を「カスタム数式」にし、以下の関数を入力します。

=IF($D3="","",WEEKDAY($D3)=7)

任意の色を設定したら、下部にある「条件を追加」をクリックしてください。

再度D3:E33のセル範囲を指定し「カスタム数式」の条件を指定します。
以下の関数を入力してください。

=IF($D3="","",WEEKDAY($D3)=1)

任意の色を設定し「完了」をクリックします。

土日のセルの色が変更されました。

holidayで定めた非稼働日にも色を付けたいので、新しく条件を追加しましょう。
D3:E33のセル範囲を指定し「カスタム数式」の条件を指定、以下の関数を入力してください。

=COUNTIF( INDIRECT("holiday!A1:A"),$D3)>0

非稼働日のセルに色が付いていれば成功です。

おわりに

今回はSlackとGoogleスプレッドシートを活用した打刻システムと勤怠管理表のつくりかたを紹介しました。
少し手間はかかってしまいますが、環境を整えてしまえば管理がものすごく楽になります。紹介した関数を応用しながら、自社に合わせたシステムをつくってみてくださいね。
本記事が皆様の業務効率化の一助となれば幸いです。