# 複数PCの点検結果を「人間の手入力列を壊さず」スプレッドシートへ自動反映する 社内の各PC（あるいは各サーバ・各拠点）で走らせている点検スクリプトの結果を、 **人間が手で管理している台帳スプレッドシート**へ自動で流し込む仕組みの作り方。 前半が人間の手入力列、後半が自動更新列という「よくある台帳」を壊さずに自動化するための 設計判断と、実際に踏んだ落とし穴をそのまま載せる。 ## 前提と完成状態 - 台帳シートは既に人間が運用中。左側の列（担当者名・申告した機器名・メモなど）は**手入力**。 - 右側の列（検知したホスト名・最終報告時刻・各種指標）を**自動更新**したい。 - 各クライアント（PC）に認証情報を配りたくない。 - 完成後: 各クライアントは日次で HTTPS POST を1回投げるだけ。台帳の自動列だけが更新され、 手入力列と人間が書いたコメントは一切変わらない。同じクライアントが何回投げても行は増えない。 ## アーキテクチャ: Apps Script Web App を受け口にする ``` 各クライアント (Node スクリプト) │ HTTPS POST { token, label, ... } ▼ Apps Script Web App (doPost) ← 実行は「デプロイしたユーザー」= 台帳の所有者 │ SpreadsheetApp ▼ 台帳スプレッドシート ``` **なぜこの形か**: Sheets API を各クライアントから直接叩くと、全クライアントに認証情報 （サービスアカウント鍵や OAuth トークン）を配る必要が出る。Web App にすると **書き込み権限がスクリプト側（＝台帳所有者の権限）に閉じ**、クライアントは共有シークレット1本だけ持てばよい。 **バインドスクリプトにしない**: 既存シートにバインドしてスコープを追加すると、 そのシートの simple トリガー（`onOpen` など）が動かなくなりカスタムメニューが消える事故がある。 台帳が既に運用中なら **standalone スクリプト + `SpreadsheetApp.openById()`** にする。 ## 設計判断①: 突合キーは「機械が生成した値」にする（最重要） 台帳の「人間が申告した機器名」列で行を探したくなるが、**これは一意でない**。 実際に「同じ申告名の行が2行あり、検知ホスト名は別物」という状態になっていた。 この列をキーにすると **2台目が1台目の行を上書きして、データが静かに消える**。 ```javascript // 第1キー: 機械生成のラベル（クライアントが自分で名乗る固定値）が入っている列で完全一致 let index = label ? rows.findIndex((row) => row[col.hostname] === label) : -1; // 第2キー: まだどのクライアントにも紐付いていない行だけを拾う // （申告名が一致し、かつ検知列が空 = 未使用の行） if (index < 0 && mappedName) { index = rows.findIndex((row) => row[col.selfName] === mappedName && !row[col.hostname]); } // どちらでも見つからなければ最下部に追記する。推測でマッチさせない。 ``` **「申告名が一致するが、検知列に別のラベルが入っている行」は絶対に触らない。** それは他のクライアントの行なので、奪うと相手のデータを失う。 紐付けられなかった行は「未マッピング」と明示して追記し、対応表を人間に埋めてもらう。 **推測でマッチさせる実装は、間違った行を上書きする方が有害。** さらに `label` が空文字のときに `findIndex` させると、**検知列が空の行に軒並み一致して行を奪う**。 空なら探索そのものをしない（上のコードの `label ?` がこれ）。 ## 設計判断②: 人間が書いたコメント列は「条件付き」でしか書かない 台帳の最終列のような「所見」欄には、人間が手で書いた文章が入っていることが多い。 そこへ機械的な値（「一致」など）を書くと、**復元できない情報が消える**。 ```javascript // 既存行に紐付けられたなら、対応表に登録が無くても「紐付いている」。 // 人間のメモがある列なので触らない。新規追記した時だけ警告を立てる。 const appended = index < 0; if (appended) values[col.remarks] = '未マッピング(対応表に追記が必要)'; ``` `対応表に登録があるか` を条件にすると、対応表が空の初期状態で**全行のコメントを潰す**。 条件は「新規追記かどうか」にする。 ## 設計判断③: タブ名を推測しない・列レターをハードコードしない - **ファイル名をタブ名として渡すと `getSheetByName()` は `null` を返し、次の行で落ちる。** ファイル名とタブ名は別物。タブ名が分からないなら **1行目に必須ヘッダが揃っているタブを探す**。 - 解決したタブ名は Script Property にキャッシュし、次回はそれを優先（毎回全タブ走査しない）。 キャッシュ先が消えていたら再走査する。 - 列は**ヘッダ文字列で解決**する。列レター（`F` 列など）をハードコードすると、 人間が列を1本挿入した瞬間に全部ずれる。 ```javascript function findSheet(spreadsheet, requiredHeaders, props) { const cached = props.getProperty('SHEET_TAB_NAME'); if (cached) { const s = spreadsheet.getSheetByName(cached); if (s) return s; } for (const sheet of spreadsheet.getSheets()) { const lastCol = sheet.getLastColumn(); if (lastCol < requiredHeaders.length) continue; const headers = sheet.getRange(1, 1, 1, lastCol).getDisplayValues()[0]; if (requiredHeaders.every((h) => headers.some((x) => norm(x) === norm(h)))) { props.setProperty('SHEET_TAB_NAME', sheet.getName()); return sheet; } } throw new Error('target tab not found'); // 黙って別タブに書くより落ちる方が安全 } ``` **ヘッダ照合は必ず正規化する。** 実セルの表記は目視できないため、全角/半角の括弧や 前後の空白の違いで「見つからない」と誤判定する。`NFKC` 正規化 + 空白除去で吸収する。 ```javascript const norm = (v) => String(v ?? '').normalize('NFKC').replace(/\s+/g, '').toLowerCase(); ``` ## 設計判断④: 並行 POST に備えてロックする クライアントが N 台あると、`getLastRow()` → 追記 の間に別の POST が割り込んで行が重複する。 ```javascript function upsert(payload) { const lock = LockService.getScriptLock(); if (!lock.tryLock(20000)) return { ok: false, status: 503, error: 'busy' }; try { // 読み取り〜書き込みを全部この中に収める } finally { lock.releaseLock(); } } ``` ## 設計判断⑤: 自動列だけを個別に書く `setValues()` で行全体を書くと手入力列も上書きする。自動列だけを個別に `setValue()` する （呼び出し回数は増えるが、日次1回なら問題にならない）。 ```javascript Object.keys(plan.values).forEach((colIndex) => { sheet.getRange(targetRow, Number(colIndex) + 1).setValue(plan.values[colIndex]); }); ``` ## 送信側の作り方 **値を再計算しない。** 既に別のスクリプトが計算して書き出している状態ファイル（JSON）を読むだけにする。 点検スクリプトが「レポートを作るときに計算済みの値」を状態ファイルへ書き出すよう1行足すのが正解で、 送信側で計算を二重に持たない。 ```javascript const state = readJson(path.join(stateDir, 'metrics-state.json')); // 既に計算済み const payload = { token: env.TARGET_TOKEN, label, // このクライアントの固定ラベル（検知列に入る） mappedName, // 対応表で解決した申告名。未登録なら null（推測しない） reportedAt: toLocalTime(state.at), ... }; ``` ### 落とし穴: 未配布のクライアントで日次処理を壊さない 設定ファイルが無いクライアントでは**無言で `exit 0`** にする。 エラーで落ちると、同じ日次バッチの後続処理（通知など）が全部止まる。 ```javascript if (!env.TARGET_URL || !env.TARGET_TOKEN) return; // 未配布なら何もしない ``` ### 落とし穴: `--dry-run` でトークンを標準出力に出さない デバッグ用の `--dry-run` が payload をそのまま `console.log` すると、 **共有シークレットが端末履歴・CI ログに残る**。状態だけ見せる。 ```javascript if (dryRun) { const shown = payload.token ? `<設定あり:${payload.token.length}文字>` : '<未設定>'; console.log(JSON.stringify({ ...payload, token: shown }, null, 2)); return; } ``` ### 落とし穴: 「データが無い」を「問題なし」と書かない コンプライアンス系の列（禁止された機能を使っていないか等）で、 状態ファイルがまだ無いときに `false → "未検出"` と書くと**誤った合格判定**になる。 `判定不能` と `未検出` を区別する。 ```javascript const known = state.flagged !== undefined; // そもそも判定できたのか const flagged = Boolean(state.flagged); const cell = flagged ? '検出' : known ? '未検出' : '判定不能'; ``` これは「外部プローブがタイムアウトしたのを『未導入』と報告して、 実は導入済みの相手に手作業を依頼してしまう」のと同じ誤りで、同じコードベースで両方踏んだ。 **判定できなかったことを、合格や不在と同一視しない。** ## 時刻の扱い 「現地時刻」と書かれた列に UTC の ISO 文字列（`2026-01-02T03:04:05.678Z`）を入れると 列名と食い違い、人間が入れた既存表記とも混ざる。列の意味に合わせて整形する。 ```javascript function toLocalTime(value, offsetHours) { const d = value ? new Date(value) : new Date(); if (Number.isNaN(d.getTime())) return ''; const t = new Date(d.getTime() + offsetHours * 3600000); const p = (n) => String(n).padStart(2, '0'); return `${t.getUTCFullYear()}-${p(t.getUTCMonth() + 1)}-${p(t.getUTCDate())} ${p(t.getUTCHours())}:${p(t.getUTCMinutes())}`; } ``` 比率も、既存行が `0%` 表記ならパーセント文字列にする（人が読む列で表記を混ぜない）。 ## 共有シークレットの配布 Web App は無認証で公開せず、共有シークレットを検証する。 ```javascript const expected = PropertiesService.getScriptProperties().getProperty('TARGET_TOKEN'); if (!expected || payload.token !== expected) { return jsonOut({ ok: false, status: 401, error: 'unauthorized' }); } ``` **シークレットは呼び出し側で生成して流し込む方向にする。** スクリプト側で生成すると、 それを外に取り出す経路（結果ファイルなど）が必要になり露出面が増える。 ホワイトリスト方式のコマンドキュー（後述）で `setToken` を1回実行すれば、 コマンドファイルは読み込み直後に破棄されるので痕跡が残らない。 ### 落とし穴: 「既存ファイルには書かない」配布の仕組み 配布スクリプトが `fs.writeFileSync(dest, body, { flag: 'wx' })` のように **既存ファイルを保護する実装**になっていることがある（事故防止として正しい）。 その場合、**全クライアントに既に存在するファイルへ新しい値を足しても永久に届かない**。エラーも出ない。 新しい値は必ず**新しいファイル名**で配る。 ### 落とし穴: 暗号化された環境変数は読み戻せない ホスティングサービスの「暗号化された環境変数」は、CLI で pull しても**値が空で返る**ことがある。 in-place 更新の手段が無い場合、「既存値を読んで merge して削除→再作成」は成立せず、 実行すると**配布物を丸ごと失う**。 対策は**追加専用の設計**にすること。`CONFIG_EXTRA`, `CONFIG_EXTRA2`, `CONFIG_EXTRA3` … を 全部 merge する実装にしておけば、追加のたびに新しい変数を足すだけで済み、既存に触らない。 ```javascript for (const name of Object.keys(process.env).filter((k) => k.startsWith('CONFIG_EXTRA')).sort()) { const raw = process.env[name]; if (!raw) continue; let extra; try { extra = JSON.parse(raw); } catch { console.log(`${name} is not valid JSON, skipped`); continue; } if (extra && typeof extra === 'object' && !Array.isArray(extra)) Object.assign(files, extra); } ``` 1本の書き間違いで配布全体を落とさないよう、壊れた JSON はスキップしてログに残す。 ## Apps Script 側の運用（作るときの順序に注意） - **`clasp create` は既存の `appsscript.json` を既定値で丸ごと上書きする。** スコープや webapp 設定を書いたファイルを先に置いてから `clasp create` すると消える （出力は `Cloned one file..` の1行だけ。未追跡ファイルなら復元もできない）。 → `clasp create` を**先に**実行し、そのあとで `appsscript.json` を書いて `clasp push -f`。 - Web App の再デプロイは `clasp deploy --deploymentId <既存ID>` にする。 新規 deploy を作ると**URL が変わり、配布済みのクライアント全台を貼り替える羽目になる**。 - 運用コマンドは Drive のコマンドキュー方式（`cmd_*.json` を置く → 1分トリガーで処理 → `result_*.txt` を書き出す）で回す。**ホワイトリストの明示 map にして `eval` を使わない**。 コマンドファイルは**読み込み直後に破棄**（再実行・二重処理の防止）。 `result_*.txt` を必ず書く（書かないと実行結果を検証できず「たぶん動いた」で終わる）。 ## 検証（ここまでやって初めて「できた」と言う） 1. **書き込み前に手入力列のスナップショットを取る**（読み取りAPIで丸ごと保存）。 2. 実際に1件 POST する。 3. 読み戻して確認する: - 手入力列が**全行**スナップショットと一致するか - 人間が書いたコメント列が保持されているか - **同じクライアントから2回投げて行数が増えないか**（upsert が効いているか） - 時刻・比率の表記が列の意味と合っているか 4. 配布経路も**実際に叩いて**確認する。配布物の一覧を取得し、 **件数が減っていないこと**を見る（値は出力しない）。 「デプロイした」「テストが通った」は検証ではない。**実物を読み戻すまでやる。** ## まとめ: この仕組みで一番危ないのは何か コードのバグではなく、**人間が育てた台帳を機械が静かに壊すこと**。 だから守るのは次の3つだけ。 1. 突合キーは機械が生成した一意な値にする。人間の入力列をキーにしない。 2. 人間が書いた列は、条件を満たすときだけ書く。既定は「触らない」。 3. 判定できなかったときは「合格」や「不在」と書かず「判定不能」と書く。 ---