How to Use JSON Data in Google Sheets

Google Sheets is the default spreadsheet tool for many teams, especially those built around Google Workspace. But Sheets was designed for CSV and tabular data — not JSON. Getting JSON data into Sheets requires a bit of creativity, but it is very doable.

This guide covers four methods, from the simplest one-off approach to automated, scheduled imports.

Method 1: Convert to Excel First, Then Import

The simplest approach for a one-time data import:

1. Convert your JSON to Excel using jsontoexcel.net
2. Open Google Sheets and go to File → Import
3. Upload the .xlsx file
4. Choose your import options (new sheet, replace, etc.) and click Import data

This is the most reliable method for complex or nested JSON, because the conversion step handles all the flattening before the data reaches Sheets.

Method 2: Apps Script — Custom Import Function

Google Apps Script is a JavaScript runtime built into Google Sheets. You can write a script that fetches JSON from a URL or a pasted string and writes it to the sheet.

Importing from a URL

1. Open your Google Sheet
2. Go to Extensions → Apps Script
3. Paste the following code and save:

function importJsonFromUrl() {
  const url = 'https://api.example.com/data';
  const response = UrlFetchApp.fetch(url);
  const json = JSON.parse(response.getContentText());

  // Handle both array-at-root and wrapped responses
  const records = Array.isArray(json) ? json : json.data || json.results || [];

  if (records.length === 0) {
    SpreadsheetApp.getUi().alert('No records found in response.');
    return;
  }

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

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

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

  SpreadsheetApp.getUi().alert(`Imported ${records.length} rows.`);
}

Adding Authentication Headers

If the API requires an API key or Bearer token:

const options = {
  headers: {
    'Authorization': 'Bearer YOUR_TOKEN_HERE',
    'Accept': 'application/json'
  }
};
const response = UrlFetchApp.fetch(url, options);

Handling Nested JSON

If your JSON has nested objects, flatten them before writing to the sheet:

function flattenObject(obj, prefix) {
  prefix = prefix || '';
  return Object.keys(obj).reduce(function(acc, key) {
    const fullKey = prefix ? prefix + '.' + key : key;
    const val = obj[key];
    if (val !== null && typeof val === 'object' && !Array.isArray(val)) {
      Object.assign(acc, flattenObject(val, fullKey));
    } else {
      acc[fullKey] = Array.isArray(val) ? val.join(', ') : val;
    }
    return acc;
  }, {});
}

Method 3: Scheduled Auto-Refresh with Apps Script Triggers

Apps Script can run on a schedule — hourly, daily, or weekly — to keep your sheet automatically updated.

1. In the Apps Script editor, click the clock icon (Triggers) in the left sidebar
2. Click + Add Trigger
3. Choose your function (importJsonFromUrl), event source (Time-driven), and interval
4. Click Save

Your sheet will now refresh automatically without any manual action.

Method 4: ImportJSON Add-on

ImportJSON is a popular Google Sheets add-on that adds a custom formula for pulling JSON directly into cells. Once installed, you can use it like any Sheets formula:

=ImportJSON("https://api.example.com/data", "/name,/age", "noHeaders")

The second parameter is a JSON path selector — it controls which fields appear as columns. The third parameter controls formatting options.

To install: Extensions → Add-ons → Get add-ons → search "ImportJSON".

Handling Common Issues

Dates appear as numbers

JSON dates are typically ISO 8601 strings like "2026-04-15". In Apps Script, format the column as a date after import:

sheet.getRange(2, dateColumnIndex, rows.length, 1)
     .setNumberFormat('yyyy-mm-dd');

Booleans show as TRUE/FALSE

Google Sheets treats the string "true" and the boolean true differently. Use String(val) when writing boolean values to ensure consistent display.

Large datasets are slow

Writing to Google Sheets row-by-row in a loop is very slow because each write is an API call. Always batch your writes using setValues() with a 2D array, as shown in the example above. This reduces thousands of API calls to a single one.

Google Sheets vs Excel for JSON Data