# 数式まみれの業務スプレッドシートを、Web アプリから壊さずに編集させる型 ## これは何の指示書か 「Excel/スプレッドシートで回している業務台帳を、社内 Web アプリの画面から直接編集できるようにしたい」という依頼に対して、 **シートの計算式を壊さずに書き込む層**を作るための設計と実装手順。Google Sheets API（Node/TypeScript）を前提に書くが、 考え方は Excel/Graph API でもそのまま使える。 想定する相手は「1枚のシートに ArrayFormula と per-row 数式と手入力値が混在していて、1セル間違えると全社の売上集計が飛ぶ」台帳。 **AI にコードを書かせる時ほど、この層を先に作る価値が高い**（AI は「値を書くだけ」と思って数式セルを平気で潰す）。 --- ## 手順0（最重要）: 推測でコードを書く前に、実物のセルを1枚ダンプする **ここを飛ばすと、テストが全部緑のまま実運用で必ず失敗する。** 実際に、フィクスチャを想像で作ったせいで 「数式が無い行の編集」と「空行への新規追加」という中心機能が両方壊れたまま単体テスト 46 件が緑になった事例がある。 読み取り専用スクリプトを1本書き、対象範囲を **数式表示と表示値の両方**で落とす: ```ts for (const render of ["FORMULA", "FORMATTED_VALUE"] as const) { const res = await sheets.spreadsheets.values.batchGet({ spreadsheetId, ranges: [`'${TAB}'!A1:AH64`], valueRenderOption: render, }); // 列記号つきで1行ずつ出す（A=..., B=... の形）。目視できる形にするのが目的 } ``` このダンプから次を**必ず**確定させる。憶測でどれか1つでも決めたら、その分だけ本番で壊れる。 | 確定させること | 見つけ方 | |---|---| | どのセルが手入力欄か | FORMULA 表示で `=` で始まらないセル | | どのセルが per-row 数式か | 各行に同型の数式が並んでいる列 | | どのセルが **ArrayFormula の展開先**か | 先頭行だけに `=ARRAYFORMULA(...)` があり、以降の行は**両方の表示で空**に見える | | 小計行・見出し行の位置 | `="項目計 "&…` のような文字列連結数式。**行番号を決め打ちしない** | | 「一見ふつうの行」の例外 | 値は入っているのに per-row 数式が1つも無い行、既定の数式を素の値で上書きした行 | **ArrayFormula の展開先が最大の地雷**。先頭行の1つの数式が数百行に広がっているだけなので、 19行目以降の1セルに書き込むと `#REF!` で列ごと全滅する。表示値では「ただの数値」に見えるため、 FORMULA 表示で確認しない限り気づけない。 --- ## 手順1: セル単位の allowlist を作り、全書き込みをそこに通す 「危ないところを避ける」ではなく「**許可した所だけ書ける**」にする。範囲外は例外で落とす。 ```ts export function assertSafeWrite(range: string, opts: { allowFormulaRestore?: boolean } = {}): void { const { column, row } = parseCell(range); // 単一セル以外は受け付けない // 1. ArrayFormula の展開先は常に拒否（復元モードのみ例外） if (SPILL_COLUMNS.includes(column) && row >= SPILL_START && !opts.allowFormulaRestore) throw new Error(`ArrayFormula 保護範囲には書き込めません: ${range}`); // 2. 明細ブロックの許可列 if (row >= ITEM_START && row <= ITEM_END) { if (ITEM_EDITABLE.includes(column)) return; if (opts.allowFormulaRestore && ROW_FORMULA_COLUMNS.includes(column)) return; throw new Error(`明細行の保護列には書き込めません: ${range}`); } // 3. ヘッダブロックの許可列（日付など既定が数式の列は専用フラグでのみ許可） if (row >= HEAD_START && row <= HEAD_END) { … } // 4. どのブロックにも属さない行は全部拒否 throw new Error(`許可範囲外には書き込めません: ${range}`); } ``` ポイント: - **単一セル以外を受け付けない**（`A1:C10` のような範囲書き込みを許すと allowlist が意味を失う） - 小計行は**行番号ではなく数式で動的に判定**する（実物では小計行が想定より多いことがある） - 「日付を上書きする」のような例外は、呼び出し側が**明示フラグを渡した時だけ**通す（既定では通さない） --- ## 手順2: 全操作を dry-run 既定にし、差分を先に出す 書き込み関数の引数は `dryRun?: boolean` で、**既定を true**にする（`args.dryRun !== false` で判定）。 dry-run では1バイトも書かず、`{range, label, from, to}` の配列だけ返す。 画面は必ず **プレビュー → 確認 → 適用**の2段にする。プレビューを飛ばして適用できる導線を作らない。 プレビュー時にシート内容のハッシュ（指紋）を作って hidden field で持ち回り、 適用時に再取得したハッシュと一致しなければ拒否する。これで「プレビュー中に他人が編集した」事故が防げる。 ```ts const token = createHash("sha256").update(JSON.stringify(snapshot)).digest("hex"); // 適用時: if (token !== currentToken) throw new Error("プレビュー後にシートが変更されました"); ``` --- ## 手順3: 適用前バックアップ + read-back + 「触っていないセルの不変検査」 適用の前後で、対象範囲を **FORMULA と表示値の両方**で取得する。 1. **適用前**: 丸ごと取得してバックアップとして保持する 2. **適用後**: 書いたセルが期待どおりか照合する（数式セルは FORMULA、値セルは表示値で比較） 3. **適用後**: **書いていないセルの数式が1つでも変化していないか**を全数比較する ← これが本命 3 がないと「ArrayFormula を壊したこと」に**その場で気づけない**。壊れた列は次に誰かが見た時に `#REF!` として発見される。 失敗した時に**バックアップを道連れにしない**こと。戻り値だけでバックアップを返す実装だと、 例外時に復元材料ごと消える。専用の例外型に載せる: ```ts export class ApplyError extends Error { constructor(message: string, readonly backup: Backup, readonly planned: Change[]) { super(message); } } ``` 呼び出し側（画面・CLI）は、この例外を捕まえたら**まずバックアップを永続化してから**エラーを返す （CLI ならファイルへ、Web アプリなら監査ログのレコードへ）。 ### 誤った失敗を作らない 表示値には `¥` や桁区切りや全角数字が混ざる。`Number("¥96,000")` は `NaN` なので、 **正しく書けているのに read-back 不一致**という嘘の失敗が出る。比較前に必ず正規化する: ```ts const normalized = String(value).normalize("NFKC").replace(/[¥￥,\s]/g, ""); ``` 検査が赤い時は、まずデータではなく**検査側を疑う**。 --- ## 手順4: 「数式が無い空行」に新規行を入れる時の数式復元 業務シートには、per-row 数式が入っている行と入っていない行が混在する。数式が無い行に値だけ書くと、 **小計にも原価にも乗らない**（画面上は入ったように見えるので発覚が遅れる）。 復元する時、**数式を文字列でハードコードしない**。同じブロック内の既存行からコピーして、行番号だけ差し替える: ```ts function retarget(formula: string, sourceRow: number, targetRow: number): string { // 相対参照だけを置換し、$付き絶対参照（$E$2 など）は据え置く return formula.replace(/(\$?[A-Z]+)(\$?)(\d+)/g, (whole, col, absRow, row) => absRow === "" && Number(row) === sourceRow ? `${col}${targetRow}` : whole); } ``` **テンプレートは「行」ではなく「列ごと」に探すこと。** 1行を丸ごとテンプレにすると、 その行のある1列だけが素の値で上書きされていた瞬間に「テンプレに数式がありません」で全体が失敗する。 実シートでは「原価の既定数式を手で数値に上書きした行」はごく普通に存在する。 - 計算の根幹になる列（小計など）はテンプレが無ければエラー - 補助的な列はテンプレが無ければ**その列だけスキップ**し、警告として利用者に見せる 既存行（値が入っていて数式が無い行）への復元は、**利用者が明示的に選んだ時だけ**にする。既定は値だけ書き、 「この行には計算式がないため合計に反映されません」と警告を出す。勝手に数式を足すと、 意図的に数式を外してある行を壊す。 --- ## 手順5: 「自動計算に戻す」を空文字で実装しない 既定が数式で、運用上そこに人が手入力で印を書くセル（例: `=IF(金額=0,"不要","")` に `○` を入れる）がある。 これを空に戻す時、**空文字を書き込むと数式が消えて空セルが残る**。 - 空にするのは `values.batchClear`（空文字の `update` を使わない） - さらに**元が数式だったセルは、バックアップに入っている元の数式をそのまま書き戻す** - UI のラベルも「空欄にする」ではなく「**自動判定（数式）に戻す**」にする --- ## 手順6: テストを「実物の形」で書く フィクスチャは手順0のダンプから作る。最低限、次の**異常系が実在する**前提で組む: 1. per-row 数式が1つも無いが値は入っている行 2. 既定の数式を素の値で上書きしてある列を持つ行 3. 見出しだけの行（値はあるが明細ではない） 4. 小計行（想定より数が多いことがある） 5. 本来は手入力欄なのに数式が入っている行 検査すべき挙動: - 保護列・保護行への書き込みが**例外になる** - dry-run で書き込み API が**一度も呼ばれない** - 通常編集で数式列に書き込まれない - 空行への新規追加で、数式が**列ごとに**正しいテンプレから復元される - 「空にする」が値列だけを消し、数式セルを残す - 触っていないセルの数式が変わった時に例外になる --- ## つまずきやすい点まとめ - **表示値だけ見て構造を決めない。** ArrayFormula の展開先は表示値では普通の数値に見える - **小計行・見出し行の行番号を決め打ちしない。** 実物は想定より1つ多い - **1行をテンプレに全列をコピーしない。** 列ごとに探す - **検査の失敗を「データが悪い」と決めつけない。** 通貨記号・全角で比較が壊れているだけのことが多い - **委譲先（AI）の「テスト全部緑」を証拠にしない。** フィクスチャが実物の形でなければ、中心機能が壊れたまま緑になる。 実データに読み取り専用で当ててから信じる - **実データへの初回の書き込みは、値が変わらない同値書き込みで経路だけ確かめる**と安全に通せる ---