How to Convert JSON to CSV for Excel and Google Sheets

Updated · 5 min read · MetaSol Technologies

Spreadsheets are still the easiest way to sort, filter and share tabular data. Converting JSON to CSV turns an API response or export into something anyone can open in Excel or Google Sheets.

What converts well

CSV is a flat table, so the ideal input is an array of objects that share the same fields:

[
  { "id": 1, "name": "Amara", "country": "NG" },
  { "id": 2, "name": "Lena", "country": "DE" }
]

Each object becomes a row and each key becomes a column.

Step by step

  1. Open the JSON to CSV converter.
  2. Paste the JSON, or drop the file onto the editor.
  3. Choose a delimiter and options, then press Convert.
  4. Download the .csv file and open it in your spreadsheet.

How nested data is handled

With Flatten nested on, nested objects become dotted column names:

[{ "id": 1, "address": { "city": "Lagos", "zip": "100001" } }]

becomes the columns id, address.city and address.zip. Arrays of simple values are joined into one cell (databases; architecture). With flattening off, nested values are written as JSON text in a single cell.

If records have different fields, the columns combine all of them, in the order they first appear, and missing values are left empty.

Excel and the delimiter

Excel uses your system's list separator. In many European locales that is a semicolon, so a comma-separated file opens with everything in column A. If that happens, convert again with the ; delimiter, or use Excel's Data → From Text/CSV import.

Protecting against formula injection

If a cell starts with =, +, - or @, spreadsheets may run it as a formula, and a malicious value can then pull in external data or worse. With Formula protection on (the default), such text cells are prefixed with an apostrophe so they display as text. Real numbers, including negative ones, are not affected.

Going the other way

The CSV to JSON converter uses the header row as keys. It can detect numbers and booleans, but it leaves values with leading zeros, such as postal codes, as text so they are not damaged.