Skip to main content
ToolsByUs

Import JSON into Google Sheets

Convert the JSON to CSV in your browser, then use Google Sheets' File → Import → Upload and set the separator to match. Sheets has no JSON importer, so CSV is the practical route; nested keys arrive as column paths like customer.name, and the column set is the union of every record rather than the first one's keys.

Local-only
Just want the tool? Open JSON to CSVConvert records to a spreadsheet grid, nesting and all.Open tool

Import JSON into Google Sheets explained

Google Sheets has no JSON importer. There is no dialog that takes an API response and lays it out in a grid, and the Apps Script route — UrlFetchApp, JSON.parse, setValues — is a real answer that most people should not have to write. CSV is the interchange format Sheets does accept, which makes the conversion step the whole job.

The conversion itself is the easy half, and the JSON to CSV converter does it without an upload. The half that goes wrong is the import, and it goes wrong in ways that are specific to Sheets rather than shared with Excel: Sheets guesses the separator from the first line rather than asking, it applies its own type coercion on the way in, and it will happily turn a column of product codes into numbers and drop the leading zeros before you have a chance to say otherwise.

This page is the recipe for getting a JSON export into a Sheets grid with none of that. What the converter produces, which import setting to use, how to stop the type coercion, and the two hard ceilings you can hit on a free account. Everything runs in your browser — a JSON export is usually real records about real people, and it does not need a detour through a server to become a spreadsheet.

The conversion happens in the JSON to CSV converter — paste the JSON, choose a separator, and copy or download the result.

If the destination is Excel rather than Sheets, take the Excel route instead: a real workbook keeps numeric cells numeric and skips the CSV import dialog entirely.

Why Sheets has no JSON import, and what to use instead

Google Sheets can read three things directly: CSV, TSV and a pasted grid. It can fetch a remote CSV with IMPORTDATA, and a published Google Sheets URL is itself a CSV endpoint — but there is no built-in path from a JSON document to a range of cells. The official answer is Apps Script, and for a one-off it is more work than the task deserves.

So CSV is the route. Convert first, import second, and the whole thing takes about a minute. The important consequence is that every CSV limitation becomes a Sheets limitation: a cell cannot distinguish the string 22201 from the number 22201, and it cannot distinguish an empty cell from a field that was absent from the record. Those two distinctions exist in your JSON and will not survive the trip, which is worth knowing before you delete the original.

The import dialog, field by field

File → Import → Upload, then choose the file. The dialog that appears is where the outcome is decided, and two of its controls matter.

The separator dropdown defaults to Detect, which looks at the first line and guesses. That guess is right often enough to be trusted and wrong often enough to be worth overriding: pick Comma or Semicolon explicitly, matching whatever the converter emitted. If the file opens with everything in column A, the guess was wrong — re-import rather than trying to split the column afterwards.

The Convert text to numbers, dates and formulas toggle is the one that does quiet damage. Left on, Sheets applies its own heuristics: a product code becomes a number and loses its leading zeros, a version string like 1.10 becomes a date, a long ID goes scientific. Turn it off to import every value as text exactly as written. You can always convert a column deliberately afterwards; you cannot recover a leading zero that was stripped on import.

Getting nested JSON into flat columns

The converter flattens nested objects to dotted paths and arrays to bracketed ones, so {"customer":{"name":"Ada"}} becomes a column called customer.name and a tags array becomes tags[0], tags[1]. The two notations are deliberately different so a column name is never ambiguous about which it came from, and so a round trip back through CSV to JSON reconstructs the nesting.

The column set is the union of every record's fields in first-seen order. This matters more for Sheets than it does for a local spreadsheet, because the import is a one-shot operation: a field that only appears on the fortieth record still gets its own column, whereas a converter that reads the header off record one loses it silently and you find out weeks later when a filter returns nothing.

JSONColumnNotes
{"customer":{"name":"Ada"}}customer.nameObject members become dotted paths
{"tags":["a","b"]}tags[0], tags[1]Array elements get a column each
{"vip":true}vipImported as TRUE, not the text "true"
{"note":null}noteAn empty cell — indistinguishable from absent

The two ceilings you can actually hit

A free Google account allows 10,000,000 cells in a spreadsheet, which is 10,000,000 values rather than rows. A 50-column export therefore runs out at 200,000 rows, and an import that exceeds the ceiling fails partway with a message about the file rather than about the limit. If your export is larger than that, split it by date range or by an ID prefix and import the parts into separate sheets.

The other ceiling is the import dialog's file size, which is 100 MB. A CSV of a hundred thousand records with twenty columns is roughly 20 MB, so this is the less likely of the two — but a JSON export with long text fields can reach it. The converter declines above 8 MB of JSON rather than freezing the tab, so in practice you will meet that limit first and it will tell you so.

Keeping the sheet in step with the source

An imported range is a snapshot. If the JSON is an export from something that changes — an orders table, a CRM, an analytics dump — the sheet is stale the moment it lands, and re-importing by hand every morning is exactly the chore that turns into a broken spreadsheet nobody trusts.

The honest options are two. If the source can publish CSV over HTTP, IMPORTDATA pulls it on a schedule into a single cell, and SPLIT plus an array formula lays it out — no conversion step at all, at the cost of the data being public to anyone with the URL. Otherwise, keep the conversion local and repeat it: the tool takes a paste, so the loop is export, paste, download, import, and it takes less than a minute. What is not worth doing is wiring an Apps Script to a private API key so that a sheet can hold a credential it should not.

Frequently asked questions

Is my JSON uploaded when I convert it?

No. The conversion is a function call inside your browser tab — there is no API route for the document to be sent to. You can verify it: open DevTools, watch the Network panel, paste a document, and no request carries it. The page's Content-Security-Policy sets connect-src 'self', so the browser would refuse an outbound upload regardless. JSON exports are usually real records, which is exactly why the conversion stays local.

Why does my CSV open with everything in one column in Sheets?

The separator in the file does not match the one the import dialog guessed. Sheets defaults to Detect and infers from the first line, so a comma-delimited file imported with Semicolon selected lands entirely in column A. Re-import with File → Import and set the separator explicitly to match what the converter emitted — comma, semicolon, tab or pipe. Splitting the column afterwards with a formula is possible and always worse than importing again.

Why did my product codes lose their leading zeros?

The Convert text to numbers, dates and formulas option in the import dialog, which is on by default. Sheets reinterprets anything number-shaped, so 00123 becomes 123 and a long ID goes scientific. Turn that option off and every value imports as text exactly as written. This is the single most common complaint about CSV imports in Sheets, and unlike Excel there is no per-column override in the dialog — it is all or nothing.

Can Sheets import JSON directly?

Not through any built-in dialog. IMPORTDATA reads CSV and TSV over HTTP, not JSON, and there is no JSON import option in File → Import. Apps Script can do it — UrlFetchApp to fetch, JSON.parse to read, setValues to write — and for a recurring job driven by an API that is a reasonable thing to build. For a one-off conversion it is considerably more work than converting to CSV and importing that.

How many rows can I import?

Ten million cells on a free Google account, which is a cell count rather than a row count: a fifty-column export therefore tops out around 200,000 rows. Exceeding it fails partway through the import. If your export is bigger, split it into parts and import each into its own sheet, or use the converter's range-free output as the source for a script rather than a grid.

Does the conversion handle nested objects and arrays?

Yes. Objects become dotted column paths — customer.name — and arrays become bracketed ones — tags[0], tags[1]. The column set is the union across every record in first-seen order, so optional fields get a column with empty cells elsewhere rather than being dropped. Explode mode round-trips back through the CSV to JSON converter; join mode packs an array of scalars into one cell and is lossy by design.

ToolsByUs team · How we check our tools