# スプレッドシート実行パネルの「結果」セルを必ずクリック可能にする（GAS） Google スプレッドシートを操作パネルにして GAS の機能をワンクリック実行させる構成で、 実行結果の「生成物リンク」がセルに出ず、利用者が別シートを探す羽目になる問題を潰すための指示書。 対象読者は AI コーディングエージェント。以下をそのまま実装させればよい。 --- ## 前提の構成 - スプレッドシートに操作パネルのシートがある。1 行 = 1 機能。 - 列は例として `B=機能名 / D=結果 / E=実行日時 / F=状態 / G=実行する関数名 / H=引数(JSON)`。 - 実行キュー（ジョブ行を積んで時間主導トリガで消化する方式）から関数を呼び、戻り値を D 列に書く。 --- ## 問題1: 結果 URL を「決め打ちのキー名」で拾うと必ず取りこぼす よくある実装: ```js var url = ret.docUrl || ret.slidesUrl || ret.url || ret.estimateSheetUrl || ''; if (url) range.setFormula('=HYPERLINK("' + url + '","開く")'); else range.setValue('完了（リンクは生成物一覧シートを参照）'); ``` 機能が増えると戻り値のキーは `sheetUrl` `proposalSheetUrl` `scheduleSheetUrl` `checkSheetUrl` `itemListUrl` `folderUrl` … と際限なく増える。上の列挙に無いキーは**URL を持っているのに フォールバック文字列に落ちる**。利用者から見ると「リンクが出ない機能がある」になる。 ### 対策: 末尾が `Url` のキーを総なめする ```js var PANEL_LINK_LABELS = { docUrl: '📄 文書', sheetUrl: '📄 シート', folderUrl: '📂 フォルダ' /* 既知だけ命名 */ }; var PANEL_LINK_ORDER = ['docUrl', 'sheetUrl', 'folderUrl']; // 表示順（未知キーは後ろ） var PANEL_LINK_MAX = 4; function PanelLinks_collect(ret) { if (!ret || typeof ret !== 'object') return []; var keys = Object.keys(ret).filter(function (k) { return /url$/i.test(k); }); keys.sort(function (a, b) { var ia = PANEL_LINK_ORDER.indexOf(a), ib = PANEL_LINK_ORDER.indexOf(b); return (ia < 0 ? PANEL_LINK_ORDER.length : ia) - (ib < 0 ? PANEL_LINK_ORDER.length : ib); }); var seen = {}, out = []; keys.forEach(function (k) { var url = ret[k]; if (typeof url !== 'string' || !/^https?:\/\//.test(url)) return; // 相対値・空文字を弾く if (seen[url]) return; seen[url] = true; if (out.length < PANEL_LINK_MAX) out.push({ label: PANEL_LINK_LABELS[k] || ('📄 ' + k.replace(/Url$/i, '')), url: url }); }); return out; } ``` **列挙式に戻さないこと。** 戻り値にキーを足すだけで自動的に拾われるのが要点。 --- ## 問題2: 生成物が複数ある機能は 1 セルに複数リンクを入れたい `=HYPERLINK()` は 1 セル 1 本しか持てない。**RichTextValue** に統一する。 ```js function PanelLinks_write(range, text, links) { var prefix = text ? String(text).slice(0, 200) + '\n' : ''; var body = '', ranges = []; links.forEach(function (link, i) { if (i > 0) body += ' / '; var start = prefix.length + body.length; body += link.label; ranges.push({ start: start, end: prefix.length + body.length, url: link.url }); }); var b = SpreadsheetApp.newRichTextValue().setText(prefix + body); ranges.forEach(function (r) { b.setLinkUrl(r.start, r.end, r.url); }); // wrap しないと説明文の下のリンク行が見えない（結果列は既定で折り返さない） range.setRichTextValue(b.build()).setWrap(true); } ``` - 説明文（件数サマリー等）とリンクを同居させられる。 - **`setWrap(true)` を忘れない**。複数行になった瞬間に下の行が視界から消える。 --- ## 問題3: 過去に実行済みの行はフォールバック文字列のまま残る 修正しても、**既に実行された行のセルは書き換わらない**。埋め戻し関数を用意する。 - 出所は「生成物一覧」シート（`caseId / 種別 / タイトル / URL / 日時` を機能実行時に記録しているもの）。 - 「機能を実行する関数名 → 生成物の種別」の対応表を作り、`(キー, 種別)` で URL を引く。 - **`dryRun` を既定 true** にし、何をどう書き換える予定かを返してから適用する。 - 対象は「フォールバック文字列と完全一致する行」＋（任意で）「空セルの行」。 - **数式が入っているセルは除外する**。`getValues()` だけでは数式セルを空と誤判定するので、 同じ範囲の `getFormulas()` も読んで非空なら skip（常時リンクの `=HYPERLINK()` を壊さないため）。 - 既にリンク済みのセルは「空でも対象文字列でもない」ので自然に対象外になる。 - **状態列は書かない**。実行していないのに「✅ 完了」と出すのは嘘になる。 - 対応表に無い関数・生成物が見つからない行は**理由付きで skip して件数を返す**（黙って諦めない）。 引数で種別が変わる機能（`当日案内(相手区分)` `打診文(相手区分)` 等）は、**種別文字列をハードコードせず config オブジェクトの表示名から組み立てる**。引数はパネルの引数列（H 列）から `JSON.parse` する （try/catch で `[]` にフォールバック）。 --- ## 問題4（最大の罠）: パネル再構築でリンクが全部消える パネルを作り直す関数は結果列を**クリアする**。せっかく貼ったリンクは再構築のたびに空へ戻る。 → **再構築処理の最後に、そのパネル 1 枚分の埋め戻しを呼ぶ**。 ```js let backfilled = 0; try { if (caseId) backfilled = (Panel_backfillResultLinks({ dryRun: false, includeEmpty: true, caseId: caseId, sheetName: p.getName() // ← sheetName 必須 }) || {}).written || 0; } catch (e) { console.warn('backfill failed: ' + e); } // 失敗しても再構築自体は成功させる return { rebuilt: true, /* … */ backfilled: backfilled }; ``` ### `sheetName` を渡さないと本番が止まる（実際に止めた） 埋め戻し関数が**全パネルを走査する**実装のまま再構築フックから呼ぶと、 「再構築 1 回 × 全パネル分の read」になる。全パネル一括再構築（既定で数枚）を経由した瞬間に Apps Script の 6 分実行制限に達し、**毎分トリガの本体（コマンドキューの消化）が毎分落ちて 操作経路ごと止まる**。埋め戻し側に「このシート名だけ処理する」オプションを持たせ、 一致しないシートは `getRange('G1')` すら読まずに `return` する。 ```js var onlySheetName = String(opts.sheetName || ''); ms.getSheets().forEach(function (panel) { if (onlySheetName && panel.getName() !== onlySheetName) return; // ← 最初に弾く /* … */ }); ``` --- ## 検証（ここまでやって初めて「直った」と言える） 1. **純ロジックはローカルテストにする**。キー収集・RichText の範囲計算・対象セル判定・対応表引きは GAS API を使わない純関数に切り出し、`node --test` で回す。 「実装が決め打ちのキー列挙に戻っていないか」を**ソース文字列の assert** で固定するのが効く。 2. **セルを read-back する debug 関数を用意する**。表示文字列・数式・**リンク先 URL**・状態を返させる。 戻り値が `ok` でも、セルが本当にクリック可能かは読み返すまで分からない。 3. **実際に 1 機能を実行して**、結果セルがリンクになることを read-back で確認する。 4. **パネルを再構築して**、リンクが残る（`backfilled` が 1 以上返る）ことを確認する。 ### 検証用の debug 関数は「表示ラベルの手打ち」に依存させない パネルを特定するキーに表示ラベル（社名 + イベント名など）を使うと、**不可視文字や全角/半角の差で 完全一致に失敗する**（実際に 2 回失敗した）。ID から引いてシート自身が持つラベルを読み直す形にする。 ```js var target = ms.getSheets().filter(isPanel).filter(function (sheet) { return resolveKey(String(sheet.getRange('G2').getValue() || '')) === String(targetId); })[0]; ``` --- ## 落とし穴チェックリスト - [ ] URL キーを列挙で拾っていないか（`/url$/i` で総なめ） - [ ] 複数リンクを 1 セルに入れられるか（RichTextValue、`setWrap(true)`） - [ ] 数式セルを空と誤判定して上書きしていないか（`getFormulas()` も読む） - [ ] 実行していない機能に「完了」表示を作っていないか（状態列は書かない） - [ ] 再構築で消えないか（再構築の最後に埋め戻し） - [ ] 埋め戻しを 1 枚に限定したか（全走査だと 6 分制限でトリガが死ぬ） - [ ] skip した行の理由と件数を返しているか - [ ] セルを read-back して確認したか（戻り値だけで完了と言わない） ---