How to Import JSON into Google Sheets (4 Methods Compared)

Open Google Sheets' File → Import dialog and you'll see options for CSV, TSV, and Excel files — but nothing for JSON. That's not an oversight. JSON is a tree-shaped format (objects nesting inside objects, arrays inside arrays), and a spreadsheet is a flat grid of rows and columns. Google Sheets has no built-in way to decide how to flatten one into the other, so it just doesn't try.

That means importing JSON always takes one extra step compared to CSV. Which extra step depends on where your JSON is coming from and whether you need it once or on a recurring basis. This guide covers all four real options, in order of how most people should actually try them.

Why Google Sheets Can't Just "Import" JSON

A CSV file already looks like a spreadsheet — commas mark column breaks, newlines mark row breaks. Google Sheets can map that onto a grid with no decisions to make.

JSON has no such 1:1 mapping. Take a single API response:

[
  { "id": 1, "name": "Ada Lovelace", "roles": ["admin", "editor"] },
  { "id": 2, "name": "Grace Hopper", "roles": ["editor"] }
]

The id and name fields map cleanly to columns. But roles is an array — does it become one cell with admin, editor inside it? Two separate columns, role_1 and role_2? A new row per role? All three are defensible, and different tools pick different defaults. That ambiguity is exactly why Google Sheets leaves JSON out of its native import options and leaves the flattening decision to you (or your tool).

Method 1: Convert JSON to CSV First (Fastest, No Code)

For a one-time import — you have a .json file and just need it in a spreadsheet — this is the method to reach for first. It takes under a minute and needs no scripting.

This works well for arrays of flat or lightly-nested objects — the shape most JSON API responses and export files actually use. If you're on a Mac and this file is one you'll open repeatedly, CSVtoSheets skips the manual upload step entirely: double-click the converted CSV and it opens straight into Google Sheets.

Best for: one-off imports, JSON files someone sent you, export files from an app or database. Limitations: deeply nested objects (arrays inside arrays inside objects) flatten to a readable but not perfectly structured cell — fine for scanning, not always ideal for formulas that need to reference individual array items.

Method 2: Pull JSON Directly with Google Apps Script

If your JSON lives behind a URL — a public API, an internal endpoint, a webhook — and you want the sheet to refresh with live data instead of re-uploading a file every time, Apps Script is the right tool. It runs on Google's servers, so the sheet updates even when your computer is off.

Open Extensions → Apps Script from any Sheet and paste in:

function importJsonToSheet() {
  const url = "https://api.example.com/users"; // your JSON endpoint
  const response = UrlFetchApp.fetch(url);
  const data = JSON.parse(response.getContentText());

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clearContents();

  if (data.length === 0) return;

  // Header row from the keys of the first object
  const headers = Object.keys(data[0]);
  sheet.appendRow(headers);

  // One row per object, flattening arrays/objects to a readable string
  data.forEach(function (item) {
    const row = headers.map(function (key) {
      const value = item[key];
      if (Array.isArray(value) || (typeof value === "object" && value !== null)) {
        return JSON.stringify(value);
      }
      return value;
    });
    sheet.appendRow(row);
  });
}

Run it once from the Apps Script editor (▶ Run) and authorize the permissions prompt — Sheets needs your approval to fetch external URLs. Every time you run the function, it re-fetches the JSON and refreshes the sheet.

If the endpoint requires an API key, add it as a header:

const response = UrlFetchApp.fetch(url, {
  headers: { Authorization: "Bearer YOUR_API_KEY" }
});

Best for: recurring pulls from an API, dashboards that need to stay current, internal tools where the data source changes daily. Limitations: requires editing a script (copy-paste is enough for this one, but any customization means touching JavaScript); Apps Script triggers have Google's execution-time and daily-quota limits, which only matter at high volume.

Method 3: Community IMPORTJSON Custom Function

Several community-written IMPORTJSON() custom functions circulate as copy-paste Apps Script snippets, meant to work like Sheets' built-in IMPORTDATA() but for JSON — you'd write =IMPORTJSON("https://api.example.com/data") directly into a cell.

They work, and they're worth knowing about, but two caveats apply:

• They're not an official Google function. Each one is someone's personal script, pasted into your project's Apps Script editor the same way as Method 2's code. Quality and maintenance vary, and Google won't ever add IMPORTJSON natively — it can't sandbox an arbitrary external fetch inside a formula the way IMPORTDATA does for plain text.
• Review the script before running it. Since it's third-party code with server access to fetch URLs on your behalf, read through what you're pasting rather than copying blindly from an unfamiliar source — the same caution you'd apply to any script you didn't write.

For most people, Method 2's version is preferable specifically because you can read and modify a 20-line function far more easily than debug someone else's custom-function implementation when it breaks.

Method 4: No-Code Connectors

Tools like Coupler.io, Sheetgo, and similar Sheets add-ons offer a visual interface for connecting an API or JSON source to a spreadsheet — point-and-click field mapping, scheduled refresh, no code at all.

They're worth considering if:

• You need to connect several different APIs and don't want to maintain separate scripts for each
• Non-technical teammates need to set up or modify the connection themselves
• You want built-in error alerting and connection monitoring

The tradeoff is that most of these tools are subscription products beyond a limited free tier, and you're depending on a third-party service to keep the connector reliable — versus Apps Script, which runs entirely within your own Google account.

Comparing the Four Methods

If you're not sure where to start: try Method 1 first. It takes under a minute and covers the majority of real-world cases — someone sending you a JSON export, a one-time API response you saved to a file, a data dump from a tool that doesn't offer CSV export directly. Only move to Apps Script (Method 2) once you actually need the sheet to stay live against a changing source.

Handling Nested and Deeply Nested JSON

The trickiest part of any JSON-to-spreadsheet conversion is deciding what to do with nested structures. A few practical rules:

• Flat objects ({"name": "Ada", "age": 36}) map directly to columns — no decisions needed.
• Arrays of primitives ("roles": ["admin", "editor"]) are usually best serialized into a single cell (admin, editor) unless you specifically need to filter or count by individual role, in which case flatten to one row per role-object pair instead.
• Nested objects ("address": {"city": "NYC", "zip": "10001"}) are cleanest when flattened to prefixed columns (address_city, address_zip) rather than a single JSON-string cell, if you plan to sort or filter on them.
• Arrays of objects nested inside a larger object are the hardest case — there's often no single "correct" flattening, and the right choice depends on what you're doing with the data downstream (a pivot table wants one row per nested item; a reference lookup wants one row per top-level item with the nested data serialized).

Our JSON to CSV converter uses the single-cell-serialization approach for nested values by default, since it's the safest choice for arbitrary JSON shapes without knowing your downstream use case. For structured, high-volume nested data where you need one row per nested item, the Apps Script route (Method 2) gives you full control over exactly how each field maps to a column.

Frequently Asked Questions

Q: Can I paste JSON directly into a Google Sheets cell and have it auto-split into columns? A: No — Sheets' automatic paste-splitting only recognizes tab and comma delimiters, not JSON syntax. Convert to CSV first (Method 1), then paste; the CSV's commas will split correctly.

Q: Does Google Sheets have a built-in IMPORTJSON function, the way it has IMPORTDATA? A: No. IMPORTDATA and IMPORTHTML are official Google functions; IMPORTJSON only exists as unofficial, community-written Apps Script that mimics the same calling pattern (see Method 3).

Q: My JSON has thousands of records. Will the browser-based converter handle it? A: Yes — the JSON to CSV converter processes everything locally in your browser, so there's no server-side upload limit. Very large files (tens of thousands of records) may take a few seconds to process depending on your device.

Q: How do I go the other direction — export a Google Sheet as JSON? A: Google Sheets doesn't offer a native JSON export either, for the same flattening-ambiguity reason. Export as CSV (File → Download → Comma-separated values) and use our CSV to JSON converter to convert it, or write a short Apps Script function that reads the sheet's rows and calls JSON.stringify().

Q: Is it safe to paste an unfamiliar community Apps Script into my Google account? A: Treat it like installing any browser extension or npm package from an unfamiliar source — read through what it does before running it, since it will ask for permission to fetch external URLs on your behalf. If you can't read JavaScript well enough to review it, Method 1 (no script needed at all) is the safer default.

---

Related Resources

• Free JSON to CSV Converter
• Free CSV to JSON Converter
• How to Import CSV to Google Sheets (4 Methods Compared)
• How to Automate CSV to Google Sheets Conversion
• What Is a CSV File? Format Guide, Examples & How to Open