How to Import JSON Data Into Google Sheets (3 Methods)

Google Sheets does not import JSON directly — but there are three reliable methods to get JSON data into a spreadsheet. The right one depends on where your JSON is coming from and whether you need a one-time import or a live-updating feed.

Method 1: Convert to Excel First, Then Upload to Google Sheets

This is the simplest approach for any JSON array:

1. Paste your JSON into the converter at the top of this page
2. Download the .xlsx file
3. Open Google Drive
4. Drag and drop the .xlsx file, or go to New > File upload
5. Right-click the uploaded file and select Open with Google Sheets

Google Sheets opens .xlsx files natively and preserves column headers, data types, and formatting. For a one-time import, this takes under two minutes with no formula or scripting knowledge required.

Method 2: IMPORTDATA and IMPORTJSON for Live API Data

If your JSON is hosted at a public URL (a REST API endpoint, a GitHub raw file, a Google Cloud Storage bucket), you can pull it into Sheets with a formula.

Google Sheets does not have a native IMPORTJSON function, but the open-source ImportJSON Apps Script is widely used:

1. In your Google Sheet, go to Extensions > Apps Script
2. Paste the ImportJSON script from GitHub (search "ImportJSON google sheets apps script")
3. Save and return to your sheet
4. Use the formula: =ImportJSON("https://api.example.com/data", "/", "noHeaders")

This auto-refreshes when the sheet reloads, making it useful for API data you want to track over time.

For simple CSV-formatted data, =IMPORTDATA("URL") works natively without any script installation.

Sell Custom Apparel — We Handle Printing & Free Shipping

Method 3: Apps Script to Paste JSON Directly

If you have a JSON string (not a URL), you can parse it directly with a custom Apps Script function:

// In Apps Script (Extensions > Apps Script)
function importJsonString(jsonString, sheetName) {
  const data = JSON.parse(jsonString);
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName) ||
    SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);

  if (!Array.isArray(data) || data.length === 0) return;

  // Write headers
  const headers = Object.keys(data[0]);
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  // Write data rows
  const rows = data.map(row => headers.map(h => row[h] ?? ''));
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

Call it from a sheet cell with =importJsonString(A1, "Output") where A1 contains your JSON string, or run it directly from the Apps Script editor.

Excel Upload vs Formula vs Apps Script — Which to Use

• One-time import, any JSON source: Use Method 1 (Excel upload). Fastest, no setup, handles nested objects via dot-notation columns.
• Live API data that updates automatically: Use Method 2 (ImportJSON formula). Data refreshes when the sheet is reopened. Best for dashboards pulling from APIs.
• Programmatic or automated import: Use Method 3 (Apps Script). Useful when another tool sends JSON to a script, or when you want to trigger imports on a schedule via Apps Script triggers.
• Private/sensitive JSON: Use Method 1 only — do not paste sensitive data into public API formulas.

Frequently Asked Questions

Does Google Sheets support direct JSON file upload?

No. Google Sheets imports CSV, TSV, and Excel (.xlsx, .xls) formats natively. For JSON, use one of the three methods above. The Excel upload method (Method 1) is the most straightforward since it requires no scripting.

Will the JSON to Excel converter preserve data for Google Sheets?

Yes. The .xlsx file produced by the converter opens in Google Sheets with correct column headers, data types (numbers as numbers, text as text), and no data loss. Google Sheets handles .xlsx files natively.

Can I use Google Sheets to connect to a JSON API automatically?

Yes, with the ImportJSON Apps Script. It calls your API URL and parses the JSON into rows and columns. It refreshes when the spreadsheet is opened, or you can set a time-based trigger to refresh on a schedule.