How to parse JSON in Google Sheets

Sample JSON for the formula examples

Paste this valid JSON into cell A1:

{
  "order_id": "ORD-1042",
  "customer": {
    "name": "Ana Ruiz",
    "email": "ana@example.com"
  },
  "active": true,
  "items": [
    { "sku": "NB-1", "quantity": 2 },
    { "sku": "DM-4", "quantity": 1 }
  ]
}

The examples below read that cell locally. They do not fetch a URL, send the payload to the JSON for Sheets remote API, or use a remote request from your plan.

Choose a formula or the visual sidebar

The sidebar also handles pasted JSON, a cell, and local files. Its preview is useful when the structure is unknown or the output could be large.

Step 1

Check whether a cell contains valid JSON

=JSON_VALID(A1)

The result is TRUE when the cell contains valid JSON and FALSE when parsing fails. Use this before other parsing formulas when data may contain trailing commas, incomplete strings, comments, or ordinary text.

JSON rules are stricter than JavaScript object syntax: property names and text strings require double quotes, comments are not allowed, and a final property cannot be followed by a trailing comma.

Step 2

Inspect an unfamiliar JSON structure

Use these formulas before guessing at paths:

JSON_TYPE returns object, array, string, number, boolean, or null. JSON_LENGTH counts array items, object keys, or string characters. JSON_KEYS lists object keys or array indexes vertically.

Step 3

Extract one JSON value into a cell

=JSON_VALUE(A1,"customer.email")

The formula returns:

Array indexes work too:

=JSON_VALUE(A1,"items[0].sku")

This returns NB-1. Add a fallback as the third argument when a path may be missing:

=JSON_VALUE(A1,"customer.phone","Not provided")

Use JSON_VALUE when the expected result is a string, number, boolean, or null-like value that belongs in one cell. When a selected value is null, the formula returns the supplied fallback or a blank cell.

Step 4

Return a selected object or array as JSON text

=JSON_QUERY(A1,"items")

The result is compact JSON in one cell:

[{"sku":"NB-1","quantity":2},{"sku":"DM-4","quantity":1}]

JSON_QUERY is useful when another formula, script, API request, or export needs valid JSON rather than separate spreadsheet columns. It can use Simple path by default, or an explicit JSONPath subset or JMESPath language through its options argument.

Step 5

Convert a JSON array into rows and columns

=JSON_TABLE(A1,"items","headers=true")

The formula spills this table into neighboring cells:

Nested objects automatically become dotted columns. Nested arrays can remain JSON, join into one cell, or expand into additional rows with array=json, array=join, or array=rows. Read the nested JSON guide for complete examples and expansion warnings.

Pretty-print or minify JSON text

Make compact JSON easier to read:

=JSON_PRETTY(A1,2)

Remove unnecessary whitespace:

=JSON_MINIFY(A1)

The optional indentation for JSON_PRETTY accepts values from 0 through 10 and defaults to 2. Both functions validate the input before returning formatted JSON text.

Understand JSON path syntax

Simple paths cover most extraction tasks. JSON_QUERY and JSON_TABLE can also use the documented JSONPath subset or JMESPath by adding language=jsonpath or language=jmespath to the options string.

Parse JSON from a URL or API in the sidebar

Do not use JSON_IMPORTfor a remote URL. It is intentionally disabled in the current release. A custom formula can recalculate when another collaborator opens or edits a shared spreadsheet, which could otherwise spend the spreadsheet owner's account allowance.

Instead, open JSON for Sheets and choose Public URL or API request. Remote work then runs under the active user's installation token and supports previews, authentication, pagination, fields, safe destinations, saved imports, and schedules.

Follow the complete JSON API guide for authentication, parameters, request bodies, pagination, and refresh behavior.

JSON parsing function reference

See the complete function reference and JSON_TABLE documentation for argument details.

Troubleshoot JSON parsing in Google Sheets

Frequently asked questions