新卒が書いている日報を先輩社員がレビューし、フィードバックする試みをディップでは行っています。

先輩社員はローテーション制で確認していますが、必ず決まった週の曜日にやるなどの規則性を持たせられないほど先輩社員が増えました。
そこでリマインダーとして GAS(Google Apps Script)からメール送信する仕組みを作りました。

どう実現するか？

GAS のトリガーを用いて日時実行されるようにしました。
ロジックは下記です。

マスタデータ

3 種類のシートを作ります。

メンバーのマスタシート

名前とメールアドレスの組だけを記載してあるマスタシートです。

担当日シート

担当日と担当者が記載されているシートです。
1 枚のシートで管理してもよかったのですが。スクロールが多くなり大変だったので、月ごとに分ける運用にしました。
（シート名は 2023/03 みたいな感じ）

運用で祝日は避けながら担当者を入れて決めていきます。
VLOOKUP 関数でメールアドレスについては取得しています。
=IFERROR(VLOOKUP($B2, 'マスタ'!$A$2:$B$4, 2, False), "") のような形です。

クエリ抽出用のシート

今回の肝になる Query 関数を実行し、結果を取得するシートです。
GAS で本日の日付になるよう、毎日データを書き換えます。

=QUERY('2023/03'!A:C, "SELECT A, B, C WHERE A = date '2023-03-28'")
こんな形で指定すると、下記のように取得できます。

GASのコードを書く

さてここからがコードの話です。

SpreadSheetから値を取得する

ざっくりとなりますが、こんな感じです。
日付をフォーマットする処理についてはいろいろありますが、今回は ChatGPT に聞いた結果をもとに、 toLocaleDateString を使ってみました。

function getSpreadSheet(sheetId) {
  // シートの取得
  const book = SpreadsheetApp.openById(sheetId);
  if(!book) {
    throw new Error("The book is not usable");
  }
  let sheet = book.getSheetByName('本日担当');
  // シートがなかった場合は新規作成
  if(!sheet) {
    sheet = book.insertSheet('本日担当');
  }
  return sheet;
}

function getSheetName(date) {
  // 2023/03 のようなシート名を取得する
  const options = { year: 'numeric', month: '2-digit' };
  return date.toLocaleDateString('ja-JP', options).replace(/\//g, "/");
}

function getTodayDate(date) {
  // 2023-03-28 のような日付を取得する
  const options = { year: 'numeric', month: '2-digit', day: '2-digit' };
  return date.toLocaleDateString('ja-JP', options).replace(/\//g, "-");
}

function getOPersonInChargeForToday(sheetId) {
  const sheet = getSpreadSheet(sheetId);
  const date = new Date();
  const targetSheet = getSheetName(date);
  const today = getTodayDate(date);
  // これができるように…
  // =QUERY('2023/03'!A:C, "SELECT A, B, C WHERE A = date '2023-03-28'")
  sheet.getRange("A1").setValue(`=QUERY(\'${targetSheet}\'!A:C, "SELECT A, B, C WHERE A = date \'${today}\'")`);
  const data = sheet.getRange("A1:C2").getDisplayValues();
  return {
    recipient: data[1][2],
    subject: `${today} のレビュー担当者です`,
    body: `本日レビュー日です。
（ここにレビュー用のURLを書く）`,
  }
}

function myFunction() {
  // ここをスタートポイントとする
  const sheetId = PropertiesService.getScriptProperties().getProperty('SHEET_ID');
  if(sheetId === null || sheetId.length === 0){
    Logger.log("Skip: Invalid SpreadSheet Id");
    return;
  }

  try {
    const result = getOPersonInChargeForToday(sheetId);
    sendMail(result);
  } catch (e) {
    Logger.log(e);
  }
}

メールを送信する

GAS を使って自分自身のメールアドレスからメールを送信する機能は簡単に実現できます。
GmailApp.sendEmail を使うだけです。

function sendMail(data) {
  // 今回はサンプルのためメールアドレスは適当になっていますが、本来は適当にチェックしておくと安心です
  // バリデーション用の関数はChatGPTとかで聞いてみるといいのかもしれません
  GmailApp.sendEmail(
    data['recipient'],
    data['subject'],
    data['body']
  );
}

改善できそうな点について

クエリの書き換え

本日の日付はうまく関数を組み合わせれば、GAS からクエリを書き換える必要はないかもしれません。
（ロジック自体が不要というお話）。
うまくやる方法については気になったときにでも ChatGPT で聞いてみようかと思います。

通知先

Slack または Teams への通知のほうがいいと思うので、この点も改善ポイントだと思います。
これも WebHook とキーがあればできるとは思うので、いろいろ試せそうですね。

GitHubの担当者アサイン

日報は Github で管理しているので、特定のメンバーが作った PR であれば担当者にアサインしておくというのも API で実現可能だと思います。
ただ先輩社員の中にはマネージャー担当をしている方もおり、Github を常に見ているわけではありません。

チームの状況にとって機能を実装すべきかどうかは検討の余地があります。

休日、祝日判定の自動化

祝日に関しては日本の祝日カレンダーをうまく使えば回避できるような気がします。
休日に関してはいい方法が浮かんでいませんが、JavaScript で土曜日・日曜日の場合は一律で送らないようにしつつ、同じように会社の休日カレンダーみたいなものを運用するのがいいのかな？　と思っています。

いろいろ考えた結果、月末月初に 5 分程度手で入れれば済むものを頑張るか？　という疑問が残り、今回は対応しませんでしたが、場合によってはそういった運用方法も検討できるかと思います。

まとめ

手でリマインドする温かみのある対応もありますが、人が増えてきたときにはこういう仕組みは必要だと思って作りました。
メールを余りチェックしないメンバーもおり、うまくワークしなかった部分もありましたが毎日リマインドするよりは運用コストは下げられました。

改善出来そうな点についてはチームの状況によっては機能を追加するなどをしていきたいですね。