スプレッドシートの「○印」で回している業務の滞留を検知して Discord で督促する

請求書の発行、入金の確認、書類の回収——現場が Google スプレッドシートのセルに ○ を打って管理している業務は多い。 打たれていない＝まだ終わっていない、なのに誰も気付かないまま数百日放置される。

この指示書は、そのシートを一次ソースにしたまま滞留を検知し、担当者へ Discord DM、責任者チャンネルへ一覧を 自動で流す仕組みの作り方をまとめたもの。Next.js + Supabase + Google Sheets API + Discord Bot を前提にしているが、 判定ロジックと落とし穴はスタックに依存しない。

実際に本番投入したときに踏んだ罠を全部書いてある。素直に作ると誤検知だらけになって使われずに終わるので、 「検証」の節は飛ばさないこと。

---

1. 一次ソースを決める前に、集約シートが本当に同期しているか確かめる

多くの現場には「個別シート」と「全案件を集めたマスタシート」の両方がある。マスタに 請求書発行 入金確認 の 列があれば、1回の API コールで全件読めるので当然そちらを使いたくなる。

しかしその列が IMPORTRANGE で生きているとは限らない。 静的にコピーされた値が放置されているだけのことがある。

見分け方は valueRenderOption: "FORMULA" で読むこと。数式が入っていなければ静的値。

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読み取り/分の制限があるので、無邪気に増やすと詰まる。

だが多くの現場では、既に何かの夜間同期が同じシートを読んでいる。売上集計、原価集計、なんでもいい。 その取得レンジを広げるだけでよい。

- range: `'${tab}'!E2:P13`   // 従来: 売上と粗利だけ
+ range: `'${tab}'!D2:P13`   // 発行日(D) 請求日(F) 担当(L) 発行印(N) 入金印(O) が同じ1コールに乗る

API 呼び出しは1回も増えない。 これが一番効く。新しい同期ジョブを立てる前に、既存ジョブが何を 読んでいるかを必ず確認すること。

列インデックスは直値で書かない

レンジを1列広げた瞬間に、既存コードの row[3] が全部ズレる。必ず索引オブジェクトを作ってから使う。

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 と綺麗に取れるので、既定のままにして受け側で正規化する。

/** 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 トリガーがあると全行のタイムスタンプが動き、それを起算日に使っている滞留日数が 毎晩リセットされる（実際にこれで日数が伸びなくなった）。

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!) を 未 に丸めると、 壊れたセルが全部「未対応」として督促に化ける。

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件も表面化しない。 依頼の主目的がまるごと達成できない状態で「完成しました」と報告することになる。

// ❌ 手前の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日……と、いかにも異常な数字が出ていた。

// 同期タイムスタンプが無い＝シートを一度も読めていない。印の有無は「不明」であって「未」ではない。
if (!deal.synced_at) {
  // 請求書・入金の段階は判定しない。代わりに「未同期」として別枠で可視化する。
}

これを直したら 64件 → 5件 になった。「null は未対応」と暗黙に扱っている箇所を全部洗い出すこと。

6. 新機能の初日に数百件のフラッドを起こさない

新しく記録を取り始める項目（この例では書類の受領日）は、過去のデータが全部「未記録」になる。 そのまま通知を回すと、初日に数百行が Discord に流れて誰も読まなくなる。

開始日ガードを入れる。

export const APPLICATION_SINCE = process.env.DUNNING_APPLICATION_SINCE ?? "<運用開始日 YYYY-MM-DD>";
// 受注日がこの日より前の案件は、この段階の督促対象にしない
if (wonAt && wonAt >= APPLICATION_SINCE) { /* 判定する */ }

既存データから拾える段階（シートの印を見る段階）にはこのガードを掛けない。そちらは 積み残しこそ見たいので。

同じ理由で、エスカレーション通知には上限を付ける。しきい値の2倍超で管理者に飛ばす設計にすると、 数百日の積み残しがある現場では初日にほぼ全件がエスカレーションになる。

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 で絞る
• それでも残る既知の退職者は、マッピングを埋める側で明示的に除外する
• 名前の突合は全角/半角スペースを除去してから比較する（山田太郎 と 山田　太郎 を一致させる）

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ステップ。

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 を用意して本番エンドポイントで叩く

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 で作業したらデプロイ前に接続情報をコピーする。

cp -r ../<メインのチェックアウト>/.vercel .

デプロイ後は URL とプロジェクト名を必ず目視すること。

---

まとめ — 順番

1. 集約シートが本当に同期しているか FORMULA で確認する（していないことが多い）
2. 既存の夜間同期のレンジを広げて相乗りする（API 増ゼロ、valueRenderOption は足さない）
3. 印のパーサは実データの分布を見てから4値で書く
4. 段階は独立判定にする（直列にすると主目的が隠れる）
5. 「未同期」と「空欄」を区別する
6. 新項目には開始日ガード、エスカレーションには件数上限
7. 通知先は在籍者に絞る
8. 実データを流し、手で確認したシートと突き合わせるまで完成と言わない