スプレッドシートを業務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. フェーズごとの折りたたみ

フェーズ見出し行を作り、その配下に行グループを作る。

sheet.getRange(firstRow, 1, count, 1).shiftRowGroupDepth(1);
var group = sheet.getRowGroup(firstRow, 1);
if (group && !shouldExpand) group.collapse();

• 見出しは <フェーズ名>　—　<済>/<総数> 完了 の形にして、進捗が畳んだまま見えるようにする
• 見出し行の隠し列に __PHASE__:<key> を書く。あとで「この行は見出しか」を1セル読むだけで判定できる
• 既定の開閉: 「今の工程」とその1つ前だけ開く。全部開くと畳んだ意味がなく、 全部畳むと毎回クリックが要る

罠3: 「現在の工程」を保存済みのステータス列から取らない

多くの現場データでは、この手の列は取り込み時に固定値が入ったまま誰も更新していない。 使うと全件が同じ工程で開く。日付から逆算する方が確実に正しい。

// 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 を持ち、 パネルは表示するだけにする。

// 手動は自動より強い。人が外したものを自動で戻さない
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} で何も言わずに捨てる。 画面にはチェックが付いたまま残るので、誰も気付けない。

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個付いている」状態になった。

埋め戻しの後に読み直して、見出しセルだけ書き直す。

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 の実装

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);
}

レイアウト変更は「バージョンマーカー＋列マップ」で移行する

全パネルを一斉に作り直すことはできない（時間制限）。新旧が混在する期間が必ずある。

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分など）の行だけを重複とみなす。

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()
• マージと行グループだけはセクション単位（避けられない）

これで約半分になった。

バッチ処理には時間予算を入れる。 ただし「開始前に経過を見る」だけでは足りない:

// 直近1枚の実測から、次を始めたら間に合わないかを予測する
function shouldStop(elapsedMs, lastItemMs, budgetMs) {
  return elapsedMs + lastItemMs * 1.3 > budgetMs;   // 1枚目は lastItemMs に初期見積もりを入れる
}

elapsed > budget だけで判定すると、予算の直前に始まった1枚が制限を突き抜ける。

戻り値は「あと何件残っているか」を正直に返す。 総数 - 今回処理した数 を返すと、何度呼んでも同じ数字が出て終わりが分からない。 「まだ旧バージョンのままの件数」を返すこと。

---

7. テスト

GAS は実行環境がクラウドなのでユニットテストしづらいが、純関数だけは切り出せる。

// ファイル末尾。GAS では module が未定義なので無害
if (typeof module !== 'undefined' && module.exports) {
  module.exports = { layoutFor, phaseFromDates, mergeEntry, isActiveDuplicate, shouldStop };
}

最低限これだけはテストする:

• 列マップが新旧それぞれ正しい値を返す（旧は存在しない列が 0）
• 日付からの工程判定の境界値（14日前 / 15日前 / 当日 / 終了翌日 / 日付なし）
• 手動と自動のマージ優先順位（4通り全部）
• 重複判定（時間内/時間外/別行/日時が壊れている）
• 全機能に一意な featureKey と既知のフェーズがあること

8. 実機確認は「セル値」だけでは足りない

折りたたみ状態はセルを読んでも分からない。読み取り専用の検査コマンドを用意する。

function debugPhaseState(itemId) {   // → [{phaseKey, headerRow, headerText, collapsed, doneInSheet, total}]
  // collapsed は getRowGroup(firstRow, 1).isCollapsed() を try/catch で
}

これがないと「見出しの数字」と「実際のチェック数」がズレていても気付けない。 実際、このズレ（罠5）はこの検査を作って初めて発見できた。

---

チェックリスト

• 可視列を増やしていないか（増やすならメタ情報の読み手を全部直したか）
• featureKey は不変か。ラベル文字列を主キーにしていないか
• 状態の正本はパネル外にあるか
• キーが空のとき黙って捨てる関数はないか（理由を返しているか）
• 集計を書く順序は、元データの更新より後か
• マーカー比較はヘルパー経由に統一されているか（grep で確認）
• 描画は範囲一括か。セクションごとに繰り返していないか
• バッチに時間予算があり、直近の実測を見ているか
• 純関数を切り出してテストがあるか
• 折りたたみ状態を機械的に確認する手段があるか