How to Import JSON Data into Google Sheets: 3 Simple Methods

Vinny Costapublished onJanuary 2, 2025

This guide will walk you through how to convert JSON into Google Sheets whether you're a developer, data analyst or have very basic spreadsheet knowledge. You'll learn how to leverage Apps Script, native formulas and third-party tools to parse your JSON data.

Method 1: Using Google Sheets Formulas

This method works great when your data has a consistent format with the same columns for each row. Think of data like customer lists, product catalogs, or reports from apps. No coding knowledge needed just copy and paste the formula.

The Formula

Let's say you have data that looks like this in cell A1:

[{"name":"John Doe","email":"john@example.com","age":30,"city":"New York"},{"name":"Jane Doe","email":"jane@example.com","age":35,"city":"Boston"}]

Requirements: Your data must have the same columns in every row, contain only text and numbers (no nested data), use double quotes around text values, and avoid commas within text values. If your data has nested structures, missing fields, or lists within fields, skip to Method 2 instead.

Copy and paste this formula into any cell (we'll use A2 as an example) to turn your data into a table:

=LET(
  json, A1,
  headers, {"Name","Email","Age","City"},
  body, REGEXREPLACE(json,"^[|]$",""),
  parts, TRANSPOSE(SPLIT(body,"},{",FALSE)),
  objects,
    ARRAYFORMULA(
      "{" &
      REGEXREPLACE(parts,"^{|}$","") &
      "}"
    ),
  table,
    BYROW(objects,
      LAMBDA(o,
        {
          REGEXEXTRACT(o,"""name"":""([^""]+)"""),
          REGEXEXTRACT(o,"""email"":""([^""]+)"""),
          VALUE(REGEXEXTRACT(o,"""age"":([0-9]+)")),
          REGEXEXTRACT(o,"""city"":""([^""]+)""")
        }
      )
    ),
  VSTACK(headers, table)
)

Customizing the Formula

To use this formula with your own data:

Change the column names in the headers part to match your data. For example, if you have products instead of people:
headers, {"Product","Price","Stock","Category"},
Update the field names inside the formula to match your data fields. Change name, email, etc. to your field names.
{ REGEXEXTRACT(o,"""product"":""([^""]+)"""), REGEXEXTRACT(o,"""price"":""([^""]+)"""), VALUE(REGEXEXTRACT(o,"""stock"":([0-9]+)")), REGEXEXTRACT(o,"""category"":""([^""]+)""") }
If your data is in a different cell (not A1), change A1 to your cell reference.

Try It Yourself

We've created a working example in Google Sheets for you to try out. Open the working example to see the formula in action.

Method 2: Using Google Apps Script

Google Apps Script gives you the power to parse complex JSON structures. Unlike formulas, Apps Script can handle nested objects, arrays, and multiple levels of data. It's perfect when you need more control over the parsing process. Apps Script is based on JavaScript, and you can learn more about it in the official Google Apps Script documentation.

Creating the Apps Script Function

Here's how to set up a custom function to parse JSON in Google Sheets:

Open your Google Sheet
Go to Extensions → Apps Script
Delete the default code and paste this function:

function parseJSON(jsonString, path) {
  try {
    const data = JSON.parse(jsonString);
    
    // Handle simple path like "name" or "email"
    if (!path.includes('/')) {
      return data[path] || '';
    }
    
    // Handle nested paths like "user/profile/name"
    const keys = path.split('/');
    let result = data;
    
    for (const key of keys) {
      if (result && typeof result === 'object' && key in result) {
        result = result[key];
      } else {
        return '';
      }
    }
    
    // If result is an array, join it
    if (Array.isArray(result)) {
      return result.join(', ');
    }
    
    return result || '';
  } catch (e) {
    return 'Error: ' + e.toString();
  }
}

Click Save and give your project a name
Return to your Google Sheet

Using the Custom Function

Now you can use parseJSON in your sheet. Let's say you have this JSON in cell A1:

{
  "users": [
    {
      "name": "John Doe",
      "contact": {
        "email": "john@example.com",
        "phone": "555-1234"
      },
      "tags": ["developer", "analyst"]
    },
    {
      "name": "Jane Smith",
      "contact": {
        "email": "jane@example.com",
        "phone": "555-5678"
      },
      "tags": ["designer", "manager"]
    }
  ]
}

To extract data into rows:

In cell B1, enter: =parseJSON(A1, "users/0/name") to get the first user's name
In cell C1, enter: =parseJSON(A1, "users/0/contact/email") to get the email
Copy these formulas down for additional users (changing the index number)

Try It Yourself

We've created a working example in Google Sheets for you to try out. Open the working example to see the formula in action.

Method 3: Using Third-Party Tools

When you need to import JSON from APIs, handle complex nested structures, or automate the process, third-party tools are your best bet. They handle parsing, error handling, and data transformation automatically.

These tools excel at:

Fetching JSON directly from API endpoints
Parsing deeply nested JSON structures
Automatically updating data on a schedule
Handling authentication and API keys
Transforming data before importing

Choosing the Right Method

Importing data into Google Sheets can be a complex task. Each method has its place. Here's when to use each one:

Formulas: Best for simple, flat JSON objects that you're importing once. No setup required, but limited to basic structures.
Apps Script: Ideal when you need to parse complex JSON but want to keep everything within Google Sheets. Requires JavaScript knowledge and has execution limits.
Third-Party Tools: Perfect for API integration, automation, and handling very complex JSON structures. Usually requires a subscription but saves significant time.

Consider your technical skills, the complexity of your JSON, and whether you need automation when choosing. For one-time imports of simple data, formulas work fine. For ongoing API integration, third-party tools are worth the investment.

Common Challenges and Solutions

Importing JSON into Google Sheets isn't always straightforward. Here are common issues and how to solve them:

Nested Objects

Deeply nested JSON structures can be tricky. Use Apps Script with path notation (like "user/profile/address/city") or third-party tools that automatically flatten nested structures.

Arrays of Objects

When your JSON contains arrays, you need to convert each array item into a row. The jsonToRows function we showed earlier handles this automatically. Third-party tools also excel at this transformation.

Large JSON Files

Google Sheets has cell size limits. Very large JSON strings might not fit. Consider parsing the JSON externally first, or use third-party tools that handle large datasets more efficiently. According to Google's documentation, individual cells can hold up to 50,000 characters, but performance may degrade with very large datasets.

Special Characters

JSON with special characters or unicode can cause parsing errors. Ensure your JSON is properly escaped. Apps Script's JSON.parse() handles most cases automatically, but malformed JSON will still fail.

API Rate Limits

When importing from APIs, be mindful of rate limits. Third-party tools often include retry logic and rate limit handling. If using Apps Script, implement delays between requests to avoid hitting limits.

Conclusion

Overall, importing JSON into Google Sheets is a hard task. JSON can have inconsistent format, deeply nested values and might require techinical knowledge to parse. These methods should help you cover some basic use cases.

These methods were created to help different levels of technical expertise. Choose the one that best fits your needs but JSON data will almost always require some level of manual input since it's highly flexible.

How to Import JSON Data into Google Sheets: 3 Simple Methods

Method 1: Using Google Sheets Formulas

The Formula

Customizing the Formula

Try It Yourself

Method 2: Using Google Apps Script

Creating the Apps Script Function

Using the Custom Function

Try It Yourself

Method 3: Using Third-Party Tools

Popular Third-Party Solutions

Choosing the Right Method

Common Challenges and Solutions

Nested Objects

Arrays of Objects

Large JSON Files

Special Characters

API Rate Limits

Conclusion