List endpoints rarely return everything in one response. Production syncs must follow next_cursor or paging.next.
syncAllOrders clears Orders, loops pages with limit/cursor query params, and stamps each row with the page number for debugging.
MAX_PAGES prevents runaway loops if the API misbehaves; sleep(200) softens rate limits.
Adjust field names to match your vendor — some use page tokens instead of cursors.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Item | Value | Purpose |
|---|---|---|
| API_BASE | …/v1/orders | List endpoint |
| PAGE_SIZE | 100 | limit query param |
| MAX_PAGES | 20 | Safety cap |
| Orders | A–E | Flattened orders + page |
| Property | API_TOKEN | Bearer auth |
What this script does
The do/while loop fetches until cursor is null or MAX_PAGES is hit, then one setValues writes all rows.
Prerequisites
Token property; Orders sheet; knowledge of the vendor’s pagination fields.
- Confirm cursor vs offset pagination
- Know rate limits
- Estimate total pages vs execution time
Walkthrough
Run syncAllOrders on a small account; verify page column increments and last page has fewer than PAGE_SIZE rows.
Edge cases
If the API returns the same cursor forever, MAX_PAGES stops the loop — log a warning when capped.
- clearContents wipes prior sync
- Offset pagination uses page=n instead of cursor
Rate limits
Respect Retry-After headers when present. The fixed 200ms sleep is a starting point — back off harder on 429.
How to test
Temporarily set PAGE_SIZE=2 and MAX_PAGES=3; expect at most 6 rows.
Hardening for production
Checkpoint cursor in Properties between executions for huge catalogs (see continuation patterns).
Variations
Parallelize independent resources carefully; usually sequential is safer for rate limits.
Full code: syncAllOrders()
Set API_TOKEN and API_BASE, then run syncAllOrders(). Lower PAGE_SIZE while testing.
/**
* Walk cursor / page-token pagination until exhausted or MAX_PAGES.
*/
const API_BASE = "https://api.example.com/v1/orders";
const MAX_PAGES = 20;
const PAGE_SIZE = 100;
const DEST = "Orders";
function syncAllOrders() {
const token = PropertiesService.getScriptProperties().getProperty("API_TOKEN");
const sheet = SpreadsheetApp.getActive().getSheetByName(DEST);
sheet.clearContents();
sheet.appendRow(["id", "total", "currency", "createdAt", "page"]);
let cursor = null;
let page = 0;
const all = [];
do {
page++;
const qs = "?limit=" + PAGE_SIZE + (cursor ? "&cursor=" + encodeURIComponent(cursor) : "");
const resp = UrlFetchApp.fetch(API_BASE + qs, {
headers: { Authorization: "Bearer " + token, Accept: "application/json" },
muteHttpExceptions: true,
});
if (resp.getResponseCode() >= 300) {
throw new Error("Page " + page + " failed: " + resp.getContentText().slice(0, 400));
}
const json = JSON.parse(resp.getContentText());
const batch = json.data || [];
batch.forEach(function (o) {
all.push([o.id, o.total, o.currency, o.created_at, page]);
});
cursor = json.next_cursor || (json.paging && json.paging.next) || null;
Utilities.sleep(200);
} while (cursor && page < MAX_PAGES);
if (all.length) {
sheet.getRange(2, 1, all.length + 1, 5).setValues(all);
}
Logger.log("Synced %s orders across %s pages", all.length, page);
}- Line 2: Hard stop against infinite pagination loops.
- Line 2: Holds next_cursor between requests.
- Line 21: Safely appends cursor to the query string.
- Line 34: Vendor-specific; also checks paging.next.
- Line 35: Small delay to reduce 429s.
- Line 39: Writes all collected rows in one batch.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: pagination sync
- 1Confirm pagination field names from API docs
- 2API_TOKEN configured
- 3MAX_PAGES * PAGE_SIZE fits runtime limits
- 4Orders sheet can be cleared
- 5Handle 429 with backoff in production
- 6Test with tiny PAGE_SIZE first
Frequently asked questions
Cursors are opaque tokens; page numbers are integers. This sample is cursor-style but easy to adapt to page=.
Full refresh avoids deleted-order drift. For incremental sync, filter by updated_at instead.
Raise the cap, or persist the cursor and continue in another execution.
Yes — append after each page to reduce memory; keep headers intact.
Empty batch, null cursor, or batch.length < PAGE_SIZE depending on the API.
Each fetch has its own timeout; total script time still caps the whole sync.
Pass endCursor into the next query variables the same way.