スプレッドシートの自動集計が途中で止まるとき｜API制限の見分け方と、呼び出しをまとめる設計
スプレッドシートの自動集計が途中で止まるとき｜API制限の見分け方と、呼び出しをまとめる設計
店舗の日報や売上をスプレッドシートに入力し、それを自動で集計してダッシュボードに出す。よくある仕組みで、実際よく動きます。
問題は、この手の仕組みがエラー画面を出さずに止まることです。管理画面は開くし、数字も表示される。ただしその数字が先月のまま止まっている。気づくのは経営者が画面を見て「6月中旬から先が無い」と言ったときです。
原因の多くはAPI（システム同士をつなぐ窓口）の制限ですが、ひとくちに制限といっても中身は3種類あり、それぞれ対処が全く違います。ここを区別しないまま「呼びすぎだろう」と待ち時間を入れても直りません。
この記事では、制限の見分け方と、そもそも制限に当たらないための呼び出し設計、そして止まったことに気づく仕組みを整理します。
結論：先に決めるのは3つ
技術的な話に入る前に、結論を書きます。自動集計を作る（あるいは発注する）ときに先に決めるのは次の3つです。
- 呼び出しをまとめる。画面を開くたび全件を取りに行く作りにしない
- 止まったことをデータ側で検知する。処理が成功したかではなく、「最終更新がいつか」で見る
- エラーの文面を鵜呑みにしない。「上限に達しました」には原因が3種類あるので、切り分けてから手を打つ
この3つが決まっていれば、多少の制限には当たっても仕組みは止まりません。逆に、この3つが無い仕組みは、動いているうちは快適で、止まったときに誰も気づけません。
API制限は3種類ある。まずここを分ける
「制限に当たりました」と言われたとき、実際に起きていることは次のどれかです。
| 種類 | 何が足りないのか | 対処 |
|---|---|---|
| 回数・頻度の制限 | 短時間に呼びすぎ | 呼び出しをまとめる・間隔を空ける |
| 容量の制限 | 保存先の空きが無い | 保存先を変える・権限を分ける |
| 利用枠が未付与 | そもそも使う許可が出ていない | 申請する（待っても直らない） |
厄介なのは、これらが似たようなメッセージで返ってくることです。とくに「上限に達しました」という趣旨のエラーは、呼びすぎのときにも、そもそも枠がゼロのときにも同じ形で返ります。
実例で説明します。あるGoogle系APIで、認証は通っているのに毎回「上限に達した」と返り続けたケースがありました。待っても、認証をやり直しても直りません。調べると割り当ての値そのものがゼロでした。これは呼びすぎではなく「まだ使う許可が出ていない」状態で、正しい対処は待つことでも間隔を空けることでもなく、利用申請を出すことでした。増枠の申請では通りません。枠がゼロなのは減らされたのではなく、最初から付いていないからです。
もう一つ。自動化用のアカウント（サービスアカウント）でスプレッドシートを新規作成しようとしたら「容量の上限を超えている」と返ったケースもあります。これも回数制限ではありません。自動化用アカウント自身の保存領域には新しいファイルを置けない一方、既存のファイルへの読み書きは今までどおりできる、という状態です。
この場合の正しい作りは、ファイルは人のアカウント側で作り、自動化用アカウントには編集権限だけを渡すことです。作成と更新で必要な権限が違う、という前提で設計しておくと、後から詰まりません。
つまり、エラーが出たら最初にやることは対処ではなく分類です。呼びすぎなのか、置き場所が無いのか、そもそも許可が無いのか。ここを飛ばすと、直らない対処を延々と繰り返すことになります。
呼びすぎを防ぐ設計：「開くたび全件」をやめる
回数の制限に当たる作りには、はっきりした共通点があります。画面を開くたびに全件を取りに行く設計です。
多店舗の売上を集計する社内ダッシュボードで、実測した数字があります。
- 全16店舗を一括で同期：57秒
- 1店舗だけを同期：約3秒
この仕組みで「サイトを開いたら自動で最新にする」を素直に実装すると、開くたびに1分近く画面が固まります。しかも複数の店舗担当者が同じ時間帯に開けば、その回数だけ全店分の呼び出しが走ります。制限に当たるかどうか以前に、使い物になりません。
正しい作りは、呼び出しの量を3段階で削ることです。
- 範囲を絞る：全店ではなく、いま表示している店舗だけを同期する（57秒 → 約3秒）
- 間隔を決める：前回の同期から30分以内なら何もしない。開き直すたびに走らせない
- 先に描いて裏で直す：まず保存済みの数字で画面を描き、裏側で同期して差し替える
3番目が地味ですが効きます。利用者から見ると画面は即座に出るため、同期を待たせる必要が無くなり、結果として「早く出したいから毎回全件取る」という設計圧力が消えます。
書き込み側も同じ考え方です。1行ずつ書き込むのではなく、更新分をためて一度に書く。1回の処理で100行更新するなら、100回呼ぶのか1回で済ませるのかで、制限までの距離が100倍変わります。
連打は待ち時間より高くつく
「制限に当たったらもう一度呼べばいい」という作りは、いちばん高くつきます。
画像の書き出しAPIを短時間に連打した結果、約95時間利用できなくなったケースがあります。日単位ではなく4日近くです。しかも制限はアカウント単位でかかるため、認証キーを再発行しても戻りません。
このとき役に立ったのは2つでした。ひとつは、拒否の応答にあと何秒待てば解除されるかの数値が含まれていたこと。推測ではなく実数で復帰時期が分かります。もうひとつは、同じサービスでも別の入口（エンドポイント）は制限の対象外だったこと。目的が達成できるなら、混んでいない入口に回すという判断ができます。
失敗すると自動で即リトライする作りは、制限中にさらに呼び出しを積み上げ、解除を遠ざけます。再送は等間隔にせず、間隔を広げていくのが基本です。実運用しているツールでは、1分 → 5分 → 15分 → 1時間 → 6時間の順で最大5回まで、という設計にしています。ここまで広げれば、一時的な混雑はほぼ吸収できます。
あわせて決めておくとよいのが、処理の途中でサーバーが再起動したときの扱いです。「送信中」のまま宙に浮いたジョブを、起動時に再送待ちへ戻す。これが無いと、再起動のたびに数件ずつ静かに欠けていきます。
本当に怖いのは「止まったことに気づかない」
ここまで制限の話をしてきましたが、実害が大きいのは制限そのものではありません。止まったのに誰も気づかないことです。
毎日決まった時刻に自動実行する仕組みで、実行そのものは毎日成功していたのに、中身は認証エラーで空振りし続け、52日間データが更新されていなかったという事例があります。実行ログには成功が並んでいました。当日の朝も、たしかに動いていました。それでも数字は2か月前で止まっていた。
これは特殊な事故ではなく、外部サービスを呼ぶ自動処理では普通に起こります。処理が起動したことと、処理が仕事を終えたことは別だからです。
対策は難しくありません。成否を処理側ではなくデータ側で見ることです。
- 各データに「最終同期の時刻」を持たせる
- 「N時間以上同期されていない対象」を一覧で出せるようにする
- その一覧を人が見る場所（管理画面やチャット通知）に出す
このとき一つ落とし穴があります。既存の「更新日時」列を最終同期時刻の代わりに使わないことです。データベースの設定によっては、書き込んだ値が前回と同じ場合に更新日時が変わりません。売上がゼロの日が続けば、同期は成功しているのに更新日時は古いままになり、正常なのに異常に見えます。逆も起こります。同期時刻は同期時刻として、専用に持たせてください。
もう一つ、外部と通信する自動処理では経路そのものを減らすのも有効です。同じサーバーの中にある処理なのに、わざわざ外向きの通信で自分のログイン画面を通ってから実行する、という作りは珍しくありません。この形はログイン情報の管理が増え、認証が変わった瞬間に静かに止まります。同じサーバー内なら、直接実行できないかを先に検討する価値があります。
依頼する側が確認する4項目
社内に作れる人がいない場合でも、次の4つを聞けば、止まりにくい作りかどうかは判断できます。
- 画面を開くたびに全件取りに行きますか（＝範囲を絞る設計になっているか）
- 前回いつ更新できたかを、画面で確認できますか（＝止まったことに気づけるか）
- 失敗したとき、どれくらいの間隔で何回まで再送しますか（＝連打で自滅しない設計か）
- 更新が止まったら誰にどう伝わりますか（＝人が気づく経路があるか）
いずれも専門知識が要る質問ではありません。それでいて、答えられない仕組みは、たいてい止まったときに気づけない作りになっています。
まとめ
スプレッドシートを使った自動集計が途中で止まる原因は、多くがAPIの制限です。ただし制限は「呼びすぎ」「容量不足」「利用枠が未付与」の3種類があり、切り分けずに対処すると直りません。
そのうえで、設計としてやることは次の3つです。
- 範囲を絞り、間隔を決め、書き込みはまとめる。開くたび全件は避ける
- 再送は間隔を広げながら、回数を決めて止める。連打は復帰を遠ざける
- 止まったことをデータ側で検知する。実行の成功ではなく、最終更新の時刻で見る
自動化の価値は、動いている間に手が空くことではなく、止まったときにすぐ分かることまで含めて設計されているかで決まります。作る前に、あるいは発注する前に、この3つを決めておいてください。