OneDriveの月謝表をGoogleスプレッドシートへ毎日同期する

何を自動化したか（塾 GAS 自動化・Google Apps Script スプレッドシート 同期）

総合学習教室ブリッジでは、月謝と未入金の「正本」は妻が編集するOneDriveのExcel（横並びの月ブロック形式）で、生徒管理アプリが読んでいるのはGoogleスプレッドシート「bridge生徒管理」です。この2つが別々に存在する限り、どちらかを見て手で転記するしかなく、転記漏れや二重管理のズレが避けられませんでした。

そこで、OneDriveのExcelを定期的にダウンロードしてGoogleスプレッドシートに変換し、月ごとのブロックを突き合わせて「合計」セルの値と文字色（赤=未入金）だけをアプリ側へ反映するGASを作りました。手で転記する作業自体をなくすのが狙いです。実際に何件・何分減ったかは計測していないため数字では出せませんが、体感では「月謝表を開いて目を凝らして数字を拾う」作業が丸ごとなくなりました。

設計で最も気を配ったのは、生徒名・保護者名・金額そのものといった個人情報を扱う処理でありながら、書き込み対象を「合計セルの値と色」だけに絞り、行の追加・削除やレイアウト変更は一切しないことです。正本を壊さない・アプリ側の見た目を変えないという制約を先に決めてから実装しています。

仕組み（データの流れ）

安全のため3段階に分けています。いきなり書き込む設計にはしていません。

段階	関数	やること
1. 診断	Inspect	読み取りのみ。両シートの構造（タブ名・月・人数）を診断シートに書き出す
2. プレビュー	DryRun	読み取りのみ。生徒ごとの差分（一致／不一致／未照合）を専用シートに書き出す
3. 反映	Apply	実書き込み。有効化フラグが立っているときだけ動く

データの流れは次のとおりです。

手順	処理
1	OneDriveの共有Excel（またはMicrosoft連携済みなら本人ドライブ）からxlsxを取得し、一時的にGoogleスプレッドシートへ変換して開く
2	各シートの1行目を走査し、「6月」「７月」のような月見出しセルをブロックの開始位置として検出する
3	ブロック内の各行から生徒名と「合計」列（末尾側で最初に数値が拾える列）を取り出し、文字色が赤なら未入金と判定する
4	同じ「西暦・月」のブロック同士を、まず並び順（位置）で対応づけを試み、人数が合わない・名前の並びが崩れている場合は正規化した氏名（＋手動の氏名対応表）で対応づける
5	差分（アプリ側の現在値とOneDrive側の値・未入金フラグ）を集計し、不一致だけをアプリ側の該当セルへ書き込む
6	入金状態を、アプリのバックエンドが備考欄で使う「[済]」テキストにも変換して付け外しする

「合計」列は生徒ごとに列がずれることがあるため、月ブロックの末尾側から手前へ走査し、最初に数値として読める列を合計とみなしています。

コード（要点）

月見出しの検出は、全角数字（６月）や余分な空白が混じっていても拾えるよう正規化してから判定します。

function bridgeMonthFromHeader_(v) {
  var s = String(v == null ? '' : v)
    .replace(/[０-９]/g, function (c) { return String.fromCharCode(c.charCodeAt(0) - 0xFEE0); })
    .replace(/[\s　]/g, '');
  var m = s.match(/^(\d{1,2})月$/);
  if (!m) return null;
  var n = parseInt(m[1], 10);
  return (n >= 1 && n <= 12) ? n : null;
}

未入金の判定は文字色から行います。「赤が強く、緑・青が弱い」を基準にしているので、多少の色味の違いも拾えます。

function bridgeIsRed_(hex) {
  if (!hex) return false;
  var m = String(hex).toLowerCase().match(/^#?([0-9a-f]{6})$/);
  if (!m) return false;
  var r = parseInt(m[1].substr(0, 2), 16);
  var g = parseInt(m[1].substr(2, 2), 16);
  var b = parseInt(m[1].substr(4, 2), 16);
  return r >= 150 && g <= 110 && b <= 110;
}

月ブロック内の対応づけは、位置優先・氏名フォールバックの2段構えです。

function bridgeAlignBlock_(odBlock, appBlock, aliases) {
  var odS = odBlock.students, appS = appBlock.students;
  // (a) 人数一致 かつ 正規化氏名が並び順で8割以上一致 → 位置で対応
  if (odS.length === appS.length && odS.length > 0) {
    var same = 0;
    for (var i = 0; i < odS.length; i++) if (odS[i].normName === appS[i].normName) same++;
    if (same / odS.length >= 0.8) {
      return odS.map(function (o, idx) { return { od: o, app: appS[idx], method: '位置' }; });
    }
  }
  // (b) 氏名（＋氏名対応表のエイリアス）で対応
  var appIdx = {};
  appS.forEach(function (a) { if (a.normName) appIdx[a.normName] = a; });
  return odS.map(function (o) {
    var key = aliases[o.normName] || o.normName;
    return { od: o, app: appIdx[key] || null, method: '氏名' };
  });
}

実際の書き込みは、不一致のセルの値と文字色だけを更新します。入金状態は備考欄の「[済]」表記へも変換します。

result.diffs.forEach(function (d) {
  if (d.status !== '不一致') return;
  var cell = sh.getRange(d.bRow + 1, d.bCol + 1); // 0始まり→1始まり
  cell.setValue(d.odTotal);
  cell.setFontColor(d.odUnpaid ? '#ff0000' : '#000000');
});
// 備考欄の [済] を色（未入金/入金済）に合わせて付け外しする
var wantPaid = !d.odUnpaid;
var hasPaid = /\[済\]/.test(note);
if (wantPaid && !hasPaid) {
  sh.getRange(r + 1, noteCol + 1).setValue((note ? note + ' ' : '') + '[済]');
} else if (!wantPaid && hasPaid) {
  sh.getRange(r + 1, noteCol + 1).setValue(note.replace(/\s*\[済\]/g, '').trim());
}

反映前には毎回、対象スプレッドシートのコピーを自動でバックアップします（同日分は1つにまとめる）。実行のたびにファイルが増えて肥大化しないための工夫です。長い関数（OneDriveの取得を複数方式で試すフォールバック処理やMicrosoft連携部分）は全文をnoteに掲載しています。

ハマった点と回避策

全角の月見出しを見落としていた

2026年のシートで6月・7月の見出しが全角（６月・７月）で入力されており、半角前提の正規表現ではそのブロックごと検出できていませんでした。見出し判定の前に全角数字を半角へ正規化する処理を先頭に足して解決しました。

正本に新しい月が増えても永遠に同期されない

月が変わって新しい月ブロックが正本側に追加されても、アプリ側に受け皿となる列がなければ、突き合わせ処理は「対応するブロックが無い」として読み飛ばしてしまいます。実際にある月でアプリ側にその月の数字が一切出ない状態が発生しました。対策として、反映処理の冒頭で「正本にあってアプリ側に無い月ブロック」を検出し、値と文字色ごと年シートの空き列にコピーしてから差分計算をする処理を追加しました。既存のブロックには触れず、右側に足すだけにしています。

取得経路の失敗理由が埋もれる

OneDriveの共有リンク取得は環境によって複数の失敗パターンがあり、認証連携が切れているだけなのに「共有リンクが401」という表面的なエラーだけが表に出て、根本原因（再認証が必要）に気づけないことがありました。各取得方式のログを配列にため、最終的なエラーメッセージの先頭に必ず載せる形にしています。

導入手順

1. アプリのバックエンドを管理しているGoogle Apps Scriptプロジェクトに、月謝表の月ブロック解析・突き合わせ・反映を行うスクリプトを追加する
2. スクリプトプロパティに以下を設定する（値そのものはコードに書かない）
   • ONEDRIVE_SHARE_URL … OneDriveの共有リンク（「リンクを知っている全員が閲覧可」）
   • BRIDGE_SHEET_ID … 反映先スプレッドシートのID
   • MONTHLY_SYNC_ENABLED … 'true' のときだけ実書き込みを許可する（既定は無効）
   • NOTIFY_EMAIL … （任意）同期結果を受け取るメールアドレス
3. まず「診断」を実行し、両シートの月ブロックが想定どおり検出できているかを確認する
4. 「プレビュー」を実行し、差分（一致／不一致／未照合）の件数を確認する。未照合が出たら「氏名対応表」タブに読み替えを1行足す
5. 問題なければ MONTHLY_SYNC_ENABLED を 'true' にし、既存の定期実行の仕組み（トリガー）から反映処理を呼ぶようにする
6. 初回の反映結果を確認し、バックアップファイルが作成されていることも合わせて見ておく

よくある質問

Excelの行やレイアウトを変えても壊れませんか

月見出しの位置から動的にブロックを検出しているため、列の増減にはある程度追従します。ただし生徒の並び順を大きく入れ替えると、人数不一致から氏名対応に切り替わり、表記ゆれがあれば「未照合」になります。その場合は氏名対応表に1行足すだけで次回から解決します。

間違って書き込んでしまったら

反映のたびに対象スプレッドシートのコピーを自動でバックアップしているので、そこから復元できます。加えて、まず「診断」「プレビュー」の読み取り専用2段階を必ず通す設計にしているため、想定外の反映が起きにくくなっています。

OneDriveの共有リンクが期限切れになったら止まりますか

共有リンク方式に加えて、Microsoftアカウントでのサインインによる連携にも対応しており、そちらが失効した場合は自動で再サインインを促す通知を送る仕組みにしています（詳細はnote版のコード全文を参照してください）。