【GAS】スプレッドシートに記載のある期日を見つけてアラートメールを送信させる方法

Googleスプレッドシート用のGAS備忘録。
会社で利用するチャットツールにアラートを飛ばす目的で作成したスクリプトですが、意外と汎用性高く利用できそうだったので残します。

/**
 * スプレッドシートの複数の期限列をチェックし、本日が期日の案件があればアラートメールを送信します。
 */
function sendDeadlineAlerts() {
  const SPREADSHEET_NAME = "発注管理"; // 処理対象のシート名
  const RECIPIENT_EMAIL = "XXXXXXXX@XXYYZZ.com"; // アラートの送信先メールアドレス

  //  アラート設定リスト 
  // 案件によってチェックする列やメールの内容が異なる場合に、ここに追加・変更します。
  const alertConfigs = [
    {
      // ○○作成期日のアラート設定
      dueDateColumnIndex: 11, // L列 (インデックス11)
      projectNameColumnIndex: 5, // F列 (インデックス5)
      subject: "本日が【○○作成期日】の案件があります",
      deadlineName: "○○作成の期日"
    },
    {
      // △△期限のアラート設定
      dueDateColumnIndex: 14, // O列 (インデックス14)
      projectNameColumnIndex: 5, // F列 (インデックス5)
      subject: "本日が【△△期限】の案件があります",
      deadlineName: "△△の期日"
    }
    //  新しい期限チェックが必要な場合は、ここに新しいオブジェクトを追加してください。
  ];

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName(SPREADSHEET_NAME);

  if (!sheet) {
    Logger.log("シートが見つかりません: " + SPREADSHEET_NAME);
    return;
  }

  // データ範囲の取得 (4行目から最終行まで)
  const dataRange = sheet.getRange(4, 1, sheet.getLastRow() - 3, sheet.getLastColumn());
  const values = dataRange.getValues();

  const today = new Date();
  // タイムゾーンをJSTに指定し、比較用の形式にフォーマット
  const todayFormatted = Utilities.formatDate(today, "JST", "yyyy/MM/dd");
  Logger.log("本日の日付 (JST): " + todayFormatted);

  const spreadsheetUrl = spreadsheet.getUrl();
  let totalAlertsSent = 0;

  // 設定リストをループし、それぞれのアラートをチェック・送信
  alertConfigs.forEach(config => {
    let emailBody = "";
    let alertCount = 0;

    // 件名のプレースホルダーを設定ごとの件名に置き換え
    const subject = config.subject;

    values.forEach(function(row) {
      const dueDateValue = row[config.dueDateColumnIndex]; // 設定で指定された期限列の値
      let dueDate;

      if (dueDateValue) {
        if (dueDateValue instanceof Date) {
          dueDate = new Date(dueDateValue);
        } else {
          dueDate = new Date(dueDateValue);
        }

        // 有効な日付であり、かつ本日付と一致するかチェック
        if (!isNaN(dueDate.getTime())) {
          const dueDateFormatted = Utilities.formatDate(dueDate, "JST", "yyyy/MM/dd");

          if (dueDateFormatted === todayFormatted) {
            const projectName = row[config.projectNameColumnIndex]; // 案件名列の値
            if (projectName) {
              emailBody += "案件名：「" + projectName + "」の" + config.deadlineName + "は本日までです。\n";
              alertCount++;
            }
          }
        }
      }
    });

    // アラートメールの送信
    if (alertCount > 0) {
      emailBody += "\nスプレッドシートのURL:\n" + spreadsheetUrl;
      MailApp.sendEmail(RECIPIENT_EMAIL, subject, emailBody);
      Logger.log(`${config.deadlineName}に関するアラートメールを送信しました。（${alertCount}件）`);
      totalAlertsSent++;
    } else {
      Logger.log(`本日が期日の${config.deadlineName}の案件はありませんでした。`);
    }
  });

  if (totalAlertsSent === 0) {
    Logger.log("本日は期限アラートの送信はありませんでした。");
  }
}

プレースホルダとしている

const RECIPIENT_EMAIL = "XXXXXXXX@XXYYZZ.com"; // アラートの送信先メールアドレス

const alertConfigs = [
  {
    // ○○作成期日のアラート設定
    dueDateColumnIndex: 11, // L列 (インデックス11)
    subject: "本日が【○○作成期日】の案件があります",
    deadlineName: "○○作成の期日"
  },
  // ... (△△の設定も同様)
];

この部分はご自身の必要に応じて書き換えてください。

トリガーの設定は以下の表を見てください。

おまけ

汎用化されたコードで「期日が来たらスプレッドシートの特定のセルから情報を取得してメールを送る」スクリプトについても書いておきます。

/**
 * Sheet1のD列（送信日）が本日と一致する案件に対し、
 * Sheet2のテンプレートを用いてメールを送信します。
 */
function sendDeadlineAlerts() {
  const DATA_SHEET_NAME = "Sheet1";
  const TEMPLATE_SHEET_NAME = "Sheet2";

  // データ列のインデックス (A=0, B=1, C=2, D=3)
  const PROJECT_NAME_COL = 0;   // A列: 案件名
  const CONTACT_PERSON_COL = 1; // B列: 担当者
  const RECIPIENT_EMAIL_COL = 2;// C列: メールアドレス
  const DUE_DATE_COL = 3;       // D列: 送信日（期日）

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = spreadsheet.getSheetByName(DATA_SHEET_NAME);
  const templateSheet = spreadsheet.getSheetByName(TEMPLATE_SHEET_NAME);

  if (!dataSheet) {
    Logger.log("データシートが見つかりません: " + DATA_SHEET_NAME);
    return;
  }
  if (!templateSheet) {
    Logger.log("テンプレートシートが見つかりません: " + TEMPLATE_SHEET_NAME);
    return;
  }

  // Sheet2から件名と本文のテンプレートを取得
  const subjectTemplate = templateSheet.getRange("A2").getValue();
  const bodyTemplate = templateSheet.getRange("B2").getValue();

  if (!subjectTemplate || !bodyTemplate) {
    Logger.log("Sheet2のA2(件名)またはB2(本文)にテンプレートが設定されていません。");
    return;
  }

  // データ範囲の取得 (2行目から最終行までを想定)
  // 1行目はヘッダーとしてスキップします
  const lastRow = dataSheet.getLastRow();
  if (lastRow < 2) {
    Logger.log("Sheet1に案件データがありません（2行目以降）。");
    return;
  }

  const dataRange = dataSheet.getRange(2, 1, lastRow - 1, dataSheet.getLastColumn());
  const values = dataRange.getValues();

  // 今日の日付を取得し、比較用にJSTでフォーマット
  const today = new Date();
  const todayFormatted = Utilities.formatDate(today, "JST", "yyyy/MM/dd");
  Logger.log("本日の日付 (JST): " + todayFormatted);

  let alertCount = 0;

  values.forEach(function(row) {
    const projectName = row[PROJECT_NAME_COL];
    const contactPerson = row[CONTACT_PERSON_COL];
    const recipientEmail = row[RECIPIENT_EMAIL_COL];
    const dueDateValue = row[DUE_DATE_COL]; // D列の値（送信日）
    
    // 必須情報が欠けていたらスキップ
    if (!projectName || !contactPerson || !recipientEmail || !dueDateValue) {
      // Logger.log("必須項目が不足している行をスキップしました。");
      return;
    }

    let dueDate;
    // 日付として認識されるか、文字列からの変換を試みる
    if (dueDateValue instanceof Date) {
      dueDate = new Date(dueDateValue);
    } else {
      dueDate = new Date(dueDateValue);
    }

    // 有効な日付か確認し、本日付と一致するかチェック
    if (!isNaN(dueDate.getTime())) {
      const dueDateFormatted = Utilities.formatDate(dueDate, "JST", "yyyy/MM/dd");

      if (dueDateFormatted === todayFormatted) {
        
        // 1. メール本文の組み立て (案件名, 担当者名を文頭に挿入)
        let emailBody = `${projectName}\n`;
        emailBody += `${contactPerson} 様\n\n`;
        emailBody += bodyTemplate;
        
        // 2. メール送信
        MailApp.sendEmail(
          recipientEmail, 
          subjectTemplate, 
          emailBody
        );
        
        alertCount++;
        Logger.log(`[${projectName}] のアラートを ${recipientEmail} 宛に送信しました。`);
      }
    }
  });

  if (alertCount > 0) {
    Logger.log(`本日が送信日の案件について、計 ${alertCount} 件のメールを送信しました。`);
  } else {
    Logger.log("本日が送信日の案件はありませんでした。");
  }
}

トリガーは先の設定を参考にしてください。

スプレッドシートに書き込む日付については、条件を守って記載するようにしてください。
全角数字・日本語等、ルール無視の記載の場合、スクリプトはきちんと動作しません。