ysskrishna logoysskrishna

Chapter 3 of 6 · 2 min

Import a JSON array

Turn API output into rows and columns: the shape Table Converter expects, how keys become headers, and what to do with nested data.

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

  1. Get the JSON. From an API, a browser's network tab, or a file.
  2. Make sure the top level is an array [ … ]. If the API wraps it, as in {"data": [ … ]}, copy only the array.
  3. Sidebar → Import → JSON Array, paste, choose a new sheet for a first try, click Import.
  4. 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.json

Output:

[{"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:

  1. The header row should hold id, name, active and score.
  2. Meera has no score, so her score cell should be empty.
  3. Check active: Sheets treats TRUE/FALSE as 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

  1. The API returns {"items": [...]}. What do you paste?Only the array inside "items".

  2. What becomes the header row?The object keys.

  3. Why flatten nested objects before import?A flat table has one value per cell; flattening gives each nested field its own column.

Continue exploring

ProductGoogle WorkspaceGoogle Sheets Table Converter

Google Sheets Table Converter

Pipe tables, JSON, CSV, or HTML in and out of Sheets, without leaving the grid you are already in.
Google Sheets Table Converter
Markdown & Docs
Text & Data Conversion
Google Workspace Add-ons
BlogCover image for Import JSON, Markdown, HTML & CSV Tables into Google Sheets Without Breaking Data

Import JSON, Markdown, HTML & CSV Tables into Google Sheets Without Breaking Data

Google Sheets Table Converter
Google Workspace Add-ons
+3
2 min read
BlogCover image for Convert Google Sheets to Markdown, HTML, JSON & CSV Without Breaking Tables

Convert Google Sheets to Markdown, HTML, JSON & CSV Without Breaking Tables

Google Sheets Table Converter
Google Workspace Add-ons
+3
2 min read
ProductGoogle WorkspaceGoogle Sheets Tab Manager

Google Sheets Tab Manager

Your workbook outgrew the tab bar. Clean it up in batches.
Google Sheets Tab Manager
Google Workspace Add-ons
Google Sheets
Google Apps Script
CourseCover image for Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Delete multiple tabs in Google Sheets at once, hide and unhide in bulk, rename with find and replace, colour and sort tabs A–Z with the free Sheets Tab Manager.

Google Sheets Tab Manager
Google Sheets
+1

6 chapters · 24 min

ProductGoogle WorkspaceGoogle Sheets Merge Tools

Google Sheets Merge Tools

Repeatable merges in Google Sheets, without the misclick marathon.
Google Sheets Merge Tools
Google Workspace Add-ons
Google Sheets
Google Apps Script