How to automatically import JSON into Google Sheets

Key Takeaways

• JSON’s nested structure doesn’t map cleanly to spreadsheet rows — the real work is flattening and transforming data reliably, not just fetching it.
• Google Apps Script gives you a free, flexible option if you’re comfortable with JavaScript, but it requires ongoing maintenance as JSON schemas evolve.
• No-code automation platforms like n8n, Make, or Zapier are the practical choice for most small businesses that need recurring, reliable imports without engineering overhead.
• The most common failure points are not handling API rate limits, ignoring schema changes, and skipping error handling — all fixable with upfront planning.
• Choose your method based on import frequency, data complexity, and your actual tolerance for maintenance — not just what’s easiest to set up today.

---

Why JSON Import Breaks Down in Practice

If you’ve ever tried to paste a JSON API response directly into Google Sheets, you already know the problem. The data doesn’t land cleanly in rows and columns. Nested objects collapse into unreadable strings. Arrays either disappear or overwrite adjacent cells. You end up spending more time cleaning the output than you saved by pulling the data in the first place.

The underlying issue is structural. JSON is designed for hierarchical, relationship-aware data. Google Sheets is designed for flat tables. Bridging that gap requires a deliberate transformation step — and that step is where most DIY setups either cut corners or eventually break.

This guide walks through three practical approaches to automating that transformation: using Google Apps Script for a native, code-based solution; using no-code automation tools for recurring workflows; and handling complex nested structures that don’t flatten automatically. Each section covers setup, common failure points, and what to expect when you run it in production.

---

What Makes JSON Import Technically Difficult

Before choosing an approach, it helps to understand what you’re actually asking Google Sheets to do.

A simple JSON array — a flat list of objects with identical keys — maps to a spreadsheet reasonably well. One object per row, one key per column. This is the easy case and it works with minimal transformation.

The harder cases, which represent the majority of real API responses, include:

• Nested objects: A field like customer.contact.email needs to become a column header like customer_contact_email or be split across related tables.
• Arrays within objects: An order record containing multiple line items needs either one row per line item (creating duplicate parent data) or a concatenated summary cell.
• Inconsistent field presence: Some records have a discount field, others don’t. Your import process needs to handle null and missing values without breaking column alignment.
• Mixed data types: A field that’s a number in some records and a string in others will create silent formula errors downstream.
• Large payloads: Google Sheets has a practical limit of around 10 million cells per spreadsheet. API responses that paginate or return thousands of records need batching logic built in from the start.

Understanding which of these applies to your data determines which import method makes sense.

---

Method 1: Google Apps Script

Apps Script is Google’s built-in JavaScript environment, accessible from any Sheet via Extensions > Apps Script. It runs in Google’s cloud, so it doesn’t depend on your machine being on, and it can be triggered on a schedule or by a spreadsheet event.

The approach involves writing a function that fetches JSON from a URL, parses the response, flattens the structure into rows, and writes those rows to the sheet. Here’s a minimal working pattern:

function importJSONData() {
  var url = "https://api.example.com/records";
  var options = {
    headers: { "Authorization": "Bearer YOUR_TOKEN" }
  };

  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Import");
  sheet.clearContents();

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

  // Write headers from first record
  var headers = Object.keys(flattenObject(data[0]));
  sheet.appendRow(headers);

  // Write each record as a row
  data.forEach(function(record) {
    var flat = flattenObject(record);
    var row = headers.map(function(h) { return flat[h] || ""; });
    sheet.appendRow(row);
  });
}

function flattenObject(obj, prefix) {
  prefix = prefix || "";
  var result = {};
  for (var key in obj) {
    var val = obj[key];
    var newKey = prefix ? prefix + "_" + key : key;
    if (typeof val === "object" && val !== null && !Array.isArray(val)) {
      var nested = flattenObject(val, newKey);
      for (var nk in nested) result[nk] = nested[nk];
    } else if (Array.isArray(val)) {
      result[newKey] = JSON.stringify(val);
    } else {
      result[newKey] = val;
    }
  }
  return result;
}

To run this automatically, go to Triggers in Apps Script and set importJSONData to fire on a time-based schedule — hourly, daily, or whatever fits your data freshness requirements.

What works well: It’s free, lives entirely in Google’s ecosystem, and handles moderate data volumes reliably. API keys and tokens can be stored securely using PropertiesService rather than hardcoded in the script.

Where it struggles: When the JSON schema changes — a renamed field, a new nesting level, an added array — the script needs manual updating. If you’re not monitoring it, you may not notice a quiet failure for days. You also need to build your own error handling and retry logic; there’s no dashboard showing you what ran, when, and whether it succeeded.

For teams with a developer on staff or a technically comfortable founder, Apps Script is a solid, zero-cost option. For everyone else, the maintenance burden tends to accumulate.

---

Method 2: No-Code Automation Platforms

For businesses that need recurring JSON imports without writing or maintaining code, automation platforms are the practical choice. The main options in this space — Make, Zapier, and n8n — all support HTTP requests, JSON parsing, and Google Sheets write operations through visual workflow builders.

General workflow structure:

1. A trigger fires the workflow on a schedule or receives a webhook from your data source.
2. An HTTP module fetches the JSON from your API endpoint, with headers for authentication.
3. A data transformation step flattens nested fields and handles arrays.
4. A Google Sheets module appends or updates rows with the parsed data.

Make (formerly Integromat) handles complex data transformations well and is generally the better fit for non-trivial JSON structures. Its iterator module processes arrays natively, creating one row per array item without custom code.

Zapier has a gentler learning curve and more extensive documentation, which matters when you’re setting something up for the first time. Its Formatter step handles basic JSON parsing, though deeply nested structures may require multiple formatter steps in sequence.

n8n is an open-source option that can be self-hosted, making it attractive if you have existing infrastructure and want to avoid per-task pricing. It requires more technical setup than the other two but offers more flexibility at scale.

Practical considerations:

• Build in an error branch that logs failures to a separate sheet or sends a notification. Silent failures are harder to catch than noisy ones.
• Add a deduplication check if your API returns overlapping records across runs — a unique ID column and a lookup before appending saves a lot of cleanup time.
• Most APIs have rate limits. Check the documentation and add a delay between paginated requests if you’re pulling large datasets in batches.

In our work helping founder-led businesses automate their data workflows, the most common gap we see with no-code setups isn’t the initial configuration — it’s the absence of monitoring. A workflow that stops running without alerting anyone can leave reporting dashboards stale for weeks before someone notices.

---

Method 3: Handling Complex Nested JSON

Both Apps Script and no-code platforms can struggle with JSON that’s genuinely complex — multiple levels of nesting, arrays of objects, polymorphic fields. This section covers the transformation logic you need regardless of which method you’re using.

Flattening nested objects means converting dot-notation paths into column headers. A field at customer.contact.email becomes the column customer_contact_email. The flattenObject function in the Apps Script example above does this recursively. In Make, the Map function can extract nested fields by path.

Handling arrays requires a decision: do you want one row per array item, or a summary in a single cell?

One row per item is better for analysis — you can filter, aggregate, and build pivot tables. But it means parent-level data repeats across multiple rows, which can cause issues if someone tries to count unique records.

A summary cell (JSON-stringified or concatenated values) keeps the sheet clean but makes the array data harder to query. For most reporting use cases, one row per item is the right call, with a parent ID column to link related rows.

Missing fields need a default. An empty string works for text fields. A zero or null for numeric fields, depending on whether the absence means zero or unknown. Decide this upfront and apply it consistently — inconsistent nulls break SUMIF and COUNTIF formulas in ways that are annoying to debug.

Date and time fields in JSON are typically ISO 8601 strings (2024-03-15T09:00:00Z). Google Sheets doesn’t automatically recognize these as dates. You’ll need to parse and convert them during import, either with new Date() in Apps Script or a date formatter step in your automation platform.

---

Choosing the Right Approach for Your Situation

The right method depends on three variables: how often you need to import, how complex your JSON structure is, and how much ongoing maintenance you’re willing to own.

Scenario	Recommended Approach
One-time or occasional import, simple JSON	Manual copy-paste with a JSON-to-CSV converter tool
Recurring import, technical team available	Google Apps Script with scheduled triggers
Recurring import, no developer on staff	Make or Zapier with error alerting configured
Real-time updates via webhook	Apps Script doPost() handler or Make webhook trigger
Complex nesting, large volume, business-critical	Consider a dedicated ETL tool or professional implementation

A few things worth noting: setup time is not the same as total time. A Zapier workflow might take 90 minutes to configure versus 3 hours for an Apps Script solution. But if the Zapier workflow needs no maintenance for a year and the Apps Script breaks twice in six months, the total time cost is different from what the initial setup suggested.

Budget for maintenance time honestly. APIs change. JSON schemas evolve. Authentication tokens expire. None of these are edge cases — they’re the normal lifecycle of any data integration.

---

Common Mistakes That Cause Silent Failures

Not validating JSON before importing. A single malformed record can cause an entire batch to fail, depending on how your parser handles errors. Run JSON through a validator and test with edge-case records before deploying to production.

Overwriting instead of appending. Clearing the sheet and re-importing everything works for small datasets but becomes slow and error-prone at scale. Design your import to append new records and update existing ones by key, rather than wiping and reloading.

Skipping rate limit handling. Exceeding an API’s rate limit returns a 429 error, and if your script doesn’t handle that gracefully, it either fails silently or enters an infinite retry loop. Check the API documentation and build in appropriate delays for paginated requests.

Not monitoring scheduled jobs. A trigger that stops firing, an authentication token that expires, an API endpoint that changes — none of these will tell you something’s wrong unless you’ve set up alerting. Log every run’s outcome and alert on consecutive failures.

Letting sheets grow without a data management plan. Google Sheets degrades in performance as data accumulates. If you’re importing daily, plan for how you’ll archive older rows before the sheet becomes unusable. A monthly archiving step that moves data to a secondary sheet or exports to a CSV is straightforward to automate.

---

Getting This Right the First Time

JSON-to-Sheets automation is one of those tasks that looks simple until you’re maintaining it in month four and the API provider has changed three field names and added a new nested array without updating their documentation.

The techniques in this guide — flattening nested objects, handling arrays, building error handling into your imports, monitoring scheduled jobs — are what separate workflows that run reliably from workflows that need constant patching.

If you’re dealing with JSON import as part of a larger operational workflow — pulling CRM data, processing webhook events, aggregating reporting across multiple sources — it’s worth thinking about the full picture before building individual scripts in isolation.

If you want a second opinion on how to structure your data workflow before you build it, Basalt Studio offers AI strategy calls for exactly this kind of conversation. No pitch, no package — just a direct look at what you’re trying to automate and what approach is likely to hold up.

Book a call with the team here.