Fetch API Data into Excel with Python requests

A surprising share of reporting data has no database behind it: exchange rates, a payment provider's settlements, a project tracker, a marketing platform's numbers. All of it arrives as JSON over HTTP, and all of it eventually has to become columns in a workbook that someone opens on a Monday.

The mechanics are not hard. What makes the difference between a script that works while you watch it and one that can run unattended is the boilerplate around the call: a timeout so a slow endpoint cannot hang the job, a retry policy that distinguishes a rate limit from a bad request, a pagination loop that terminates, and a cache so re-running does not re-download. This guide is part of Moving Data Between Excel and Databases.

Prerequisites

pip install requests pandas openpyxl

You also need an endpoint and, usually, a token. Keep the token in an environment variable — a key pasted into the script is a key that ends up in version control, in a screenshot and in a ticket.

Step 1: Configure the session once

A Session reuses the TCP connection across requests and holds the headers and retry policy in one place:

import os

import requests
from requests.adapters import HTTPAdapter
from urllib3.util.retry import Retry

TIMEOUT = (5, 30)          # (connect, read) seconds


def make_session(token=None):
    session = requests.Session()
    session.headers.update({
        "Accept": "application/json",
        "User-Agent": "monthly-report/1.0",
        "Authorization": f"Bearer {token or os.environ['API_TOKEN']}",
    })
    retry = Retry(
        total=4,
        backoff_factor=1.5,                       # 0s, 1.5s, 3s, 6s
        status_forcelist=(429, 500, 502, 503, 504),
        allowed_methods=frozenset({"GET"}),
        respect_retry_after_header=True,
    )
    session.mount("https://", HTTPAdapter(max_retries=retry))
    return session

The status_forcelist encodes the only retry rule that matters: 429 means "you are going too fast" and 5xx means "we are broken", both of which may succeed unchanged in a few seconds. A 400 or a 401 will fail identically forever, so retrying them only delays the alert — the same distinction made in Retry a Failed Excel Report Job in Python.

respect_retry_after_header=True makes the client honour the API's own backoff instruction instead of guessing, which is what keeps a job on the right side of a rate limit.

Step 2: Page until the API says stop

Nearly every paginated API uses one of three schemes — a cursor, a page number, or an offset. All three need the same two guards: stop on an empty page, and cap the number of pages so a broken cursor cannot loop forever.

def fetch_all(session, url, params=None, max_pages=200):
    """Follow cursor pagination and return every record."""
    params = dict(params or {})
    records, pages, cursor = [], 0, None

    while pages < max_pages:
        if cursor:
            params["cursor"] = cursor
        response = session.get(url, params=params, timeout=TIMEOUT)
        response.raise_for_status()
        payload = response.json()

        batch = payload.get("data", [])
        if not batch:
            break
        records.extend(batch)
        pages += 1

        cursor = (payload.get("meta") or {}).get("next_cursor")
        if not cursor:
            break
    else:
        raise RuntimeError(f"stopped after {max_pages} pages — is the cursor advancing?")

    return records

raise_for_status() is the line that separates data from an error page. Without it, an HTML "503 Service Unavailable" body reaches response.json(), raises a JSON decode error, and sends you looking at your parsing code instead of at the endpoint. The while ... else clause runs only when the loop exhausts max_pages without breaking, which turns an infinite-pagination bug into an immediate, named failure.

Step 3: Flatten the JSON into columns

API responses nest; spreadsheets do not. pd.json_normalize bridges the two, and its two important arguments are record_path (the list that becomes rows) and meta (the parent fields repeated onto each row):

import pandas as pd

payload = [
    {"invoice": "INV-001", "customer": {"id": 7, "name": "Acme"},
     "lines": [{"sku": "A-1", "qty": 2, "net": 40.0},
               {"sku": "B-3", "qty": 1, "net": 15.5}]},
    {"invoice": "INV-002", "customer": {"id": 9, "name": "Globex"},
     "lines": [{"sku": "A-1", "qty": 5, "net": 100.0}]},
]

df = pd.json_normalize(
    payload,
    record_path="lines",                       # one row per line
    meta=["invoice", ["customer", "name"]],    # repeated on every line
)
df = df.rename(columns={"customer.name": "customer"})
print(df)
#    sku  qty    net  invoice customer
# 0  A-1    2   40.0  INV-001     Acme
# 1  B-3    1   15.5  INV-001     Acme
# 2  A-1    5  100.0  INV-002   Globex

Nested keys arrive as dotted names — customer.name — which are legal DataFrame columns but ugly headings, so rename them before writing. When a field is sometimes missing, json_normalize fills NaN rather than raising, so check for unexpected nulls afterwards instead of assuming every record had the key.

Step 4: Type the columns before they reach Excel

JSON has no date type and often no numeric type either — amounts arrive as strings, timestamps as ISO text. Convert explicitly, or Excel receives text that looks right and refuses to sum:

df["net"] = pd.to_numeric(df["net"], errors="coerce")
df["fetched_at"] = pd.Timestamp.now().floor("s")

with pd.ExcelWriter("invoices.xlsx", engine="openpyxl",
                    datetime_format="yyyy-mm-dd hh:mm") as writer:
    df.to_excel(writer, sheet_name="Lines", index=False)
    ws = writer.sheets["Lines"]
    for cell in ws["C"][1:]:                 # the net column
        cell.number_format = '#,##0.00'
    ws.freeze_panes = "A2"

The fetched_at column is worth the one line. API data is a snapshot, and a workbook that does not say when it was taken will be treated as current for as long as it exists on someone's desktop.

Step 5: Cache responses while you are developing

Iterating on the parsing while hitting a live rate-limited endpoint is slow and rude. A file cache keyed by the request makes the second run instant:

import hashlib
import json
from pathlib import Path

CACHE = Path(".api-cache")


def cached_get(session, url, params=None, max_age_hours=6):
    CACHE.mkdir(exist_ok=True)
    key = hashlib.sha256(f"{url}?{sorted((params or {}).items())}".encode()).hexdigest()
    path = CACHE / f"{key}.json"

    if path.is_file():
        age_hours = (pd.Timestamp.now().timestamp() - path.stat().st_mtime) / 3600
        if age_hours < max_age_hours:
            return json.loads(path.read_text())

    response = session.get(url, params=params, timeout=TIMEOUT)
    response.raise_for_status()
    payload = response.json()
    path.write_text(json.dumps(payload))
    return payload

Hashing the URL and the sorted parameters means two calls that differ only in parameter order share a cache entry, which is what you want. Add .api-cache/ to .gitignore, and keep the cache out of the scheduled run — or set max_age_hours low enough that a daily report can never serve yesterday's data.

Common pitfalls and gotchas

Performance and scale notes

The wall-clock cost of an API export is nearly all latency: 200 pages at 300 ms each is a minute, and no amount of pandas tuning changes that. Ask for larger pages if the API allows it, request only the fields you need, and use an incremental filter — updated_since — so a daily job fetches a day rather than the whole history.

If the API supports concurrency and permits it, a small thread pool of four to eight workers over independent page ranges cuts the time proportionally. Keep it small: a job that trips the rate limit spends longer backing off than it saved. Where the same data feeds several reports, fetch once into a database or a Parquet file and let the reports read from there rather than each calling the API.

Conclusion

The API call itself is one line; everything that makes it survivable is around it. Configure one session with an auth header, a timeout and a retry policy that distinguishes 429 and 5xx from a bad request. Page with two guards so the loop always terminates. Flatten with json_normalize, rename the dotted columns, coerce the types, and stamp the workbook with the time the snapshot was taken. Cache during development so iteration costs nothing and the endpoint is left alone.

Frequently asked questions

Why does my scheduled job hang forever some mornings? A request with no timeout. requests waits indefinitely by default, so one unresponsive endpoint blocks the job until someone notices. Always pass timeout, and prefer a tuple of connect and read timeouts.

How do I flatten nested JSON into spreadsheet columns?pd.json_normalize, with record_path for the list you want as rows and meta for the parent fields to repeat on each row. It produces dotted column names you can rename afterwards.

Should I retry a failed API call? Retry 429 and 5xx responses with backoff; never retry a 400 or 401, which will fail identically. urllib3's Retry adapter does this for you at the session level.

How do I avoid re-downloading the same data during development? Cache each response to a file keyed by the request, and read the cache when it exists. It makes iteration fast and keeps you inside the rate limit.

Related

Up to the parent guide:

• Moving Data Between Excel and Databases — where API data fits alongside SQL sources.

Related guides:

• Export SQL Query Results to Excel with Python — the same workbook-writing step from a database source.
• Retry a Failed Excel Report Job in Python — the retry rules this session policy encodes.
• Refresh an Excel Report from a Database on a Schedule — running the fetch unattended.
• Merge Two Excel Files on a Common Column in Python — joining fetched reference data onto your own.