SlackとGASを連携させて、勤怠打刻システムを作ってみた

By

社外の社員の勤怠状況がわからないという悩み

シスマックには悩みがありました。
弊社は受託開発以外にもSES（技術者派遣）が事業の柱としてありまして、当然みな勤務先がばらばらになるため社員の勤怠状況がすぐにわからないんです。

  • 稼働時間が多くてもそれを把握できるタイミングが勤務表が提出される月初
  • なんらかの理由で急な欠勤が生じても、わからない

その結果以下のような問題が実際に起こりました

  • 稼働の高い現場で、営業が現場に対して働きかける前に社員が体調を崩してしまう
  • 現場に連絡を入れられないほど体調不良の社員がいても、派遣先からの連絡でそれを知るため、対応が後手に回る

一応社内に対して日報は書いてもらっていますが、書き方やタイミングを自主性に任せている部分が多く、リアルタイムでの状況把握には役立ちにくいという欠点があります。

以前は月一の帰社日でそれなりに様子は把握できたのですが、コロナのご時世でそれもなくなり、ますます会社と社員の距離は広がるばかり…

Slackで勤怠の仕組みが作れる！？

しかし先日、救世主が現れました。仕事中（？）Twitterを見ていたらこんなツイートを見かけ衝撃を受けました。

その僅か一週間後、解説記事が公開されるという仕事の早さ！
（優秀な人って、仕事が早いだけじゃなく外部への情報発信力も優れているんですよね…）

丁度弊社でもチャットツールをSlackに乗り換えたばかりで、活用率を上げたいと思っていました。そこで、同じような仕組みを弊社にも導入しようと思い立ちました！

そして作る！

そしてパク作りました！
にっしーさんのブログを何度も読み返し、最大限参考にさせていただきました。
この仕組みはにっしーさんの存在なしには生まれませんでした。この場を借りて心から敬意と謝意を表したいと思います！！

パクったなりに工夫した点は以下です。

  • 社員の体調管理が目的なので、出退勤のタイミングが遅れた人を早く知る仕組みを入れる
  • 弊社ではfreeeは導入していないためスプレッドシートで前日の労働時間を計算
  • 社員全員ではなく社外で作業している社員のみを対象にする
  • 無料プランでできる範囲のことをする

主な機能

朝・夕方の時間帯に、チャンネルにメッセージが送信され、それに対してスタンプを押すと、出退勤のログがスプレッドシートに記録されます。
もし、スタンプを押していない人がいると、管理者チャンネルにメッセージが送信され欠勤・残業の即時検知に繋がる、という仕組みです。

↑定時メッセージ
↑管理者向けメッセージ
↑チャンネル参加者がスタンプを押した後のメッセージ

というのを、以下にのようなシート設計・GAS(Google Apps Script)の定時実行スクリプト・GASのウェブアプリの組み合わせで実現しています。
順番に解説します。

スプレッドシートの設計

以下のシートをまず作成します。

  • kintai_log
    • 出退勤ログ
  • today_shukkin_log
    • 当日の出勤ログをクエリで抜き出したもの
  • today_taikin_log
    • 当日の退勤ログをクエリで抜き出したもの
  • channel_users
    • 社外で作業している社員のみに処理を実行したいので、Slackのconversations.members というAPIを使い、チャンネルに参加しているユーザのIDを保存
    • あと、出勤と退勤のログから、前日の稼働時間を計算しています
  • slack_users
    • ユーザIDの名寄せに使用
  • calendar
    • GASで取得した祝日一覧とスプシの関数で取得した当月の平日を組み合わせ、営業日数を計算
「today_shukkin_log」シート
「calendar」シート：今月の残り営業日を計算するのに使用。
祝日一覧はcommon.gs内のgetYearlyHolidays()を実行して取得し、あとはお盆や年末年始のお休みを手動で足します
「channel_users」シート：前日の稼働時間を計算し、出勤時にお知らせ

だんだん「これDB作ったほうが良くね？」と思うほどあとから思いつきで機能を膨らませていってしまいました。

定時実行するスクリプト

  • morningGreeting
    • 朝になるとチャンネルにメッセージを送信
  • morningCheck
    • 出勤していない人がいたら、管理者チャンネルにメッセージ送信
  • eveningGreeting
    • 夕方になるとチャンネルにメッセージを送信
  • eveningCheck
    • 退勤していない人がいたら、管理者チャンネルにメッセージ送信
  • fetchSlackUsers
    • 日次でユーザー一覧を取得
  • fetchChannelUsers
    • 日次で勤怠報告チャンネル参加ユーザ一覧を取得

GASのコード

コードの全体像はこんな感じです
以下、可能な限り載せます

function doPost(e) {
  const LogSheet = SpreadsheetApp.getActiveSheet();
  const params = JSON.parse(e.postData.getDataAsString());

  // SlackのEvent SubscriptionのRequest Verification用
  if (params.type === 'url_verification') {
    return ContentService.createTextOutput(params.challenge);
  }
  const event = params.event
  const user = event.user
  const reaction = event.reaction
  const eventId = params.event_id

  // 特定のチャネル以外のスタンプは無視
  if (event.item.channel !== CHANNEL_ID) return;

  // shukkin, taikin 以外のスタンプは無視
  if (reaction !== SHUKKIN && reaction !== TAIKIN) return;

  // Slackからの再送リクエストを無視するためにキャッシュを使う（10分）
   const cache = CacheService.getScriptCache()
   const cached = cache.get(eventId)
   if (cached) {
     LogSheet.appendRow(`すでに処理したイベントなのでスルーします。（eventId: ${eventId})`)
     return
   }
   cache.put(eventId, true, 60 * 10) 

  if (reaction === SHUKKIN) {
    const todayShukkinSheet = SpreadsheetApp.getActive().getSheetByName('today_shukkin_log');
    const users = todayShukkinSheet.getRange('B2:B').getValues().flat();
    // すでに出勤打刻済みなら無視
    if (users.includes(user)) return;

    var messageForUser ='今日も頑張りましょう';

    const calendarSheet = SpreadsheetApp.getActive().getSheetByName('calendar');
    const ZAN_WORK_DAY = calendarSheet.getRange('E2').getValues();

    const channelUserSheet = SpreadsheetApp.getActive().getSheetByName('channel_users');
    const userData = channelUserSheet.getRange('A2:E').getValues().find((u) => u[0] === user);

    const stringWorkTime = userData[4];
    var messageForWorkTime = '';

    if(userData[4] != ''){
      const workTimeMinutes = Utilities.formatDate(stringWorkTime, 'Asia/Tokyo', 'HH時間mm分');
      const workTime = Utilities.formatDate(stringWorkTime, 'Asia/Tokyo', 'H');
      messageForWorkTime = ':timer_clock:前日の勤務時間は' + workTimeMinutes + 'です\n'

      if(workTime > 9){
        messageForUser = 'あまり頑張りすぎず、健康第一で働きましょう';
      }else if(workTime > 1)
      {
        messageForUser = '今日も定時退社を目指しましょう';
      }
    }
    else{
      messageForWorkTime = '';
    }

    sendSlackDm(getUserName(user) + 
    'さん、勤怠を登録しました。\n' +
    ':calendar:今月の勤務日数は残り'+ ZAN_WORK_DAY + '日です\n' +
    messageForWorkTime +
    ':teacher::skin-tone-2: '+ 
    messageForUser, user);
  }
  else if (reaction === TAIKIN) {
    const todayTaikinSheet = SpreadsheetApp.getActive().getSheetByName('today_taikin_log');
    const taikinUsers = todayTaikinSheet.getRange('B2:B').getValues().flat();

    // 打刻済みの退勤ログがあれば削除
    if (taikinUsers.includes(user)){
      const kintaiSheet = SpreadsheetApp.getActive().getSheetByName('kintai_log');
      const users = kintaiSheet.getRange('B2:B').getValues().flat();

      var userRow = 0;
      for(var i = 0 ;i < users.length; i++){
        if(users[i] == user){
          userRow = i;
        }
      }
      kintaiSheet.deleteRow(userRow + 2);
    }
    sendSlackDm(getUserName(user) + 'さん、お疲れ様でした！:wave:', user);
  }

  // kintai_logに記録
  const logSheet = SpreadsheetApp.getActive().getSheetByName('kintai_log')
  const datetime = Utilities.formatDate(new Date(event.event_ts * 1000), 'Asia/Tokyo', 'yyyy-MM-dd HH:mm:ss')
  logSheet.appendRow([datetime, user, reaction])

}
function morningCheck() {
  if ( isTodayHoliday()) return;

  const slackUserSheet = SpreadsheetApp.getActive().getSheetByName('slack_users');

  //channeluser一覧取得
  const channelUserSheet = SpreadsheetApp.getActive().getSheetByName('channel_users');
  const channelUsers = channelUserSheet.getRange('A2:A').getValues().flat();

  //勤怠シートから出勤者一覧取得
  const todayShukkinSheet = SpreadsheetApp.getActive().getSheetByName('today_shukkin_log');
  const shukkinUsers = todayShukkinSheet.getRange('B2:B').getValues().flat();

  //未出勤者取得
  let notShukkinUsers = channelUsers.filter(i => shukkinUsers.indexOf(i) == -1);

  if(notShukkinUsers.length == 0) return;  

  let notShukkinUserName = [];
  for(let user of notShukkinUsers){
    // [[id, name, real_name, email], [id, name, real_name, email], ...]
    const userData = slackUserSheet.getRange('A2:D').getValues().find((u) => u[0] === user);
    notShukkinUserName.push(userData[2]);
    }

  //メッセージ送信
  sendKintaiSummary('出勤チェック：' + notShukkinUserName + 'さんがまだ出勤していません');
}

function eveningCheck() {
  const slackUserSheet = SpreadsheetApp.getActive().getSheetByName('slack_users');

  //当日出勤した人一覧取得
  const todayShukkinSheet = SpreadsheetApp.getActive().getSheetByName('today_shukkin_log');  
  const shukkinUsers = todayShukkinSheet.getRange('B2:B').getValues().flat();

  //退勤した人一覧取得
  const todayTaikinSheet = SpreadsheetApp.getActive().getSheetByName('today_taikin_log');
  const taikinUsers = todayTaikinSheet.getRange('B2:B').getValues().flat();

  //出勤したけどまだ退勤していない人取得
  let notTaikinUsers = shukkinUsers.filter(i => taikinUsers.indexOf(i) == -1);

  if(notTaikinUsers.length == 0) return;

  let notTaikinUserName = [];
  for(let user of notTaikinUsers){
    // [[id, name, real_name, email], [id, name, real_name, email], ...]
    const userData = slackUserSheet.getRange('A2:D').getValues().find((u) => u[0] === user);
    notTaikinUserName.push(userData[2]);
    }
    
  //メッセージ送信
  sendKintaiSummary('退勤チェック：' + notTaikinUserName + 'さんがまだ働いているっぽいです！');
}
function morningGreeting() {
  //今日が土日・祝日だったら送らない
  if ( isTodayHoliday()) return;

  var slackApp = SlackApp.create(SLACK_TOKEN);
  let options = {
  }
  //出勤スタンプを押しましょう！という誘導
  slackApp.postMessage(CHANNEL_ID, 'おはようございます！出勤したらこのメッセージに:shukkin:をつけましょう！', options);
}
function eveningGreeting() {
  //今日が土日・祝日だったら送らない
  if ( isTodayHoliday()) return;

  var slackApp = SlackApp.create(SLACK_TOKEN);
  let options = {
  }
  //退勤スタンプを押しましょう！という誘導
  slackApp.postMessage(CHANNEL_ID, 'お疲れ様です、退勤したらここに:taikin:をつけましょう！', options);
}
function fetchSlackUsers() {
  // https://api.slack.com/methods/users.list
  const apiResponse = callWebApi(SLACK_TOKEN, 'users.list', {});
  const json = JSON.parse(apiResponse);

  const users = []
  json.members.filter((m) => !m.deleted && !m.is_bot ).forEach((m) => {
    users.push([m.id, m.name, m.real_name, m.profile.email])
  });

  const sheet = SpreadsheetApp.getActive().getSheetByName('slack_users')
  sheet.getRange('A2:D').clear()
  sheet.getRange(2, 1, users.length, 4).setValues(users)
}

function callWebApi(token, apiMethod, payload) {
  const response = UrlFetchApp.fetch(
    `https://www.slack.com/api/${apiMethod}`,
    {
      method: 'post',
      contentType: 'application/json; charset=UTF-8',
      headers: { 'Authorization': `Bearer ${token}` },
      payload: JSON.stringify(payload),
    }
  );
  return response;
}
// 勤怠打刻チャンネルのユーザー一覧を取得
function fetchChannelUsers() {
  const limit =100;
  
  //チャンネルのユーザー取得
  const options = {
    'method' : 'get',
    'contentType': 'application/x-www-form-urlencoded',
    'payload' : {
      'token': SLACK_TOKEN,
      'cursor': '',
      'limit':limit,
      'channel':CHANNEL_ID
    }
  };
  
  const url = 'https://slack.com/api/conversations.members';
  const apiResponse = UrlFetchApp.fetch(url, options);
  const members = JSON.parse(apiResponse).members;

  let users = [];
  for (const member of members) {
    let id = member;
    // Botはスキップ
    if(id == BOT_ID) break;
    users.push([ id ]);
  }
  
  //スプレッドシートに書き込み
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('channel_users');
  sheet.getRange('A2:A').clear();
  sheet.getRange(2, 1, users.length, users[0].length).setValues(users);
}
const SLACK_TOKEN = 'xoxb-XXXX-YYYY-ZZZZ';
const CHANNEL_ID = 'XXXXXXXXXXX';
const MANAGER_CHANNEL_ID = 'C04ELKTKBPF';
const SHUKKIN = 'shukkin';
const TAIKIN = 'taikin';
const CALENDAR_ID = 'ja.japanese#holiday@group.v.calendar.google.com'; 
const BOT_ID = 'XXXXXXXXXXX';
//翌日が休日(土日、祝日)か判定する関数
function isTodayHoliday(){

 today = new Date();
const weekInt = today.getDay();
if(weekInt <= 0 || 6 <= weekInt){ return true; } 

//祝日を判定するため、日本の祝日を公開しているGoogleカレンダーと接続する 
const calendar = CalendarApp.getCalendarById(CALENDAR_ID); 

const todayEvents = calendar.getEventsForDay(today); 
if(todayEvents.length > 0){
  return true;
}
  return false;
}

//１年間の祝日を計算する関数
function getYearlyHolidays(month = new Date()){
  const calendar = CalendarApp.getCalendarById(CALENDAR_ID); 

  const date = new Date(); 
  const year_now = Utilities.formatDate(date, 'JST', 'yyyy'); 

  let result = [];

  const period_from = new Date(year_now + '/01/01');
  const period_to = new Date(year_now + '/12/31');

  const events = calendar.getEvents(period_from, period_to);

  for(let i = 0; i < events.length; i++){
    result.push([events[i].getAllDayStartDate(), events[i].getTitle()]);
  }

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('calendar');
  sheet.getRange('F2:G').clear()
  sheet.getRange(2, 6, result.length, result[0].length).setValues(result)
}

// 管理者チャンネルに投稿する関数
function sendKintaiSummary(message) {
  const slackApp = SlackApp.create(SLACK_TOKEN);
  let options = {

  }
  slackApp.postMessage(MANAGER_CHANNEL_ID, message, options);
}

//ユーザー名（real_name）を取得する関数
function getUserName(id){
  const sheet = SpreadsheetApp.getActive().getSheetByName('slack_users')
  // [[id, name, real_name, email], [id, name, real_name, email], ...]
  const user = sheet.getRange('A2:D').getValues().find((u) => u[0] === id);
  return !!user ? user[2] : undefined
}

//チャンネルにメッセージを投稿する関数：今のところ未使用
function sendSlackMessage(message) {
  var slackApp = SlackApp.create(SLACK_TOKEN);
  let options = {
  }
  slackApp.postMessage(CHANNEL_ID, message, options);
}

//指定のユーザにDMを送信する関数
function sendSlackDm(message, userId){
  const message_options = {
    'method' : 'post',
    'contentType': 'application/x-www-form-urlencoded',
    'payload' : {
      'token': SLACK_TOKEN,
      'channel': userId,
      'text': message
    }
  };
  
  //必要scope = chat:write
  const message_url = 'https://slack.com/api/chat.postMessage';
  UrlFetchApp.fetch(message_url, message_options);
}

Slackのスコープ

最終的にこのくらいのスコープが必要でした

ハマったこと

  • デプロイ時の設定がわからなくSlack API設定画面のRequest URLがなかなかVerifiedにならなかった
    　- 次のユーザとして実行→自分
    　- アクセスできるユーザー→全員
  • ワークスペースへのBotのインストール作業が必要
    勝手にはされないんですね(^_^;)
  • チャンネルへのBot追加が必要
    勝手にはされないんですね(^_^;)

今回学んだこと

おまけですが、自分にとって学びになったことを書き連ねます

Googleスプレッドシートめっちゃ便利！

正直エクセルの下位互換だと思ってましたが、GASとの連携で威力発揮しまくりだし、QUERYが使えるのがエンジニアフレンドリーで最強すぎる

GASをウェブアプリとしてデプロイできる

知りませんでした。POSTで実行できるとか凄すぎひん？

GASのライブラリにSlackを追加できる

メッセージ送信とかがすごい楽！

あと、XLOOKUPというエクセルやスプシ共通で使える関数も今回作る中で知りました。VLOOPUPを完全に過去のものにしました、

今後について

現在は有志による試験運用中で、来月から本格運用するため、また色々手直しが入る予定です。
そういった経験値も溜まったら記事にしようかなと思います。
（24時を超えて退勤スタンプ押したため、労働時間がうまく算出されないという想定外のケースが既に発生しました笑、いや笑えないけど）

また、思ったよりコードが長大になってしまい繰り返しの処理が多くなったため、リファクタリングの必要性を感じていますが、「動きゃいいじゃん」という気持ちとせめぎ合っています

そして、この仕組みが便利なものだとみんなにわかってもらえたら、さらなる機能拡充とSlackの利用促進のために、有料プランへの移行を上長に進言するつもりです！💪

追記
この記事公開から1ヶ月後、このツールの有用性が認められ、見事有料プランへ移行しました！
ワークフロービルダー関連の記事などを今後書いていきたいです

おわりに

最初にも書きましたが、今回実現した仕組みはにっしーさんのツイートとZennの記事がなければありえませんでした。
あと、無数のGASについての記事も助けになりました。
そういった人たちの情報発信があって、毎日技術者として一応やっていけてるんだなあと思うと、その無償の労力に頭が下がります。
その分、これからは自分もそういったことをやっていかないと、という責任のようなものも感じます。

というわけで、シスマックは池袋と那覇を拠点に、日本のIT業界の発展の一助になるべく、これからも技術情報を発信していきます！
それでは！！👋

コメントする

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

株式会社シスマック
〒171-0014　
東京都豊島区池袋2-54-3 KS7ビル6階

Copyright © 2020-2024 Sysmac Co.Ltd. All Rights Reserved.