スプレッドシート「対応済みチェックボックス」＋期限超過の担当者別Discord催促

適用場面

既存のGoogleスプレッドシートに「誰かが後で対応すべき行」が日々追加され続ける業務（問い合わせ対応・架電結果・申請処理など）で、 「一定時間（例: 24時間）経ってもチェックが入らない行」を検知し、担当者にDiscordで催促したい場合。 すでに毎日決まった時刻に動いている自動処理（日次レポート等のcron）があるなら、そこに相乗りすると新規cronが不要になる。

全体設計

1. 対象シートに「対応済み」列を1つ追加（チェックボックス、Sheets APIのsetDataValidation + BOOLEAN条件）
2. 既存行は導入時に一括で「対応済み=TRUE」にする（重要）。これをやらないと、導入前の全履歴が一斉に「未対応」判定されて大量催促が飛ぶ事故になる。導入以降に新規追加される行だけがチェック欄が空＝真の監視対象になる。
3. 催促ロジックは状態を持たない純関数にする: 「その時点でチェックが未入力 かつ 処理日時から一定時間経過 かつ 担当者が空でない」行を毎回ゼロから抽出する。催促済みフラグ等の追加状態は不要——チェックが入るまで、次の実行のたびに自動的に対象であり続ける（＝繰り返し催促が状態管理なしで実現できる）。
4. 既存の定時cron（日次レポート等）に処理を追加する形にし、新しいcronジョブを作らない。追加処理は既存処理と独立したtry/catchで囲み、失敗しても既存の日次レポート等を巻き込まない。
5. 担当者ごとにグルーピングして1メッセージにまとめる（同じ担当者に複数の未対応行があっても1回のメンションで済ませる。Discord Webhookのレート制限対策にもなる）。
6. 未対応が0件の日は通知を送らない。

実装の骨子（TypeScript / Google Sheets API + Discord Webhook の例。他言語・他チャットツールでも考え方は同じ）

// 1. 対応済み列のセットアップ（初回のみ実行される自己修復関数）
async function ensureResponseCheckbox(sheets, spreadsheetId, sheetName, headerCell, checkboxColIndex0based) {
  // ヘッダーセルが既に埋まっていれば「セットアップ済み」として即return（冪等性）
  const header = await sheets.spreadsheets.values.get({ spreadsheetId, range: `'${sheetName}'!${headerCell}` })
  if (header.data.values?.[0]?.[0]) return

  await sheets.spreadsheets.values.update({
    spreadsheetId, range: `'${sheetName}'!${headerCell}`,
    valueInputOption: 'RAW', requestBody: { values: [['対応済み']] },
  })

  const meta = await sheets.spreadsheets.get({ spreadsheetId })
  const sheetId = meta.data.sheets.find(s => s.properties.title === sheetName)?.properties.sheetId

  // チェックボックスのデータ検証を設定
  await sheets.spreadsheets.batchUpdate({
    spreadsheetId,
    requestBody: { requests: [{
      setDataValidation: {
        range: { sheetId, startRowIndex: 1, endRowIndex: 10000, startColumnIndex: checkboxColIndex0based, endColumnIndex: checkboxColIndex0based + 1 },
        rule: { condition: { type: 'BOOLEAN' }, strict: true },
      },
    }] },
  })

  // ★重要: 導入前の既存行は一括で「対応済み」にする（過去データの誤催促を防止）
  const colA = await sheets.spreadsheets.values.get({ spreadsheetId, range: `'${sheetName}'!A:A` })
  const lastRow = (colA.data.values || []).length
  if (lastRow >= 2) {
    const colLetter = /* checkboxColIndex0based を A1形式の列名に変換 */ String.fromCharCode(65 + checkboxColIndex0based)
    await sheets.spreadsheets.values.update({
      spreadsheetId, range: `'${sheetName}'!${colLetter}2:${colLetter}${lastRow}`,
      valueInputOption: 'RAW', requestBody: { values: Array.from({ length: lastRow - 1 }, () => [true]) },
    })
  }
}

// 2. 純関数: 未対応行の抽出（テストしやすい・状態を持たない）
function findOverdue(rows, now, thresholdMs = 24 * 60 * 60 * 1000) {
  return rows.filter(row => {
    if (row.checked || row.assignee.trim() === '') return false
    const t = parseTimestamp(row.timestamp) // タイムゾーンを明示して変換すること
    return t !== null && now.getTime() - t.getTime() >= thresholdMs
  })
}

// 3. 純関数: 担当者別グルーピングして通知文を組み立て
function buildReminderMessage(overdueRows, mentionResolver) {
  if (overdueRows.length === 0) return null
  const groups = new Map()
  for (const row of overdueRows) {
    const list = groups.get(row.assignee) ?? []
    list.push(row)
    groups.set(row.assignee, list)
  }
  const sections = [...groups.entries()].map(([assignee, rows]) =>
    [mentionResolver(assignee), ...rows.map(r => `　・${r.title}`)].join('\n')
  )
  return ['⚠️ 対応の確認をお願いします（一定時間以上未対応）', '', sections.join('\n\n'), '', 'シートの「対応済み」列にチェックを入れてください。'].join('\n')
}

// 4. 既存の日次cronに追加（独立したtry/catchで囲む）
async function existingDailyCronHandler() {
  // ...既存の日次処理...
  try {
    await ensureResponseCheckbox(sheets, spreadsheetId, '<シート名>', 'S1', 18)
    const rows = await getAllRowsWithStatus(sheets, spreadsheetId, '<シート名>')
    const now = new Date(/* タイムゾーン調整 */)
    const overdue = findOverdue(rows, now)
    const message = buildReminderMessage(overdue, resolveMention)
    if (message) await postToWebhook(process.env.WEBHOOK_URL, message)
  } catch (e) {
    console.error('reminder error:', e) // 既存処理を巻き込まない
  }
  // ...既存の日次処理の続き...
}

ハマりどころ（実際に踏んだ）

1. **values.getでA:Sのような複数列範囲を読むと、チェックボックスのデータ検証を広い行範囲（例: 2〜10000行目）に設定した直後から、データが何もない行でもチェックボックス列にFALSEという値が「存在する」とAPIに認識され、rows.lengthが実データ行数よりずっと多く返ってくることがある。単一列（A:Aなど、チェックボックスを置いていない列）で本当の最終データ行を数え、それを基準に「対応済み」の一括書き込み範囲を決めること。
2. 導入時の一括TRUE化を忘れると事故る: チェックボックス列を追加した直後は全既存行が「未チェック」に見えるため、一定時間の閾値を超えた過去データが全部「未対応」として一度に検出され、大量の担当者に一斉催促が飛ぶ。本番投入前に「現時点の未対応件数」を送信せずに数える確認ステップを必ず挟む。
3. タイムゾーン変換: シートの日時文字列（例: 2026/07/06 09:32）をJavaScriptのDateにそのまま渡すと実行環境のタイムゾーンに依存して解釈がぶれる。年月日時分を正規表現で分解し、明示的にオフセットを加減して変換する。

汎用化のポイント

• シート・列・催促条件（時間閾値・対象条件）・通知先（Discord/Slack/メール）は環境ごとに違うが、 「①チェックボックス列＋②導入時一括TRUE化＋③状態を持たない純関数での毎回再判定＋④既存cronへの相乗り＋⑤担当者グルーピング」の5点セットはそのまま流用できる。