# 別シートの「列番号」を保存して参照する設計が腐ったときの直し方（名前照合＋日付タイブレーク） **対象**: 表計算（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 に触る部分と、照合ロジックを**必ず分ける**。照合側を純関数にすると、 実データから起こした事故ケースをそのまま単体テストにできる。 ```js // 純関数: シート 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 を参照先へ書き戻す処理などが、同じ腐った位置を使っている）。 読み取り側を名前照合にした上で、書き込み側の影響調査は別途行う。 ---