Fetch Paginated API Data into Excel

Almost every API that returns lists — orders from a shop platform, tickets from a helpdesk, invoices from an accounting system — returns them a page at a time: 50 or 100 records per request, with some way to ask for the next page. A script that calls the endpoint once and writes the result to Excel silently exports only the first page, and the report looks complete. Fetching everything means looping until the API says there is nothing more, and doing it robustly means coping with the three things that go wrong on long runs: rate limits, temporary server errors, and records that shift between pages while you read. This page builds a fetcher for the common pagination styles with requests, flattens the JSON with pandas, and checks the result before writing the workbook. It belongs to Moving Data Between Excel and Databases.

Prerequisites

pip install requests pandas openpyxl

The examples call an orders API with 237 orders, exposed in two styles: /v1/orders with page numbers and a total count, and /v2/orders with cursor pagination and a next link. To keep the page self-contained, the outputs were produced against a small local stand-in for the API (its address is in BASE) that also returns one 429 Too Many Requests and one 503 along the way, as real APIs do. Replace BASE and add your authentication header to use the code with a real service.

Step 1 — A session that retries politely

Every request goes through one requests.Session with a timeout, and a small wrapper that waits and retries when the server says "too many requests" or has a temporary failure:

import time
import requests

session = requests.Session()
session.headers.update({"Accept": "application/json"})      # add Authorization here

def get_json(url, params=None, retries=5):
    for attempt in range(retries):
        resp = session.get(url, params=params, timeout=30)
        if resp.status_code in (429, 500, 502, 503, 504):
            wait = float(resp.headers.get("Retry-After", 2 ** attempt))
            print(f"  {resp.status_code} — waiting {wait:.0f}s before retry {attempt + 1}")
            time.sleep(wait)
            continue
        resp.raise_for_status()
        return resp.json()
    raise RuntimeError(f"gave up on {url} after {retries} attempts")

429 means the client is calling too fast; the Retry-After header says how many seconds to wait, and honouring it is both polite and the fastest way to get going again. The 5xx codes in the list are usually temporary, so they get the same treatment, with exponential back-off when no header is given. Every other error — 401 for a bad token, 404 for a wrong URL — is raised immediately by raise_for_status(), because retrying will not fix it. The timeout prevents a stalled connection from hanging the job forever. A general retry pattern for whole jobs is in Retry a Failed Excel Report Job in Python.

Step 2 — Page-number pagination

The simplest style takes a page number and a page size, and often reports the total. Loop until a page comes back short or empty:

def fetch_pages(url, per_page=50):
    records, page = [], 1
    while True:
        body = get_json(url, params={"page": page, "per_page": per_page})
        batch = body["data"]
        records.extend(batch)
        print(f"page {page}: {len(batch)} records")
        if len(batch) < per_page:
            return records, body.get("meta", {}).get("total")
        page += 1

orders_v1, reported_total = fetch_pages(f"{BASE}/v1/orders")
print(len(orders_v1), "records fetched; API reports", reported_total)

Five pages of up to 50 records cover all 237 orders, and the loop stops when the fifth page returns only 37. Stopping on a short page is more reliable than computing the number of pages from the total up front, because the total can change while you fetch. The 429 from the stand-in appears in the output as a wait and a retry, and the fetch carries on. When the API offers it, request the largest page size it allows: fewer requests mean fewer chances to be rate-limited and a faster export. Offset-based APIs (offset=0, 50, 100 …) work the same way, with the offset increasing by the page size instead of the page number by one.

Step 3 — Cursor pagination with next links

Many modern APIs use cursors instead: each response includes a token or a full next URL pointing to the page after the last record you received. Follow it until the API says there is no more:

def fetch_cursor(first_url):
    records, url = [], first_url
    while url:
        body = get_json(url if url.startswith("http") else BASE + url)
        records.extend(body["data"])
        url = body.get("next") if body.get("has_more") else None
    return records

orders_v2 = fetch_cursor(f"{BASE}/v2/orders?limit=100")
print(len(orders_v2), "records via cursor; first id", orders_v2[0]["id"], "last id", orders_v2[-1]["id"])

Cursor pagination is the more reliable style: because each request asks for records after the last one seen, a new order inserted while you fetch does not shift the remaining pages, and nothing is skipped or repeated. Some APIs return the next link in the HTTP Link header instead of the body; requests parses that into resp.links["next"]["url"]. Either way the loop is the same: follow the pointer until there is none.

Step 4 — Flatten the JSON into a table

API records are nested — here, a total with amount and currency, a customer object and a list of order lines. pd.json_normalize flattens nested objects into dotted columns; lists need a decision:

import pandas as pd

df = pd.json_normalize(orders_v2)
df["created"] = pd.to_datetime(df["created"]).dt.tz_convert("Europe/Berlin").dt.tz_localize(None)
df["line count"] = df["lines"].str.len()
df = df.drop(columns="lines").rename(columns={"total.amount": "amount", "total.currency": "currency",
                                              "customer.id": "customer", "customer.country": "country"})
print(df.dtypes.to_dict())
print(df.head(3).to_string(index=False))

lines = pd.json_normalize(orders_v2, record_path="lines", meta=["id"])
print(lines.head(3).to_string(index=False))

json_normalize turns total.amount and customer.country into ordinary columns. The order lines are a list per order, which cannot sit in one cell sensibly, so the orders table keeps only a count, and a second call with record_path="lines" produces a separate lines table with the order ID repeated — the shape you would put on a second sheet. Timestamps arrive in UTC with a Z suffix; converting them to local time and dropping the zone is necessary because Excel has no time zones and openpyxl refuses zone-aware datetimes. Nested JSON in general is covered in Convert an Excel Sheet to JSON with Python, which goes the other way.

Step 5 — Check completeness and write the workbook

Before writing, check the export is whole: no duplicate IDs, and as many records as the API reported. Then write the two tables:

dupes = df["id"].duplicated().sum()
print("duplicates:", dupes, "| fetched:", len(df), "| expected:", reported_total)
assert dupes == 0 and len(df) == reported_total, "incomplete or inconsistent export"

with pd.ExcelWriter("orders_from_api.xlsx", engine="openpyxl") as xw:
    df.to_excel(xw, sheet_name="Orders", index=False)
    lines.to_excel(xw, sheet_name="Lines", index=False)
print("written", len(df), "orders and", len(lines), "lines")

The duplicate check catches the classic page-number problem — a record that moved from page 2 to page 3 between requests and was fetched twice — and if duplicates are possible, drop_duplicates("id", keep="last") keeps the most recent copy. Comparing the count with the API's reported total catches the opposite problem, a skipped page. Failing loudly is better than writing a workbook that silently misses records, because the people reading it have no way to tell. For daily exports, store the newest created timestamp and ask the API only for records after it next time, so each run fetches the changes rather than everything.

Common pitfalls

• Calling the endpoint once. You get the first page only, and nothing tells you.
• Ignoring 429 responses. Honour Retry-After instead of retrying immediately.
• Retrying 401 or 404. These will not succeed on retry; fail fast.
• Page numbers on changing data. Records can be skipped or repeated; prefer cursors and de-duplicate.
• Zone-aware timestamps. Convert to local time and drop the zone before writing Excel.

Conclusion

To get a complete paginated API result into Excel, loop until the API says there is no more — stopping on a short page for page and offset styles, following the next link or cursor until has_more is false for cursor styles — through a session that honours Retry-After on 429 and retries temporary 5xx errors. Flatten the records with json_normalize, put nested lists on their own sheet, convert timestamps to local naive datetimes, and check for duplicates and against the API's total before writing the workbook.

Frequently asked questions

How do I get all pages from an API in Python? Request the first page, then keep requesting the next one — by page number, offset or the next link the API returns — until a page is short, empty, or the API says there are no more results.

How do I handle API rate limits when exporting to Excel? When the API returns 429 Too Many Requests, wait for the number of seconds in the Retry-After header before retrying, and use the largest page size allowed to make fewer requests.

How do I turn nested API JSON into Excel columns? Use pandas json_normalize, which turns nested objects into dotted columns. Put nested lists into a separate table with the record_path option.

Why does my API export have duplicate rows? With page-number pagination, records can move between pages while you fetch. Prefer cursor pagination if available and de-duplicate on the record ID.

Related

• Up one level: Moving Data Between Excel and Databases — getting data in and out.
• Fetch API Data into Excel with Python requests — the single-request basics.
• Cache API Responses When Building Excel Reports — avoiding repeat fetches.
• Handle Microsoft Graph Throttling for Excel — rate limits in one specific API.