Convert JSON to Google Sheets: Paste JSON, Get a Table

Why Google Sheets Has No Native JSON Function

Google Sheets ships IMPORTXML, IMPORTRANGE, IMPORTHTML and IMPORTFEED — reaching into XML, other spreadsheets, HTML tables, and RSS feeds. Checked against Google’s own function list, there is no IMPORTJSON, no JSON() function, and nothing else that parses a JSON string on the worksheet at all. That is not a documentation gap; the function simply does not exist, on any Sheets plan. Excel closed most of the equivalent gap in 2016 with Power Query’s Json.Document — not obvious, but real. Sheets has nothing standing in for it.

The answer that fills that hole online is a script called IMPORTJSON, a third-party Apps Script library, not a Google feature, that has been copied and pasted between spreadsheets since roughly 2012. Using it means opening Extensions → Apps Script, pasting several hundred lines of someone else’s code into your account, saving it, and then clicking through an OAuth consent screen asking you to authorize a script you did not write to act on your Google account. For someone who only wants to see an API response as a table, that is a bigger ask than the actual problem, and it is a real reason non-developers give up before finishing. It is also brittle in a specific way: most published versions handle a flat array fine and then dump a wall of unreadable stringified JSON into a cell the moment a nested object shows up.

That is the gap this page fills without asking you to run anyone else’s code: paste JSON, get a table, copy it into a cell. It works as a json to google sheets converter online in the plain sense — no install, no account, no code editor — and the same page handles the reverse job too, when what you actually need is to export json to google sheets for a one-off report rather than parse it programmatically.

How Dot-Path Flattening Works

A spreadsheet cell holds one flat value; it has no concept of “an object inside a cell.” So the first job of turning JSON into a table is flattening — converting a nested object into one column per leaf value, named after the path you would walk to reach it. This tool joins each level with a separator, a dot by default:

{
  "id": 42,
  "user": {
    "name": "Amy",
    "address": { "city": "Lyon", "zip": "69000" }
  }
}

flattens to:

Nest three, four, five levels and the column name just gets longer; nothing about the value itself changes, and nothing is silently collapsed the wrong way. If your downstream tooling dislikes a dot inside a header — some BI dashboards and formula tools do — switch the path separator above to an underscore or a slash. The flattening is identical either way; only the punctuation in the header row changes.

Arrays of Objects: Turning an API Response Into Rows

Most real JSON that lands in a spreadsheet is not a single object — it is an array of them, because that is what an API returns: a list of orders, users, products, log lines. This is the single most common shape this tool exists to handle, so it gets the clearest treatment: each object in the array becomes one row, and the column set is the union of every key seen across every object, not just the first one’s keys, which is where naive converters quietly drop data.

Any object missing a key that another object has gets a blank cell there — never the word undefined, never an error:

[
  { "id": 1, "name": "Amy", "plan": "pro" },
  { "id": 2, "name": "Ben" }
]

Row 2 has no plan key in the source at all. It gets an empty cell in the plan column, keeping every row aligned under the same headers — which is exactly what lets you paste the result straight into Sheets and have VLOOKUP, filters, and pivot tables work against it immediately, instead of choking on ragged rows.

A JSON array of plain values, with no objects at all — ["a", "b", "c"] — is simpler still: each value becomes one row in a single column named value, since there is no key to name the column after.

Arrays Nested Inside an Object: The Decision We Made

The genuinely hard case — and the one every competitor found in the audited search results visibly punts on — is an array living inside an object, like a tags list on a user record or a list of line items on an order. There is no single universally correct way to squeeze that into one flat row, because a row is one thing and an array is a variable-length list of things. Two approaches exist, and they trade off differently:

• Stringify it (the default here). Keep the array as one cell, written back out as JSON text — tags becomes ["vip","new"]. This always works, never loses data, and never changes your row count. It is the safe choice.
• Explode it into rows (opt-in). Turn each array element into its own row, repeating the rest of that record’s values across each one. Often what you actually want for line items — but ambiguous the moment a record has more than one nested array, and it changes your row count in a way that can surprise anything downstream that assumed one row per record.

This tool defaults to stringify and ships explode as an explicit toggle, not a silent guess. With explode on, it expands the first array found in each record — walking the object top to bottom, left to right — into repeated rows. If a record contains more than one nested array, only that first one is exploded; the rest stay stringified, rather than being combined into an undocumented cross-product nobody asked for:

{ "id": 1, "items": [{"sku":"A","qty":2}, {"sku":"B","qty":1}] }

with Explode nested arrays into rows turned on:

Pasting the Result Into Google Sheets (or Excel)

The primary output is tab-separated values — TSV — because that is the format a spreadsheet’s clipboard actually understands for a multi-cell paste: each tab starts a new column, each newline starts a new row. Click into cell A1 in Sheets or Excel, paste, and the whole table lands in place, columns aligned, with no import dialog and no intermediate CSV file to download and re-upload.

The place a naive TSV export breaks is a value that itself contains a tab, a newline, or a quote character — common in free-text fields pulled from an API. Left unescaped, a tab inside a value silently starts a new column mid-row, and the paste misaligns from that cell onward with no error to flag it. This tool quotes any such value and doubles internal quote characters, the same convention Sheets and Excel already use for CSV, so a stray tab in a description field cannot quietly shift every column after it.

A Download CSV button sits next to the copy button for when a file is more useful than a clipboard paste — archiving the converted data, or importing it somewhere that expects a .csv upload rather than a paste into an open sheet.

Malformed JSON, Large Payloads, and What Runs Where

Paste something that is not valid JSON — a trailing comma, an unquoted key, a missing closing brace — and the box above reports the problem in plain language, naming what went wrong, instead of returning a blank result or failing silently in the browser console. Nothing is submitted anywhere to fail on a server: whatever you paste to parse json to google sheets is read by JSON.parse running in your browser, and it reports back immediately.

Flattening runs client-side too, and for the realistic upper end of what someone pastes into a browser tab — a few thousand rows, tens of thousands of individual values — it finishes in a fraction of a second. The preview table caps itself at the first 100 rows so a huge payload does not bog the page down rendering a giant table, but the full result, every row, is always present in the TSV output and the CSV download, uncapped.

Because everything happens in JavaScript running on your machine, nothing you paste here is ever sent to a server. That matters more for this tool than most: the JSON people paste into a converter like this is disproportionately likely to be a real API response, a customer export, or business data with a client’s name inside it — not a synthetic test string. Open your browser’s network tab before you paste and watch it stay empty through the whole conversion.