毎朝、外部APIから記事の集計値を取得し、Googleスプレッドシートへ保存してからメールを送るGoogle Apps Scriptを動かしています。
ある朝、この処理が約6分後にExceeded maximum execution timeで停止しました。翌日にはまたメールが届いたため、最初は「一時的に遅かっただけかもしれない」と考えました。
しかし、シートを確認すると、失敗した日は途中までの行しか保存されておらず、合計行もありませんでした。さらに翌日の前日比は、その不完全なシートを参照したことで一部が#N/Aになっていました。
ここで必要だったのは、単にappendRow()をsetValues()へ変えて高速化することだけではありませんでした。
- API取得が終わるまで既存データを変更しない
- 日次データが完全かどうかを判定する
- 不完全な前日データから数値を作らない
- 再実行で重複保存やメールの二重送信を起こさない
この記事では、タイムアウトをきっかけに日次バッチの保存境界と再実行を見直した流れをまとめます。
対象にしている処理
対象はApps Script V8で動かす、次のような日次処理です。
- ページングされた外部APIから全データを取得する
- 当日のシートへ集計値と前日比を書く
- 合計や増減をまとめたメールを送る
Googleの公式資料では、Apps Scriptの実行時間は1回あたり6分です。上限へ達すると例外で処理が止まるため、途中まで外部へ書いた内容が自動で元に戻るわけではありません。
今回のようにスプレッドシートを日次スナップショットとして使う場合は、「6分以内に終わるか」と「途中で止まっても次回の入力を壊さないか」を分けて考える必要があります。
最初の実装は、取得しながら1行ずつ書いていた
修正前は、ページを取得するたびに記事をループし、getLastRow()とappendRow()を繰り返していました。
function saveDailyStats(sheet, fetchPage) {
sheet.clear();
sheet.appendRow(HEADER_ROW);
let page = 1;
let total = 0;
while (true) {
const items = fetchPage(page);
if (items.length === 0) break;
for (const item of items) {
const row = sheet.getLastRow() + 1;
sheet.appendRow([
item.id,
item.title,
item.count,
`=C${row}-VLOOKUP(A${row}, 'previous'!A:C, 3, false)`,
]);
total += item.count;
}
page += 1;
}
sheet.appendRow(['sum', '合計', total, '']);
}
この実装には、速度以外にも問題がありました。
sheet.clear()した直後から当日のシートが書き換わり、タイムアウトまでに成功したappendRow()はそのまま残ります。処理が最後まで到達した証拠になる合計行はありませんが、翌日の処理から見ると「前日のシートが存在する」状態です。
また、既存シートがあれば再利用する分岐もありました。不完全なシートを完全なスナップショットと区別できないため、単純に再実行しても修復されない可能性がありました。
調査で事実と推測を分けた
確認できた事実は次のとおりです。
- 実行は約6分で時間切れになった
- 失敗日のシートには途中までのデータだけが残った
- 合計行は作成されていなかった
- 翌日は処理が完了したが、前日比の一部は計算できなかった
- 修正前のコードは記事ごとに
getLastRow()とappendRow()を呼んでいた
一方、どの処理が何秒かかったのかを示す実行ログは残っていませんでした。外部APIの応答遅延、スプレッドシートへの書き込み、数式の再計算がそれぞれどの程度影響したかは断定できません。
Googleのベストプラクティスでは、サービス呼び出しを減らし、配列で処理してからまとめて読み書きする方法が推奨されています。今回も行単位の書き込みは有力な改善対象でしたが、「原因はappendRow()だけだった」とは扱わず、工程ごとの時間をログへ残すことにしました。
取得と保存を分け、書き込みを1回にする
まず、APIから全ページを取得し終えるまでシートを変更しない形にしました。
function fetchAllItems(fetchPage) {
const items = [];
const ids = new Set();
for (let page = 1; page <= 500; page += 1) {
const chunk = fetchPage(page);
if (chunk.length === 0) {
if (items.length === 0) {
throw new Error('0件のため保存を中止しました');
}
return items;
}
for (const item of chunk) {
if (!item.id || ids.has(String(item.id))) {
throw new Error(`IDが不正または重複しています page=${page}`);
}
ids.add(String(item.id));
items.push(item);
}
}
throw new Error('取得ページ上限に達しました');
}
HTTPステータス、JSONの形式、数値、IDの重複も取得中に検査します。APIエラーを空配列として扱うと、「全件取得できた」のか「取得に失敗して0件に見える」のか区別できないためです。
全件を取得した後に行データをメモリ上で組み立て、最後にsetValues()を1回だけ呼びます。
function writeSnapshot(sheet, rows) {
const values = [HEADER_ROW, ...rows];
sheet.clearContents();
sheet
.getRange(1, 1, values.length, HEADER_ROW.length)
.setValues(values);
SpreadsheetApp.flush();
}
公式資料が説明する「配列へ読み、JavaScriptで処理し、まとめて書く」という形です。サービス呼び出しは減りますが、setValues()自体がデータベースのトランザクションになるわけではありません。そのため、速度改善とは別に完了状態も持たせます。
WRITINGとCOMPLETEで、途中データを成功扱いしない
保存前にシートのメタデータをWRITINGへ変更し、書き込み後の読戻し検証が通った場合だけCOMPLETE:<件数>へ進めます。
function saveSnapshot(sheet, rows) {
setSnapshotStatus(sheet, 'WRITING');
writeSnapshot(sheet, rows);
const saved = sheet.getDataRange().getValues();
const inspected = inspectSnapshot(saved);
if (!inspected.complete || inspected.count !== rows.length - 1) {
throw new Error('書き込み後の検証に失敗しました');
}
setSnapshotStatus(sheet, `COMPLETE:${inspected.count}`);
}
inspectSnapshot()では、メタデータだけでなく次も確認します。
- ヘッダーが想定どおりか
- 記事IDが空または重複していないか
- 集計値が0以上の整数か
- 合計行が末尾に1行だけあるか
- 各行の合計と合計行が一致するか
- 保存した全セルを読み戻した結果が元データと一致するか
完了フラグだけを先に書くと、その直後の失敗で不完全なデータを成功扱いしてしまいます。データを書き、flush()し、読み戻してから完了へ進める順序にしました。
当日の不完全なシートを置き換えるときは、先にシートをコピーして残します。過去日の集計値は現在のAPIレスポンスから正確に復元できないため、失敗時の観測値を消してしまわないためです。
欠けた前日を0として扱わない
今回、翌日の処理が成功しても、前日が不完全なら正しい前日比は作れません。
不足した記事を0件として計算すると、大きな増加に見えます。数式エラーを0へ置き換えても、レポートが読みやすくなるだけでデータの正確性は戻りません。
そこで前日のスナップショットが完全な場合だけIDで突き合わせ、不完全なら数値を出さず「比較不可」としました。
function buildRows(currentItems, previous) {
return currentItems.map((item) => {
const before = previous.complete
? previous.byId.get(String(item.id))
: null;
return [
item.id,
item.title,
item.count,
before ? item.count - before.count : '比較不可（前日データ欠損）',
];
});
}
タイトルではなくIDをキーにしたのは、タイトル変更や同名記事で別の記事を参照しないためです。前日に存在しない新規記事も、0から増えたと決めつけず比較不可にしています。
再実行は、保存とメールで別に考える
同じ時刻のトリガーと手動実行が重ならないよう、処理全体をScriptLockで囲みました。
function runDailySummary() {
const lock = LockService.getScriptLock();
if (!lock.tryLock(1000)) return;
try {
createAndSaveSnapshot();
sendMailOnce();
} finally {
lock.releaseLock();
}
}
保存済みの日次スナップショットが完全なら、再実行ではAPIを取り直さず、そのデータを再利用します。これにより、同じ日でも再実行時刻によって累積値が変わる問題を避けます。
メールにはScript PropertiesでSENDINGとSENTを持たせました。送信前にSENDINGを記録し、成功後にSENTへ変更します。
const props = PropertiesService.getScriptProperties();
const key = `DAILY_MAIL:${date}`;
const state = props.getProperty(key);
if (state === 'SENDING' || state === 'SENT') return;
props.setProperty(key, 'SENDING');
MailApp.sendEmail({ to, subject, body });
props.setProperty(key, 'SENT');
これは厳密なexactly-once配信ではありません。メール送信には成功したもののSENTの保存に失敗した場合、SENDINGが残ります。自動再送で二重メールになることを避ける代わりに、実行履歴と送信済みメールを確認して手動復旧する設計です。
6分へ到達する前に止める
処理開始から4分を超えた場合は、次のAPI取得、書き込み、メール送信へ進む前に停止するチェックも追加しました。
function checkBudget(startedAt, phase) {
if (Date.now() - startedAt > 240_000) {
throw new Error(`Time budget exceeded before ${phase}`);
}
}
このチェックは、実行中のUrlFetchApp.fetch()やスプレッドシート操作を中断できません。そのため6分以内の終了を保証するものではなく、新しい破壊的な工程へ入らないための余裕です。API取得だけで4分近くかかるなら、処理の分割や別の実行基盤も検討する必要があります。
モックテストで確認したこと
Apps ScriptのサービスをNode.jsのvm上でモック化し、26件の回帰テストを実行しました。主な確認内容は次のとおりです。
- ヘッダー、ID、数値、合計が一致する場合だけ完全と判定する
- 不完全な前日から誤った数値差分を作らない
- タイトル変更や同名記事でもIDで照合する
- HTTPエラー、不正JSON、重複ID、0件取得では保存しない
- 当日の不完全なシートをバックアップしてから1回だけ書く
- 書き込み内容が壊れた場合は
WRITINGのまま失敗する - 完全な当日データは再実行でも再取得・再書き込みしない
- ロック中は実行せず、送信結果が不明なメールを自動再送しない
テストでは、件数の異なる合成データを使って欠損日の比較も確認しています。実際の記事タイトル、URL、集計値はfixtureへ含めていません。
ここまででGitHub上のコード変更とモックテストは確認できています。一方、本番Apps Scriptへ反映した後の実行時間、数日間のトリガー実行、実メールの到達はこの記事の検証範囲に含めていません。短縮秒数や改善倍率も実測していないため記載していません。
注意点
今回の設計にも境界があります。
- シート書き込みはトランザクションではなく、書き込み中の瞬間を外部から見られる可能性がある
-
SENDINGが残った場合は、メールが未送信でも自動再送されない - Script Propertiesのキーは増えるため、運用ルールを決めて古いものを整理する必要がある
- 外部APIのページング中にデータ順が変わると、重複検出で保存を中止する場合がある
- 過去日の累積値は、現在のAPI値から正確に作り直せない
日次集計では、間違った数値を出すより「比較できない」と明示するほうを優先しました。
まとめ
Apps Scriptの6分タイムアウトを調べると、setValues()による一括書き込みが最初の改善候補になります。ただし、定期バッチでは速度だけ直しても、途中データや再実行の問題が残ります。
今回の見直しでは、次の順序を意識しました。
- APIデータを全件取得して検証する
- メモリ上で行を作り、まとめて書く
- 保存結果を読み戻してから完全と記録する
- 不完全な前日から差分を作らない
- ロックと状態で再実行・二重送信を制御する
タイムアウト通知が届いたときは、失敗した実行だけでなく、途中まで書かれた保存先と、そのデータを読む翌日の処理まで確認したほうがよいと思います。