Pulling 50 records from an API is easy. Pulling 50,000 records across hundreds of pages in Google Apps Script is a great way to watch your script crash mid-execution. Between the platform’s strict execution limits and external API rate limits, standard loops just won’t cut it. Let’s look at how to build a reliable pagination handler in Apps Script that handles both cursor and offset APIs without hitting a wall.
The Google Apps Script Pagination Bottleneck
When you build a basic Google Apps Script automation, you usually just fetch an endpoint, parse the JSON, and dump it into a sheet. That works fine until your dataset grows. To keep things fast, almost every modern API limits its responses to 50, 100, or 250 records per request.
To get everything, you have to paginate. But if you just run a basic loop to fetch pages one by one, you’ll run straight into three major Apps Script bottlenecks:
- The 6-minute execution limit: Standard Google accounts kill scripts that run longer than 6 minutes. Workspace accounts get 30 minutes, but a slow API can easily eat through that.
- Sheet write performance: If you append rows to a Google Sheet inside your loop, you’re making a network call every single time. It slows your script to a crawl.
- API rate limits: Fire off hundreds of rapid requests, and the target API will quickly shut you down with
429 Too Many Requestserrors.
To keep your script from crashing, you need to handle pagination in memory, batch your sheet writes, and build a way to pause and resume if you get close to that 6-minute limit.
Setting Up Your Script and Config
Before writing any loops, open a fresh Google Sheet, head to Extensions > Apps Script, and clear out the placeholder code.
We’ll use the built-in UrlFetchApp service for our requests. I like to define configuration variables at the very top of the script so I’m not hunting through loops later to change an API key.
const API_KEY = 'your_api_key_here';
const BASE_URL = 'https://api.example.com/v1/customers';
const LIMIT = 100; // Records per pageWith the config set up, let’s write the fetching logic. We’ll cover the two patterns you’ll run into most: cursor-based and offset-based pagination.
Handling Cursor-Based Pagination
Cursor-based pagination is what you’ll find on most modern APIs like Stripe, Slack, or GoHighLevel. Instead of asking for page 5, the API returns a cursor token (like starting_after or next_page_token) alongside your data. You have to pass this token back in your next request to get the next batch.
Here is how I set up a clean cursor loop in Apps Script. I use a do...while loop so the first request always runs, and it keeps going only as long as the API returns a valid next-page token.
function fetchAllCursorData() { let allRecords = []; let nextPageToken = null; let hasMore = true; let pageCounter = 1; do { // Build URL with cursor token if it exists let url = BASE_URL + '?limit=' + LIMIT; if (nextPageToken) { url += '&starting_after=' + nextPageToken; } const options = { method: 'get', headers: { 'Authorization': 'Bearer ' + API_KEY, 'Accept': 'application/json' }, muteHttpExceptions: true }; console.log('Fetching page ' + pageCounter + '...'); const response = UrlFetchApp.fetch(url, options); const statusCode = response.getResponseCode(); if (statusCode !== 200) { console.error('API Error: ' + response.getContentText()); break; } const json = JSON.parse(response.getContentText()); const records = json.data || []; // Merge new records into our master list allRecords = allRecords.concat(records); // Update cursor variables nextPageToken = json.next_page_token || null; hasMore = json.has_more && nextPageToken !== null; pageCounter++; } while (hasMore); console.log('Total records retrieved: ' + allRecords.length); return allRecords;
}This loop keeps spinning until has_more is false or the next_page_token disappears. We use the standard JS Array concat method to merge each new batch into our main in-memory array.
Handling Offset-and-Limit Pagination
Older APIs and database wrappers usually rely on offset-and-limit pagination. You tell the API how many records to skip (the offset) and how many to return (the limit). Usually, the API returns a total_count so you know exactly when to stop.
This pattern is straightforward because you can calculate the total number of requests before you even start, though it can get slow on the database side once offsets get really high.
function fetchAllOffsetData() { let allRecords = []; let offset = 0; let totalRecords = null; let pageCounter = 1; while (totalRecords === null || offset < totalRecords) { const url = BASE_URL + '?limit=' + LIMIT + '&offset=' + offset; const options = { method: 'get', headers: { 'Authorization': 'Bearer ' + API_KEY }, muteHttpExceptions: true }; console.log('Fetching offset ' + offset + ' (Page ' + pageCounter + ')...'); const response = UrlFetchApp.fetch(url, options); if (response.getResponseCode() !== 200) { console.error('Failed to fetch offset ' + offset); break; } const json = JSON.parse(response.getContentText()); const records = json.results || []; allRecords = allRecords.concat(records); // Set total count on the first run if (totalRecords === null) { totalRecords = json.total_count || 0; console.log('Total records to fetch: ' + totalRecords); } offset += LIMIT; pageCounter++; } return allRecords;
}When you sync Google Sheets data to external APIs, you’ll often need wrapper functions like this to pull down your source data cleanly before you start pushing updates.
Beating the 6-Minute Execution Limit
If your API has 10,000 records and takes 500ms per request, fetching everything takes under a minute. No big deal. But if the API is slow (say, 2 seconds per request) or you have 100,000 records, your script will hit the 6-minute wall and die mid-execution, leaving you with a half-empty sheet and no idea where you left off.
To fix this, we can save our progress (the last cursor we used) in the PropertiesService. By tracking the elapsed execution time, we can stop the script around the 5-minute mark, save our state, set up a time-based trigger to run the script again, and exit gracefully.
function fetchLargeDatasetWithTimeout() { const startTime = new Date().getTime(); const maxExecutionTimeMs = 5 * 60 * 1000; // 5 minutes safety limit const properties = PropertiesService.getScriptProperties(); // Resume from where we left off, or start fresh let cursor = properties.getProperty('LAST_CURSOR') || null; let isFinished = false; let batchRecords = []; while (!isFinished) { // Check if we are running out of time const currentTime = new Date().getTime(); if (currentTime - startTime > maxExecutionTimeMs) { console.warn('Approaching timeout limit. Saving state and scheduling resume...'); properties.setProperty('LAST_CURSOR', cursor); // Save accumulated data to a temp sheet or append it before exiting writeToSheet(batchRecords, true); setupResumeTrigger(); return; } let url = BASE_URL + '?limit=' + LIMIT; if (cursor) { url += '&starting_after=' + cursor; } const response = UrlFetchApp.fetch(url, { headers: { 'Authorization': 'Bearer ' + API_KEY }, muteHttpExceptions: true }); if (response.getResponseCode() !== 200) break; const json = JSON.parse(response.getContentText()); const records = json.data || []; batchRecords = batchRecords.concat(records); cursor = json.next_page_token || null; if (!cursor) { isFinished = true; } } // If we finished successfully, clear properties and triggers properties.deleteProperty('LAST_CURSOR'); clearResumeTriggers(); writeToSheet(batchRecords, false); console.log('Sync complete!');
} function setupResumeTrigger() { const triggers = ScriptApp.getProjectTriggers(); if (triggers.length === 0) { ScriptApp.newTrigger('fetchLargeDatasetWithTimeout') .timeBased() .after(1 * 60 * 1000) // Run again in 1 minute .create(); }
} function clearResumeTriggers() { const triggers = ScriptApp.getProjectTriggers(); for (let i = 0; i < triggers.length; i++) { ScriptApp.deleteTrigger(triggers[i]); }
}By scheduling that trigger, the script starts up again in a fresh execution context with a brand-new 6-minute window. This lets you fetch millions of records across multiple runs without losing your place.
Writing Data to Google Sheets Without Slowing to a Crawl
One of the biggest mistakes I see is calling sheet.appendRow() inside the pagination loop. Every single call to the Sheets API carries network overhead. If you call it 500 times in a loop, your script spends 90% of its time waiting on sheet updates.
Instead, parse all your paginated data into a single 2D array in memory first, then write it to the sheet in a single call using setValues(). This drops your write times from minutes to milliseconds.
function writeToSheet(records, appendMode) { if (records.length === 0) return; const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Map JSON objects to 2D array matching your columns const rows = records.map(record => [ record.id || '', record.email || '', record.created_at || '', record.status || '' ]); if (appendMode) { // Find the last row and append the block const lastRow = sheet.getLastRow(); const range = sheet.getRange(lastRow + 1, 1, rows.length, rows[0].length); range.setValues(rows); } else { // Clear and overwrite the entire sheet sheet.clearContents(); // Add headers sheet.getRange(1, 1, 1, 4).setValues([['ID', 'Email', 'Created At', 'Status']]); // Write data const range = sheet.getRange(2, 1, rows.length, rows[0].length); range.setValues(rows); }
}This batch-writing approach keeps execution times low and respects Google’s internal limits. If you’re dealing with concurrent writes from multiple triggers, you’ll also want to prevent race conditions using LockService so you don’t overwrite your own data.
Handling API Rate Limits and Backoffs
If you’re fetching data rapidly, you’ll eventually hit a rate limit. Most APIs will throw a 429 Too Many Requests status code when you cross the line. If you ignore this and keep hammering their servers, you risk getting your access token temporarily blocked.
We can handle this by writing a simple exponential backoff wrapper. If we get a 429, we pause the execution using Utilities.sleep() and retry. If it fails again, we double the wait time and try once more.
function fetchWithRetry(url, options, retries = 3, delayMs = 1000) { for (let i = 0; i < retries; i++) { const response = UrlFetchApp.fetch(url, options); const statusCode = response.getResponseCode(); if (statusCode === 200) { return response; } if (statusCode === 429) { console.warn('Rate limit hit. Retrying in ' + delayMs + 'ms...'); Utilities.sleep(delayMs); delayMs *= 2; // Exponential backoff continue; } // For other errors, log and stop immediately console.error('Fetch failed with status ' + statusCode); break; } return null;
}This helper replaces your raw UrlFetchApp.fetch() calls. It keeps your loops running through temporary network hiccups and sudden API spikes. If you're running high-volume production setups and Apps Script is feeling too restrictive, you might want to look into how to handle external API rate limits in a dedicated backend environment.
Frequently Asked Questions
How do I know if my API uses cursor or offset pagination?
Check the API docs. If you see query parameters like page, offset, or skip, it’s offset-based. If you see parameters like starting_after, ending_before, next_cursor, or pageToken, it’s cursor-based.
Why does Apps Script say “Exceeded maximum execution time”?
Your script ran past the 6-minute limit (or 30 minutes on a Workspace account). You’ll need to implement the PropertiesService state-saving logic we covered above to split your work across multiple triggered runs.
Can I write data to the sheet row by row if I have a small dataset?
You can, but it’s a bad habit. Even for 50 rows, batch writing with setValues() is significantly faster and keeps you safe from hitting Google’s daily quota limits on spreadsheet operations.
Next Steps for Your Automation
Now that you can fetch paginated data without crashing, you can start building more complex data sync pipelines. Try expanding this setup to push your cleaned rows directly into external tools. For a complete walkthrough on pushing data back out, check out our guide on how to sync Google Sheets data to external APIs.
