DEV Community

Cover image for Convert a JSON API Response to Excel: 3 Ways (No-Code, Python, Power Query)
switchpdf
switchpdf

Posted on

Convert a JSON API Response to Excel: 3 Ways (No-Code, Python, Power Query)

#json #python #excel #webdev

You call an API, get back JSON, and someone non-technical asks for it "in Excel." Here are three ways to do that — pick based on whether it's a one-off or a repeatable pipeline — plus the part everyone trips on: nested objects.

The sample data 

[
  { "id": 1, "name": "Ada",  "address": { "city": "London", "zip": "EC1" }, "roles": ["admin", "editor"] },
  { "id": 2, "name": "Alan", "address": { "city": "Oxford", "zip": "OX1" }, "roles": ["viewer"] }
]
Enter fullscreen mode Exit fullscreen mode

The catch: address is a nested object and roles is an array. Neither drops cleanly into a flat spreadsheet cell — address has to become address.city / address.zip columns, and you have to decide what to do with the list.

Way 1 — No-code (fastest for a one-off)

Paste the JSON into a converter and export. I use SwitchPDF's JSON tool: it shows a live table preview, auto-flattens nested objects into dot-notation columns, and exports .xlsx, .csv, or a styled PDF. No signup, no watermark. The export is processed server-side in memory and discarded right after — nothing's stored beyond a short-lived file.

Best when you just need the file now and don't want to write code.

Way 2 — Python (best for a repeatable script)

pandas handles the flattening with json_normalize:

import pandas as pd
import requests

data = requests.get("https://api.example.com/users").json()

df = pd.json_normalize(data)          # address.city, address.zip become columns
df.to_excel("users.xlsx", index=False)
Enter fullscreen mode Exit fullscreen mode

For deeper nesting, control the separator and depth:

df = pd.json_normalize(data, sep=".", max_level=2)
Enter fullscreen mode Exit fullscreen mode

The array gotcha: json_normalize flattens nested objects but leaves lists (like roles) as a single cell. Two common fixes:

# Option A: keep one row per record, join the list into one cell
df["roles"] = df["roles"].apply(lambda r: ", ".join(r))

# Option B: one row per role (explode the list)
df = df.explode("roles")
Enter fullscreen mode Exit fullscreen mode

Way 3 — Excel Power Query (no code, stays in Excel)

  1. Data → Get Data → From File → From JSON (or From Web for a live URL).
  2. In the Power Query editor, click the expand icon on the record/list columns to flatten them.
  3. Close & Load.

It refreshes on demand, which makes it the right pick for a recurring report.

Which should you use?

Situation Use
One-off, want the file now Way 1 (converter)
Part of a script / pipeline Way 2 (pandas)
Recurring report, Excel-native team Way 3 (Power Query)

If your JSON is deeply nested or the shape is inconsistent across records, json_normalize gives you the most control. For a quick hand-off to a non-technical person, the no-code converter is the least friction.

What's your go-to for JSON → Excel? Drop it in the comments — always curious what edge cases people hit.

Top comments (0)