JSON to XLSX — how a nested record becomes a row of columns
A colleague opens the workbook and finds a header called Address.City. That is the flattening rule working as designed: a grid has no nesting, so nested objects are joined into dotted column names before the records are written. This page is about those rules, and about what they cannot express.
Cell formatting, colours, column widths and merged ranges are dropped; only stored values survive.
Processed on your device. Files aren’t sent anywhere.
Nested objects become dotted columns
Each level of an object is joined with a dot to form one column name. The leaf values are written as text.
| JSON field | Column | Cell |
|---|---|---|
| "name": "Ada" | name | Ada |
| "address": {"city": "Riga"} | address.city | Riga |
| "address": {"zip": "LV-1000"} | address.zip | LV-1000 |
| "tags": ["a", "b"] | tags | ["a","b"] |
Arrays are written as JSON text
A grid cannot hold a list inside a cell, so arrays are serialised as JSON text in one cell. A spreadsheet can still read it as characters, and a script reading the workbook can parse it again.
Objects, arrays and single values at the top level
The converter accepts several shapes and normalises them before writing.
Use a sample containing the awkward records, not only the first clean object. An empty list and a populated list should both be present when you inspect the generated cells. An array in one cell is still one cell; it is not a separate worksheet you can filter member by member. If that is the workflow you need, reshape the input objects into ordinary rows before conversion.
- An array of objects writes one row per record.
- A single object writes one row.
- An array of arrays writes rows directly, without a header derived from keys.
- A bare scalar becomes a single value in a value column.
- Invalid JSON is refused with the parser's message rather than written as an empty sheet.
Columns are the union of every record
The header row is built from every key that appears anywhere in the data. A record missing a field leaves an empty cell in that column rather than shifting its other values, so a sparse dataset produces a wide sheet with gaps.
Questions
Why does the header say Address.City?
What happens to an array field?
Why is a column empty for some rows?
Are numbers written as numeric cells?
Reading the grid back
The output is one sheet named after the source file. Values are written as text, so a quantity arrives as characters and needs converting to a number before it can be summed. Dotted headers are the price of flattening: unflattening them is a step you do deliberately, not something the workbook remembers.
- Convert the JSON file and download the workbook.
- Open it and read the header row.
- Check that every key you expected appears as a column.
- Convert numeric columns to numbers before summing.
- Check array cells still contain valid JSON text.