経営企画が毎月各部門から集めるKPIのスプレッドシートについて、未提出と入力値の異常を拾い、部門ごとの督促と確認の文面を下書きする

処理の起点を決める

時間主導型トリガーを10分おきに動かし、スクリプトの中で「今日が処理する日か」「朝9時を過ぎたか」を判定します。 締切は第5営業日なので、カレンダーの日付で決め打ちできません。祝日の一覧をシートに持ち、営業日を数えて前日（第4営業日）と翌日（第6営業日）だけ処理に進みます。

締切の当日には動かしません。 当日の昼に点検すると、入力の途中のシートを「空欄だらけ」と判定してしまいます。

トリガーは、経営企画の担当者が自分のアカウントで作ります。 インストール型トリガーは作った人のアカウントで動き、下書きもその人の Gmail にできるためです。担当者が異動するときは、トリガーを作り直す必要があります。 手順を1枚にまとめておきます。

入力データを集める

質を決めるのは、KPIの定義です。 「急変とみなす幅」は項目ごとに違います。売上の20％の変動と、不良率の20％の変動は意味が違います。 不良率は0.5％が0.6％になっただけで20％です。項目ごとに、割合の幅と絶対値の幅の両方を持たせます。

単位の列が、桁違いを拾う手がかりになります。 「千円」の項目に円で入れれば1,000倍、「%」の項目に小数で入れれば100分の1になります。単位を知っていれば、10倍や1,000倍のずれを「桁違いの疑い」として名前を付けて拾えます。

データの取得方法を決める

部門のシートは、管理シートに並べたIDで SpreadsheetApp.openById() を使って開きます。シートの範囲は getValues() で一度に読み、1セルずつ読みません。 60枚 × 30項目 × 13か月を1セルずつ読むと、6分の上限に届きます。

getValues() と getDisplayValues() を両方読むのは、エラーと文字の混入を見分けるためです。 数式が壊れたセルや、「約120」のように文字で入った値は、値だけを見ると扱いが紛らわしくなります。

1回の実行で全部を処理しきれないことを前提にします。 処理済みの部門コードをスクリプト プロパティに保存し、5分を過ぎたら区切って、次の実行で続きから処理します。 スクリプト プロパティは、スクリプトのすべての利用者で共有される設定の置き場所で、アプリ全体の設定に使うものとされています。続きは、10分おきに動かしている時間主導型トリガーの次の回で拾います。未処理の部門が残っていない回は、何もせずにすぐ終わります。

AIへ渡す前に整形する

1. 提出の確認 … 提出のチェックが入っていないシートは、点検せずに督促の対象にします
2. 型の確認 … 数字のはずの欄に文字列が入っていれば「型の違い」、表示が # で始まるエラーなら「エラー」として拾います
3. 空欄の確認 … 必須の項目が空なら「空欄」。0 と空欄は区別します
4. 桁違いの確認 … 前月の値との比が10倍以上か10分の1以下なら「桁違いの疑い」。単位が千円・％の項目では、1,000倍・100倍のずれを名前付きで拾います
5. 急変の確認 … 定義の割合の幅と絶対値の幅の両方を超えたら「急変」。季節で動く項目は前年同月と比べます
6. 過去の幅の確認 … 過去12か月の最小と最大の外に出たら「過去の幅の外」として付記します

3番目の「0 と空欄の区別」を軽く見ないでください。 不良が無かった月の不良件数は0で、入れ忘れた月は空欄です。getValues() は空のセルを空の文字列で返すため、数値の0と取り違えないよう比較を書きます。

規則の部分は、たとえば次のように書けます。

function checkItem(def, cur, prev, lastYear, display) {
  if (String(display).startsWith('#')) return 'error';
  if (cur === '' || cur === null) return def.required ? 'blank' : null;
  if (typeof cur !== 'number') return 'type_mismatch';
  const base = def.compareTo === 'last_year' ? lastYear : prev;
  if (typeof base !== 'number' || base === 0) return null;
  const ratio = cur / base;
  if (ratio >= 10 || ratio <= 0.1) return 'digit_error';
  const pct = Math.abs(ratio - 1) * 100;
  const abs = Math.abs(cur - base);
  if (pct >= def.pctLimit && abs >= def.absLimit) return 'sudden_change';
  return null;
}

比べる相手が0や空のときは、急変を判定しません。 新しく始めた項目や、前月がたまたま0だった項目で、割り算が意味を持たないからです。過去12か月の幅の外かどうかは、この関数とは別に付記します。

ここまでが規則の仕事です。 生成AIに渡すのは、4番から6番で拾った項目と、その項目の備考だけです。拾われなかった項目は渡しません。

AIに処理させる

させるのは、規則が拾った項目それぞれについて、部門の備考が理由の説明になっているかを判定することと、部門ごとの連絡を下書きすることです。

「桁違いの疑い」は、備考があっても explained にしません。 単位の誤りは、理由の説明では直らないからです。部門に値そのものを確かめてもらいます。

4行目がいちばん起きやすい失敗です。 備考が空でも、生成AIは「季節的な要因と考えられます」と理由を作って explained にしがちです。書かれていない理由で確認を省くと、本当の入力の誤りがそのまま経営会議に出ます。

指示内容を固定する

あなたは経営企画の担当者です。各部門が入力したKPIのうち、
規則で「確認が必要」と拾われた項目について判定し、部門への連絡を下書きします。

【判定のしかた】
各項目について judgement を次から選んでください。
- explained ..... 備考が、その項目のその動きの理由を具体的に説明している
- unexplained ... 備考が空、または別の項目のことしか書いていない
- unclear ....... 備考はあるが、その動きの説明になっているか判断できない
迷ったときは unclear を選んでください。

【厳守事項】
- 備考に書かれていない理由を補わないでください。
  「季節要因」「一時的な変動」などを自分で考えて explained にしないでください。
- rule が digit_error（桁違いの疑い）の項目は、備考があっても unexplained にしてください。
- 値の正誤を判断したり、正しい値を推測したりしないでください。
- 割合や差を計算しないでください。与えられた値をそのまま使ってください。
- KPIの良し悪しや、部門の取り組みへの評価を書かないでください。
- reason には、判定の根拠にした備考の文言をそのまま引用してください。

【連絡の下書き】
- unexplained と unclear の項目だけを、1通にまとめてください。
- 項目ごとに、項目名・当月の値・前月の値・確認したいことを1行で書いてください。
- 締切や期限は {reply_due} と書いてください。
- 丁寧に、短く書いてください。前置きのあいさつは1文までにしてください。

【部門】{dept_name}
【KPIの定義】{kpi_definitions}
【拾われた項目】{flags}

「迷ったときは unclear」と書いているのは、explained が連絡を省く判定だからです。 誤って explained にすると、確認すべき項目が誰にも聞かれずに残ります。誤って unclear にしても、確認の連絡が1行増えるだけです。 誤りの重さが違うので、迷ったら軽いほうに倒します。

出力形式を固定する

次の形のJSONで受け取ります。

{
  "dept_code": "",
  "items": [
    {
      "kpi_code": "", "rule": "blank | digit_error | sudden_change | out_of_range | error | type_mismatch",
      "current": "", "previous": "",
      "judgement": "explained | unexplained | unclear",
      "reason": ""
    }
  ],
  "message": { "needed": true, "subject": "", "body": "" }
}

Gemini API の構造化出力で、この形を指定します。 JSON スキーマを渡すと、構文として正しい JSON が返ります。ただし公式の説明でも、値の意味の正しさは保証されないため、アプリケーションの側で検証するよう書かれています。

スクリプトの側で確かめるのは3つです。

3行目が、上で書いた「書かれていない理由を補わない」の機械の側の守りです。 引用が備考に無ければ、生成AIが理由を作ったということです。

検証を通った後の下書きは、たとえば次のようになります。

件名：【確認のお願い】9月度KPIの入力値について（第2工場 製造部）

第2工場 製造部 ご担当者様

9月度のKPIをご提出いただきありがとうございます。
次の2項目について、値をご確認ください。

・在庫日数：当月 48.0日／前月 23.5日（前月の約2倍。備考の記載なし）
・不良率：当月 12／前月 0.8（単位は％です。入力の桁をご確認ください）

お手数ですが、{reply_due} までにシートを修正いただくか、
備考欄に理由をご記入ください。

経営企画部

値と前月の値を必ず並べて書かせます。 部門の担当者がシートを開かなくても、何を聞かれているかが分かるようにするためです。「約2倍」のような言い方は、スクリプトが計算した比を渡して書かせ、生成AIには計算させません。

システムへ連携する

UrlFetchApp.fetch() は、method に post、contentType に JSON、payload に要求の本文を入れて呼びます。muteHttpExceptions を true にすると、失敗の応答でも例外を投げずに応答を返すため、状態コードを見て再試行するかを決められます。

Gemini API のキーは、コードに書かずスクリプト プロパティに置きます。 ただしスクリプト プロパティは、スクリプトの利用者の間で共有される置き場所です。スクリプトの編集権限を持つ人を、経営企画の担当者に限ります。

GmailApp.createDraft() は、宛先・件名・本文と、cc などのオプションを取ります。部門長を cc に入れるかは、部門の一覧の列で部門ごとに決めておきます。

人が確認する

1. 確認一覧を部門ごとに見る … 規則・当月と前月の値・判定・引用を1行で読めるようにしてあります
2. explained の行も流し見る … 引用された備考が、本当にその動きの説明かを確かめます
3. 下書きを直して送る … 宛先、cc、強さを直して送ります
4. 判定を覆したら記録する … explained を unexplained に、またはその逆に直した行に印を付けます

2番目を省かないでください。 explained の行は連絡に載らないため、誰も見なければ、それきりになります。 引用の一致は機械で確かめていますが、引用した備考が別の月の事情を書いていることもあります。

例外に対処する

4行目で、生成AIが使えないときも点検は止めません。 規則の結果だけで確認一覧を作れば、経営企画が備考を読んで判定できます。生成AIは連絡を減らすための部品で、点検そのものの前提ではありません。

記録を残す

• 実行した日時、処理した部門、区切った位置
• 規則で拾った項目（規則の種類、当月・前月・前年同月の値）
• 生成AIに渡した備考と、返ってきたJSON
• 検証で直した判定と、その理由
• 作った下書きと、人が送った日時（送らなかったものも）
• 人が判定を覆した記録

最後の行は、急変の幅を見直す材料になります。 unclear が毎月同じ項目に出るなら、その項目の幅が狭すぎるか、備考の書き方の例をひな形に入れるべきです。