# GAS + スプレッドシートで数値・日付が「静かに壊れる」3つの罠と回避策 Apps Script でスプレッドシートをデータストアとして使う業務アプリを作るときに、**構文エラーも例外も出さずにデータが壊れる**3つの罠と、その回避・修復手順をまとめた指示書。 在庫管理・受発注・予実管理など「数値の正しさが業務判断に直結する」アプリで特に効く。実際にこの3つを踏むと、ダッシュボードが平然と**誤った在庫数と誤った発注判断**を表示する。 --- ## 罠1: スプレッドシートのタイムゾーンとスクリプトのタイムゾーンは別物 `appsscript.json` の `timeZone` は**スクリプトの実行タイムゾーン**であり、**セルの値の解釈を担うスプレッドシート側のタイムゾーンとは別設定**。 CLI（clasp）等で自動生成したスプレッドシートは既定ロケール（例: `America/New_York`）のままになる。この状態で日付文字列を書き込むと、読み出し時に時差の分だけずれる。 ### 症状 `"2026-08"` と書き込んだセルを読むと `2026-07-31 05:00:00`（19時間前）になる。**8月の実績が7月として集計される。** ### 対策 初期化処理の先頭で、スプレッドシート側のタイムゾーンを明示的に合わせる。 ```javascript function setupOnce() { var ss = SpreadsheetApp.getActiveSpreadsheet(); // appsscript.json はスクリプト側だけの設定。 // セルの値の解釈はスプレッドシート側のタイムゾーンで決まるため、両方を揃える必要がある。 if (ss.getSpreadsheetTimeZone() !== "") { ss.setSpreadsheetTimeZone(""); } // ... } ``` --- ## 罠2: `"2026-08"` `"2026-09-01"` のような文字列はセルに書くと日付へ自動変換される Sheets は日付に見える文字列を自動で日付値に変換する。文字列として保持したい列は**表示形式をテキスト（`"@"`）に固定**しなければならない。 ### 対策 シートごとに「テキストで保持する列」を定義し、**シート全体（`getMaxRows()`）**に適用する。追記・置換の直前にも再適用する（行が増えたときに書式が引き継がれないケースがあるため）。 ```javascript // 年月・日付など「文字列として保持したい列」の列番号 var テキスト列 = { 月次減少: [1], 使用予定: [2], 棚卸: [2] }; function 書式を適用(sheet, cols) { cols.forEach(function (col) { sheet.getRange(1, col, sheet.getMaxRows(), 1).setNumberFormat("@"); }); } ``` --- ## 罠3【最も危険】列を増やすと、旧レイアウトの表示形式がその位置に残る ヘッダー文字列を書き換えて列構成を変更しても、**セルの NumberFormat は前のまま残る**。旧レイアウトで日付列だった位置を新レイアウトで数値列に転用すると、**書き込んだ数値が日付として解釈される**。 ### 実際に起きた症状 7列 → 12列へ拡張した際、旧 G列は「登録日時」（日付書式）だった。新 G列は個数を入れる数値列。 - `200` を書き込むと、セルには **`1900/07/18`**（シリアル値200の日付表示）が入る - `getValues()` は `Date` オブジェクトを返す - 「不正値は0にする」防御ロジックがその Date を 0 に落とし、**入力した200が静かに消えた** - 別経路では Date が数値化され、集計に **`2,191,914,000,832`** という桁の値が混入 - 結果、下限割れ寸前の項目が「**適正・発注不要**」と表示された ### 対策A: 全列の表示形式を明示的に定義する（推測に頼らない） 「テキスト列だけ指定して残りは放置」ではなく、**全シート・全列**の種類を定義として持つ。 ```javascript // 列の種類: "text"（文字列保持）/ "number"（数値）/ "datetime"（登録日時） var 列書式 = { 使用予定: ["text","text","text","text","number","text","number","number","number","number","text","datetime"], // ...全シート分を定義する }; var 書式文字列 = { text: "@", number: "General", datetime: "yyyy-mm-dd hh:mm:ss" }; ``` `modelEnsureSheets()` と**ヘッダー移行処理の両方**で、シート全体に適用する。 ### 対策B: 既に Date 化してしまったセルの修復 値そのものは無傷（シリアル値）なので、書式を直したうえでシリアル値に戻せば復元できる。 ```javascript // Sheets のシリアル値の基準日は 1899-12-30 function dateをシリアル値へ戻す(d) { return (d.getTime() - new Date(1899, 11, 30).getTime()) / 86400000; } ``` 修復関数は「何をどう直したか」をシート別・列別の件数で返すようにする。実セルを書き換えるため、実行前のバックアップを促すコメントを残すこと。 --- ## 罠を早期に発見するための3原則 ### 原則1: 防御ロジックは必ず警告を可視化する 「不正な値は 0 にする」は正しい防御だが、**黙って 0 にすると発覚が致命的に遅れる**。0 に落としたときは必ず、**シート名・行番号・列名・元の値**を記録し、画面上部に警告バナーとして出す。 上記の罠3は、この可視化が無かったために「防御が働いて静かにデータが消える」状態になり、発見が大幅に遅れた。 ### 原則2: 単体テストでは原理的に検出できないと知る メモリ上のきれいな配列を関数に渡すテストは、**シートの表示形式に起因するバグを構造的に検出できない**。テストが全部通っていても実機では壊れている。 最低限、**書き込み用の行配列を生成する処理を純関数に切り出し、その出力を読み出しロジックに通す往復テスト**を書く。ただしこれも「書式」の問題は拾えないことを理解しておく。 ### 原則3: 検証は必ず実機の生データまで見る API の戻り値だけを見ていると、防御ロジックに隠されて真の姿が見えない。**スプレッドシートのセルを直接読む**まで検証を終えないこと。 罠3は、API の戻り値では「色別数量が 0」としか見えなかったが、生データを直接読んで `1900/07/18` という表示を見た瞬間に原因が確定した。 --- ## チェックリスト（GAS + シートの業務アプリを作るとき） - [ ] `setupOnce()` でスプレッドシート側のタイムゾーンを明示設定したか - [ ] 全シート・全列の表示形式を定義として持ち、シート全体に適用しているか - [ ] 列構成を変更する処理で、ヘッダーだけでなく**表示形式も更新**しているか - [ ] 数値として読む箇所で、Date・文字列・異常に大きい値への防御を入れたか - [ ] その防御が **0 に落としたことを利用者に見える形で警告**しているか - [ ] 既存データの表示形式を直す修復コマンドを用意したか - [ ] デプロイ後、**実機のセルを直接読んで**期待どおりの値が入っていることを確認したか --- ## 補足: 列構成の移行はデータ0件のときだけ自動実行する ヘッダーを書き換える移行処理は、**データ行が1件でもあれば実行しない**ようにする。移行を自動で走らせて既存データの列がずれると、復旧が非常に困難になる。 ```javascript if (sheet.getLastRow() > 1) { Logger.log("データ行があるためヘッダー移行を行いませんでした。"); return; } ``` データがある状態で移行が必要になった場合は、列を挿入して値を移し替える専用の移行処理を、**バックアップを取ったうえで**個別に書くこと。 ---