How to Export API JSON Data to Excel

Step 1: Get the JSON Response

How you capture the response depends on the API:

• Public GET endpoints: open the URL in a browser. Firefox shows a JSON viewer with a Save button; in other browsers, use Save Page As or copy the text.
• APIs that need a key or token: use Postman, Insomnia or a similar client, send the request with your credentials, then save the response body.
• Command line:

curl -H "Authorization: Bearer $API_TOKEN" \
     "https://api.example.com/v1/orders?limit=100" \
     -o orders.json

Keeping the token in an environment variable rather than typing it into the command avoids leaving it in your shell history. Never paste API keys into online tools; the response body is all you need to convert.

Step 2: Find the Records Inside the Response

A typical paginated API response looks like this:

{
  "data": [
    { "id": "ord_81", "total": 49.9, "status": "paid", "customer": { "email": "[email protected]" } },
    { "id": "ord_82", "total": 15, "status": "refunded", "customer": { "email": "[email protected]" } }
  ],
  "meta": { "page": 1, "per_page": 100, "total": 2340 },
  "links": { "next": "https://api.example.com/v1/orders?page=2" }
}

Only data holds the rows you want. meta and links describe the response. Common names for the records array include data, results, items, records, entries and rows, but any name is possible, and some APIs nest it deeper (response.body.items).

If you convert the whole response as-is, you get one row with columns like data, meta.page and links.next, which isn't useful.

Step 3: Convert the Records to Excel

Using an online JSON to Excel converter:

1. Paste the response or upload the saved file.
2. The converter scans the JSON and lists the arrays of records it finds, such as data, with the number of records in each. Pick the one you want.
3. Choose how to handle nested fields. In the example, customer.email flattens into a column; arrays of line items can go to their own sheet. How to convert nested JSON to Excel explains the options.
4. Remove columns you don't need, rename the rest, and download the .xlsx.

The conversion runs in your browser, so the response isn't uploaded to a server, which matters when API data includes customer details.

Dealing With Pagination

Most APIs return a limited number of records per request. To export everything, you need every page:

• Page or offset parameters: ?page=2, ?offset=200.
• Cursor links: follow links.next or a next_cursor value until it's empty.

For a handful of pages, save each response and combine the record arrays. One approach is JSON Lines: put each record on its own line in a .jsonl file, which converters can read directly. With jq installed:

jq -c '.data[]' page1.json page2.json page3.json > orders.jsonl

That command extracts every record from each page's data array and writes one record per line. For hundreds of pages, a short script or Power Query is more practical than doing it by hand.

Refreshable Exports With Power Query

If you'll need the same export every week, let Excel fetch it:

1. Data → Get Data → From Other Sources → From Web.
2. Enter the API URL. Use the Advanced option to add headers such as an API key, if the API requires one.
3. In the Power Query editor, drill into the records list (for example data), convert it to a table and expand the columns.
4. Close & Load. Next time, click Refresh.

Power Query can also loop through pages with custom M code, though that takes more setup. Check your organisation's policy before storing API credentials in a workbook that others can open.

Keeping Data Types Intact

API data often includes values that spreadsheets mangle:

• IDs that look numeric but have leading zeros or more than 15 digits should stay as text.
• Timestamps usually arrive as ISO 8601 strings (2026-10-07T09:30:00Z). Excel treats them as text until you convert them, and the trailing Z means UTC, so adjust for your time zone if needed.
• Money may be given in minor units (cents) as integers. Check the API docs before summing.
• null vs missing both end up as empty cells, which is usually what you want.

JSON vs CSV vs Excel explains why exporting to CSV loses even more of this type information.

Summary

Save the response, find the records array inside the wrapper, combine pages if needed, and convert with sensible handling of nested fields and IDs. Use Power Query when the export needs to be refreshed regularly.