個人workページ

技術メモ・ゲーム・日常の気づきをゆるくまとめるブログです。

Slackで出退勤情報を収集して活用した際のメモ

Google Apps Script
はじめに

所属部署の勤退管理まわりがいつの時代だ・・・みたいな運用をされていたので実施した内容を残しておきます。

仕組み
┌──────────────────────────────────────────────────────────────────────
│        Slack（チャンネル画面）
│  ┌───────────────────────────────────────────────────────
│  │ ユーザー：業務開始 10/20 10:00
│  │ ユーザー：業務終了 10/20 19:00
│  └───────────────────────────────────────────────────────
└──────────────────────────────────────────────────────────────────────
                ▲
                │ API通信                
                ▼
┌──────────────────────────────────────────────────────────────────────
│  Slack App
│  ├─ Bot User(投稿を監視)
│  ├─ Event Subscriptions[新メッセージ受取]
│  └─ Token設定[Bot Token Scopes]
│       ・channels:history
│       ・channels:read
│       ・users:read
└──────────────────────────────────────────────────────────────────────
                ▲
                │ GASによるAPI通信
                ▼
┌──────────────────────────────────────────────────────────────────────
│ Google Apps Script[メイン処理]
│  ├─ getConfig()：プロパティから機密情報取得
│  ├─ main()：全体の実行フロー
│  │    ├ スプレッドシートを開く
│  │    ├ 今月のシートを作成／初期化
│  │    ├ ヘッダー書き込み
│  │    ├ Slackメッセージを取得・解析
│  │    ├ ユーザー名解決[ID→名前]
│  │    ├ 勤怠データ集計
│  │    └ 稼働時間（H列）に計算式自動挿入
│  ├─ aggregateMessages()：業務開始/終了を解析
│  └─ fillAutoIncrementFormulaInHColumn()：稼働時間計算
└──────────────────────────────────────────────────────────────────────
                ▲
                │ 時間トリガー（自動実行）
                ▼
┌──────────────────────────────────────────────────────────────────────
│     Google スプレッドシート（出力先）
│  ┌─────────────────────────────────────────────────────── 
│  │ 日付　　| ユーザID  | ユーザ名 |  開始  |  終了  | 稼働時間
│  │ 10/20　| AB123456 | 山田太郎 | 10:00 | 19:10  | 8:00
│  └───────────────────────────────────────────────────────
│  ※自動ヘッダー装飾（黄色）・稼働時間式自動挿入
└──────────────────────────────────────────────────────────────────────
                ▲
                │ 時間トリガー（自動実行）
                ▼
┌──────────────────────────────────────────────────────────────────────
│    トリガー設定（午前11時～12時／午後19時～20時）
│    → main() 関数が自動実行
└──────────────────────────────────────────────────────────────────────
                │ 時間トリガー（自動実行）
                ▼
┌──────────────────────────────────────────────────────────────────────
│  　この先は各環境に合わせて実施
└──────────────────────────────────────────────────────────────────────
前提条件

・Slackは無償プラン(お金が追加でかからない前提)
・データ蓄積はGoogleスプレッドシートを利用
・SlackAPP利用
・可能な限りメンテ不要にする
・第三者が改修しやすいようにする

コード
///                           　↑手動実行する場合は[main]に合わせて実行を押下
///_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_
///_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_
///   タイトル  :勤怠管理
///   作成者    :AWA
///   ---処理内容------------------------------
///   スプレッドシートを開く
///   今の月の名前でシートを取得（なければ作る）し、あれば一旦中身をクリア
///   ヘッダー（列名）を書き込む
///   過去1ヶ月分のSlackメッセージを取得
///   SlackのユーザーIDからユーザー名を取得
///   メッセージを解析し、「業務開始」「業務終了」の時間や備考をまとめる
///   まとめたデータをスプレッドシートに書き込む
///   ---------------------------------------------------------
///   ※上記内容を時間ベーストリガーで定期的に実施(午前11時～12時/午後19時～20時)
///   
///   ◆SlackAPPにて以下Scopes設定必須◆
///   ---Bot Token Scopes を設定---------------------------------
///   channels:history      パブリックチャンネルの履歴取得
///   channels:read         チャンネル情報の取得
///   users: read           ユーザー情報の取得
///   ----------------------------------------------------------
///_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_
///_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_

// ------------------------------
// 機密情報をGASプロパティから取得する関数(プロジェクトの設定→スクリプトプロパティより設定可能)
// ------------------------------
function getConfig() {
  const props = PropertiesService.getScriptProperties();   // スクリプトプロパティの取得
  return {
    SLACK_BOT_TOKEN: props.getProperty('SLACK_BOT_TOKEN'), // Slack Botトークン
    SPREADSHEET_ID: props.getProperty('SPREADSHEET_ID'),   // 勤怠スプレッドシートID
    CHANNEL_ID: props.getProperty('CHANNEL_ID'),           // 対象SlackチャンネルID
  };
}


// ------------------------------
// メイン処理
// ------------------------------
function main() {
  try {
    // 設定情報を取得
    const { SLACK_BOT_TOKEN, SPREADSHEET_ID, CHANNEL_ID } = getConfig();

    // スプレッドシートをIDで開く
    const ss = SpreadsheetApp.openById(SPREADSHEET_ID);

    // 現在の年月を取得してシート名とする（例：2025-10）
    const sheetName = getCurrentMonthSheetName();

    // 指定した名前のシートを取得（存在しなければ新規作成）
    let sheet = ss.getSheetByName(sheetName);
    if (!sheet) sheet = ss.insertSheet(sheetName);           // なければ新規作成

    // シートの内容を全てクリア（書式は残す）
    sheet.clearContents();

    // ヘッダー行の定義と追加
    const headers = ['日付', 'ユーザID', 'ユーザ名', '業務開始時刻', '業務開始備考', '業務終了時刻', '業務終了備考', '1日稼働時間'];
    sheet.appendRow(headers);                                 // 1行目にヘッダーを追加

    // ヘッダー装飾：背景色と太字
    const headerRange = sheet.getRange(1, 1, 1, headers.length);
    headerRange.setBackground('#FFFF00').setFontWeight('bold');

    // 現在日時と1ヶ月前の日時を取得（Slack API用のUnixタイム）
    const now = new Date();
    const oneMonthAgo = new Date(now.getFullYear(), now.getMonth() - 1, now.getDate());
    const oldest = Math.floor(oneMonthAgo.getTime() / 1000);    // 1ヶ月前のUnixタイム
    const latest = Math.floor(now.getTime() / 1000);            // 現在のUnixタイム

    // Slackメッセージ取得（チャンネル指定・期間指定）
    const messages = fetchSlackMessages(CHANNEL_ID, oldest, latest, SLACK_BOT_TOKEN);

    // Slackユーザー一覧を取得して、ID→名前のマップを作成
    const userMap = getUserMap(SLACK_BOT_TOKEN);

    // メッセージを解析して日付・ユーザー単位で業務開始/終了を集計
    const dailyData = aggregateMessages(messages, userMap);

    // 集計結果をスプレッドシートに出力
    for (const key in dailyData) {
      const row = dailyData[key];
      sheet.appendRow([
        row.date,           // 日付
        row.userId,         // ユーザーID
        row.userName,       // ユーザー名
        row.startTime,      // 業務開始時刻
        row.startNote || '',// 業務開始備考
        row.endTime,        // 業務終了時刻
        row.endNote || '',  // 業務終了備考
        ''                  // 稼働時間（後で計算式を挿入）
      ]);
    }

    // H列（1日稼働時間）に計算式を挿入
    fillAutoIncrementFormulaInHColumn(sheet);

    // 処理完了ログ
    Logger.log('処理完了');
  } catch (e) {
    // エラー発生時はログに出力
    Logger.log('Error: ' + e.message);
  }
}


// ------------------------------
// 現在の年月を「yyyy-MM」形式で取得する関数
// ------------------------------
function getCurrentMonthSheetName() {
  const now = new Date();
  return Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyy-MM');     // タイムゾーンに合わせて整形
}


// ------------------------------
// Slack API：メッセージ履歴取得
// ------------------------------
function fetchSlackMessages(channelId, oldest, latest, token) {
  // API URLを構築
  const url = `https://slack.com/api/conversations.history?channel=${channelId}&oldest=${oldest}&latest=${latest}&limit=1000`;

  const options = {
    method: 'get',
    headers: { Authorization: 'Bearer ' + token }, // 認証ヘッダー
    muteHttpExceptions: true,                      // エラーでも例外を投げない
  };

  // Slack APIを呼び出す
  const response = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(response.getContentText());

  // エラー処理
  if (!data.ok) throw new Error('Slack API error: ' + data.error);

  return data.messages;                             // メッセージ一覧を返す
}


// ------------------------------
// Slack API：ユーザー一覧取得
// ------------------------------
function getUserMap(token) {
  const url = 'https://slack.com/api/users.list?limit=1000';
  const options = {
    method: 'get',
    headers: { Authorization: 'Bearer ' + token },
    muteHttpExceptions: true,
  };

  const response = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(response.getContentText());

  if (!data.ok) throw new Error('Slack API error: ' + data.error);

  // ユーザーID→名前のマップ作成
  const map = {};
  data.members.forEach(user => {
    map[user.id] = user.real_name || user.name || 'Unknown';    // 名前がなければUnknown
  });
  return map;
}


// ------------------------------
// Slackメッセージを解析し、日付・ユーザーごとに業務開始/終了を集計
// ------------------------------
function aggregateMessages(messages, userMap) {
  const result = {};
  const regex = /(業務開始|業務終了)\s+(\d{1,2}\/\d{1,2})\s+(\d{1,2}:\d{2})(?:\s+備考:\s*(.*))?/;

  messages.forEach(msg => {
    if (!msg.user || !msg.text) return;         // ユーザーIDまたはメッセージなしはスキップ
    const match = msg.text.match(regex);
    if (!match) return;                         // 正規表現にマッチしなければスキップ

    const [_, type, datePart, timePart, note] = match;
    const year = new Date().getFullYear();
    const dateTimeStr = `${year}/${datePart} ${timePart}`;
    const dateObj = new Date(dateTimeStr);
    if (isNaN(dateObj)) return;                 // 日付不正ならスキップ

    const dateStr = Utilities.formatDate(dateObj, Session.getScriptTimeZone(), 'yyyy/MM/dd');
    const key = `${dateStr}|${msg.user}`;

    // 初回の場合はオブジェクトを初期化
    if (!result[key]) {
      result[key] = {
        date: dateStr,
        userId: msg.user,
        userName: userMap[msg.user] || 'Unknown',
        startTime: null,
        startNote: '',
        endTime: null,
        endNote: ''
      };
    }

    // 業務開始時刻の更新
    if (type === '業務開始') {
      if (!result[key].startTime || dateObj < new Date(`${dateStr} ${result[key].startTime}`)) {
        result[key].startTime = Utilities.formatDate(dateObj, Session.getScriptTimeZone(), 'HH:mm');
      }
      if (note) result[key].startNote += (result[key].startNote ? '\n' : '') + note.trim();
    }
    // 業務終了時刻の更新
    else if (type === '業務終了') {
      if (!result[key].endTime || dateObj > new Date(`${dateStr} ${result[key].endTime}`)) {
        result[key].endTime = Utilities.formatDate(dateObj, Session.getScriptTimeZone(), 'HH:mm');
      }
      if (note) result[key].endNote += (result[key].endNote ? '\n' : '') + note.trim();
    }
  });

  return result;
}


// ------------------------------
// H列（1日稼働時間）に計算式を自動挿入
// ※午前休（13:00以降開始）または午後休（12:00以前終了）の場合は休憩時間を引かない
// ------------------------------
function fillAutoIncrementFormulaInHColumn(sheet) {
  const startRow = 2;                   // データ開始行（ヘッダーの次）
  const lastRow = sheet.getLastRow();
  if (lastRow < startRow) return;       // データなしなら終了

  for (let row = startRow; row <= lastRow; row++) {
    const formula = `
      =IF(
        OR(D${row}="",F${row}=""),
        "",
        IF(
          OR(HOUR(D${row})>=13, HOUR(F${row})<=12),
          F${row}-D${row},
          F${row}-D${row}-TIME(1,0,0)
        )
      )
    `.replace(/\s+/g, '');               // 不要な改行・空白を除去
    sheet.getRange(`H${row}`).setFormula(formula);
  }
}
TypeScript
詳細

◆SlackAPPにてチャンネルから拾ってくる情報（Scopesを利用）
channels:history　　 パブリックチャンネルの履歴取得
channels:read　　　 チャンネル情報の取得
users: read　　　　　ユーザー情報の取得

◆月単位で新しいシートを自動で作成
A列 日付(yyyy/mm/dd)
B列 ユーザID
C列 ユーザ名
D列 業務開始時刻  ※投稿文字列から取得
E列 業務終了時刻    ※投稿文字列から取得
F列 1日稼働時間（休憩時間-1時間を関数で算出/午前休or午後休は関数で判定し該当すれば除外

<span class="fz-12px">=IF(OR(D●●="",F ●● =""),"",IF(OR(HOUR(D ●●)>=13,HOUR(F2)<=12),F ●● -D ●●,F ●● -D ●● -TIME(1,0,0)))</span>


◆Botトークンは GASのプロパティサービス に保存し、コードに直接書かない

function getConfig() {
  const props = PropertiesService.getScriptProperties();   // スクリプトプロパティの取得
  return {
    SLACK_BOT_TOKEN: props.getProperty('SLACK_BOT_TOKEN'), // Slack Botトークン
    SPREADSHEET_ID: props.getProperty('SPREADSHEET_ID'),   // 勤怠スプレッドシートID
    CHANNEL_ID: props.getProperty('CHANNEL_ID'),           // 対象SlackチャンネルID
  };
}
まとめ

戻る