GASで明細を可変行に対応！品目数が変わっても自動PDF化（第4回）

仕事

「見積書の明細が1行のときもあれば、5行になるときもある。
でも毎回レイアウトを直すのはイヤ。フォームの“品目数”に合わせて自動で整形してほしい。」

そんなワガママを、GAS＋Googleフォーム＋スプレッドシートだけで叶えるのが今回のテーマです。

第3回までで作ったのは、

  • フォーム送信
  • 見積テンプレのコピー
  • PDF化
  • メール送信
  • 送信ログ

まで全部自動でやってくれる見積システムでした。

第4回ではそこに 「明細の可変行対応」 を足していきます。

今回のゴールと前提

ゴール

  • Googleフォームで 「品目数」＋「品名1〜5／数量1〜5／単価1〜5」 を入力
  • 回答内容に応じて、
  • 1行だけの見積
  • 3行だけ使う見積
  • 5行フルの見積
    が自動で整形されてPDF化される

学べるポイント

  • for / map / filter を使った 明細の配列処理
  • 空白行を filter でサクッと除外する考え方
  • 別シートを「裏方データシート」にしてレイアウトを守るテンプレ設計
  • 将来、1〜10行対応にするときに楽できる作り方

前提（第1〜3回から引き継ぎ）

  • Googleフォーム → 回答シート → 見積テンプレ → PDF → メール送信 の流れは完成済み
  • 見積Noの自動採番や送信ログも動いている
  • 今回はその「既存スクリプト」をベースに、
    明細まわりだけを🟩追加／🟦変更／🟥削除でアップデートします

設計方針：レイアウトはそのまま、明細は「裏方シート」で管理

最初にぶつかった壁がこれでした。

「レイアウトをきれいにしたくて、品名〜数量〜単価〜金額を結合セルで組んだら、
GAS からうまく書き込めない…」

スプレッドシートでは、結合セルに対して「4列分の値」を一気に入れることはできません。
そこで方針を変えました。

方針

  • 見積書シート：
  • これまで通り、見た目重視のレイアウト（結合セルOK）
  • 明細セルには =INDEX(EST_明細範囲, 行, 列) の式だけ入れておく
  • 裏方シート（例：明細DATA）：
  • セル結合なしの 5行×4列（品名／数量／単価／金額） の表
  • ここに GAS が 2次元配列で書き込む
  • この表全体に名前付き範囲 EST_明細範囲 を付ける

こうすると、

  • GAS は「四角い表」にだけ集中できる
  • 見積書のレイアウトは壊さずに済む
  • 行数を増やしたくなったら、
  • EST_明細範囲 を広げる
  • MAX_ITEMS を10にする
    だけでOK、という気持ちのいい構造になります。

フォーム側の準備：1〜5行の明細を入れられるようにする

今回はまず 5行まで 対応にしています（後で10行も対応可）。

フォームの質問構成

  • 宛名（必須）
  • お客様メール（必須）
  • 件名
  • 見積日
  • 納期
  • 品目数（1〜5の数値）←今回のテーマ
  • 品名1〜品名5（短答式）
  • 数量1〜数量5（短答式＋「数値・0以上」で検証）
  • 単価1〜単価5（同上）
  • 備考
  • 担当者
  • 担当者メール

回答シートを見ると、列名はこのようになります。

品名1, 品名2, ..., 品名5
数量1, 数量2, ..., 数量5
単価1, 単価2, ..., 単価5
品目数

この「列名」が parseAnswers() で拾うキー になるので、
ここはきっちり合わせておきます。

テンプレート側の準備：EST_明細範囲を「裏方シート」に作る

  1. スプレッドシートで、新しいシートを作成（例：明細DATA）
  2. A1:D1 にヘッダを書いておきます（任意） A列 B列 C列 D列 品名 数量 単価 金額
  3. A2:D6 に、明細5行ぶんの空セルを用意
  4. A2:D6 を選択 → データ → 名前付き範囲 →
    名前を EST_明細範囲 にする

見積書シートの明細は、例えば1行目をこうします。

  • 品名セル：
    =INDEX(EST_明細範囲, 1, 1)
  • 数量セル：
    =INDEX(EST_明細範囲, 1, 2)
  • 単価セル：
    =INDEX(EST_明細範囲, 1, 3)
  • 金額セル：
    =INDEX(EST_明細範囲, 1, 4)

2行目は INDEX(..., 2, 列)、3行目は INDEX(..., 3, 列)…という形で5行まで設定します。

これで、

  • GAS：EST_明細範囲 にだけ集中して書き込む
  • 見積書：INDEX() 経由でその値を参照

という構造が完成です。

GASコードの変更点（第3回→第4回の差分）

ここからは、第3回のコードからどこを触ればいいかを
🟩追加／🟦変更／🟥削除でまとめていきます。

明細まわりの設定を追加

/** ===== 設定（抜粋） ===== */

// 既存：テンプレID／出力フォルダ／回答シート名 などは第3回のままです

/** 🟩追加：明細まわりの基本設定 */
const MAX_ITEMS = 5;  // まずは1〜5行対応（10行にしたければここを10に）
const DETAIL_COLS = ['品名', '数量', '単価', '金額'];　// テンプレの列順に合わせる

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

/** フォーム質問タイトル → キー（タイトルはフォームと一致させる） */
const FORM_KEYS = {
  '宛名': 'atena',
  '件名': 'kenmei',
  '見積日(yyyy/MM/dd)': 'date',
  '納期(yyyy/MM/dd)': 'exp',
  // 🟩追加：可変行用
  '品目数': 'itemCount',
  // 明細1..10（質問タイトルは実フォームに合わせて）
  // 例：品名1/数量1/単価1, 品名2/数量2/単価2, ...
  // ※gAnyで拾うので厳密一致じゃなくてもOK
  // 既存メール系キーもそのまま
  //'品名': 'item1',　 使わない
  //'数量': 'qty1',  　使わない  
  //'単価': 'price1',  使わない
  '備考': 'notes',
  '担当者': 'tanto',　　// ←既存追加2⃣
  // 既存追加3⃣：宛先メール＆担当者メール（フォームに項目を追加してください）
  'お客様メール': 'customerEmail',
  '担当者メール': 'staffEmail'
};

/** 既存：名前付き範囲（テンプレ側） */
const NR = {
  atena: 'EST_宛名',
  kenmei: 'EST_件名',
  date: 'EST_見積日',
  exp:  'EST_納期',
  notes:'EST_備考',
  tanto:'EST_担当者',
  estNo:'EST_見積No',
  // 🟩追加：裏方シート側の明細テーブル
  detailRange: 'EST_明細範囲',
  // 🟩追加：合計金額（任意。テンプレで計算するなら省略可）
  total: 'EST_合計'
};

// 🟥削除：単一行用の item1/qty1/price1 は第4回では不使用
//  NR.item1 / NR.qty1 / NR.price1 ...

/** 既存変更3⃣：第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 });

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

    // 既存追加3⃣：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) {
    // 既存追加3⃣：致命エラーもログに残す（種類=SYSTEM）
    logSend_({
      kind: 'SYSTEM',
      status: 'ERROR',
      to: '',
      subject: 'onFormSubmit2 failed',
      estNo: '',
      pdfUrl: '',
      error: String(err && err.stack ? err.stack : err)
    });
    throw err;
  }
}

parseAnswers()：フォーム回答を「配列の明細」に変換

/** 🟦変更：parseAnswers — 可変行の吸い上げを実装 */
/** ===== 2) 回答パース ===== */
function parseAnswers(e) {
  const nv = (e && e.namedValues) ? e.namedValues : {};
  const g = (title) => (nv[title] ? String(nv[title][0]).trim() : '');
  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日') : '';

  // 🟩追加：品目数（1〜MAX_ITEMSに丸め）
  const rawCount = toNum(gAny(['品目数','明細数','行数']));
  const itemCount = Math.max(1, Math.min(MAX_ITEMS, rawCount || 1));

  // 🟩追加：1..MAX_ITEMS を走査して明細配列を作る
  const items = Array.from({ length: MAX_ITEMS }, (_, i) => {
    const n = i + 1;
    const name  = gAny([`品名${n}`,  `品名_${n}`,  `item${n}`]);
    const qty   = toNum(gAny([`数量${n}`, `数量_${n}`, `qty${n}`]));
    const price = toNum(gAny([`単価${n}`, `単価_${n}`, `price${n}`]));
    const amount = qty * price;
    return { name, qty, price, amount };
  })
  // 🟩追加：空行除外（品名が空 or 0円行を落とす）
  .filter(r => r.name && (r.qty || r.price));

  // 🟩追加：フォーム回答の「品目数」までで切り詰め
  const normalized = (items && items.length > 0)
    ? items.slice(0, itemCount)
    : [{ name:'', qty:0, price:0, amount:0 }];

  // 🟩追加：スプレッドシートに貼付用テーブル
  const table = normalized.map(r => [r.name, r.qty, r.price, r.amount]);

  // 🟩追加：残りは空行でMAX_ITEMS行に合わせる（範囲を綺麗に更新したい場合）
  while (table.length < MAX_ITEMS) table.push(['','','','']);

　// ✅ reduce() の安全化（空でも0を返す）
  const total = (normalized && normalized.length > 0)
    ? normalized.reduce((s, r) => s + (r.amount || 0), 0)
    : 0;

  return {
    atena: g('宛名'),
    kenmei: g('件名'),
    date: fmtDate(gAny(['見積日(yyyy/MM/dd)', '見積日'])),
    exp:  fmtDate(gAny(['納期(yyyy/MM/dd)', '納期'])),
    notes: g('備考'),
    tanto: g('担当者'),
    // 既存追加3⃣：メール宛先
    customerEmail: gAny(['お客様メール', 'メール', '宛先メール']),
    staffEmail: gAny(['担当者メール']),
    // 🟩追加：可変行データ
    itemCount,           
    items: normalized,  
    itemsTable: table,  
    total: normalized.reduce((s, r) => s + r.amount, 0)  
  };
}

ここが今回の一番おいしいポイントです。

  • Array.from＋インデックスで1〜MAX_ITEMSを回す
  • filter で空白行を除外
  • slice で「品目数」までに絞る
  • map でスプレッドシート用の2次元配列に変換
  • reduce で合計金額を算出

for をガリガリ書くより、
配列メソッドを並べた方が「やりたいこと」が読み取りやすくなります。

createEstimateFromTemplate()：見積テンプレに明細を差し込む

/** 🟦変更：見積生成 — 明細テーブル＆合計を差し込み */
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.notes, ans.notes);
  setNR_(ss, NR.tanto, ans.tanto);
  if (NR.estNo) setNR_(ss, NR.estNo, ans.見積No);

  // 🟥削除：上記ヘッダ単一行の item1/qty1/price1 差し込み（第4回では不要）
  // setNR_(ss, NR.item1, ans.item1); など

  // 🟩追加：明細テーブルを書き込む（裏方シート側）
  writeDetails_(ss, ans.itemsTable);

  // 🟩追加：合計セルがあればセット（テンプレ側で式に任せるなら省略可）
  if (NR.total) setNR_(ss, NR.total, ans.total);

  SpreadsheetApp.flush();

  // 既存：PDF化〜ファイル保存〜URL返却はそのまま
  const sheet = ss.getSheets()[0];
  const sheetId = sheet.getSheetId();
  const url = ss.getUrl().replace(/edit$/, '') +
    'export?format=pdf' +
    '&gid=' + sheetId +
    '&range=A1:J32' +
    '&size=A4' +
    '&portrait=true' +
    '&fitw=true' +
    '&scale=4' +
    '&gridlines=false' +
    '&sheetnames=false' +
    '&printtitle=false' +
    '&pagenum=UNDEFINED';
  const token = ScriptApp.getOAuthToken();
  const response = UrlFetchApp.fetch(url, { headers: { Authorization: 'Bearer ' + token } });

  const pdfBlob = response.getBlob().setName(`${name}.pdf`);
  const pdfFile = DriveApp.getFolderById(OUTPUT_FOLDER_ID).createFile(pdfBlob);

  return { ssUrl: ss.getUrl(), pdfUrl: pdfFile.getUrl(), pdfFileId: pdfFile.getId() };
}

writeDetails_()：EST_明細範囲への一括書き込み

/** 🟩追加：明細範囲の一括書き込み（裏方シート側） */
function writeDetails_(ss, table) {
  if (!NR.detailRange) return;
  const r = ss.getRangeByName(NR.detailRange);
  if (!r) return;

  const rows = r.getNumRows();
  const cols = r.getNumColumns();

  // 列数チェック（想定は DETAIL_COLS.length 列）
  if (cols < DETAIL_COLS.length) {
    throw new Error(`EST_明細範囲の列数が不足：期待${DETAIL_COLS.length}列 / 実際${cols}列`);
  }

  // 行数に合わせてテーブルを整形（余りは空行で埋める）
  const fixed = table.slice(0, rows).map(row => {
    const a = row.slice(0, DETAIL_COLS.length);
    while (a.length < DETAIL_COLS.length) a.push('');
    return a;
  });
  while (fixed.length < rows) fixed.push(Array(DETAIL_COLS.length).fill(''));

  // 一旦全クリアしてから一括 setValues
  r.clearContent();
  r.offset(0, 0, rows, DETAIL_COLS.length).setValues(fixed);
}

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

/** ===== 4) 回答行へURLを書き戻し ===== */
以下、第3回のままです
// 既存：ヘッダ確保＆列位置マップ／回答行への書き戻し／ヘッダ名から列番号を取得／見積No採番／メール送信（お客様＆担当者）／単発送信（例外握り＆結果返却）など

ここが 「配列→名前付き範囲」ブリッジ の部分です。

  • どんなに複雑なフォームでも、
    最終的に table = [[品名,数量,単価,金額], ...] にさえなっていればOK
  • テンプレ側は「四角い EST_明細範囲」を1つ持っていればOK

という、きれいな責務分離になっています。

動作確認とトラブルシュート

まずは5行フルでテスト

  • 品目数：5
  • 品名1〜5、数量1〜5、単価1〜5 をすべて埋めてフォーム送信
  • 見積PDFを開いて
  • 5行すべてに 品名／数量／単価／金額 が入っている
  • 合計金額も一致している
    ことを確認します。

次に「品目数3」でテスト

  • 品目数：3
  • 品名1〜3だけ入力、4〜5は空欄
  • 出力された見積PDFで
  • 1〜3行目：入力した明細
  • 4〜5行目：空行
    になっていれば成功です。

よくあるエラー

  • 数量・単価が 0 になる／空欄になる
    → フォームの質問名とコード側の 品名1/数量1/単価1 がずれている
  • EST_明細範囲が見つからない
    → 裏方シート側の名前付き範囲のスペルを再確認
  • 見積レイアウトが崩れる
    → 見積書シートの明細セルにだけ INDEX() を入れているか、
    セル結合の範囲が正しいかをチェック

1〜10行対応に広げるには？

この記事ではまず 1〜5行対応 にしましたが、
将来的に 10行まで扱いたくなったらやることは2つだけです。

  1. 裏方シートの EST_明細範囲 を 10行ぶん（例：A2:D11）に広げる
  2. GASの定数を変更
// 🟦変更：10行対応にする場合
const MAX_ITEMS = 10;

フォームの「品目数」の上限、
「品名6〜10／数量6〜10／単価6〜10」の質問を増やすのを忘れずに。

まとめ：明細の可変行は「配列＋裏方シート」で攻める

今回のポイントを振り返ると：

  • 明細を 配列（items） で扱うことで
  • 空白行除外
  • 品目数での切り詰め
  • 合計金額の計算
    が一気に書きやすくなった
  • レイアウトを守るために、
    裏方シートの EST_明細範囲 にだけ素のデータを書き込む設計 にした
  • 見積書シートは INDEX(EST_明細範囲, 行, 列) で参照するだけにして、
    デザインとロジックを分離した

これで、

「フォームの品目数に応じて、見積書の明細レイアウトが自動で整形される」

という今回のゴールは達成です。

第1回〜第4回で、「フォーム → 見積作成 → PDF化 → メール送信 → 可変明細対応」まで、一通りの自動フローがそろいました。


ここから先は、レイアウトや計算ロジックを少しずつチューニングしていくだけで、“現場にそのまま置けるツール”に育っていきます。あなたの環境でも、ぜひこの第4回のコードを土台に、オリジナルの見積システムへ進化させてみてください。

今回の学べるポイント総まとめ

可変行の明細を扱うとき、GASでは
① 行を集める（for）
② 欲しい形に整える（map）
③ いらない行を落とす（filter）
という“三段階のお片付け”が鉄板パターンになります。


そして、この整えた配列を 「裏方シート」にストンと落とすことで、テンプレ側のレイアウトを崩さずに可変行へ対応できるようになります。
実務の見積書でも、棚卸表でも、請求書でも、この構造はそのまま応用可能。今回の実装は、その“最もシンプルな成功パターン”になっています。

コメント

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