# スプレッドシートの「○印」で回している業務の滞留を検知して Discord で督促する 請求書の発行、入金の確認、書類の回収——現場が Google スプレッドシートのセルに `○` を打って管理している業務は多い。 打たれていない＝まだ終わっていない、なのに誰も気付かないまま数百日放置される。 この指示書は、**そのシートを一次ソースにしたまま**滞留を検知し、担当者へ Discord DM、責任者チャンネルへ一覧を 自動で流す仕組みの作り方をまとめたもの。Next.js + Supabase + Google Sheets API + Discord Bot を前提にしているが、 判定ロジックと落とし穴はスタックに依存しない。 実際に本番投入したときに踏んだ罠を全部書いてある。**素直に作ると誤検知だらけになって使われずに終わる**ので、 「検証」の節は飛ばさないこと。 --- ## 1. 一次ソースを決める前に、集約シートが本当に同期しているか確かめる 多くの現場には「個別シート」と「全案件を集めたマスタシート」の両方がある。マスタに `請求書発行` `入金確認` の 列があれば、1回の API コールで全件読めるので当然そちらを使いたくなる。 **しかしその列が `IMPORTRANGE` で生きているとは限らない。** 静的にコピーされた値が放置されているだけのことがある。 見分け方は `valueRenderOption: "FORMULA"` で読むこと。数式が入っていなければ静的値。 ```js const res = await sheets.spreadsheets.values.get({ spreadsheetId: MASTER_SHEET_ID, range: `'<タブ名>'!A<行>:AM<行>`, valueRenderOption: "FORMULA", // ← 数式かどうかを見る }); // 返ってきたセルが "=IMPORTRANGE(...)" でなく "○" や "" なら静的値。同期していない。 ``` **実例**: マスタの「範囲」列に `'管理画面'!A2:AE2` と参照範囲まで書いてあり、いかにも IMPORTRANGE に見えた。 だが FORMULA で読むと数式はゼロ。個別シート側が `○` になっている案件でも、マスタ側は空のままだった。 これに気付かず集計して「未発行42件」と報告し、後で実際は5件だったと訂正する羽目になった。 集約シートが信用できないと分かったら、**個別シートを一次ソースにする**。件数が多くても次節の方法で API コストは増えない。 ## 2. API コールを増やさずに列を増やす — 既存の夜間同期に相乗りする 個別シートが1000件あると「毎日1000回読むのか」という話になる。Sheets API はユーザーあたり **60読み取り/分**の制限があるので、無邪気に増やすと詰まる。 だが多くの現場では、**既に何かの夜間同期が同じシートを読んでいる**。売上集計、原価集計、なんでもいい。 その取得レンジを広げるだけでよい。 ```diff - range: `'${tab}'!E2:P13` // 従来: 売上と粗利だけ + range: `'${tab}'!D2:P13` // 発行日(D) 請求日(F) 担当(L) 発行印(N) 入金印(O) が同じ1コールに乗る ``` **API 呼び出しは1回も増えない。** これが一番効く。新しい同期ジョブを立てる前に、既存ジョブが何を 読んでいるかを必ず確認すること。 ### 列インデックスは直値で書かない レンジを1列広げた瞬間に、既存コードの `row[3]` が全部ズレる。必ず索引オブジェクトを作ってから使う。 ```js const COL = { D: 0, E: 1, F: 2, G: 3, H: 4, I: 5, J: 6, K: 7, L: 8, M: 9, N: 10, O: 11, P: 12 }; const sales = toNum(row[COL.H]); // レンジを変えても COL を直すだけで済む ``` ### `valueRenderOption` を足してはいけない（金額が変わる） 日付をシリアル値で取りたくなって `UNFORMATTED_VALUE` を付けると、**同じジョブが計算している金額の 丸めまで変わる**。 ``` FORMATTED : H="1,058,952" J="649,386" UNFORMATTED : H=1058952 J=649385.9199999999 ``` 粗利が端数分ずれて全案件で DB が書き換わり、既存ダッシュボードの数字が動く。 日付は FORMATTED でも `2026/07/31` と綺麗に取れるので、**既定のまま**にして受け側で正規化する。 ```js /** Sheets の日付文字列またはシリアル値を YYYY-MM-DD にする */ export function normalizeSheetDate(value) { if (value == null || String(value).trim() === "") return null; const text = String(value).trim(); const serial = Number(text); if (Number.isFinite(serial) && serial > 30_000 && serial < 80_000) { return new Date(Date.UTC(1899, 11, 30) + serial * 86_400_000).toISOString().slice(0, 10); } const m = text.match(/^(\d{4})[\/.\-](\d{1,2})[\/.\-](\d{1,2})/); return m ? `${m[1]}-${m[2].padStart(2, "0")}-${m[3].padStart(2, "0")}` : null; } ``` ### 値が変わったときだけ UPDATE する 新しい列を無条件に patch へ入れると、変更検知が死んで**毎晩全件 UPDATE** が走る。 `updated_at` トリガーがあると全行のタイムスタンプが動き、それを起算日に使っている滞留日数が 毎晩リセットされる（実際にこれで日数が伸びなくなった）。 ```js const patch = {}; if (current.invoice_issued_raw !== fresh.issuedRaw) patch.invoice_issued_raw = fresh.issuedRaw; // …他の列も同様に差分だけ if (Object.keys(patch).length > 0) patch.synced_at = nowIso; // 変更があった時だけ ``` ## 3. 「印」のパーサは実データの分布を見てから書く `○` だけを見る実装は必ず取りこぼす。実際の1400行の分布はこうだった。 | 種類 | 実例 | 件数 | |---|---|---| | 印 | `○` `〇`(漢数字ゼロ) `◯` `⚪︎` | 269 | | 印を含む自由文 | `"○\n請求額：残金（US$17,787.70＝2,685,942）"` `"〇（担当者に確認中？）"` | 数件 | | 対象外 | `不要` `経理確認不要` `不要　※担当確認` | 414 | | 失注コメント | `"8/3失注のため発行ストップ：<担当名>"` `"3/4失注のため発行不要:<担当名>"` | 数件 | | 壊れた値 | `#REF!` | 24 | | 空 | | 692 | **4値を返す関数にする。** `済 / 不要 / 未 / 不明` の4つ。`不明`(`#REF!`) を `未` に丸めると、 壊れたセルが全部「未対応」として督促に化ける。 ```js export function parseMark(raw) { const v = String(raw ?? "").trim(); if (v.includes("#REF!")) return "不明"; if (/[○〇◯⚪◎]/u.test(v)) return "済"; // 自由文の中に印があっても拾う if (/不要|発行ストップ|発行不要/.test(v)) return "不要"; return "未"; // 空も、人が何か書いた文字列も「未」 } ``` 判定順が重要。`#REF!` → 印 → 不要 → 未。印を先に見るのは `"○ …ただし一部不要"` のような混在を「済」に倒したいから。 ## 4. 段階は「直列」にしてはいけない 書類回収 → 請求書発行 → 入金確認、と業務は直列に進む。だから素直に書くと 「最も手前の未完了段階だけを返す」実装になる。**これが最大の落とし穴。** 書類回収の記録は今日から始まるので、既存の受注案件は全件「書類未回収」で止まる。 その結果、**本当に検知したかった「請求書未発行」「入金未確認」が1件も表面化しない**。 依頼の主目的がまるごと達成できない状態で「完成しました」と報告することになる。 ```js // ❌ 手前の1段階だけ返す export function evaluate(deal, today) { if (書類なし) return 書類の督促 || null; // ← ここで return すると後続が永久に隠れる if (請求書未発行) return 請求書の督促; ... } // ✅ 該当する段階を全部返す export function evaluate(deal, today) { const items = []; if (書類なし && ...) push(書類の督促); if (issued === "未") push(請求書の督促); if (issued === "済" && paid === "未") push(入金の督促); return items; // 0〜3件 } ``` `#REF!` の扱いも段階ごとに分ける。請求書欄が壊れているだけなら、書類回収の督促は普通に出す。 ## 5. 「同期できていない」と「シート上で空欄」を区別する これも実データで初めて出た。同期ジョブが**同じシートURLを共有する複数レコードのうち代表1件にしか 書き込まない**設計だと、非代表のレコードは新しい列が `null` のまま残る。 `parseMark(null)` は `"未"` を返すので、**それが全部「未対応」に化ける**。 実際、督促64件のうち63件がこれだった。滞留日数の上位は同じ案件が2件ずつ並ぶ形で、 523日・502日・479日……と、いかにも異常な数字が出ていた。 ```js // 同期タイムスタンプが無い＝シートを一度も読めていない。印の有無は「不明」であって「未」ではない。 if (!deal.synced_at) { // 請求書・入金の段階は判定しない。代わりに「未同期」として別枠で可視化する。 } ``` これを直したら 64件 → 5件 になった。**「null は未対応」と暗黙に扱っている箇所を全部洗い出すこと。** ## 6. 新機能の初日に数百件のフラッドを起こさない 新しく記録を取り始める項目（この例では書類の受領日）は、過去のデータが全部「未記録」になる。 そのまま通知を回すと、初日に数百行が Discord に流れて誰も読まなくなる。 **開始日ガードを入れる。** ```js export const APPLICATION_SINCE = process.env.DUNNING_APPLICATION_SINCE ?? "<運用開始日 YYYY-MM-DD>"; // 受注日がこの日より前の案件は、この段階の督促対象にしない if (wonAt && wonAt >= APPLICATION_SINCE) { /* 判定する */ } ``` 既存データから拾える段階（シートの印を見る段階）にはこのガードを**掛けない**。そちらは 積み残しこそ見たいので。 同じ理由で、**エスカレーション通知には上限を付ける**。しきい値の2倍超で管理者に飛ばす設計にすると、 数百日の積み残しがある現場では初日にほぼ全件がエスカレーションになる。 ```js const LIMIT = Number(process.env.DUNNING_ESCALATION_LIMIT ?? 20); const shown = escalated.slice(0, LIMIT); // 末尾に「…ほか N 件（全件: <一覧ページURL>）」を付ける ``` ただし**通知履歴テーブルへの記録は上限で切らない**。切ると翌日また「未送信」として湧いてくる。 ## 7. 通知先 — 退職者に督促を送らない 名簿テーブルの `is_active` は、退職しても true のまま放置されていることがある。 「退職者を確認するスクリプト」が別に存在するのに、その結果が名簿へ反映されていない、という状態はよくある。 社員名 → Discord ユーザーID のマッピングを作るとき、**退職者を機械的に混ぜてしまう**と 督促 DM が退職者に届く。 - 名簿の `is_active = true` で絞る - それでも残る既知の退職者は、マッピングを埋める側で明示的に除外する - 名前の突合は全角/半角スペースを除去してから比較する（`山田太郎` と `山田　太郎` を一致させる） ```js const normalize = (s) => s.replace(/[\s　]/g, ""); const ids = new Map(staff.filter(s => s.is_active).map(s => [normalize(s.name), s.discord_user_id])); ``` ### Discord への送信 DM は「DMチャンネルを作ってから投稿」の2ステップ。 ```js const dm = await fetch("https://discord.com/api/v10/users/@me/channels", { method: "POST", headers: { Authorization: `Bot ${token}`, "Content-Type": "application/json" }, body: JSON.stringify({ recipient_id: userId }), }); const { id } = await dm.json(); // この id に対して通常のチャンネル投稿と同じ POST /channels/{id}/messages ``` メッセージ上限は2000文字。**切り捨てではなく分割する**。一覧が途中で消えたら通知の意味がない。 そして文面には必ず**「何をすれば消えるか」を1行入れる**。 ``` ① 請求書 未発行 — 7営業日経過 ・<顧客名> / <案件名> → 見積シートの N2 に ○ を入れると消えます: <シートURL> ``` ## 8. 重複抑止と、担当者不明の受け皿 - 通知履歴テーブルに `(対象ID, 段階, 送信日)` のユニーク制約を張り、**同じ対象＋段階は数日に1回**に絞る。 毎日同じ DM が来ると人は読まなくなる。ただしエスカレーションに切り替わった日は必ず送る。 - **担当者が特定できない案件を握りつぶさない。** 実運用では担当者データが入っていないことのほうが多い （この案件では受注228件中、担当が判明したのは32件だった）。 「担当未設定」セクションを作って責任者チャンネルの一覧に必ず出す。ここが実際の主戦場になる。 ## 9. 検証 — 実データを通すまで完成ではない 型検査もテストもビルドも通り、コードは全部揃っている。それでも**実データを流すまで、この種の機能は 必ず誤検知を抱えている**。上に書いた罠のうち、5番と6番は実データを流して初めて出た。 やること: 1. マイグレーションを本番 DB に適用し、同期を1回走らせて列を埋める 2. **手で中身を確認したシートを2〜3件選び**、判定結果と突き合わせる （例: シート上 `N2=○` `O2=空` の案件が「入金未確認」で出るか） 3. 段階別・担当別の件数を出し、**桁がおかしくないか**を見る（64件は多すぎる、と気付けるかどうか） 4. 通知を実際に送らない `?dryRun=1` を用意して本番エンドポイントで叩く ```js const dryRun = request.nextUrl.searchParams.get("dryRun") === "1"; // …判定はする、Discord には送らない、結果 JSON だけ返す return NextResponse.json({ evaluated, items, dmSent, dmFailed, channelPosted, broken }); ``` `dryRun` は作っておくと運用に入ってからも効く。しきい値を変えたときの影響を送信せず確認できる。 ## 10. デプロイ時の落とし穴（git worktree） CLI デプロイ運用の場合、**git worktree には接続情報（`.vercel/` 等）が無い**。そのまま `vercel --prod` するとディレクトリ名で**新しいプロジェクトが作られる**。本番へは出ない。 worktree で作業したらデプロイ前に接続情報をコピーする。 ```bash cp -r ../<メインのチェックアウト>/.vercel . ``` デプロイ後は URL とプロジェクト名を必ず目視すること。 --- ## まとめ — 順番 1. 集約シートが本当に同期しているか FORMULA で確認する（していないことが多い） 2. 既存の夜間同期のレンジを広げて相乗りする（API 増ゼロ、`valueRenderOption` は足さない） 3. 印のパーサは実データの分布を見てから4値で書く 4. 段階は独立判定にする（直列にすると主目的が隠れる） 5. 「未同期」と「空欄」を区別する 6. 新項目には開始日ガード、エスカレーションには件数上限 7. 通知先は在籍者に絞る 8. 実データを流し、手で確認したシートと突き合わせるまで完成と言わない ---