脱Excel！見積PDFを自動送信！GASで営業通知を完全自動化（第3回）

仕事
2025.11.10

「フォーム入力→PDF生成」で止まっていませんか？ 本稿ではさらに一歩踏み込み、見積PDFを自動添付してメール送信、ついでに送信履歴をシートに蓄積します。
ポイントは3つ。GmailAppの使い方／PDFのBlob化／ログ管理。10分で“現場で使える自動化”に進化させましょう。

今日やること（ゴール）

  • フォーム回答と同時に2通のメールを自動送信
    ① お客様向けメール（PDF添付）
    ② 担当者向け通知（PDF添付＋各種リンク）
  • 送信履歴シートに「日時／宛先／件名／見積No／PDF URL／ステータス／エラー」を自動記録

前提（第1回・第2回からの続き）

  • 第1回：フォーム→テンプレ複製→名前付き範囲へ反映→PDF化
  • 第2回：見積Noの自動採番＋PDF名へ刻印＋回答行へURL書き戻し
  • 第3回（今回）：GmailAppでの送信＆ログを追加

事前準備（1分）

  1. フォームに2項目追加
    　「お客様メール」「担当者メール」
    　※担当者メールが空でもOK（デフォルト宛先＝現在ログインのGoogleアカウント）。
  2. トリガー設定
    　Apps Script → トリガー → 関数：onFormSubmit2／イベントの種類：フォーム送信時（スプレッドシート）
    　※手動実行だと e.range が来ないので注意。
  3. 初回の権限承認
    　forceGmailAuth_() を1回だけ実行（Gmail送信のスコープ許可）。

追加する学び（超要点だけ）

  • GmailApp.sendEmail(to, subject, body, {htmlBody, attachments})
    → テキストとHTMLを両方送りつつ、PDFをBlobで添付できる。
  • DriveApp.getFileById(id).getBlob()
    → PDFファイルを“中身（Blob）”として取得、メールに添付可能。
  • ログ出力（履歴管理）
    → 失敗時も記録して、後から原因追跡。実務は“痕跡”が命。

追加コード（差分だけ・色分け表示）

既存スクリプトに、以下を追記／差し替えしてください。
🟩＝追加、🟦＝変更

/** ===== 設定（置き換え必須） ===== */
const TEMPLATE_FILE_ID = 'YOUR_TEMPLATE_SPREADSHEET_ID';   // 見積テンプレ
const OUTPUT_FOLDER_ID = 'YOUR_OUTPUT_FOLDER_ID';          // 生成物保存フォルダ
const FORM_SHEET_NAME = 'フォームの回答 1';                // 回答シート名
const COL_ESTIMATE_URL = '見積ファイルURL';
const COL_PDF_URL = 'PDFダウンロードURL';

/** 🟩追加：メール／ログ関連の設定 */
const LOG_SHEET_NAME = '送信履歴';  // 自動作成
const DEFAULT_STAFF_EMAIL = Session.getActiveUser().getEmail(); // フォーム未入力時の担当者宛て

/** フォーム質問タイトル → キー（タイトルはフォームと一致させる） */
const FORM_KEYS = {
  '宛名': 'atena',
  '件名': 'kenmei',
  '見積日(yyyy/MM/dd)': 'date',
  '納期(yyyy/MM/dd)': 'exp',
  '品名': 'item1',
  '数量': 'qty1',
  '単価': 'price1',
  '備考': 'notes',
  '担当者': 'tanto',    // ←既存の追加
  // 🟩追加：宛先メール＆担当者メール（フォームに項目を追加してください）
  'お客様メール': 'customerEmail',
  '担当者メール': 'staffEmail'
};

/** 名前付き範囲（テンプレ側） */
const NR = {
  atena: 'EST_宛名',
  kenmei: 'EST_件名',
  date: 'EST_見積日',
  exp: 'EST_納期',
  item1: 'EST_品名1',
  qty1: 'EST_数量1',
  price1: 'EST_単価1',
  notes: 'EST_備考',
  tanto: 'EST_担当者', // ←既存の追加
  // 🟩追加（テンプレにあれば）：見積No
  estNo: 'EST_見積No'
};

/** ===== 1) フォーム送信トリガー =====
 * スクリプトエディタ → トリガー → 「onFormSubmit2」を"フォーム送信時"で登録
 * 旧 onFormSubmit は残してOKだが、実運用は onFormSubmit2 に付け替え
 */
function onFormSubmit(e) { // 旧：残しておく（お好みで）
  const ans = parseAnswers(e);
  const { ssUrl, pdfUrl } = createEstimateFromTemplate(ans);
  writeBackUrls_(e, ssUrl, pdfUrl);
}

/** 🟦変更：第2回メインのオーケストレータ（今回ここからメール送信も追加） */
function onFormSubmit2(e) {
  try {
    // 1) 回答パース
    const ans = parseAnswers(e);

    // 2) 見積Noを採番して ans に格納
    const estNo = nextEstimateNo_();
    ans.見積No = estNo;

    // 3) 見積生成（テンプレに見積No差し込み、PDF名にも反映）
    const { ssUrl, pdfUrl, pdfFileId } = createEstimateFromTemplate(ans);

    // 4) 回答行へ「見積No / PDF URL / 見積ファイルURL」を書き戻し
    writeBackRow_(e, { estNo, ssUrl, pdfUrl });

    // 🟩追加：5) メール送信（お客様＆担当者）
    const results = sendEstimateEmails_(ans, { ssUrl, pdfUrl, pdfFileId });

    // 🟩追加：6) 送信ログ記録（2通分）
    results.forEach(r => logSend_({
      kind: r.kind,
      status: r.ok ? 'SUCCESS' : 'ERROR',
      to: r.to,
      subject: r.subject,
      estNo,
      pdfUrl,
      error: r.ok ? '' : (r.errorMessage || '')
    }));

  } catch (err) {
    // 🟩追加：致命エラーもログに残す（種類=SYSTEM）
    logSend_({
      kind: 'SYSTEM',
      status: 'ERROR',
      to: '',
      subject: 'onFormSubmit2 failed',
      estNo: '',
      pdfUrl: '',
      error: String(err && err.stack ? err.stack : err)
    });
    throw err;
  }
}

/** ===== 2) 回答パース ===== */
function parseAnswers(e) {
  const nv = (e && e.namedValues) ? e.namedValues : {};
  const g = (title) => (nv[title] ? String(nv[title][0]).trim() : '');
  // 🟩追加：タイトル揺れ吸収（例：「見積日」or「見積日(yyyy/MM/dd)」）
  const gAny = (titles) => {
    for (const t of titles) {
      const v = g(t);
      if (v) return v;
    }
    return '';
  };
  const toNum = (v) => Number(String(v).replace(/[, ]/g, '')) || 0;
  const fmtDate = (s) => s ? Utilities.formatDate(new Date(s), 'Asia/Tokyo', 'yyyy年M月d日') : '';

  return {
    atena: g('宛名'),
    kenmei: g('件名'),
    date: fmtDate(gAny(['見積日(yyyy/MM/dd)', '見積日'])),
    exp:  fmtDate(gAny(['納期(yyyy/MM/dd)', '納期'])),
    item1: g('品名'),
    qty1: toNum(g('数量')),
    price1: toNum(g('単価')),
    notes: g('備考'),
    tanto: g('担当者'),
    // 🟩追加：メール宛先
    customerEmail: gAny(['お客様メール', 'メール', '宛先メール']),
    staffEmail: gAny(['担当者メール'])
  };
}

/** ===== 3) 見積生成 ===== */
function createEstimateFromTemplate(ans) {
  // 🟦変更：テンプレ名の生成（テンプレートリテラルで安全に）
  const ymd = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyyMMdd_HHmm');
  const safeKenmei = safe_(ans.kenmei || '無題');
  const name = `見積_${ans.見積No}_${safeKenmei}_${ymd}`;

  const copyFile = DriveApp.getFileById(TEMPLATE_FILE_ID).makeCopy(name, DriveApp.getFolderById(OUTPUT_FOLDER_ID));
  const ss = SpreadsheetApp.openById(copyFile.getId());

  // 名前付き範囲へ値を注入
  setNR_(ss, NR.atena, ans.atena);
  setNR_(ss, NR.kenmei, ans.kenmei);
  setNR_(ss, NR.date, ans.date);
  setNR_(ss, NR.exp, ans.exp);
  setNR_(ss, NR.item1, ans.item1);
  setNR_(ss, NR.qty1, ans.qty1);
  setNR_(ss, NR.price1, ans.price1);
  setNR_(ss, NR.notes, ans.notes);
  setNR_(ss, NR.tanto, ans.tanto);

  // 🟩追加：見積Noをテンプレへ反映（NRにあればNR、なければ直接）
  if (NR.estNo) {
    setNR_(ss, NR.estNo, ans.見積No);
  } else {
    const rNo = ss.getRangeByName('EST_見積No');
    if (rNo) rNo.setValue(ans.見積No);
  }
  SpreadsheetApp.flush();

  // PDF化（URLエクスポート）
  const sheet = ss.getSheets()[0]; // 1枚目のシート
  const sheetId = sheet.getSheetId();
  const url = ss.getUrl().replace(/edit$/, '') +
    'export?format=pdf' +
    '&gid=' + sheetId +
    '&range=A1:J32' +　　　　// 範囲指定
    '&size=A4' +　　　　　　 // A4
    '&portrait=true' +　　　 // タテ向き
    '&fitw=true' +　　　　　 // ページ幅に収める
    '&scale=4' +　　　　　　 // 1ページにフィット
    '&gridlines=false' +　　 // 枠線なし
    '&sheetnames=false' +　　// シート名非表示
    '&printtitle=false' +　　// タイトル行/列なし
    '&pagenum=UNDEFINED';　　// ページ番号非表示
  const token = ScriptApp.getOAuthToken();
  const response = UrlFetchApp.fetch(url, { headers: { Authorization: 'Bearer ' + token } });

  // 🟦変更：PDFファイルの保存（名前に見積Noを刻印）
  const pdfBlob = response.getBlob().setName(`${name}.pdf`);
  const pdfFile = DriveApp.getFolderById(OUTPUT_FOLDER_ID).createFile(pdfBlob);

  // 🟩追加：IDも返す（添付で getBlob() できるように）
  return { ssUrl: ss.getUrl(), pdfUrl: pdfFile.getUrl(), pdfFileId: pdfFile.getId() };
}

/** 名前付き範囲にセット（存在しなければ無視：安全側） */
function setNR_(ss, name, value) {
  if (!name) return;
  const r = ss.getRangeByName(name);
  if (r) r.setValue(value);
}

/** ===== 4) 回答行へURLを書き戻し ===== */
function writeBackUrls_(e, ssUrl, pdfUrl) {
  const sheet = e.range.getSheet();
  const row = e.range.getRow();
  ensureHeader_(sheet, [COL_ESTIMATE_URL, COL_PDF_URL]);

  const map = headerMap_(sheet);
  // 🟦変更：数式は文字列で渡す（テンプレートリテラル）
  sheet.getRange(row, map[COL_ESTIMATE_URL])
    .setFormula(`=HYPERLINK("${ssUrl}","見積ファイル")`);
  sheet.getRange(row, map[COL_PDF_URL])
    .setFormula(`=HYPERLINK("${pdfUrl}","PDF")`);
}

/** ヘッダ確保＆列位置マップ */
function headerMap_(sheet) {
  const lastCol = sheet.getLastColumn();
  const headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
  const map = {};
  headers.forEach((h, i) => (map[h] = i + 1));
  return map;
}

function ensureHeader_(sheet, need) {
  const map = headerMap_(sheet);
  const adds = need.filter((h) => !map[h]);
  if (adds.length === 0) return;
  const lastCol = sheet.getLastColumn();
  sheet.getRange(1, lastCol + 1, 1, adds.length).setValues([adds]);
}

/** 回答行への書き戻し（見積No列が無ければ作成） */
function writeBackRow_(e, payload){
  const { estNo, ssUrl, pdfUrl } = payload;
  const sh  = e.range.getSheet();
  const row = e.range.getRow();

  const colNo  = getOrCreateColumnByHeader_(sh, '見積No', true);
  const colPdf = getOrCreateColumnByHeader_(sh, 'PDF URL', true);
  const colSs  = getOrCreateColumnByHeader_(sh, '見積ファイルURL', true);

  sh.getRange(row, colNo ).setValue(estNo);
  sh.getRange(row, colPdf).setValue(pdfUrl);
  sh.getRange(row, colSs ).setValue(ssUrl);
  SpreadsheetApp.flush();
}

/** ヘッダ名から列番号を取得（なければ作る） */
function getOrCreateColumnByHeader_(sh, headerName, createIfMissing = false) {
  const lastCol = sh.getLastColumn() || 1;
  const headers = sh.getRange(1,1,1,lastCol).getValues()[0].map(v => String(v||'').trim());
  let idx = headers.findIndex(h => h === headerName);
  if (idx === -1 && createIfMissing) {
    sh.getRange(1, lastCol + 1).setValue(headerName);
    return lastCol + 1;
  }
  return idx + 1;
}

/** 見積No採番（月次リセット＋重複防止） */
function nextEstimateNo_() {
  const ym  = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyyMM'); // 例: 202510
  const key = `ESTNO_${ym}`;
  const lock = LockService.getScriptLock();
  lock.waitLock(10 * 1000);
  try {
    const props = PropertiesService.getScriptProperties();
    const cur = parseInt(props.getProperty(key) || '0', 10) || 0;
    const next = cur + 1;
    props.setProperty(key, String(next));
    return `${ym}-${String(next).padStart(4,'0')}`; // 202510-0001
  } finally {
    lock.releaseLock();
  }
}

/** 🟩追加：メール送信（お客様＆担当者） */
function sendEstimateEmails_(ans, refs) {
  const { ssUrl, pdfUrl, pdfFileId } = refs;
  const pdfBlob = DriveApp.getFileById(pdfFileId).getBlob(); // ← 学べるポイント：getBlob()

  // 宛先の安全確保
  const toCustomer = (ans.customerEmail || '').trim();
  const toStaff    = (ans.staffEmail || '').trim() || DEFAULT_STAFF_EMAIL;

  const subjectBase = `【お見積り】${ans.kenmei || ''}（${ans.見積No}）`;
  const links = { ssUrl, pdfUrl };

  const results = [];

  // お客様向け
  if (toCustomer) {
    results.push(sendOneMail_({
      kind: 'CUSTOMER',
      to: toCustomer,
      subject: subjectBase,
      bodyText: renderCustomerText_(ans, links),
      bodyHtml: renderCustomerHtml_(ans, links),
      attachments: [pdfBlob]
    }));
  }

  // 担当者向け（必ず送る）
  results.push(sendOneMail_({
    kind: 'STAFF',
    to: toStaff,
    subject: `【見積通知】${ans.見積No}／${ans.kenmei || ''}`,
    bodyText: renderStaffText_(ans, links),
    bodyHtml: renderStaffHtml_(ans, links),
    attachments: [pdfBlob]
  }));

  return results;
}

/** 🟩追加：単発送信（例外握り＆結果返却） */
function sendOneMail_({ kind, to, subject, bodyText, bodyHtml, attachments }) {
  try {
    GmailApp.sendEmail(to, subject, bodyText, {
      htmlBody: bodyHtml,
      attachments: attachments,
      replyTo: '', // 必要なら設定
      name: '見積自動送信（GAS）'
    });
    return { kind, to, subject, ok: true };
  } catch (err) {
    return { kind, to, subject, ok: false, errorMessage: String(err) };
  }
}

/** 🟩追加：お客様向け本文（TEXT） */
function renderCustomerText_(ans, links) {
  return [
    `${ans.atena} 様`,
    '',
    'このたびはお見積りのご依頼ありがとうございます。',
    `件名：${ans.kenmei}`,
    `見積番号：${ans.見積No}`,
    `見積日：${ans.date}／納期：${ans.exp}`,
    '',
    'PDFを添付しております。内容をご確認ください。',
    `（スプレッドシート版）${links.ssUrl}`,
    `（PDFダウンロード）${links.pdfUrl}`,
    '',
    'ご不明点がありましたら本メールにご返信ください。',
    `担当：${ans.tanto}`,
    ''
  ].join('\n');
}

/** 🟩追加：お客様向け本文（HTML） */
function renderCustomerHtml_(ans, links) {
  return `
    <p>${escapeHtml_(ans.atena)} 様</p>
    <p>このたびはお見積りのご依頼ありがとうございます。</p>
    <ul>
      <li>件名：${escapeHtml_(ans.kenmei)}</li>
      <li>見積番号：${escapeHtml_(ans.見積No)}</li>
      <li>見積日：${escapeHtml_(ans.date)} ／ 納期：${escapeHtml_(ans.exp)}</li>
    </ul>
    <p>PDFを添付しております。内容をご確認ください。</p>
    <p>
      <a href="${links.ssUrl}">スプレッドシート版</a> ／
      <a href="${links.pdfUrl}">PDFダウンロード</a>
    </p>
    <p>ご不明点がありましたら本メールにご返信ください。<br>担当：${escapeHtml_(ans.tanto)}</p>
  `;
}

/** 🟩追加：担当者向け（TEXT） */
function renderStaffText_(ans, links) {
  return [
    '【見積通知（自動）】',
    `見積番号：${ans.見積No}`,
    `件名：${ans.kenmei}`,
    `宛名：${ans.atena}`,
    `担当：${ans.tanto}`,
    `見積日：${ans.date}／納期：${ans.exp}`,
    '',
    `スプシ：${links.ssUrl}`,
    `PDF：${links.pdfUrl}`,
    ''
  ].join('\n');
}

/** 🟩追加：担当者向け（HTML） */
function renderStaffHtml_(ans, links) {
  return `
    <p><strong>【見積通知（自動）】</strong></p>
    <ul>
      <li>見積番号：${escapeHtml_(ans.見積No)}</li>
      <li>件名：${escapeHtml_(ans.kenmei)}</li>
      <li>宛名：${escapeHtml_(ans.atena)}</li>
      <li>担当：${escapeHtml_(ans.tanto)}</li>
      <li>見積日：${escapeHtml_(ans.date)} ／ 納期：${escapeHtml_(ans.exp)}</li>
    </ul>
    <p>
      <a href="${links.ssUrl}">スプレッドシート</a> ／
      <a href="${links.pdfUrl}">PDF</a>
    </p>
  `;
}

/** 🟩追加：送信ログ */
function logSend_({ kind, status, to, subject, estNo, pdfUrl, error }) {
  const ss = SpreadsheetApp.getActive();
  let sh = ss.getSheetByName(LOG_SHEET_NAME);
  if (!sh) {
    sh = ss.insertSheet(LOG_SHEET_NAME);
    sh.appendRow(['日時','種類','ステータス','宛先','件名','見積No','PDF URL','エラー']);
  }
  sh.appendRow([
    Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss'),
    kind, status, to, subject, estNo, pdfUrl, error
  ]);
}

/** 🟩追加：初回権限通し（Gmail承認用） */
function forceGmailAuth_() {
  // 実行するとGmailスコープ承認ダイアログが出ます
  GmailApp.getRemainingDailyQuota();
}

/** 🟩追加：ユーティリティ */
function escapeHtml_(s){
  return String(s||'').replace(/[&<>"']/g, c => ({'&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;',"'":'&#39;'}[c]));
}

/** ファイル名に使えない文字を無害化 */
function safe_(s){
  return String(s||'').trim()
    .replace(/[\/:\\?"<>|#\[\]\n\r]+/g,' ')
    .substring(0,80);
}

※PDF生成や見積No採番など、第2回までの関数はそのまま使います。
createEstimateFromTemplate() が pdfFileId を返すようにしておくと、メール添付が簡単です。

動作確認（テスト手順）

  1. フォームで以下を入力
    　宛名／件名／見積日／納期／品名／数量／単価／担当者／お客様メール／担当者メール
  2. 送信 → 数秒後に2通メール到着
  3. 回答シートに 見積No／見積URL／PDF URL が書き戻り
  4. 「送信履歴」シートに SUCCESS が2行追加（失敗時はERROR＋内容）

つまずきやすいポイント（先回りで解決）

  • TypeError: Cannot read properties of undefined (reading ‘range’)
    → トリガーをスプレッドシートの「フォーム送信時」に付け直す（手動実行NG）。
  • 添付が空／壊れている
    → メール送信は DriveApp.getFileById(pdfFileId).getBlob() で取得したBlobを使う。URL文字列をattachmentsに入れない。
  • 権限エラー
    → 初回に forceGmailAuth_() を1回実行。承認後はOK。

まとめ

  • PDF添付の自動送信で、見積作成→送付までノータッチ化。
  • ログを残しておけば、ミスや送信漏れの検知もラク。
  • 次は、GASで明細を可変行に対応して、品目数に合わせて自動PDF化しましょう！

コメント

タイトルとURLをコピーしました