別シートの「列番号」を保存して参照する設計が腐ったときの直し方（名前照合＋日付タイブレーク）

対象: 表計算（Google スプレッドシート等）を業務データの一次ソースにしていて、 「別シートのこの案件は N 列目」という位置情報を自分のテーブルに保存して参照しているシステム。 GAS / Node / Python どれでも成立する話。

この指示書が解く問題

参照先のシートで列が1本挿入・削除されただけで、保存した列番号は全件が別レコードを指す。 やっかいなのは次の3点。

1. エラーにならない。存在する列を読むので、もっともらしい別レコードの値が静かに流れ続ける
2. 一部だけ壊れる。削除位置より右だけズレるので「動いている例」が必ず残り、報告が信用されない
3. 下流が完成品。読んだ値がそのまま帳票・手順書・メール文面に載るので、 気づくのは顧客側（「会場がちがう」）になりやすい

実例では、保存列を持つ 27件のうち18件が別レコードの列を指していた。 生成される現場向け手順書に、別案件の会場名と会期が入っていた。

手順

0. 直す前に「壊れている件数」を測る（これを飛ばさない）

修正の前後で同じ物差しを当てられるようにする。全件を一度に叩ける読み取り専用の診断口を用意する。

• 既存の API に、トークンで保護した action=diag を一時的に足す
• 返すのは「解決に使った位置」と「その位置に実際に入っている名前」だけ。本文データは返さない
• 全レコードを回して 保存位置の名前 == 自分の名前 を突き合わせ、不一致の件数を出す
• 測り終えたら診断口は消す（一時デプロイを作って、確認後に削除するのが安全）

OK: 9  MISMATCH: 18  NO-COL: 11
  MISMATCH R-0004 <顧客A> |自分の案件名: <イベントX> |col 16 → 参照先: <顧客A> / <イベントY>

この一覧がそのまま、後の回帰テストのケースになる。

1. 一次解決を「名前照合」に変える（読み取り側だけなら安全に先行できる）

保存された位置は最後の砦に格下げし、まず名前で引き当てる。書き込み側（参照先へ結果を書き戻す処理）は 影響範囲が広いので別タスクに切る。読み取り側の修正だけでも、下流の帳票は即座に正しくなる。

照合に必要な要素は実データではこれだけある。どれか1つ欠けると必ず取りこぼす。

要素	理由
NFKC 正規化	全角英数字（ＪＡＰＡＮ）や半角カナが混ざる。NFC では吸収できない
小文字化・空白/改行除去	参照先の名前セルは改行や全角空白で整形されている
敬称・記号の揺れ（様、＆→&）	入力者によって違う
サフィックス除去	自分側の名前が <正式名> / <地域> のように装飾されていることがある。剥がした候補も持つ
前方一致は最長優先	フェアA と フェアAジャパン名古屋 が同居する。短い方に食われる
同名複数のタイブレーク	同じ名前の列が2本ある（同じイベントの別開催）。名前だけでは原理的に割れない

2. 同名が複数あるときは「日付」で割る

同じ名前の列が複数あるのは、データの重複ではなく別開催・別年度であることが多い。消してはいけない。 自分側のレコードが持つ期間（開始日・終了日）と、参照先の各候補列が持つ日付を突き合わせて、 一致する候補が1つだけなら確定、0個または2個以上なら確定しない。

日付は yyyy年M月d日 / yyyy/M/d / M月d日 / M/d が混在する。年が省略された表記は相手の年で補う。

3. 確定できない時に「保存位置」へ落ちる条件を厳しくする

ここが一番事故る。「保存位置の顧客名が一致するなら使う」で妥協すると、 同じ顧客の別イベントを掴む。実際にそれで、1月開催の案件に別イベントの会場と会期が入った。

採用条件は次の両方を満たす時だけ:

• 保存位置（またはブロック先頭にあたる左数列）の顧客名が一致
• かつ 保存位置の名前セルが空、または自分の名前と整合（完全一致か、どちらかが他方の前方一致）

満たさなければ { col: 0, source: 'none' } を返し、値は空にする。 別レコードの値を返すくらいなら空を返す、を設計方針として明文化しておく。空なら人が気づける。

4. 解決経路をレスポンスに載せる

resolvedCol と resolvedFrom: 'matched' | 'stored' | 'none' を返す。これがあると、 外から全件叩くだけで「何件が名前で解決できたか」を機械で測れる。修正の効果を主張する根拠になり、 次に参照先の構造が変わった時も同じコマンドで再測できる。

5. 「その位置に何が入っているか」の思い込みを疑う

同じ調査で、会場 として読んでいたセルが実は地域名（関東（千葉））で、 実際の会場名は隣の列（幕張メッセ）だったことが判明した。位置ベースの参照は 「列オフセットの意味」も一緒に腐る。1レコード分のブロックをそのまま画面に出して目視で確かめること。

地域表記のような「〜（〜） の形」は正規表現で識別できるので、 「地域表記ではない最初の非空値を会場名とする」という形での判定にしておくと、多少の並び替えに耐える。

実装の型（純関数に切り出す）

表計算 API に触る部分と、照合ロジックを必ず分ける。照合側を純関数にすると、 実データから起こした事故ケースをそのまま単体テストにできる。

// 純関数: シート API に一切触らない
// headerRows = [名前行1(顧客名), 名前行2(レコード名), 日付行1, 日付行2]
function resolveColumn(headerRows, clientName, recordName, storedCol, opts) {
  // 1) 顧客名が一致する列を候補に集める
  // 2) 完全一致 → 前方一致（最長優先）→ 先頭N文字一致 の順で確定を試みる
  // 3) 同点なら opts.startDate / opts.endDate と候補列の日付で割る
  // 4) それでも決まらなければ storedCol を検証して採用 or { col: 0, source: 'none' }
}

シート依存側は「ヘッダ行をまとめて読んで純関数に渡すだけ」の薄いラッパにする。 読み取り範囲は必ず getLastColumn() 等でシート幅にクランプする（末尾のレコードで範囲外エラーになる）。

テストに必ず入れるケース

1. 同じ顧客の別レコードが並ぶ状態で、正しい列を選ぶ（実際に壊れていたケースをそのまま）
2. 短い名前が長い名前の前方一致に食われない（フェアA と フェアAジャパン名古屋 の両方向）
3. 同名の列が2本あり、期間で割れる／期間が無ければ確定しない
4. 保存位置が別レコードなら none（＝空を返す）
5. 全角英数字・半角カナ・全角空白・敬称の揺れを吸収する

検証で踏んだ罠（ここで1セッション溶かした）

日本語を含むリクエストを、シェルの引数（argv）でネイティブの HTTP クライアントに渡してはいけない。

Windows の Git Bash（MSYS2）は、ネイティブ exe を起動するとき argv を ANSI コードページへ変換する。 そのため日本語の検索語がバイト列ごと化け、API は当然「見つからない」を返す。 これをサーバ側の不具合と誤読し、仮説検証と一時デプロイに丸1セッションを費やした。 同じ文字列をスクリプト内で URL エンコードして送ったら、最初から正常に動いていたことが分かった。

• 検証スクリプトはファイルか標準入力（heredoc）で渡す。argv に日本語を載せない
• curl を使うなら、percent-encode 済みの ASCII にしてから渡す
• 「見つからない」を受け取ったら、まず API に「今なにを受け取ったか」を返させる。 受信パラメータをそのまま echo させる診断を1つ足せば、仮説を並べるより早く1往復で切り分けが終わる
• 出力側の文字化け（コンソール）と入力側の文字化けは別問題。両方を独立に潰す

根治（別タスクにする）

保存された位置そのものを再計算して書き戻すのが根治だが、書き込み側は他機能に波及する （生成物の URL を参照先へ書き戻す処理などが、同じ腐った位置を使っている）。 読み取り側を名前照合にした上で、書き込み側の影響調査は別途行う。