未納を自動で転記し、入金されたら自動で消す

何を自動化したか（塾 GAS 自動化・未納管理）

これまで、月謝の未入金者を把握するには生徒管理アプリの「概況」タブを開いて未入金一覧を目で確認し、督促が必要な生徒を別シートへ手で転記していました。転記自体が単純作業である上に、入金が確認できたあとに一覧から外す作業も忘れがちでした。

そこで、毎週月曜に「先月分の未入金者」を自動で「未納管理」シートへ転記し、その後に入金が確認できた行は自動で「回収済み(自動)」に更新するGASを作りました。転記された行には、続けて段階的な督促メールの下書きを自動生成する処理が走ります。件数の実測はしていないため数字では出せませんが、体感では「未入金者を探して書き写す」作業自体がなくなりました。

この記事で扱う仕組みは、生徒名・支払い状況・保護者連絡先という個人情報を直接扱います。そのため転記・督促生成のいずれも、データソースとなるスプレッドシートIDが未設定であれば何もしない安全設計にしてあります。

仕組み（データの流れ）

手順	処理
1	生徒管理アプリのデータソースから、入金記録シート・生徒シート・在籍コースシートを、ヘッダー名で自動的に探し出す
2	入金記録を「西暦-月 → {生徒ID: 入金済みか}」の形に整理する
3	先月時点で在籍していた生徒（入会日・退会日から判定）を洗い出す
4	在籍コースから生徒ごとの月額を概算する（先月の近似値として使う）
5	先月分が未入金かつ「未納管理」シートに未登録の生徒を、1回あたり最大20名まで自動で追記する
6	「未納管理」シートの既存行のうち、対象月の入金が確認できたものを「回収済み(自動)」に自動更新する
7	転記された行に対して、経過日数に応じた段階的な督促文（Geminiで生成）の下書きをGmailに作る

督促は3段階です。1段階目は「行き違いでしたらご容赦ください」を必ず添えたやんわりした確認、2段階目は支払期日を明記した事務的な依頼、3段階目はメールではなく電話・送迎時の声かけを促す通知に切り替えます。段階を進めるのは前回の督促から6日以上経過してからで、週次実行1回につき1段階までしか進みません。

コード（要点）

データソース側のシート構成が変わっても追従できるよう、必須の列を「ヘッダー名の別名候補」で探す仕組みにしています。

function salesSheetWithCols_(ss, required) {
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sh = sheets[i];
    var headers = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0]
      .map(function(h) { return String(h || '').trim().toLowerCase(); });
    var cols = {}, ok = true;
    for (var key in required) {
      var idx = -1;
      for (var c = 0; c < required[key].length && idx < 0; c++) {
        idx = headers.indexOf(required[key][c]);
      }
      if (idx < 0) { ok = false; break; }
      cols[key] = idx;
    }
    if (ok) return { sheet: sh, cols: cols };
  }
  return null;
}

データソースのスプレッドシートIDは、コードに直接書かずスクリプトプロパティから読みます。未設定なら何もせず終了します。

function salesSyncUnpaidToRecovery() {
  var srcId = PropertiesService.getScriptProperties().getProperty('BRIDGE_DATA_SHEET_ID');
  if (!srcId) { Logger.log('未納転記: BRIDGE_DATA_SHEET_ID 未設定のためスキップ'); return; }
  var src = SpreadsheetApp.openById(srcId);
  // ...入金記録シート・生徒シート・在籍コースシートをヘッダー名で特定
}

先月分の未入金者を、重複チェックをしながら最大20名まで追記します。

var lastPaid = paidBy[yr + '-' + mo] || {};
actives.forEach(function(a) {
  if (added.length >= 20 || lastPaid[a.sid] || seen[a.name + '|' + monthLabel]) return;
  var amount = monthlyBySid[a.sid] ? '¥' + Number(monthlyBySid[a.sid]).toLocaleString() : '';
  sheet.appendRow([a.name, '', monthLabel, amount, '', '', '自動転記 ' + today]);
  added.push('・' + a.name);
});

転記済みの行は、対象月の入金が確認できた時点で自動的に状態を更新します。

for (var j = 1; j < rows.length; j++) {
  var state = String(rows[j][4] || '').trim();
  if (state === '回収済み' || state === '回収済み(自動)' || state === '対応中止') continue;
  var m = String(rows[j][2] || '').match(/^(\d{4})年(\d{1,2})月$/);
  var rsid = nameToSid[String(rows[j][0]).trim()];
  if (!m || !rsid) continue;
  var pset = paidBy[Number(m[1]) + '-' + Number(m[2])];
  if (pset && pset[rsid]) {
    sheet.getRange(j + 1, 5).setValue('回収済み(自動)');
    recovered.push('・' + rows[j][0]);
  }
}

督促は段階に応じてトーンを変え、Geminiで文面を生成してGmailの下書きに残します。実際の送信はせず、下書きを作るところまでを自動化しています。

var tone = (nextStage === 1)
  ? 'やんわりとした確認。「行き違いでしたらご容赦ください」と必ず添える。責める雰囲気ゼロ。'
  : '丁寧だが事務的なお願い。お支払い期日を改めて明記し、難しい場合は相談してほしいと添える。';
if (nextStage >= 3) {
  // 段階3: メールではなく電話・対面を推奨（こじらせないため自動文面は作らない）
} else {
  var result = autoGeminiJson([...tone を含むプロンプト...].join('\n'));
  if (email) GmailApp.createDraft(email, result.subject, result.body);
}

入金記録の解析部分やGeminiプロンプトの全文はnoteに掲載しています。

ハマった点と回避策

督促の間隔をログ文字列から逆算している

督促の履歴は専用の日付列ではなく「段階1 9/1」のようなログ文字列に積んでいく形にしているため、次に進めてよいかの判定はログの最後の日付を正規表現で拾って比較する必要があります。列を増やさずに済む一方、ログの書式を変えると判定が壊れるため、書式（「段階N M/d」）は固定にしています。

「回収済み」判定は状態文字列の完全一致にしている

状態欄に人が「回収済」「入金確認済み」のように違う書き方をすると自動更新の対象から外れず何度も督促が走ってしまうため、自動更新が付ける文字列は必ず「回収済み(自動)」に固定し、人が手で書く「回収済み」「対応中止」とあわせて除外条件に含めています。表記が増えるたびに除外条件へ足す必要がある点は運用上の弱点です。

金額はあくまで概算

先月時点の月額ではなく、現在の在籍コースから逆算した概算値を表示しています。月の途中でコース変更があった生徒は実際の請求額とずれる可能性があるため、転記された金額は目安として扱い、督促メールの本文には具体的な金額を書かないトーンにしています。

導入手順

1. 生徒管理アプリのデータソース（入金記録・生徒情報・在籍コースの3シート）が入っているスプレッドシートのIDを控える
2. スクリプトプロパティに以下を設定する
   • BRIDGE_DATA_SHEET_ID … データソースのスプレッドシートID
3. 「未納管理」シートを用意する（初回実行時にヘッダー付きで自動作成される）。列は「生徒名・保護者メール・対象月・金額・状態・督促段階・ログ」
4. 未入金の自動転記処理を、既存の定期実行の仕組み（トリガー）に週次で1本相乗りさせる
5. 段階督促の生成処理も同じく週次で、転記処理の直後に呼ぶように登録する
6. 初回実行後、「未納管理」シートに先月分の未入金者が並んでいるか、入金済みの生徒が誤って載っていないかを確認する
7. 回収できた行は、自動更新を待たずに手動で状態欄へ「回収済み」と書いてもよい（対象から除外される）

よくある質問

督促メールは自動で送信されますか

送信はしません。Gmailの下書きを自動生成するところまでで、実際に送るかどうかは必ず人が最終確認してから送信します。督促は保護者との信頼関係に関わるため、自動送信はあえて実装していません。

入金確認のタイミングは即時ですか

週次の定期実行のタイミングでまとめて判定するため、最大で1週間ほど「回収済み」への反映が遅れます。即時性より、誤検知を防ぐために入金記録側の反映を待つことを優先しています。

対象月の判定はどこを見ていますか

実行日の前月を対象にしています。月初に実行することで、先月分の締めが終わった状態の入金記録を確実に参照できるようにしています。