Originally written for bulldo.gs — republished here with the canonical link pointing home.
I'm running a script that processes a large spreadsheet and it keeps dying with "Exceeded maximum execution time" before it finishes.
// Checkpoint-resume pattern for long-running sheet jobs
function processInBatches() {
var props = PropertiesService.getScriptProperties();
var startRow = parseInt(props.getProperty('lastRow') || '2', 10);
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastDataRow = sheet.getLastRow();
var BATCH = 200;
var SAFE_MS = 5 * 60 * 1000;
var started = Date.now();
var endRow = Math.min(startRow + BATCH - 1, lastDataRow);
var data = sheet.getRange(startRow, 1, endRow - startRow + 1, 5).getValues();
for (var i = 0; i < data.length; i++) {
if (Date.now() - started > SAFE_MS) {
props.setProperty('lastRow', String(startRow + i));
return;
}
// process data[i] here
}
if (endRow >= lastDataRow) {
props.deleteProperty('lastRow');
deleteTrigger_();
} else {
props.setProperty('lastRow', String(endRow + 1));
}
}
The 6-minute wall is per-execution, not per-task
Apps Script enforces a hard 6-minute execution time limit per run, regardless of whether you're on a free account or a Workspace account (which bumps the limit to 30 minutes, but the same cliff exists). The error doesn't mean your logic is wrong; it means one continuous call to your function took too long. The fix is to stop thinking of your job as a single execution and start thinking of it as a pipeline of short runs.
The first time I hit this, I wasted an afternoon trying to speed up the loop. Marginal gains didn't move the needle because the data volume was the real problem — 4,000 rows at one Sheets API call per row will always breach 6 minutes. The correct frame is: how do I save where I stopped and pick up there next run?
Saving and restoring a cursor with PropertiesService
PropertiesService.getScriptProperties() gives you a small key-value store that persists between executions (up to 9 KB total, 500 bytes per value). Store the last-processed row number there before you return. On the next run, read it back and start from that row. When the job finishes, delete the property so subsequent manual runs start from row 2 again.
The safety check in the snippet above — comparing Date.now() minus the start time against 5 minutes (SAFE_MS) — gives the function 60 seconds of headroom before the hard kill. Without that buffer, the process can be mid-write when the runtime terminates it, leaving a corrupt checkpoint. Set SAFE_MS to 5 * 60 * 1000 and you will never lose a checkpoint to a race condition.
To keep the job moving automatically, install a time-driven trigger pointing at the same function: ScriptApp.newTrigger('processInBatches').timeBased().everyMinutes(1).create(). The function runs every minute, advances the cursor, and returns early when time is short. Once the last row is reached, the cleanup branch calls deleteTrigger_() — a small helper that finds and deletes the trigger by handler name — so the job doesn't loop forever.
Batch your reads first — it often eliminates the problem
Before wiring up checkpointing, check whether your script is calling getValue() or getRange().getValue() inside a loop. Each individual call to the Sheets API costs roughly 50–100 ms in round-trip overhead. 500 rows at one call each is already 25–50 seconds; 3,000 rows blows past 6 minutes without a single line of real work done.
The fix is one getValues() call outside the loop: sheet.getRange(startRow, 1, rowCount, columnCount).getValues() returns a 2D array you can iterate at memory speed. In practice, switching from per-row getValue() to a single getValues() on 2,000 rows drops execution time from 3–4 minutes to under 10 seconds. That alone closes the issue for most scripts.
The snippet batches reads to 200 rows at a time partly as a defensive ceiling — even with getValues(), very wide sheets with complex data can be slow — and partly to keep each execution well inside the safe zone. Tune BATCH upward if your sheet is narrow; 500 is a safe ceiling for a 10-column sheet.
FAQ
Does the 6-minute limit reset if I call Utilities.sleep()?
No. sleep() counts against your execution time the same as any other instruction. Sleeping inside a single execution to avoid rate limits still burns the clock. The only way to get more wall-clock time is to end the execution and schedule a new one via a time-driven trigger.
Will a Workspace (paid) account give me more than 6 minutes?
Yes — Workspace accounts get a 30-minute limit per execution. But the same cliff exists; a job processing tens of thousands of rows will still hit it. The checkpoint pattern works identically at 30 minutes; just set SAFE_MS to 28 * 60 * 1000.
How do I delete the trigger when the job finishes?
Iterate ScriptApp.getProjectTriggers(), check trigger.getHandlerFunction() === 'processInBatches', and call ScriptApp.deleteTrigger(trigger). Wrap this in a private helper function named deleteTrigger_ (the underscore makes it uncallable from the UI). Failing to delete it means the function keeps firing every minute indefinitely, which burns your daily trigger quota (20 trigger-hours/day for free accounts).
My script writes results back to the sheet — how do I avoid writing duplicates on resume?
Write to the same row index you read from. Because startRow is restored from the checkpoint before any reads or writes happen, the output range for a given run starts exactly where the last run stopped. There is no window where a row gets processed twice, as long as you update the checkpoint before returning and never update it after a write failure.
Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.
