# スプレッドシートを業務UIにする — フェーズ折りたたみ＋2種チェックボックスのパネル Google スプレッドシートを「現場が毎日開く操作画面」にするための GAS 設計テンプレート。 機能を工程（フェーズ）ごとに畳み、**押すと走る ▶ ボタン**と**進捗として残る ✅ チェック**を1枚に同居させる。 対象: 案件・工程・チケットなど「1件ずつ進む仕事」を Sheets で管理していて、 機能が20個を超えてフラットな一覧では探せなくなったチーム。 --- ## 1. 全体構成 ``` マスターSS ├─ 一覧シート … 全件のインデックス ├─ <案件A> パネル … 案件1件につき1シート（この文書の主役） ├─ <案件B> パネル ├─ _ジョブキュー … 隠し。onEdit が積み、worker が処理 └─ _進捗 … 隠し。✅ の正本 ``` **なぜ案件ごとに1シートか**: 1枚を共有すると、複数人が同時に触ったとき 「選択セル」や「対象行」の取り合いが起きて予約が黙って消える。案件ごとに分ければ衝突しない。 **なぜジョブキューを挟むか**: simple `onEdit` は権限が弱く（他のスプレッドシートを開けない） 実行時間も約30秒しかない。onEdit は「キューに積むだけ」にして、 時間制限の緩いトリガー実行の worker が実処理をする。 --- ## 2. パネルのレイアウト（ここが一番事故る） ``` 行1 A1:F1 タイトル G1 = マーカー文字列 行2 A2:F2 使い方の説明 G2 = この案件の識別ラベル 行3 A3:B3 ラベル / C3:E3 案件名 G3 = 元データ上の位置 行4 G4 = 案件ID 行5 ヘッダー行 G5 = 最終更新日時 行6〜 フェーズ見出し行 + 機能行 の繰り返し ``` | 列 | 用途 | 表示 | |---|---|---| | A | ▶実行（押すと走って false に戻る） | 見える | | B | ✅済（永続。人が付け外しできる） | 見える | | C | 機能名 | 見える | | D | どんな時に使うか | 見える | | E | 結果（リンク） | 見える | | F | 状態（実行日時を文字列で内包） | 見える | | G | command ＋ **行1〜5はパネルのメタ情報** | 隠し | | H | 引数 JSON | 隠し | | I | フラグ類 | 隠し | | J | featureKey（安定ID） | 隠し | ### 🔴 罠1: 可視列を増やすとメタ情報が壊れる `G1:G5` にパネルのメタ情報（マーカー/ラベル/ID/更新日時）を置く設計にしていると、 **可視列を6→7に増やした瞬間にヘッダー行がメタ情報を上書きする**。 さらに「このシートはもう構築済みか」を `G6` の中身で判定していると、 行6がフェーズ見出しになって G6 が空になり、**開くたびに全再構築**が走る。 対策はどちらか: - 可視列を増やさない（不要な列を1つ潰して枠を作る。日時は状態セルに文字列で埋める） - メタ情報を別の隠し列へ移し、**全ての読み手を同時に直す**（`getRange('G1')` を grep で洗い出す） 前者を強く推奨。マーカー読み取りはコードの至る所に散っていて、取りこぼすと本番で静かに壊れる。 ### 罠2: 「機能名の文字列」を主キーにしない 再構築でラベルを変えた瞬間に、保存済みの進捗と結びつかなくなる。 **featureKey（`estimate` `itemList` のような短い不変ID）を隠し列に持たせる**。 一度決めたキーは変えない。 --- ## 3. フェーズごとの折りたたみ フェーズ見出し行を作り、その配下に行グループを作る。 ```javascript sheet.getRange(firstRow, 1, count, 1).shiftRowGroupDepth(1); var group = sheet.getRowGroup(firstRow, 1); if (group && !shouldExpand) group.collapse(); ``` - 見出しは `<フェーズ名>　—　<済>/<総数> 完了` の形にして、**進捗が畳んだまま見える**ようにする - 見出し行の隠し列に `__PHASE__:` を書く。あとで「この行は見出しか」を1セル読むだけで判定できる - **既定の開閉**: 「今の工程」とその1つ前だけ開く。全部開くと畳んだ意味がなく、 全部畳むと毎回クリックが要る ### 罠3: 「現在の工程」を保存済みのステータス列から取らない 多くの現場データでは、この手の列は**取り込み時に固定値が入ったまま誰も更新していない**。 使うと全件が同じ工程で開く。**日付から逆算する**方が確実に正しい。 ```javascript // today は必ず引数で受ける（関数内で new Date() を呼ぶとテストできない） function phaseFromDates(startDate, endDate, today) { if (!startDate) return 'default'; if (endDate && today > endDate) return 'after'; if (today >= dayBefore(startDate)) return 'onsite'; var days = daysUntil(startDate, today); if (days <= 14) return 'late'; if (days <= 60) return 'mid'; return 'early'; } ``` 既存データの工程ラベルを読む場合は、**表記ゆれを必ず正規化**する。 実データは同じ意味の見出しが半角カナ・全角・括弧あり/なしで混在していることが多い。 `String(x).normalize('NFKC')` してから比較する。 --- ## 4. ✅ の永続化 — 正本を別シートに置く パネルは仕様変更のたびに `clear()` して作り直す。**セルだけに持たせた状態は必ず消える。** 隠しシート `_進捗` に `itemId | label | featureKey | done | source | updatedAt | updatedBy` を持ち、 パネルは表示するだけにする。 ```javascript // 手動は自動より強い。人が外したものを自動で戻さない function mergeEntry(existing, incoming) { if (existing && existing.source === 'manual' && incoming.source === 'auto') return existing; return incoming; } ``` ### 🔴 罠4: キーが空のとき黙って捨てる関数を作らない simple `onEdit` は案件を「表示ラベル」でしか知らないことがある。 そこで内部IDを空で渡すと、保存関数が `if (!id) return {skipped: true}` で**何も言わずに捨てる**。 画面にはチェックが付いたまま残るので、**誰も気付けない**。 ```javascript function applyManual(itemId, label, featureKey, done, user) { if (!itemId) { try { itemId = resolveIdFromLabel(label); } catch (e) { console.warn(e); } if (!itemId) return { skipped: true, reason: 'IDが解決できない: ' + label }; // 理由を必ず返す } return upsert(itemId, label, featureKey, done, 'manual', user); } ``` 「失敗を偽値で返すだけ」の関数は、動かして初めて気付く。**理由を持たせる**か例外にする。 ### 🔴 罠5: 集計を書いてから元データを更新しない 「見出しに n/m を書く → あとで自動✅を埋め戻す」の順にすると、 **見出しは必ず埋め戻した分だけ古い値で固まる**。 実際に「全見出しが 0/N なのに、その下にチェックが10個付いている」状態になった。 埋め戻しの**後**に読み直して、見出しセルだけ書き直す。 ```javascript var written = backfill(...); if (written > 0) { var counts = countByPhase(features, load(itemId)); phaseHeaders.forEach(function (h) { sheet.getRange(h.row, 1).setValue( h.text.replace(/　—　\d+\/\d+ 完了$/, '　—　' + counts[h.key].done + '/' + counts[h.key].total + ' 完了')); }); } ``` --- ## 5. onEdit の実装 ```javascript function onEdit(e) { if (!e || !e.range) return; var column = e.range.getColumn(); if (column !== 1 && column !== 2) return; // ▶ と ✅ だけ if (column === 1 && e.value !== 'TRUE' && e.value !== true) return; var sheet = e.range.getSheet(), row = e.range.getRow(); var marker = String(sheet.getRange('G1').getValue() || ''); if (!isPanelMarker(marker) || row < 6) return; var layout = layoutFor(marker); // ← バージョンで列番号を切り替える var featureKey = layout.key ? String(sheet.getRange(row, layout.key).getValue() || '') : ''; var isPhaseHeader = featureKey.indexOf('__PHASE__:') === 0; if (column === 2) { // ✅（永続） if (!layout.done || isPhaseHeader || !featureKey) return; enqueue({ command: 'applyManual', args: ['', label, featureKey, e.value === 'TRUE', user()], skipDuplicateCheck: true }); // ON→OFF→ON を連続でできるように updatePhaseCount(sheet, row, layout); return; // ← false に戻さない } if (isPhaseHeader) { e.range.setValue(false); return; } // 見出しの▶は黙って戻す // …▶の処理… e.range.setValue(false); } ``` ### レイアウト変更は「バージョンマーカー＋列マップ」で移行する 全パネルを一斉に作り直すことはできない（時間制限）。**新旧が混在する期間**が必ずある。 ```javascript function layoutFor(marker) { // 0 = その列は存在しない if (marker === MARKER_V3) return { done: 2, label: 3, result: 5, status: 6, cmd: 7, args: 8, key: 10 }; return { done: 0, label: 2, result: 4, status: 6, cmd: 7, args: 8, key: 0 }; } ``` マーカーの比較は必ずヘルパー関数（`isPanelMarker`）経由にする。 `=== MARKER_V2` の直書きが1箇所でも残ると、新レイアウトのパネルが「パネルではない」と判定されて消える。 ### 罠6: 「押しても無反応」の犯人は重複判定 キューに `待機` のまま取り残された行があると、重複チェックが**永久にそれにヒットして** 以後その行は二度と実行できない。**受付が一定時間内（30分など）の行だけ**を重複とみなす。 ```javascript function isActiveDuplicate(row, panel, rowNum, nowMs) { if (!(row[TS] instanceof Date)) return false; // 日時が読めないものは古い扱い if (String(row[PANEL]) !== panel || Number(row[ROW]) !== rowNum) return false; if (row[STATE] !== '待機' && row[STATE] !== '実行中') return false; return nowMs - row[TS].getTime() <= 30 * 60 * 1000; } ``` --- ## 6. 実行時間との戦い（6分制限） フェーズ分けは**描画コストを跳ね上げる**。ブロックごとに `setValues` / `insertCheckboxes` / `setBackgrounds` / `setFontWeight` を呼ぶと、 セクション数だけ倍々になる。実測でパネル1枚 2分 → 3分45秒。 **一括化する:** - 見出し行と項目行を**1本の二次元配列**に組み立て、`setValues` は1回 - 背景色も全行ぶんの配列を作って `setBackgrounds` 1回 - チェックボックスは範囲全体に1回入れてから、見出し行だけ `clearDataValidations().clearContent()` - マージと行グループだけはセクション単位（避けられない） これで約半分になった。 **バッチ処理には時間予算を入れる。** ただし「開始前に経過を見る」だけでは足りない: ```javascript // 直近1枚の実測から、次を始めたら間に合わないかを予測する function shouldStop(elapsedMs, lastItemMs, budgetMs) { return elapsedMs + lastItemMs * 1.3 > budgetMs; // 1枚目は lastItemMs に初期見積もりを入れる } ``` `elapsed > budget` だけで判定すると、予算の直前に始まった1枚が制限を突き抜ける。 **戻り値は「あと何件残っているか」を正直に返す。** `総数 - 今回処理した数` を返すと、何度呼んでも同じ数字が出て終わりが分からない。 「まだ旧バージョンのままの件数」を返すこと。 --- ## 7. テスト GAS は実行環境がクラウドなのでユニットテストしづらいが、**純関数だけは切り出せる**。 ```javascript // ファイル末尾。GAS では module が未定義なので無害 if (typeof module !== 'undefined' && module.exports) { module.exports = { layoutFor, phaseFromDates, mergeEntry, isActiveDuplicate, shouldStop }; } ``` 最低限これだけはテストする: - 列マップが新旧それぞれ正しい値を返す（旧は存在しない列が 0） - 日付からの工程判定の**境界値**（14日前 / 15日前 / 当日 / 終了翌日 / 日付なし） - 手動と自動のマージ優先順位（4通り全部） - 重複判定（時間内/時間外/別行/日時が壊れている） - 全機能に**一意な** featureKey と既知のフェーズがあること ## 8. 実機確認は「セル値」だけでは足りない 折りたたみ状態はセルを読んでも分からない。**読み取り専用の検査コマンド**を用意する。 ```javascript function debugPhaseState(itemId) { // → [{phaseKey, headerRow, headerText, collapsed, doneInSheet, total}] // collapsed は getRowGroup(firstRow, 1).isCollapsed() を try/catch で } ``` これがないと「見出しの数字」と「実際のチェック数」がズレていても気付けない。 実際、このズレ（罠5）はこの検査を作って初めて発見できた。 --- ## チェックリスト - [ ] 可視列を増やしていないか（増やすならメタ情報の読み手を全部直したか） - [ ] featureKey は不変か。ラベル文字列を主キーにしていないか - [ ] 状態の正本はパネル外にあるか - [ ] キーが空のとき黙って捨てる関数はないか（理由を返しているか） - [ ] 集計を書く順序は、元データの更新より後か - [ ] マーカー比較はヘルパー経由に統一されているか（`grep` で確認） - [ ] 描画は範囲一括か。セクションごとに繰り返していないか - [ ] バッチに時間予算があり、直近の実測を見ているか - [ ] 純関数を切り出してテストがあるか - [ ] 折りたたみ状態を機械的に確認する手段があるか ---