How to import JSON into Google Sheets – and export a sheet as JSON

Google Sheets cannot open a JSON file on its own: the import dialog understands CSV, TSV and Excel files, and the IMPORTDATA function only reads CSV or TSV. So when you have an API response, an export from another tool or a .json file and want it in a spreadsheet, you need something in between.

This guide covers both directions. First, how to import JSON into Google Sheets with SheetDB in about a minute, without writing a script, and how to do the same with Apps Script if you prefer to keep everything inside Google. Then the reverse: how to get any Google Sheet as JSON through a REST API, so your website or app can read the sheet, and how to keep the sheet and your JSON in sync.

Import a JSON file or URL into Google Sheets with SheetDB

SheetDB creates a new spreadsheet in your Google Drive from the JSON you give it, fills it with the data and gives you a REST API for that spreadsheet at the same time. The steps:

1. Sign in to SheetDB with your Google account. Google asks you to grant SheetDB access to your Google Sheets and to your basic profile (name and email address).
2. Click Create new API and open the Create new spreadsheet from JSON tab.
3. Type a Name. This becomes the file name of the new spreadsheet in your Drive.
4. Pick the Import source: Paste JSON to paste the content of your file into the text area, or Import JSON from URL to give the address of a public JSON file or API endpoint.
5. Click Create API. SheetDB creates the spreadsheet, writes the rows and shows the new API in your list. Open the spreadsheet from there or from Google Drive.

The whole thing takes less time than reading this paragraph, and you end up with two things: a normal Google Sheet you can edit, share and chart, and an endpoint such as https://sheetdb.io/api/v1/58f61be4dda40 that returns the same data as JSON.

What the import expects

The JSON should be an array of objects, one object per row. A single object is accepted too and becomes a one-row sheet.

[
  { "id": 1, "name": "Tom",   "age": 15, "comment": "" },
  { "id": 2, "name": "Alex",  "age": 24, "comment": "" },
  { "id": 3, "name": "John",  "age": 51, "comment": "" },
  { "id": 4, "name": "Steve", "age": 22, "comment": "special" },
  { "id": 5, "name": "James", "age": 19, "comment": "" }
]

This is how it maps to the sheet:

• Column headers come from the keys of the first object, in the order they appear: id, name, age, comment.
• Each object becomes one row. Values are written in the order they appear in the object, so every object should have the same keys in the same order. Most exports and API responses already look like this.
• Nested objects and arrays are stored as JSON text in their cell, for example {"city":"Berlin","zip":"10115"}. A cell cannot hold a sub-table. If you need nested fields as separate columns, flatten the JSON before importing (address.city becomes address_city).
• null becomes an empty cell. Numbers, dates and booleans are written the way you would type them, so Google Sheets recognises 24 as a number and 2026-09-28 as a date.
• Top-level arrays of plain values ([1, 2, 3]) and empty arrays are rejected, because there is no key to use as a column name.

Size limits

• Pasted JSON can be up to 1 000 000 characters (roughly 1 MB).
• A JSON URL must be publicly reachable, respond within 10 seconds, redirect at most 3 times and return at most 5 MB. Addresses pointing at private networks are refused.
• Google's own limits still apply: a single cell holds at most 50 000 characters and a spreadsheet at most 10 million cells. A very long nested object in one field is the usual way to hit the first one.

Import JSON into an existing sheet from code

Once an API exists, you can also import JSON into it over HTTP, which is handy in a deployment script or a nightly job. Use an empty tab so that the first row becomes the header, because the import appends below whatever is already there.

curl -X POST https://sheetdb.io/api/v1/{id}/import/json \
  -H "Content-Type: application/json" \
  -d '{"json": [{"id": 1, "name": "Tom", "age": 15}, {"id": 2, "name": "Alex", "age": 24}]}'

# or let SheetDB fetch the file for you
curl -X POST https://sheetdb.io/api/v1/{id}/import/url \
  -H "Content-Type: application/json" \
  -d '{"url": "https://example.com/data.json", "sheet": "Sheet2"}'

Both endpoints respond with {"created": 2}, require the API's create permission and clear the API cache afterwards. Reference: other methods in the docs.

Import JSON with Apps Script (manual alternative)

If you would rather not use a third-party service, Google Apps Script can fetch a JSON URL and write it into the active sheet. Open your spreadsheet, go to Extensions → Apps Script, paste the function below, save, and run it once. Google will ask you to authorise the script the first time.

function importJson() {
  const url = 'https://example.com/data.json';
  const rows = JSON.parse(UrlFetchApp.fetch(url).getContentText());
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const headers = Object.keys(rows[0]);
  const values = rows.map((row) => headers.map((key) => {
    const value = row[key];
    return value !== null && typeof value === 'object' ? JSON.stringify(value) : (value ?? '');
  }));

  sheet.clearContents();
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(2, 1, values.length, headers.length).setValues(values);
}

It works, and for a one-off import it is a perfectly good answer. The trade-offs show up later:

• The script lives inside one spreadsheet. Every new sheet needs a copy, and every change to the JSON structure needs a code change.
• Refreshing the data means adding a time-driven trigger and staying within Apps Script's daily quotas for UrlFetchApp and execution time.
• It only goes one way. You still have no API to read the sheet from a website or app, and no way to write rows back.
• Error handling is yours: a failed fetch or a malformed response leaves you with a half-cleared sheet unless you add checks.

If the JSON changes rarely and nobody else needs the data, use the script. If the sheet is going to feed a website, an app or a team, the import is only the first step and the next section matters more.

Get Google Sheets data as JSON (Sheets → JSON API)

Every API you create in SheetDB, whether from an imported JSON or from an existing spreadsheet, exposes the sheet as JSON. A plain GET request returns every row as an object keyed by the header row:

curl https://sheetdb.io/api/v1/58f61be4dda40

[
  { "id": "1", "name": "Tom",   "age": "15", "comment": "" },
  { "id": "2", "name": "Alex",  "age": "24", "comment": "" },
  { "id": "3", "name": "John",  "age": "51", "comment": "" },
  { "id": "4", "name": "Steve", "age": "22", "comment": "special" },
  { "id": "5", "name": "James", "age": "19", "comment": "" }
]

Try it in the browser: the example above is a real endpoint backed by this public spreadsheet. Useful parameters and endpoints:

• ?sheet=Sheet2 reads another tab; ?limit=20&offset=40 paginates; ?sort_by=age&sort_order=desc sorts.
• ?cast_numbers=id,age returns those columns as JSON numbers instead of strings.
• /search?age=>18&comment=special filters rows (AND); /search_or matches any condition (OR). Wildcards, negation and comparisons are supported.
• /keys returns the column names (["id","name","age","comment"]) and /count the number of rows.

Reference: Read and Search. Read requests are cached by SheetDB (see cache), so a page that fetches the JSON on every visit does not exhaust Google's quota; the current request limits are listed on the limits page.

Because the response is ordinary JSON, you can consume it from anywhere: a fetch() call in the browser, PHP, Node.js, a no-code tool, or a plain HTML snippet that displays the rows on your website. If you are planning to build on top of this, the guide on using Google Sheets as a database goes through structure, limits and caching in detail.

Keep JSON and the sheet in sync

Importing is a one-time copy. When the source data keeps changing, you have two options with the same API.

Re-import on a schedule. From a cron job, GitHub Action or automation tool, first empty the tab with DELETE /api/v1/{id}/all?with_first_row=true (the import writes its own header row, so the old header has to go too), then call /import/url or /import/json. Simple, but every run rewrites the whole sheet, so edits people made by hand are lost.

Push only the changes. Treat the sheet as a table and send the same operations you would send to a database:

curl -X POST https://sheetdb.io/api/v1/58f61be4dda40 \
  -H "Content-Type: application/json" \
  -d '{"data": [{"id": "INCREMENT", "name": "Mark", "age": "35", "comment": ""}]}'

curl -X PATCH https://sheetdb.io/api/v1/58f61be4dda40/id/4 \
  -H "Content-Type: application/json" \
  -d '{"data": {"comment": "special, renewed"}}'

curl -X DELETE https://sheetdb.io/api/v1/58f61be4dda40/id/2

POST appends rows (INCREMENT fills in the next id), PATCH /{column}/{value} updates the matching rows and DELETE /{column}/{value} removes them. Reference: Create, Update, Delete. Protect writes with HTTP Basic Auth in the API settings, or switch the create, update and delete permissions off for a read-only feed.

Which method should you use?

FAQ

Have question?

If you have any questions feel free to ask us via chat or .