A spreadsheet request sounds simple: take the JSON returned by an API and turn it into rows. The first attempt often produces a single column of [object Object], or columns that shift when one record carries an extra field. JSON and CSV disagree about structure. JSON allows nested objects and missing keys; CSV expects a rectangle of cells. Conversion works when the source is a flat array of objects and you understand how the converter builds the header, fills gaps and escapes values.
Part of the developer toolkit guide. Run the conversion in the JSON to CSV converter. If the payload needs repair first, the JSON parse error guide covers the common syntax faults.
The flat array rule
The converter accepts one shape: a JSON array whose entries are objects. A top-level object fails with “JSON must be an array of objects.” An array of strings or numbers has no stable field names, and a null entry has no keys, so neither belongs in a CSV export. Wrap each record in an object with named properties before converting.
Flat means each value is a scalar: a string, number, boolean or null. Nested objects are not flattened. The converter calls String(value), so {"customer":{"name":"Ada"}} becomes [object Object] in the cell. Arrays are turned into comma-joined text, which can collide with the CSV delimiter. If a cell must hold a list, serialize it deliberately first, for example as math|science.
An empty array returns empty output, not a header, because there are no keys to union. A single object outside an array is rejected even though it looks like one record. If the API wraps the rows in a field such as data or results, unwrap that field first; the converter does not search inside an object for the first array it finds. These strict shapes are deliberate: guessing where the rows live would create silent, hard-to-audit exports.
The tool also has no delimiter menu. Output is comma-separated. If a locale expects semicolons, do not assume conversion will adapt; convert first, then handle the separator in your spreadsheet import step. The header is built from JSON keys, not from a template you supply.
Union keys and column order
The header is the union of every key in the array, in first-seen order. It is not taken from the first row alone. A field that appears only in the third record still becomes a column, placed after the keys seen earlier. Records without that key get an empty cell. This design prevents data loss, but it also means the column order depends on the order of the source array.
Example 1 shows the effect. The input is [{"id":1,"name":"Ada","team":"math"},{"id":2,"name":"Grace","email":"g@example.com"},{"id":3,"name":"Linus","team":"kernel","active":true}]. The output header is id,name,team,email,active. Ada has no email or active value, Grace has no team or active value, and Linus has no email. The rows are still aligned because every row is written against the same header.
This is why a single sample record is a poor template. Before exporting, scan the whole array for the largest set of keys. If Excel must see a fixed order, normalize the objects in the source, or reorder the CSV columns after conversion. The tool does not alphabetize or let you drag columns.
Escaping commas, quotes and newlines
Simple values pass through unchanged. Any value containing a comma, double quote or newline is wrapped in double quotes, and internal quotes are doubled. That is standard CSV, and it is the reason a cell can contain punctuation without splitting the row. The table shows the behavior you should expect.
| Value in JSON | Cell in CSV | Rule |
|---|---|---|
| Ada | Ada | No special characters, no wrapping |
| Ada, Jr. | "Ada, Jr." | Comma forces quotes |
| She said "hi" | "She said ""hi""" | Quotes are doubled inside a quoted cell |
| Line one Line two | "Line one Line two" | Newline stays inside one quoted cell |
| null | (empty) | Null and missing values become blanks |
| true | true | Booleans are written as text |
| {"a":1} | [object Object] | Nested objects are not flattened |
One limit sits outside the cell rules: header names are joined directly and are not escaped. A key containing a comma, quote or newline can break the header row. Rename such keys before conversion. Long numeric IDs are another quiet problem, because JSON.parse rounds integers beyond its safe range; store identifiers as strings when precision matters.
Nested JSON: flatten before converting
Example 2 is a typical order record: [{"order":1001,"customer":{"name":"Ada","city":"London"},"items":[{"sku":"A1","qty":2}],"total":49.5}]. The converter produces a header of order,customer,items,total and a row where both customer and items read [object Object]. The numbers survive; the structure does not. That output is not a bug in the escaping, it is the flat-array rule meeting data that is not flat.
Flatten the record before conversion. A workable line-item table is [{"order":1001,"customer_name":"Ada","customer_city":"London","item_sku":"A1","item_qty":2,"total":49.5}]. If an order has several items, emit one row per item and repeat the order fields, which is the usual shape for spreadsheet analysis. If you need one row per order instead, join the SKUs into a single documented field such as A1|B2 rather than letting an array stringify on its own.
Decide the grain before you touch the tool. One row per order answers revenue questions; one row per line item answers product questions. A mixed file with some orders collapsed and others expanded will produce misleading totals, even when every cell is technically valid.
The reverse direction and the 500KB cap
The same tool converts CSV back to JSON with a local parser. The first row becomes the header keys, duplicate header names get numeric suffixes, and blank headers become generated column names. Values come back as strings, so a price of 19.99 returns as text. The parser also guards spreadsheet formula injection by prefixing cells that start with =, +, - or @ with an apostrophe.
CSV input is capped at 500KB. Anything beyond the cap is truncated before parsing, and the interface warns that only the first part was converted. Split large exports or trim unnecessary columns. The JSON-to-CSV direction is the one to use when you already have structured API data; the CSV-to-JSON direction is for data that started life in a spreadsheet.
A conversion workflow that survives review
- Confirm the source is an array, not a wrapper object with a
datafield. Unwrap it first. - List every key across all records. Note the odd fields that appear in only one row.
- Flatten nested objects and choose one row grain before converting.
- Convert, then count the header cells and compare them with the key list.
- Open the CSV in a spreadsheet and check blank cells, quoted commas and multi-line values.
- Keep the original JSON and the flattening logic so the export can be reproduced.
If the CSV opens with every row in one column, the spreadsheet likely ignored the delimiter or the file was saved with a different separator. If columns are shifted, a header name probably contained a comma or a row was edited by hand. Fix the source, not the symptoms.
For large exports, validate the JSON before converting. The parse error fixes save time when the failure is a trailing comma rather than a data shape problem. When URLs or encoded identifiers appear in cells, the URL encoding guide explains why a value may look different after a round trip.