Working with JSON APIs: Extract and Export Data to Excel

REST APIs are the primary way modern systems expose data. Whether you are pulling sales figures from Salesforce, user data from your own backend, or public data from a government portal, the response almost always comes back as JSON.

Getting that JSON into Excel for analysis, reporting, or sharing is a common challenge. This guide covers several approaches depending on how technical you are and how often you need to repeat the process.

Understanding the JSON API Response

Most APIs return data in one of two shapes:

Array of objects (most common)

[
  { "id": 1, "name": "Alice", "sales": 4200 },
  { "id": 2, "name": "Bob",   "sales": 3800 }
]

Wrapped response (very common)

{
  "status": "ok",
  "total": 2,
  "data": [
    { "id": 1, "name": "Alice", "sales": 4200 },
    { "id": 2, "name": "Bob",   "sales": 3800 }
  ]
}

In the second shape, your actual data lives inside a nested key (data in this example). You will need to drill into that key before converting to Excel.

Method 1: Save JSON to File, Then Convert

The simplest approach: fetch the API response, save it as a JSON file, then convert with an online tool or Excel.

Using curl (any terminal)

curl -H "Authorization: Bearer YOUR_TOKEN" \
     -o response.json \
     https://api.example.com/reports/sales

Then upload response.json to jsontoexcel.net to get an Excel file instantly.

Using PowerShell (Windows)

$headers = { Authorization = "Bearer YOUR_TOKEN" }
$response = Invoke-RestMethod -Uri "https://api.example.com/reports/sales" -Headers $headers
$response | ConvertTo-Json -Depth 10 | Out-File response.json

Method 2: Python — Fetch and Export Directly

This method fetches the data and writes the Excel file in one script — no intermediate JSON file needed:

import requests
import pandas as pd

# Fetch the data
response = requests.get(
    'https://api.example.com/reports/sales',
    headers={'Authorization': 'Bearer YOUR_TOKEN'}
)
response.raise_for_status()  # raise error if request failed

# Handle wrapped responses — drill into 'data' key if needed
json_data = response.json()
if isinstance(json_data, dict) and 'data' in json_data:
    records = json_data['data']
else:
    records = json_data

# Convert to DataFrame and export
df = pd.json_normalize(records)
df.to_excel('sales_report.xlsx', index=False)
print(f"Exported {len(df)} rows to sales_report.xlsx")

Handling Paginated APIs

Many APIs return data in pages. Collect all pages before exporting:

import requests
import pandas as pd

all_records = []
page = 1

while True:
    response = requests.get(
        'https://api.example.com/reports/sales',
        params={'page': page, 'per_page': 100},
        headers={'Authorization': 'Bearer YOUR_TOKEN'}
    )
    data = response.json()
    records = data.get('data', [])

    if not records:
        break  # no more pages

    all_records.extend(records)
    page += 1

df = pd.json_normalize(all_records)
df.to_excel('sales_complete.xlsx', index=False)
print(f"Exported {len(df)} total rows")

Method 3: Excel Power Query — Live API Connection

Power Query can connect directly to a web API and create a refreshable Excel table. This means every time you click "Refresh", Excel re-fetches the latest data from the API automatically.

Setting Up the Connection

1. Open Excel and go to Data → Get Data → From Other Sources → From Web
2. Enter the API URL (e.g., https://api.example.com/reports/sales)
3. If authentication is required, click Advanced and add an Authorization header with your token
4. Click OK — Power Query opens and shows the raw JSON response
5. Click Convert → Into Table if needed, then expand the columns
6. Click Close & Load to import the data

Refreshing the Data

Right-click the table in Excel and select Refresh, or use Data → Refresh All to update all queries at once. You can also set automatic refresh intervals under Data → Queries & Connections → Properties.

Method 4: Google Sheets with IMPORTDATA or Apps Script

If your team uses Google Sheets rather than Excel, you can pull JSON API data using Apps Script:

function importApiData() {
  const url = 'https://api.example.com/reports/sales';
  const options = {
    headers: { Authorization: 'Bearer YOUR_TOKEN' }
  };

  const response = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(response.getContentText());
  const records = data.data || data;

  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.clearContents();

  if (records.length > 0) {
    const headers = Object.keys(records[0]);
    sheet.appendRow(headers);
    records.forEach(row => sheet.appendRow(headers.map(h => row[h])));
  }
}

Dealing with Deeply Nested API Responses

Some APIs return deeply nested JSON where the data you want is several levels deep:

{
  "report": {
    "generated_at": "2026-02-18",
    "results": {
      "sales": [
        { "rep": "Alice", "total": 4200 }
      ]
    }
  }
}

In Python, navigate to the array before normalizing:

records = response.json()['report']['results']['sales']
df = pd.json_normalize(records)

Choosing the Right Method