How to import JSON into Google Sheets

By Jake Marsh · Updated

To import JSON into Google Sheets, open Extensions > Cellfetch > Open, click New request, choose Any API (blank) and paste the JSON URL. Click Preview: Cellfetch finds the list of records in the response, shows the first 25 rows, and lets you pick the columns. Then click Save and run to write the table into your sheet. Google Sheets has no built-in function that reads JSON: IMPORTDATA treats a JSON response as CSV and cuts it apart at the commas.

Steps

  1. Open the spreadsheet and choose Extensions > Cellfetch > Open.
  2. Click New request and pick Any API (blank) under Start from. To try it first, Demo: world countries (no key) loads a public JSON list.
  3. Paste the URL into Request, for example https://api.example.com/v1/products. If the API needs a key, add it under Connection (authentication).
  4. Click Preview. Under Records and columns, Cellfetch shows "Found the records at" a JSONPath like $.data or $.items. Click Use it.
  5. Pick your columns. Click Choose from them to tick fields, or type one per line. Rename a column with as: price.amount as Price.
  6. Choose how nested lists come out under Nested lists: join into one cell, one column per item, or keep as JSON text.
  7. Set Where it goes (tab and starting cell) and click Save and run.

How JSON becomes rows

Given this response:

{
  "data": [
    { "id": 1, "name": "Desk lamp", "price": { "amount": 34, "currency": "USD" }, "tags": ["home", "light"] },
    { "id": 2, "name": "Chair", "price": { "amount": 120, "currency": "USD" }, "tags": ["home"] }
  ]
}

With the records at $.data and the default settings, you get:

idnameprice.amountprice.currencytags
1Desk lamp34USDhome, light
2Chair120USDhome

Nested objects become dotted column names, and lists are joined into one cell unless you choose otherwise.

Useful options

  • Turn ISO dates (2026-09-30T14:00:00Z) into real dates so Sheets can sort and filter them.
  • Add a "Fetched at" column to record when each row came in.
  • Use a cell value anywhere with {{Settings!B2}}, for a search term or an ID that changes.
  • Refresh automatically every hour, 6 hours, day or week.

As a formula

For a quick lookup, type =CELLFETCH("https://api.example.com/v1/products", "$.data[*]") in a cell. The table spills from that cell. Add options as a third argument, such as "fields=id,name as Name; pages=3". Formulas read up to 10 pages and 5,000 rows and cache the result for 10 minutes.

What it costs

The trial is 50 runs or 14 days, whichever comes first; previews don't count. Then $15 a month or $144 a year. Install from the Cellfetch page. To read an API's OpenAPI or Swagger file and find its endpoints first, our free OpenAPI viewer opens it in the browser.

Cellfetch: API & JSON Importer for Sheets

Import any API or JSON into Google Sheets, on a schedule $15 a month or $144 a year per Google account.


Questions

Can I import a JSON file from Google Drive?

Cellfetch reads JSON from a URL over https. A file in Drive needs a direct download link; an API or a public URL is the usual source.

Does it work with CSV too?

Yes. If the URL returns CSV, Cellfetch reads it as rows.

What if the JSON is a single object, not a list?

Point the records path at the object, and you get one row with a column per field.

Why does IMPORTJSON show #NAME? in my sheet?

IMPORTJSON isn't a Google Sheets function. It was a script people copied into their sheets, and it shows #NAME? when the script isn't there. Cellfetch is installed as an add-on, so =CELLFETCH works in any sheet where it's installed.


More Cellfetch guides


More Google guides