はじめに

データビジネス事業本部のまさきです。ビジネス職から参戦しております(・ω・)ノ

私は普段、クライアント向き合いで広告の営業～提案～配信準備～運用まで一気に担当するスタイルで働いています。やりがいはあるのですが、自分の案件管理だけで精一杯になり、「同じチームのメンバーの案件状況をキャッチアップする余裕がない」というのが悩みでした。

本来ならチームメンバー宛の連絡すべてに目を通すべきなのですが、忙しいとなかなかそうもいきません。そこで今回は、現在稼働中の案件ステータス管理を簡単に行える仕組みを作ってみましたのでご紹介します。

使ったツールなど

flows (Google WorkspaceのAI agents)
Gemini (今回はカスタムGemを作成)
GAS (Google Apps Script)
Google スプレッドシート

行ったこと -全体像-

案件シート準備
現在動いている案件名、担当者名などを記載したスプレッドシートを用意（これは手動で作成しました）
Slackのログ抽出
GASとSlack APIを使い、指定期間・指定チャンネルの会話ログを抽出
メールのログ抽出
flowsを使い、チームメンバー宛のメール内容をスプレッドシートに自動書き出し
Geminiでステータス管理
1〜3の情報をGemini（Gems）に渡し、ステータスを要約・更新させる

各工程について

1. 案件シート準備

今回は手動で作成をしており、A~C列の内容は自分で入力したものを準備しておいて、
D列にあたる部分をこのあとの工程でまとめてもらうようにしています。

2. Slackのログ抽出

社内にあったSlack APIを使ったログ抽出のGASコードを参考にしました。
正直なところ、詳しいコードの中身までは理解しきれていません……（笑）。

ただ、だいたい文系職がつまずくのはAPI連携周りなので、そこさえクリアできているコードがあれば、あとはGeminiに聞いて「よく分からないけれどなんか作れたっ！」となればOK、という精神で進めました。

現在は、毎日1回、直近5日間のログを更新するようにGASのトリガーを設定しています。

こんな感じでデータが集まります！（※ダミーデータです）

サンプルコード

// ここから任意期間のSlackLogを抽出するコード
function slackMessagesArchive() {
  // 処理に必要なシート情報を取得
  var spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");
  var optionSheet     = spreadsheet.getSheetByName("setting");
  var slackChannelId  = "SlackのchannelのID"
  var targetSheetName = "任意"
  var startDate       = optionSheet.getRange("B2").getValue();
  var endDate         = optionSheet.getRange("B3").getValue();
  var targetSheet     = spreadsheet.getSheetByName(targetSheetName);

  // コールするAPIと権限情報を定義
  // ※ conversations.history メソッドのエンドポイントを指定してください
  var slackApiUrl = 'https://slack.com/api/conversations.history'; 
  var slackToken  = 'APIキー入れる'; 
   
  // Log取得範囲の日付を取得
  var startDateObject = new Date(startDate);
  var endDateObject   = new Date(endDate);
  
  // 開始日の00:00:00
  var oldest = Math.floor(startDateObject.getTime() / 1000);
  // 終了日の23:59:59 (日付が変わる直前まで取得する場合)
  // ※もし単に日付のみ指定で0時基準ならそのままでも動きはしますが、終了日を含めるために調整することが一般的です。
  // ここでは元のロジックを尊重しますが、必要に応じて調整してください。
  var latest = Math.floor(endDateObject.getTime() / 1000); 

  // APIをコールしてログデータを取得
  var params = {
    method: 'get',
    headers: { 'Authorization': `Bearer ${slackToken}` }
  };

  // メッセージ取得
  // ※取得件数が多い場合、pagination（next_cursor）の処理が必要ですが、今回は元の仕様通り1回のリクエストとします
  var response = UrlFetchApp.fetch(`${slackApiUrl}?channel=${slackChannelId}&oldest=${oldest}&latest=${latest}&limit=1000`, params);
  var responseJson = JSON.parse(response.getContentText());
  var messages = responseJson.messages;

  if (!messages) {
    Logger.log("メッセージが取得できませんでした。権限やIDを確認してください。");
    return;
  }

  let dailyMessages = {};

  messages.forEach(message => {
    if (message.subtype !== 'bot_message') {
      var timestamp = parseInt(message.ts.split('.')[0]) * 1000;
      var date = formatDate(new Date(timestamp));
      var time = formatTime(new Date(timestamp));
      var user = getUserInfo(slackToken, message.user);
      var text = message.text || '';

      if (!dailyMessages[date]) {
        dailyMessages[date] = [];
      }

      dailyMessages[date].push(`${date} ${time} ${user}: ${text}`);

      // スレッドの返信を取得
      if (message.thread_ts) {
        var replies = getThreadReplies(slackToken, slackChannelId, message.ts);
        replies.forEach(reply => {
          // 親メッセージ自体もrepliesに含まれる場合があるため、tsが一致しないものだけ処理、あるいは元のロジック通りbot除外
          if (reply.subtype !== 'bot_message' && reply.ts !== message.ts) {
            var replyTimestamp = parseInt(reply.ts.split('.')[0]) * 1000;
            var replyDate = formatDate(new Date(replyTimestamp));
            var replyTime = formatTime(new Date(replyTimestamp));
            var replyUser = getUserInfo(slackToken, reply.user);
            var replyText = reply.text || '';
            dailyMessages[date].push(` └ ${replyDate} ${replyTime} ${replyUser}: ${replyText}`);
          }
        });
      }
    }
  });

  // --- ここから書き込み処理の変更部分 ---

  // 1. シートをクリア（既存の内容を全て消す）
  targetSheet.clear();

  // 2. ヘッダーを追加
  targetSheet.appendRow(['Date', 'Messages']);

  // 3. データを整形
  let newRows = [];
  // 日付順にソート（古い日付が上に来るように昇順ソート）
  Object.keys(dailyMessages).sort().forEach(date => {
      // 既存データのチェック(existingDatesSet)を削除し、無条件に追加
      var messagesText = dailyMessages[date].join('\n');
      newRows.push([date, messagesText]);
  });

  // 4. データがある場合、まとめて書き込み
  if (newRows.length > 0) {
    // ヘッダーが1行目にあるので、2行目から書き込み開始
    targetSheet.getRange(2, 1, newRows.length, 2).setValues(newRows);

    // セルの書式設定
    targetSheet.getRange(2, 2, newRows.length, 1).setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP); // 折り返し
    targetSheet.getRange(2, 1, newRows.length, 2).setVerticalAlignment("top"); // 上揃え（見やすくするため推奨）
  }
}

// --- 以下ヘルパー関数（変更なし） ---

function getUserInfo(slackToken,userId) {
  // キャッシュ機構がないため、大量のメッセージ処理時はAPI制限にかかる可能性があります
  if (!userId) return 'Unknown User';
  
  var userApiUrl = 'https://slack.com/api/users.info';
  var params = {
    method: 'get',
    headers: { 'Authorization': `Bearer ${slackToken}` },
    muteHttpExceptions: true
  };

  try {
    var response = UrlFetchApp.fetch(`${userApiUrl}?user=${userId}`, params);
    var result = JSON.parse(response.getContentText());
    if (result.ok && result.user) {
        return result.user.real_name || result.user.name;
    } else {
        return 'Unknown User';
    }
  } catch(e) {
    return 'Unknown User';
  }
}

function getThreadReplies(slackToken, slackChannelId, threadTs) {
  var repliesApiUrl = 'https://slack.com/api/conversations.replies';
  var params = {
    method: 'get',
    headers: { 'Authorization': `Bearer ${slackToken}` },
    muteHttpExceptions: true
  };

  try {
    var response = UrlFetchApp.fetch(`${repliesApiUrl}?channel=${slackChannelId}&ts=${threadTs}`, params);
    return JSON.parse(response.getContentText()).messages || [];
  } catch(e) {
    return [];
  }
}

function formatDate(date) {
  var year = date.getFullYear();
  var month = (date.getMonth() + 1).toString().padStart(2, '0');
  var day = date.getDate().toString().padStart(2, '0');
  return `${year}/${month}/${day}`;
}

function formatTime(date) {
  var hours = date.getHours().toString().padStart(2, '0');
  var minutes = date.getMinutes().toString().padStart(2, '0');
  var seconds = date.getSeconds().toString().padStart(2, '0');
  return `${hours}:${minutes}:${seconds}`;
}

3. Gmailのログ抽出

ここはGoogle Workspaceの flows を活用しました。

「Gmailのデータを使って何かしてみたい！」とは前々から思っていましたが、やはりここでもAPI連携の壁に何度も挫折してきました……。

しかし、flowsであれば同じGoogle Workspace内なので面倒な連携作業が不要で、驚くほど簡単に設定できました。

「Toが特定のアドレスのメールの時に～」という設定をしています
上記の条件のメールの時、指定したスプレッドシートに行を足して追記するように指示しています
追記する内容は、指定したスプレッドシートであらかじめ用意しておいた項目名（件名、from、to、時間、本文、cc）を勝手に反映してくれています。それぞれに項目が対応するデータを設定すればOKです！

こんな感じでデータが集まります！（※ダミーデータです）

4. Gemで学習

集まったデータを元に、Geminiに指示を出します。

写真のように、スプレッドシートを「知識」としてGemsに読み込ませることができるのが非常に便利です。

スプレッドシート自体は自動更新されるようにしているので、Gem側の編集は特に不要でとても楽です。

サンプルプロンプト

あなたは優秀なプロジェクトマネージャーのアシスタントです。
接続されているスプレッドシートのデータを使い、各案件の進捗状況を要約したレポートを作成してください。

### 参照データ
1. **案件リスト:**  「案件まとめ_group2」のスプレットシート（A列:CID, B列:案件名, C列:担当者名）
2. **ログデータ:**  「Log_Mail」「Log_Slack」のスプレッドシート

### 実行手順
「案件管理」シートに記載されている全ての案件（行）について、上から順に以下の処理を行ってください。

1. **検索:** その案件の「ID」または「案件名」に関連する情報を、「Log_Mail」と「Log_Slack」の両方から検索してください。
2. **要約:** 直近のやり取りやトラブルの有無、次のアクションなどを踏まえ、現在の状況を「2〜3文」で簡潔に要約してください。（日付があれば「12/8時点で〜」のように含めるとベストです）
3. **該当なし:** ログが見つからない場合は「ログなし」と記載してください。

### 出力形式
以下のヘッダーを持つ**表形式（テーブル）**で出力してください。

コピー＆ペーストしやすいように、「案件管理」シートの並び順を崩さないでください

| CID | 案件名 | 担当者名 |状況まとめ（D列用） |

| :--- | :--- | :--- | :--- |

| (A列の値) | (B列の値) | (C列の値) |(生成された要約テキスト) |

これで毎日このGemに「おねがいします～」など挨拶すると以下のようにまとめてくれます
この出力結果は再度スプレッドシートにすることも可能なので使い勝手がとても良いです。

おわりに

まだまだインティメート・マージャーアドベントカレンダーは続きます！
明日の記事もお楽しみに！

また、インティメート・マージャーでは、新卒から中途採用まで幅広く採用募集中です！
記事を読んで弊社に興味を持ってくれた方は、下記より採用情報をチェック！