セミナー動画の送付を効率化｜GASで名前入りGmail下書きを一括作成

/**
 * セミナー案内メール作成ツール
 * Gmail下書き一括作成・履歴管理
 *
 * ■ 設定シート
 * B2：セミナー名
 * B3：件名
 * B4：動画URL
 * B5：セミナー資料URL（空欄可）
 * B6：メール本文
 * B7：送信元メールアドレス
 * B8：送信者名
 *
 * ■ 送信先シート
 * A列：氏名
 * B列：メールアドレス
 * C列：状態
 * D列：下書き作成日時
 * E列：下書きID
 *
 * ■ 状態
 * 空欄             ：未処理
 * 処理中           ：処理途中
 * 下書き作成済み   ：正常終了
 * エラー           ：下書き作成前などにエラー
 * 要確認           ：下書き作成後に後続処理でエラー
 */

const CONFIG = {
  SETTING_SHEET: '設定',
  RECIPIENT_SHEET: '送信先',
  HISTORY_SHEET: '下書き履歴',

  STATUS_PROCESSING: '処理中',
  STATUS_COMPLETED: '下書き作成済み',
  STATUS_ERROR: 'エラー',
  STATUS_REVIEW: '要確認',

  ALLOWED_STATUSES: [
    '',
    '処理中',
    '下書き作成済み',
    'エラー',
    '要確認'
  ]
};


// ==================================================
// スプレッドシートを開いたときにメニュー追加
// ==================================================
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('セミナーメール')
    .addItem('下書きを一括作成', 'createSeminarDrafts')
    .addSeparator()
    .addItem('処理中・要確認の行を確認', 'showRowsNeedingAttention')
    .addItem('下書き履歴を開く', 'openHistorySheet')
    .addItem('利用可能な送信元を確認', 'showAvailableSenders')
    .addToUi();
}


// ==================================================
// Gmail下書きを一括作成
// ==================================================
function createSeminarDrafts() {
  const ui = SpreadsheetApp.getUi();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const lock = LockService.getDocumentLock();

  // 二重実行防止
  if (!lock.tryLock(3000)) {
    ui.alert('現在、別の処理が実行中です。');
    return;
  }

  try {
    const settingSheet = ss.getSheetByName(CONFIG.SETTING_SHEET);
    const recipientSheet = ss.getSheetByName(CONFIG.RECIPIENT_SHEET);

    // --------------------------------------------------
    // シート確認
    // --------------------------------------------------
    if (!settingSheet) {
      throw new Error('「設定」シートがありません。');
    }

    if (!recipientSheet) {
      throw new Error('「送信先」シートがありません。');
    }

    // E1が空欄なら自動入力
    if (!String(recipientSheet.getRange('E1').getValue()).trim()) {
      recipientSheet.getRange('E1').setValue('下書きID');
    }

    // --------------------------------------------------
    // 設定取得
    // --------------------------------------------------
    const seminarName =
      String(settingSheet.getRange('B2').getValue()).trim();

    const subjectTemplate =
      String(settingSheet.getRange('B3').getValue()).trim();

    const videoUrl =
      String(settingSheet.getRange('B4').getValue()).trim();

    const materialUrlRaw =
      String(settingSheet.getRange('B5').getValue()).trim();

    const bodyTemplate =
      String(settingSheet.getRange('B6').getValue());

    const fromAddress =
      String(settingSheet.getRange('B7').getValue()).trim();

    const senderName =
      String(settingSheet.getRange('B8').getValue()).trim();

    // 資料URLが空欄なら「該当なし」
    const materialUrl =
      materialUrlRaw || '該当なし';

    // --------------------------------------------------
    // 必須項目チェック
    // --------------------------------------------------
    const missingItems = [];

    if (!seminarName) {
      missingItems.push('セミナー名');
    }

    if (!subjectTemplate) {
      missingItems.push('件名');
    }

    if (!videoUrl) {
      missingItems.push('動画URL');
    }

    if (!bodyTemplate.trim()) {
      missingItems.push('メール本文');
    }

    if (!fromAddress) {
      missingItems.push('送信元メールアドレス');
    }

    if (!senderName) {
      missingItems.push('送信者名');
    }

    if (missingItems.length > 0) {
      throw new Error(
        '次の設定項目を入力してください。\n\n' +
        missingItems.join('\n')
      );
    }

    // --------------------------------------------------
    // 送信元チェック
    // --------------------------------------------------
    const senderInfo = getSenderInfo_(fromAddress);

    if (!senderInfo.valid) {
      throw new Error(
        '指定した送信元メールアドレスを利用できません。\n\n' +
        '指定：\n' +
        fromAddress +
        '\n\n' +
        senderInfo.availableText
      );
    }

    // --------------------------------------------------
    // 下書き履歴シートを先に確認
    // --------------------------------------------------
    const historySheet =
      getOrCreateHistorySheet_(ss);

    // --------------------------------------------------
    // 送信先取得
    // --------------------------------------------------
    const lastRow =
      recipientSheet.getLastRow();

    if (lastRow < 2) {
      throw new Error(
        '送信先が登録されていません。'
      );
    }

    const recipientData =
      recipientSheet
        .getRange(
          2,
          1,
          lastRow - 1,
          5
        )
        .getValues();

    // --------------------------------------------------
    // 全件バリデーション
    // 不備が1件でもあれば処理を止める
    // --------------------------------------------------
    const validationErrors = [];
    const emailRows = new Map();

    recipientData.forEach((row, index) => {
      const rowNumber = index + 2;

      const name =
        String(row[0]).trim();

      const email =
        String(row[1]).trim();

      const status =
        String(row[2]).trim();

      const createdAt =
        row[3];

      const draftId =
        String(row[4]).trim();

      const completelyBlank =
        !name &&
        !email &&
        !status &&
        !createdAt &&
        !draftId;

      if (completelyBlank) {
        return;
      }

      // 状態欄の想定外文字
      if (!CONFIG.ALLOWED_STATUSES.includes(status)) {
        validationErrors.push(
          `${rowNumber}行目：状態「${status}」は想定外です。`
        );
      }

      // 氏名が空欄
      if (!name && email) {
        validationErrors.push(
          `${rowNumber}行目：氏名が空欄です。`
        );
        return;
      }

      // メールアドレスが空欄
      if (name && !email) {
        validationErrors.push(
          `${rowNumber}行目：メールアドレスが空欄です。`
        );
        return;
      }

      // 氏名・メールが空欄なのに状態等が残っている
      if (!name && !email) {
        validationErrors.push(
          `${rowNumber}行目：氏名・メールアドレスが空欄ですが、状態等のデータが残っています。`
        );
        return;
      }

      // メール形式
      if (!isValidEmail_(email)) {
        validationErrors.push(
          `${rowNumber}行目：メールアドレスの形式を確認してください。\n${email}`
        );
      }

      // メールアドレス重複
      const normalizedEmail =
        email.toLowerCase();

      if (emailRows.has(normalizedEmail)) {
        validationErrors.push(
          `${rowNumber}行目：メールアドレスが重複しています。\n` +
          `${email}\n` +
          `（${emailRows.get(normalizedEmail)}行目と重複）`
        );
      } else {
        emailRows.set(
          normalizedEmail,
          rowNumber
        );
      }
    });

    if (validationErrors.length > 0) {
      throw new Error(
        '送信先に確認が必要な箇所があります。\n\n' +
        validationErrors.join('\n\n')
      );
    }

    // --------------------------------------------------
    // 状態が空欄の行だけ処理対象
    // --------------------------------------------------
    const targetIndexes = [];

    recipientData.forEach((row, index) => {
      const name =
        String(row[0]).trim();

      const email =
        String(row[1]).trim();

      const status =
        String(row[2]).trim();

      if (
        name &&
        email &&
        status === ''
      ) {
        targetIndexes.push(index);
      }
    });

    if (targetIndexes.length === 0) {
      ui.alert(
        '新しく下書きを作成する送信先がありません。\n\n' +
        '「状態」が空欄の行だけが処理対象です。'
      );
      return;
    }

    const skippedCount =
      recipientData.filter(row => {
        const name =
          String(row[0]).trim();

        const email =
          String(row[1]).trim();

        const status =
          String(row[2]).trim();

        return (
          name &&
          email &&
          status !== ''
        );
      }).length;

    // --------------------------------------------------
    // 実行前確認
    // --------------------------------------------------
    let confirmMessage =
      `${targetIndexes.length}件のGmail下書きを作成します。\n\n` +
      `セミナー：\n${seminarName}\n\n` +
      `送信元：\n${senderName} <${fromAddress}>\n\n` +
      `送信元種類：${senderInfo.type}`;

    if (skippedCount > 0) {
      confirmMessage +=
        `\n\n処理対象外：${skippedCount}件` +
        '\n（状態が入力済みのため）';
    }

    confirmMessage +=
      '\n\nよろしいですか？';

    const response =
      ui.alert(
        '下書き作成',
        confirmMessage,
        ui.ButtonSet.YES_NO
      );

    if (response !== ui.Button.YES) {
      return;
    }

    // --------------------------------------------------
    // 1件ずつ処理
    // --------------------------------------------------
    let createdCount = 0;
    let errorCount = 0;
    let reviewCount = 0;

    const errors = [];

    targetIndexes.forEach((index, position) => {
      const sheetRow =
        index + 2;

      const name =
        String(recipientData[index][0]).trim();

      const email =
        String(recipientData[index][1]).trim();

      let draft = null;
      let draftId = '';
      let createdAt = '';

      // 進捗表示
      ss.toast(
        `${position + 1} / ${targetIndexes.length}件目：${name}さん`,
        'セミナーメール処理中',
        3
      );

      try {
        // ------------------------------------------
        // 「処理中」を先に保存
        // ------------------------------------------
        recipientSheet
          .getRange(sheetRow, 3)
          .setValue(CONFIG.STATUS_PROCESSING);

        SpreadsheetApp.flush();

        // ------------------------------------------
        // 件名・本文作成
        // ------------------------------------------
        const subject =
          replaceTags_(
            subjectTemplate,
            name,
            seminarName,
            videoUrl,
            materialUrl
          );

        const body =
          replaceTags_(
            bodyTemplate,
            name,
            seminarName,
            videoUrl,
            materialUrl
          );

        // ------------------------------------------
        // Gmail下書き作成
        // ------------------------------------------
        if (senderInfo.isPrimary) {
          // Gmail本体アドレス
          draft = GmailApp.createDraft(
            email,
            subject,
            body,
            {
              name: senderName
            }
          );
        } else {
          // Gmail送信元エイリアス
          draft = GmailApp.createDraft(
            email,
            subject,
            body,
            {
              from: senderInfo.alias,
              name: senderName
            }
          );
        }

        createdAt =
          new Date();

        draftId =
          draft.getId();

        // ------------------------------------------
        // 作成日時・下書きIDを即時保存
        // ------------------------------------------
        recipientSheet
          .getRange(
            sheetRow,
            4,
            1,
            2
          )
          .setValues([[
            createdAt,
            draftId
          ]]);

        SpreadsheetApp.flush();

        // ------------------------------------------
        // 下書き履歴へ保存
        // ------------------------------------------
        historySheet.appendRow([
          createdAt,
          seminarName,
          name,
          email,
          subject,
          fromAddress,
          senderName,
          senderInfo.type,
          videoUrl,
          materialUrl,
          draftId,
          '下書き作成'
        ]);

        // ------------------------------------------
        // 最後に完了
        // ------------------------------------------
        recipientSheet
          .getRange(sheetRow, 3)
          .setValue(CONFIG.STATUS_COMPLETED);

        SpreadsheetApp.flush();

        createdCount++;

      } catch (error) {
        // ------------------------------------------
        // Gmail下書き作成後にエラー
        // → 二重作成防止のため「要確認」
        // ------------------------------------------
        if (draft) {
          try {
            if (!draftId) {
              draftId = draft.getId();
            }
          } catch (_) {
            // ID取得失敗時は空欄
          }

          if (!createdAt) {
            createdAt = new Date();
          }

          recipientSheet
            .getRange(
              sheetRow,
              3,
              1,
              3
            )
            .setValues([[
              CONFIG.STATUS_REVIEW,
              createdAt,
              draftId
            ]]);

          SpreadsheetApp.flush();

          reviewCount++;

          errors.push(
            `${name} / ${email}：要確認\n${error.message}`
          );

          return;
        }

        // ------------------------------------------
        // 下書き作成前にエラー
        // ------------------------------------------
        recipientSheet
          .getRange(sheetRow, 3)
          .setValue(CONFIG.STATUS_ERROR);

        SpreadsheetApp.flush();

        errorCount++;

        errors.push(
          `${name} / ${email}：エラー\n${error.message}`
        );
      }
    });

    // --------------------------------------------------
    // 完了表示
    // --------------------------------------------------
    ss.toast(
      '処理が完了しました。',
      'セミナーメール',
      5
    );

    let message =
      `下書き作成済み：${createdCount}件`;

    if (skippedCount > 0) {
      message +=
        `\n処理対象外：${skippedCount}件`;
    }

    if (errorCount > 0) {
      message +=
        `\nエラー：${errorCount}件`;
    }

    if (reviewCount > 0) {
      message +=
        `\n要確認：${reviewCount}件`;
    }

    // エラー再処理方法
    if (errorCount > 0) {
      message +=
        '\n\n「エラー」の行を再処理する場合は、' +
        '原因を確認したうえで状態欄を空欄に戻してください。';
    }

    // 要確認時の注意
    if (reviewCount > 0) {
      message +=
        '\n\n「要確認」の行は、Gmailの下書きを確認してから' +
        '状態欄を修正してください。' +
        '\n下書きが存在する状態で空欄に戻すと、二重作成の原因になります。';
    }

    if (errors.length > 0) {
      message +=
        '\n\n詳細：\n\n' +
        errors.join('\n\n');
    }

    ui.alert(
      '処理完了',
      message,
      ui.ButtonSet.OK
    );

  } catch (error) {
    console.error(error);

    ui.alert(
      '処理中にエラーが発生しました',
      error.message,
      ui.ButtonSet.OK
    );

  } finally {
    lock.releaseLock();
  }
}


// ==================================================
// 差し込みタグ置換
// ==================================================
function replaceTags_(
  text,
  name,
  seminarName,
  videoUrl,
  materialUrl
) {
  return String(text)
    .replace(/\{\{name\}\}/g, name)
    .replace(/\{\{seminar\}\}/g, seminarName)
    .replace(/\{\{video_url\}\}/g, videoUrl)
    .replace(/\{\{material_url\}\}/g, materialUrl);
}


// ==================================================
// メールアドレス簡易チェック
// ==================================================
function isValidEmail_(email) {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email);
}


// ==================================================
// 送信元判定
// ==================================================
function getSenderInfo_(fromAddress) {
  const primaryAddress =
    String(
      Session
        .getEffectiveUser()
        .getEmail()
    ).trim();

  const aliases =
    GmailApp.getAliases();

  const normalizedFrom =
    fromAddress.toLowerCase();

  const normalizedPrimary =
    primaryAddress.toLowerCase();

  const isPrimary =
    primaryAddress !== '' &&
    normalizedFrom === normalizedPrimary;

  const matchedAlias =
    aliases.find(
      alias =>
        alias.toLowerCase() === normalizedFrom
    );

  // Gmail本体
  if (isPrimary) {
    return {
      valid: true,
      isPrimary: true,
      alias: '',
      type: 'Gmail本体',
      availableText: ''
    };
  }

  // 登録済みエイリアス
  if (matchedAlias) {
    return {
      valid: true,
      isPrimary: false,
      alias: matchedAlias,
      type: '送信元エイリアス',
      availableText: ''
    };
  }

  // 利用できない場合
  let availableText =
    '【Gmail本体】\n';

  if (primaryAddress) {
    availableText +=
      primaryAddress;
  } else {
    availableText +=
      '取得できませんでした。';
  }

  availableText +=
    '\n\n【登録済み送信元】\n';

  if (aliases.length > 0) {
    availableText +=
      aliases.join('\n');
  } else {
    availableText +=
      'なし';
  }

  return {
    valid: false,
    isPrimary: false,
    alias: '',
    type: '',
    availableText: availableText
  };
}


// ==================================================
// 下書き履歴シート取得・作成
// ==================================================
function getOrCreateHistorySheet_(ss) {
  const headers = [
    '処理日時',
    'セミナー名',
    '氏名',
    'メールアドレス',
    '件名',
    '送信元メールアドレス',
    '送信者名',
    '送信元種類',
    '動画URL',
    'セミナー資料URL',
    '下書きID',
    '処理内容'
  ];

  let sheet =
    ss.getSheetByName(
      CONFIG.HISTORY_SHEET
    );

  // ------------------------------------------
  // 存在しなければ作成
  // ------------------------------------------
  if (!sheet) {
    sheet =
      ss.insertSheet(
        CONFIG.HISTORY_SHEET
      );

    sheet
      .getRange(
        1,
        1,
        1,
        headers.length
      )
      .setValues([headers]);

    formatHistorySheet_(sheet);

    return sheet;
  }

  // ------------------------------------------
  // 空シートの場合
  // ------------------------------------------
  if (sheet.getLastRow() === 0) {
    sheet
      .getRange(
        1,
        1,
        1,
        headers.length
      )
      .setValues([headers]);

    formatHistorySheet_(sheet);

    return sheet;
  }

  // ------------------------------------------
  // 列構成確認
  // ------------------------------------------
  const currentHeaders =
    sheet
      .getRange(
        1,
        1,
        1,
        headers.length
      )
      .getValues()[0]
      .map(
        value =>
          String(value).trim()
      );

  if (!arraysEqual_(currentHeaders, headers)) {
    throw new Error(
      '「下書き履歴」シートの列構成が想定と異なります。\n\n' +
      '列を手動で変更していないか確認してください。'
    );
  }

  formatHistorySheet_(sheet);

  return sheet;
}


// ==================================================
// 下書き履歴シート書式
// ==================================================
function formatHistorySheet_(sheet) {
  sheet.setFrozenRows(1);

  sheet
    .getRange('A:A')
    .setNumberFormat(
      'yyyy/mm/dd hh:mm:ss'
    );
}


// ==================================================
// 配列一致
// ==================================================
function arraysEqual_(a, b) {
  return (
    a.length === b.length &&
    a.every(
      (value, index) =>
        value === b[index]
    )
  );
}


// ==================================================
// 下書き履歴を開く
// ==================================================
function openHistorySheet() {
  const ui =
    SpreadsheetApp.getUi();

  try {
    const ss =
      SpreadsheetApp
        .getActiveSpreadsheet();

    const historySheet =
      getOrCreateHistorySheet_(ss);

    ss.setActiveSheet(
      historySheet
    );

  } catch (error) {
    console.error(error);

    ui.alert(
      '下書き履歴を開けませんでした',
      error.message,
      ui.ButtonSet.OK
    );
  }
}


// ==================================================
// 処理中・要確認の行を確認
// ==================================================
function showRowsNeedingAttention() {
  const ui =
    SpreadsheetApp.getUi();

  const ss =
    SpreadsheetApp
      .getActiveSpreadsheet();

  const sheet =
    ss.getSheetByName(
      CONFIG.RECIPIENT_SHEET
    );

  if (!sheet) {
    ui.alert(
      '「送信先」シートがありません。'
    );
    return;
  }

  const lastRow =
    sheet.getLastRow();

  if (lastRow < 2) {
    ui.alert(
      '送信先がありません。'
    );
    return;
  }

  const data =
    sheet
      .getRange(
        2,
        1,
        lastRow - 1,
        5
      )
      .getValues();

  const rows = [];

  data.forEach((row, index) => {
    const status =
      String(row[2]).trim();

    if (
      status === CONFIG.STATUS_PROCESSING ||
      status === CONFIG.STATUS_REVIEW
    ) {
      rows.push({
        row: index + 2,
        name: String(row[0]).trim(),
        email: String(row[1]).trim(),
        status: status,
        draftId: String(row[4]).trim()
      });
    }
  });

  if (rows.length === 0) {
    ui.alert(
      '「処理中」または「要確認」の行はありません。'
    );
    return;
  }

  const message =
    rows
      .map(item => {
        let text =
          `${item.row}行目：${item.status}\n` +
          `${item.name} / ${item.email}`;

        if (item.draftId) {
          text +=
            `\n下書きID：${item.draftId}`;
        }

        return text;
      })
      .join('\n\n');

  ui.alert(
    '確認が必要な行',
    message +
      '\n\nGmailの下書きを確認してから、状態欄を手動で修正してください。',
    ui.ButtonSet.OK
  );
}


// ==================================================
// 利用可能な送信元を確認
// ==================================================
function showAvailableSenders() {
  const ui =
    SpreadsheetApp.getUi();

  try {
    const primaryAddress =
      String(
        Session
          .getEffectiveUser()
          .getEmail()
      ).trim();

    const aliases =
      GmailApp.getAliases();

    let message =
      '【Gmail本体】\n';

    if (primaryAddress) {
      message +=
        primaryAddress;
    } else {
      message +=
        '取得できませんでした。';
    }

    message +=
      '\n\n【登録済み送信元】\n';

    if (aliases.length > 0) {
      message +=
        aliases.join('\n');
    } else {
      message +=
        'なし';
    }

    ui.alert(
      '利用可能な送信元',
      message,
      ui.ButtonSet.OK
    );

  } catch (error) {
    console.error(error);

    ui.alert(
      '送信元を確認できませんでした',
      error.message,
      ui.ButtonSet.OK
    );
  }
}