# 既存スプレッドシートを正本にした業務アプリの「列マッピング」を壊れない設計にする 現場が何年も使っている Google スプレッドシートを**正本のまま**、Web アプリ（Next.js 等）から 読み書きするときの設計指示書。列の対応付けを間違えると、**別の列に書き込んで台帳を壊す**。 実運用で踏んだ失敗を前提に、壊れない解決順・診断手段・検証手順まで指定する。 対象読者は AI コーディングエージェント。この MD をそのまま渡せば実装できる粒度で書いている。 --- ## 前提の状況 - スプレッドシートは現場が編集し続けている（列が増える・見出し文言が変わる・同名列が生える） - アプリはその一部の列を読み、一部の列に書き戻す - シートの値が報酬計算・請求・在庫など**お金や実務に直結**している - 列の位置を変えてもらう交渉は現実的でない（＝アプリ側が耐えるしかない） ## 絶対に避けたい事故（実際に起きたもの） 1. **列レター直指定が古くなる** 設定ファイルや DB に `完了日 = B列` と持たせていたが、シートに列が増えて実態とズレた。 気付かないまま書き込み続け、別の列に日付が入った。 2. **短い候補名が同名の別列に完全一致する** ヘッダ名で解決する実装にしたところ、シートに `到着確認 大阪事務所より「届いた」メッセージがあればチェック`（本来の列）と `到着確認`（後から誰かが足した別列）の**2列**があり、 短い候補名 `到着確認` が後者に完全一致した。結果 **読み取りは新しい列 / 書き込みは元の列**という 不一致が生まれた。ユニットテストは自作ヘッダで通っており、 **本番シートの実ヘッダをダンプするまで発覚しなかった**。 3. **読み取り側の列を一つ増やし忘れて「アプリとフォームの情報が違う」と言われる** フォームには入力欄があるのにアプリが読んでいない列があり、 利用者は結局スプレッドシートを開き直していた。しかもその未読の列が 別機能の必須項目（外部会計サービス連携のタグ）で、**その機能は押すたびに必ずエラー**だった。 --- ## 実装指示 ### 1. 列定義は1箇所の定数に集約する 論理名 → { 候補ヘッダ名の配列, 設定キー } の対応表を**1つの定数**として定義し、 読み取りも書き込みも**必ずこの定数を参照**する。 ラベル文字列をコードの2箇所に書かない（書き込み側だけ古いラベルのまま、が起きる）。 ```ts export const COLUMN_DEFINITIONS = { completedAt: { names: ['対応完了日'], mapKey: 'completed_at' }, arrivalConfirmed: { // ★実ヘッダのフルラベルを第一候補にする（短い名前は保険として後ろ） names: ['到着確認 <実際の説明文まで含めたフルラベル>', '到着確認'], mapKey: 'arrival_confirmed', }, // ... } as const; ``` ### 2. 解決順は「完全一致 → 前方一致 → 設定の列レター」 ``` 1. ヘッダの完全一致（空白・改行をすべて除去して比較） 2. ヘッダの前方一致（説明文が後ろに付いた見出しに耐えるため） 3. 設定（DB / 設定ファイル）の列レター ← 最後の保険。ここに落ちたら warn を出す 4. どれも駄目なら missing ``` **候補名は配列の順に試す。** フルラベルを先頭に置くのは、上の事故2を防ぐため。 配列内のどれかに一致する最初のヘッダ列を採用する（左から走査）。 列レター↔インデックスの変換は `A..Z, AA, AB...` の複数文字に対応させること。 ### 3. 完全一致が複数あったら警告する ```ts if (exactMatches.length > 1) { console.warn(`列名が重複しています: ${name} → ${letters.join(', ')}（先頭を使用）`); } ``` 同名列が生えたことに気付ける唯一の仕掛け。**先頭（左側）を使う**と決めておく。 ### 4. 書き込みは「解決済みインデックス」からしか行わない 書き込み API に渡すキーを、設定の列レターから直接組み立ててはいけない。 **その書き込みの直前に実ヘッダを読んで解決した結果**から列を決める。 - 対象列が `missing` なら **例外を投げる**。黙って別の列に書かない。 メッセージ例: `シートに「対応完了日」列が見つかりません（列構成の変更を確認してください）` - `列レター` にフォールバックして解決した場合も warn を出す - 1回の書き込み処理でヘッダ読み取りは1回にまとめる（毎フィールド読み直さない） ### 5. 行の特定（アンカー）は fail-fast 行を「タイムスタンプ＋依頼者＋期日＋品名の先頭N字」等の組み合わせで特定している場合、 **そのアンカー列が1つでも解決できなければ即座に throw** する。 アンカーが空文字に縮退すると全行が同じハッシュになり、**別の行を書き換える**。 ### 6. 部分更新は「キーを省略する」で表現する 空文字を書くと既存セルを消す。値が無いフィールドは `updates` オブジェクトに**キー自体を入れない**。 ### 7. 同期（シート→DB）で「シート側が空なら既存値を消さない」 アプリ側で入力された値を、シートの空欄で NULL 上書きしてはいけない。 ```ts projectCode: sheetRow.projectCode ?? existing.projectCode, ``` ### 8. ステータスは「前進のみ」反映する シートはアプリの中間状態（調査中・承認待ち等）を表現できない。 定期同期でそれらを巻き戻さないよう、**ランク比較して前進または終了状態のときだけ**反映する。 --- ## 必須: 実ヘッダ診断 CLI を作る **これが無いと上の事故2は絶対に見つからない。** ログイン不要で実行できる CLI を用意する。 出力するもの: 1. **実ヘッダ行のダンプ** — `列レター: ラベル` の一覧（改行は半角スペースに畳む、空セルは `(空)`） 2. **論理列ごとの解決結果** — `論理名 / 列レター / 解決方法(header|letter|missing)` 3. **取込結果の件数** — 主要フィールドが「何行埋まっているか」 3 が効く。修正前後で `0 件 → 2469 件` のように**数字が動くこと**を確認できる。 `0 件` のまま気付かず「直った」と報告するのを防ぐ。 個人情報（氏名・住所・電話）は出力しない。読み取り専用にし、同期処理は呼ばない。 --- ## 検証手順（この順で。1つでも飛ばさない） 1. `typecheck` / `lint` / ユニットテスト - **同名列が2つあるヘッダ**でのテストを必ず入れる（フルラベル側が選ばれること） - 設定の列レターが**実ヘッダと違っていても**ヘッダ側が優先されること - アンカー列が両方で解決できないときに throw すること 2. **本番シートに対して診断 CLI を実行し、ヘッダのダンプを目視で突き合わせる** - 全列が `header` で解決されているのが理想 - `letter` にフォールバックした列は、ダンプと照らして**その列レターが本当に正しいか**確認する 3. 取込件数を修正前後で比較する 4. スキーマ変更を伴うなら、**DB の列追加をデプロイより先に**適用する（冪等な `add column if not exists`） 5. デプロイ後にヘルスチェックと主要画面の HTTP ステータス --- ## 認証情報の取り回し（ホスティング環境） サービスアカウント鍵をホスティング先の環境変数にだけ置いている場合、 ローカルから診断 CLI を回せず検証が止まる。多くのホスティング CLI には 環境変数をローカルへ取得するコマンドがあるので、それで鍵だけ取り出して `--env-file` を2枚重ねる（鍵のファイル → ローカル設定ファイルの順。後勝ちで上書きされる）。 ``` --env-file=<鍵だけのファイル> --env-file=.env.local scripts/diagnose-columns.ts ``` 鍵をリポジトリに書き込まない。診断用の一時ファイルはスクラッチ領域に置く。 --- ## AI エージェントに投げるときの注意 この種の改修を AI コーディングエージェントに委譲すると、実測で次を踏んだ。 指示書に**明示的に禁止と書いても破られる**ので、受け取り側で必ず検査する。 - **フォーマッタの全体実行**でリポジトリ中のファイルが再整形され、差分が1万行を超えてレビュー不能になる → 「整形は編集したファイルだけ」と指定し、受け取ったら `git status` のファイル数を必ず数える - **依頼していない機能を勝手に実装してコミット・プッシュする** → 委譲後は `git log --oneline -5` と `git status --porcelain` を先に確認する - **テストが通ることは機能が動くことの証明にならない** （自作のヘッダ配列でテストを書けば、実シートとズレていても緑になる） → 必ず本番データに対する診断を通す --- ## 完了条件のテンプレート - [ ] 全論理列が実ヘッダで解決される（`letter` フォールバックが残るなら、その列レターをダンプで確認済み） - [ ] 同名列テスト・アンカー欠落テストが入っている - [ ] 書き込み対象列が解決できない場合に例外を投げる - [ ] 修正前後で取込件数が期待どおり動いた（数字で示す） - [ ] DB の列追加をデプロイより先に適用した - [ ] デプロイ後のヘルスチェックが通った ---