数式まみれの業務スプレッドシートを、Web アプリから壊さずに編集させる型

これは何の指示書か

「Excel/スプレッドシートで回している業務台帳を、社内 Web アプリの画面から直接編集できるようにしたい」という依頼に対して、 シートの計算式を壊さずに書き込む層を作るための設計と実装手順。Google Sheets API（Node/TypeScript）を前提に書くが、 考え方は Excel/Graph API でもそのまま使える。

想定する相手は「1枚のシートに ArrayFormula と per-row 数式と手入力値が混在していて、1セル間違えると全社の売上集計が飛ぶ」台帳。 AI にコードを書かせる時ほど、この層を先に作る価値が高い（AI は「値を書くだけ」と思って数式セルを平気で潰す）。

---

手順0（最重要）: 推測でコードを書く前に、実物のセルを1枚ダンプする

ここを飛ばすと、テストが全部緑のまま実運用で必ず失敗する。 実際に、フィクスチャを想像で作ったせいで 「数式が無い行の編集」と「空行への新規追加」という中心機能が両方壊れたまま単体テスト 46 件が緑になった事例がある。

読み取り専用スクリプトを1本書き、対象範囲を 数式表示と表示値の両方で落とす:

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 を作り、全書き込みをそこに通す

「危ないところを避ける」ではなく「許可した所だけ書ける」にする。範囲外は例外で落とす。

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 で持ち回り、 適用時に再取得したハッシュと一致しなければ拒否する。これで「プレビュー中に他人が編集した」事故が防げる。

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! として発見される。

失敗した時にバックアップを道連れにしないこと。戻り値だけでバックアップを返す実装だと、 例外時に復元材料ごと消える。専用の例外型に載せる:

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 不一致という嘘の失敗が出る。比較前に必ず正規化する:

const normalized = String(value).normalize("NFKC").replace(/[¥￥,\s]/g, "");

検査が赤い時は、まずデータではなく検査側を疑う。

---

手順4: 「数式が無い空行」に新規行を入れる時の数式復元

業務シートには、per-row 数式が入っている行と入っていない行が混在する。数式が無い行に値だけ書くと、 小計にも原価にも乗らない（画面上は入ったように見えるので発覚が遅れる）。

復元する時、数式を文字列でハードコードしない。同じブロック内の既存行からコピーして、行番号だけ差し替える:

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）の「テスト全部緑」を証拠にしない。 フィクスチャが実物の形でなければ、中心機能が壊れたまま緑になる。 実データに読み取り専用で当ててから信じる
• 実データへの初回の書き込みは、値が変わらない同値書き込みで経路だけ確かめると安全に通せる