# 本番スプレッドシートの既存セルへ一括書き込みする前に、現況を全件実測して上書き規則を決める型（GAS） 「登録したらこのセルに自動で値を入れておいて」という依頼は、たいてい**そのセルが空である前提**で降ってくる。 だが本番シートのセルはほぼ確実に空ではない。見出し、マニュアルへのリンク、前任者の手書きメモが入っている。 そこへ `setValue` を流すと、全レコード分を一撃で潰す。 この指示書は、その事故を起こさずに一括書き込み機能を本番投入するための手順を定める。 ## 結論（先に守るべき3つ） 1. **書き込みロジックより先に、判定ロジックを書いて dryRun で全件の現況をダンプする。** 仕様を決めるのはその後。 2. **上書き規則は「空なら書く」ではなく「壊してはいけないものを名指しで守る」形で書く。** 残りは上書き可としてよい。 3. **1件ごとに read-back verify（flush → getValue → 一致確認）を通す。** `setValue` は結合セル・保護範囲・入力規則違反で**黙って無視される**。 --- ## ステップ1: 判定を純関数に切り出す 書き込み可否の判断を、シートに触らない純関数にする。ここがテスト可能な唯一の場所になる。 ```js /** セルの現在値から、書き込み可否を決める */ function _decideWrite(currentValue, currentFormula) { const value = String(currentValue == null ? '' : currentValue); const formula = String(currentFormula == null ? '' : currentFormula); // 壊してはいけないもの = 既に「本物の値」が入っている場合 const existing = _extractTarget(value) || _extractTarget(formula); if (existing) return { action: 'reuse', value: existing }; // それ以外（空・見出し・マニュアルリンク）は上書き可 return { action: 'write', previous: (value || formula).slice(0, 80) }; } ``` ポイント: - **値と数式の両方を見る。** 見出しは `=HYPERLINK("<マニュアルURL>","■項目名")` の形で入っていることが多く、 `getValue()` だけでは表示テキストしか取れずリンク先を見落とす。 - `previous` に元の値を残す。あとで「何を潰したか」を報告できる。 ## ステップ2: dryRun を先に流して現況を実測する 書き込み処理を1行も書く前に、**判定結果だけを全件返すモード**を作って本番データに当てる。 ```js function backfillAll(opts) { opts = opts || {}; const all = listAllRecords(); const offset = Math.max(0, Number(opts.offset) || 0); const limit = Math.max(1, Number(opts.limit) || 30); const targets = all.slice(offset, offset + limit); const result = { ok: true, total: targets.length, created: 0, reused: 0, replaced: [], skipped: [], failed: [] }; targets.forEach(function (rec) { try { const one = ensureOne(rec.id, { dryRun: opts.dryRun === true }); if (!one.ok) result.skipped.push({ id: rec.id, reason: one.reason, current: String(one.current || '').slice(0, 80) }); else if (one.reused) result.reused++; else { if (!opts.dryRun) result.created++; if (one.previous) result.replaced.push({ id: rec.id, previous: one.previous }); } } catch (e) { result.failed.push({ id: rec.id, error: String(e && e.message || e) }); } }); result.remaining = Math.max(0, all.length - offset - targets.length); if (result.remaining) result.nextOffset = offset + targets.length; return result; } ``` **この dryRun の出力が仕様書になる。** 想像で「たぶん空」と決めない。 実例（筆者のケース・38レコード）: | 現況 | 件数 | 判明したこと | |---|---|---| | 見出し文字列（マニュアルへのリンク付き） | 32 | 上書きしてよい ＝ 依頼者に確認して確定 | | 既に本物の値が入っている | 6 | **絶対に上書きしない**（共有済みリソースを殺す） | | 空 | 0 | ← 当初の想定はこれが38件だった | 最初の実装で「空でなければ skip」というガードを入れていたため、32件の巻き添えを回避できた。 ガードが無ければ全滅していた。 ## ステップ3: 実測結果を依頼者に見せて上書き規則を確定する dryRun の出力（`current` の実物）をそのまま依頼者に見せる。 「32件に `■項目名` というラベルが入っています。これは何ですか」と聞けば、 「マニュアルのリンクなので上書きしてよい」と1往復で確定する。 **推測で決めない。** ここを飛ばすと、善意の上書きが業務データの消失になる。 ## ステップ4: read-back verify を必ず通す ```js range.setValue(newValue); SpreadsheetApp.flush(); if (String(sheet.getRange(CELL).getValue()) !== newValue) { throw new Error('read-back verify に失敗しました'); } ``` `setValue` は例外を投げずに失敗する経路がある（結合セルの非 top-left / 保護範囲 / データ入力規則違反）。 `flush()` を挟まないと読み戻しが古い値を返すことがある。 ## ステップ5: 6分の実行上限を offset/limit で割る GAS の実行は6分で打ち切られる。1レコードあたり「別スプレッドシートを開く＋外部API」が入ると **20件で約5分**かかる。`offset`/`limit` と `remaining`/`nextOffset` を戻り値に持たせ、 複数バッチに分けて呼ぶ。途中で落ちても再開できる。 1件の失敗で全体を止めない（`failed` に積んで続行）。全件処理してから失敗一覧を見るほうが早い。 ## ステップ6: 本処理への組み込みは「非致命的」に 新規登録フローの最後に呼ぶときは、必ず try/catch で包む。 ```js let extra = null; try { extra = ensureOne(newId); } catch (e) { extra = { ok: false, reason: String(e && e.message || e) }; } return { ok: true, id: newId, /* ... */ extra: extra }; ``` **付帯機能の失敗で本体の登録を失敗させてはいけない。** 登録は成功、付帯は後追いで直せる形にする。 --- ## 付録: 会議URL（Google Meet）を案件ごとに発行する場合 「レコードごとの専用ミーティングURL」を自動発行したい場合の選択: | 方式 | 追加セットアップ | 判定 | |---|---|---| | Calendar Advanced Service でイベントを作り `hangoutLink` を取る | `appsscript.json` に1ブロック足すだけ | **採用** | | Meet REST API v2 `spaces.create` | 標準 GCP プロジェクトへの切替＋API有効化＋**利用者全員の再認可** | 不採用 | ```js const created = Calendar.Events.insert({ summary: title, start: { dateTime: startIso, timeZone: '<タイムゾーン>' }, end: { dateTime: endIso, timeZone: '<タイムゾーン>' }, transparency: 'transparent', // 予定あり扱いにせずカレンダーを埋めない reminders: { useDefault: false, overrides: [] }, conferenceData: { createRequest: { requestId: 'rec-' + recordId + '-' + Date.now(), conferenceSolutionKey: { type: 'hangoutsMeet' } } } }, calendarId, { conferenceDataVersion: 1 }); let url = String(created.hangoutLink || ''); for (let i = 0; !url && i < 3; i++) { // 会議の作成は非同期で pending になりうる Utilities.sleep(2000); url = String(Calendar.Events.get(calendarId, created.id).hangoutLink || ''); } if (!url) { Calendar.Events.remove(calendarId, created.id); // ゴミイベントを残さない throw new Error('会議URLを取得できませんでした'); } ``` `appsscript.json`: ```json { "dependencies": { "enabledAdvancedServices": [ { "userSymbol": "Calendar", "serviceId": "calendar", "version": "v3" } ] }, "oauthScopes": ["...既存は消さない...", "https://www.googleapis.com/auth/calendar"] } ``` ### スコープ追加時の挙動（実測） `oauthScopes` に新スコープを足して push しても、**既存の time-based トリガーは止まらない**。 止まるのは「その新スコープを実際に使う呼び出し」だけで、そこだけが例外で落ちる。 つまり **push は安全**。手順としては: 1. push する 2. 新スコープを使わない dryRun を1回流し、トリガーが生きていることを確認する 3. 新スコープを使う処理を**1件だけ**試し、認可エラーの実物を取る 4. その実物を根拠に、利用者へ再認可を依頼する 「たぶん再認可が要る」と先回りで頼まない。実測エラーを見せてから頼む。 ### 再認可の導線は「バインド先シートのメニュー項目」が最短 スクリプトエディタを開かせるより、シートのカスタムメニューに承認専用の項目を1つ足すほうが、 非エンジニアには圧倒的に楽になる。`SpreadsheetApp.getUi()` を使う関数をメニューに登録すれば承認ダイアログが出る。 ```js function authorizeOnce() { const ui = SpreadsheetApp.getUi(); try { Calendar.CalendarList.get('primary'); // スコープを実際に踏むだけ ui.alert('連携', '承認が完了しました。このウィンドウは閉じて大丈夫です。', ui.ButtonSet.OK); } catch (e) { ui.alert('連携', '承認できませんでした:\n' + String(e && e.message || e), ui.ButtonSet.OK); } } ``` --- ## よくある失敗 | 失敗 | 実際に起きること | 回避 | |---|---|---| | 「このセルは空のはず」で `setValue` を全件に流す | 全レコードの既存データが消える | ステップ2の dryRun を必ず先に | | `getValue()` だけで空判定する | `=HYPERLINK(...)` の見出しを見落とす | `getFormula()` も見る | | read-back verify を省く | 結合セル・保護範囲で黙って失敗し、成功報告だけが残る | ステップ4 | | 全件を1回の実行で回す | 6分で打ち切られ、どこまで書けたか分からなくなる | offset/limit + `remaining` | | 1件の失敗で throw する | 残り全部が未処理になる | `failed` に積んで続行 | | 付帯機能を本体の中で throw させる | 付帯の失敗で本体の登録まで失敗する | 非致命的に try/catch | | 権限エラーを「たぶん共有されてない」で片付ける | 実際は「読めるが書けない」だった等、対処がずれる | dryRun（読みのみ）と本実行（書き）を分けて切り分ける | ## 適用できないケース - 対象セルが数式で他シートから引かれている場合（`setValue` で数式が消える）。先に `getFormula()` で確認する。 - レコード数が数百を超える場合。offset/limit の分割回数が増えるので、トリガーで自動継続する仕組みを別途足す。 ---