The JSON import expects a JSON array, per the sidebar's format name. The most useful shape is an array of flat objects, where every object has the same keys:
[
{"id": 1, "name": "Text Power Tools", "users": "2K+", "free": true},
{"id": 2, "name": "Table Converter", "users": "162", "free": true}
]Keys become the header row; each object becomes a row.
Step by step
- Get the JSON. From an API, a browser's network tab, or a file.
- Make sure the top level is an array
[ … ]. If the API wraps it, as in{"data": [ … ]}, copy only the array. - Sidebar → Import → JSON Array, paste, choose a new sheet for a first try, click Import.
- Check the headers, row count and a few values.
Nested data
Real APIs nest:
[{"id": 1, "owner": {"name": "Asha", "team": "core"}, "tags": ["sheets", "free"]}]A flat table can't hold that shape directly. Expect nested objects and arrays to arrive as text you can split later, or flatten before importing.
Flattening with jq before you paste:
jq '[.[] | {id, owner_name: .owner.name, owner_team: .owner.team, tags: (.tags | join(", "))}]' data.jsonOutput:
[{"id": 1, "owner_name": "Asha", "owner_team": "core", "tags": "sheets, free"}]Big payloads
Pasting very large JSON into a sidebar text box can be slow. For big files, filter to the fields you need with jq first, or split the array into chunks.
Types
true/false and numbers may be interpreted by Sheets after import. Check columns that look numeric but are IDs (zip codes, phone numbers) and format them as Plain text.
Practice: a small API response
[
{"id": 1, "name": "Asha", "active": true, "score": 92.5},
{"id": 2, "name": "Ravi", "active": false, "score": 78},
{"id": 3, "name": "Meera", "active": true}
]Import it with JSON Array and check:
- The header row should hold
id,name,activeandscore. - Meera has no
score, so her score cell should be empty. - Check
active: Sheets treatsTRUE/FALSEas booleans, which you can count with=COUNTIF(C2:C4, TRUE). If the cells hold the text "true", that formula returns 0.
When the data is not an array
Many APIs wrap the array: {"data": [ ... ], "page": 1}. The importer expects the array itself, so copy only the part between the outer [ and ] of data, or extract it first with jq '.data'. A single object {...} on its own is not an array either; wrap it as [{...}] to import one row.
Quick check
-
The API returns
{"items": [...]}. What do you paste?Only the array inside "items". -
What becomes the header row?The object keys.
-
Why flatten nested objects before import?A flat table has one value per cell; flattening gives each nested field its own column.






