JSON APIレスポンスをExcelに変換する3つの方法（ノーコード、Python、Power Query）

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" ] } ] 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 ) For deeper nesting, control the separator and depth: df = pd . json_normalize ( data , sep = " . " , max_level = 2 ) 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 " ) Way 3 — Excel Power Query (no code, stays in Excel) Data → Get Data → From File → From JSON (or From Web for a live URL). In the Power Query editor, click the expand icon on the record/list columns to flatten them. 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. 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" ] } ] 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 ) For deeper nesting, control the separator and depth: df = pd . json_normalize ( data , sep = " . " , max_level = 2 ) 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 " ) Way 3 — Excel Power Query (no code, stays in Excel) Data → Get Data → From File → From JSON (or From Web for a live URL). In the Power Query editor, click the expand icon on the record/list columns to flatten them. 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.