{
  "$type": "site.standard.document",
  "bskyPostRef": {
    "cid": "bafyreia3eqwdhmhteh3pkfeftmk7vqiuxao2jguvuwsh7ncc7lmanjjnga",
    "uri": "at://did:plc:25rdn5elo5izoxrmtis34zuk/app.bsky.feed.post/3mp6hjvdejzg2"
  },
  "coverImage": {
    "$type": "blob",
    "ref": {
      "$link": "bafkreig2yjt3esd2pj5odlk5ffsbaycew4bmrjt3o5qadxoitpnhtdiyde"
    },
    "mimeType": "image/webp",
    "size": 160750
  },
  "path": "/switchpdf/convert-a-json-api-response-to-excel-3-ways-no-code-python-power-query-3ab0",
  "publishedAt": "2026-06-26T07:29:25.000Z",
  "site": "https://dev.to",
  "tags": [
    "json",
    "python",
    "excel",
    "webdev",
    "SwitchPDF's JSON tool"
  ],
  "textContent": "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**.\n\n##  The sample data\n\n\n    [\n      { \"id\": 1, \"name\": \"Ada\",  \"address\": { \"city\": \"London\", \"zip\": \"EC1\" }, \"roles\": [\"admin\", \"editor\"] },\n      { \"id\": 2, \"name\": \"Alan\", \"address\": { \"city\": \"Oxford\", \"zip\": \"OX1\" }, \"roles\": [\"viewer\"] }\n    ]\n\n\nThe 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.\n\n##  Way 1 — No-code (fastest for a one-off)\n\nPaste 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.\n\nBest when you just need the file _now_ and don't want to write code.\n\n##  Way 2 — Python (best for a repeatable script)\n\n`pandas` handles the flattening with `json_normalize`:\n\n\n\n    import pandas as pd\n    import requests\n\n    data = requests.get(\"https://api.example.com/users\").json()\n\n    df = pd.json_normalize(data)          # address.city, address.zip become columns\n    df.to_excel(\"users.xlsx\", index=False)\n\n\nFor deeper nesting, control the separator and depth:\n\n\n\n    df = pd.json_normalize(data, sep=\".\", max_level=2)\n\n\n**The array gotcha:** `json_normalize` flattens nested _objects_ but leaves _lists_ (like `roles`) as a single cell. Two common fixes:\n\n\n\n    # Option A: keep one row per record, join the list into one cell\n    df[\"roles\"] = df[\"roles\"].apply(lambda r: \", \".join(r))\n\n    # Option B: one row per role (explode the list)\n    df = df.explode(\"roles\")\n\n\n##  Way 3 — Excel Power Query (no code, stays in Excel)\n\n  1. **Data → Get Data → From File → From JSON** (or _From Web_ for a live URL).\n  2. In the Power Query editor, click the **expand** icon on the record/list columns to flatten them.\n  3. **Close & Load.**\n\n\n\nIt refreshes on demand, which makes it the right pick for a recurring report.\n\n##  Which should you use?\n\nSituation | Use\n---|---\nOne-off, want the file now | Way 1 (converter)\nPart of a script / pipeline | Way 2 (pandas)\nRecurring report, Excel-native team | Way 3 (Power Query)\n\nIf 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.\n\n_What's your go-to for JSON → Excel? Drop it in the comments — always curious what edge cases people hit._",
  "title": "Convert a JSON API Response to Excel: 3 Ways (No-Code, Python, Power Query)"
}