Turning Nested JSON Into Spreadsheet Columns

Nested JSON becomes spreadsheet columns by flattening: every leaf value gets its own column, named with the path of keys that leads to it. {"customer": {"name": "Lena Ortiz"}} becomes a column called customer.name holding Lena Ortiz. This article uses the flattening rules in the AnyToExcel JSON converter, with a real input and the exact output it produces.

What does a nested API response look like as a spreadsheet?

A nested API response becomes one row per record and one column per leaf value. This is a typical paginated response: a status, a data array of orders, and a meta object.

{
  "status": "ok",
  "data": [
    {
      "id": "ord_1001",
      "customer": { "name": "Lena Ortiz", "address": { "city": "Lyon" } },
      "items": [{ "sku": "A1", "qty": 2 }],
      "tags": ["priority", "gift"],
      "total": 59.8
    },
    {
      "id": "ord_1002",
      "customer": { "name": "Sam Okafor", "address": { "city": "Leeds" } },
      "items": [],
      "tags": [],
      "total": 12,
      "coupon": "AUTUMN"
    }
  ],
  "meta": { "page": 1 }
}

Each rule behind that table is explained in its own section below: the dot-path columns, the tags cell, the items cell, the missing status and meta, and the coupon column that only the second record has.

How does dot-path flattening name JSON columns?

Dot-path flattening names each JSON column by joining the keys from the top of the record to the value with a dot. The value at customer → address → city goes in a column called customer.address.city. Flattening goes as deep as the nesting does, so a record nested five levels deep still produces one row with one column per leaf.

Dot-path column names keep the original JSON structure readable from the header row: customer.name and customer.address.city sit side by side and show they belong to the same object. An empty object, such as "address": {}, still gets a column named customer.address, left empty, so the column set does not change between files.

What happens to an array of plain values in JSON to Excel?

An array of plain values, such as "tags": ["priority", "gift"], becomes one cell with the values joined by a comma and a space: priority, gift. An empty array becomes an empty cell.

Joining keeps a tags field in one readable column. The alternative, one column per position (tags.0, tags.1, tags.2), makes a sheet as wide as the longest list in the file, and position 0 means nothing when one record's first tag is "priority" and the next record's is "gift".

What happens to an array of objects inside a JSON record?

An array of objects inside a JSON record, such as the items line items of an order, is written into one cell as compact JSON text: [{"sku":"A1","qty":2}]. AnyToExcel does not expand it into extra rows or columns.

Line items are a one-to-many relationship: one order, several items. A single sheet can only show that by repeating the order on every item row, which duplicates the order total and breaks any sum over it. Keeping the array as JSON text keeps one row per order and leaves the items readable. If you need one row per item, convert the items array on its own as a separate JSON file.

How does JSON to Excel handle a {status, data: [...]} wrapper?

For a JSON file whose top level is an object, such as {"status": "ok", "data": [...], "meta": {...}}, AnyToExcel uses the largest array of objects found directly inside that object as the records, and ignores the other keys. In the example, data becomes the rows, and status and meta are not in the sheet.

The wrapper is only looked through one level. A response shaped {"data": {"items": [...]}} has no array directly under the top level, so the whole response becomes a single row with one column, data.items, holding the array as JSON text. To convert that shape, save the inner items array as its own file first.

What happens when JSON records have different fields?

When JSON records have different fields, every field from every record becomes a column, in the order the fields first appear in the file, and a record without that field gets an empty cell. In the example, only the second order has a coupon, so the coupon column exists and the first order's cell is empty.

Are JSON numbers, dates and JSON Lines files handled?

JSON numbers, dates and JSON Lines files are all handled by the AnyToExcel JSON converter. After flattening, each column is tested the same way a CSV column is: total above becomes a number column, and strings such as 2026-04-03 become dates. A string like "00123" stays text with its zeros.

• JSON Lines (.jsonl, NDJSON): if the file is not one valid JSON document, each line is parsed on its own. A line that is not valid JSON is skipped and named in a warning; the other lines still convert.
• Booleans become the text true or false. null becomes an empty cell.
• A top-level array of plain values, such as [1, 2, 3], becomes one column called value.

To convert a file with these rules, upload it to the JSON to Excel converter. It accepts .json and .jsonl files up to 5 MB, with no account, and the file is not stored.

Which JSON shapes do not convert cleanly to Excel?

Three JSON shapes do not convert cleanly to Excel with AnyToExcel: records nested more than one level below the top-level object, arrays of objects inside a record, and single objects that are not lists of records. Each one produces a valid sheet, but not the rows you might expect.