Json-for-Google-Sheets

JSON for Sheets

Robust, analyst-friendly JSON Functions for Google Sheets™.

Overview

JSON for Sheets creates a bridge between complex data structures and your spreadsheets. Whether you are working with API responses, configuration files, or complex logs, this script brings the power of structural data parsing directly into your Google Sheets workflow.

Stop struggling with custom Apps Script coding or complex parsing logic. Copy this script once, and use native-like formulas to parse, filter, and transform JSON data instantly.

Key Features

🚀 Powerful Formulas

Unlock a suite of custom functions designed for data analysts and developers:

=PARSE_JSON(json_string) Convert JSON strings into readable, flattened 2D tables or key-value maps instantly.
=JSON_GET(json_string, path) Extract specific values using intuitive dot and bracket notation (e.g., user.address.city or items[0].id).
=JSON_TO_TABLE(json_array) Transform arrays of objects into structured tables with automatic headers. Perfect for API lists.
=JSON_FILTER(json_array, expression) Filter JSON arrays directly in your formula using simple expressions like age > 21 or status == "active".
=JSON_VALIDATE(json_string, schema) Validate your JSON integrity against a defined schema to ensure data quality.

🔒 Secure & Private

Local Execution: All data processing happens locally within your Google Sheets execution environment.
No External Servers: Your data does not leave Google’s infrastructure to be processed by our servers.

Quick Setup

[!IMPORTANT] This is NOT a Google Workspace Add-on and NOT a library. It is a simple, plug-and-play script that runs entirely inside your sheet’s own Apps Script editor. No marketplace installation is required, and your data remains 100% private.

All you have to do is copy and paste the script:

Open your Google Sheet.
Go to Extensions > Apps Script in the top menu.
If there is any default code in the editor, clear it.
Copy the entire content of JSON_Fuctions_for_google_sheets.gs from this repository and paste it into the editor.
Click Save (the floppy disk icon) or press Cmd + S / Ctrl + S.
Go back to your Google Sheet. The functions are now ready to use! Just type the formulas directly into any cell (e.g., =JSON_GET(...)).

Usage Examples

1. Extracting a Single Value

Got a cell A1 with JSON content: {"id": 101, "name": "Alice"}?

=JSON_GET(A1, "name")

Result: Alice

2. Converting API Data to a Table

If cell A1 contains a JSON array of users:

=JSON_TO_TABLE(A1)

Result: A dynamic table expanding to fit all rows and columns from the JSON data.

3. Filtering Data on the Fly

Filter a list of orders in A1 where the total is greater than 100:

=JSON_FILTER(A1, "total > 100")

Why Choose JSON for Sheets?

SEO & Data Analysis Friendly: Perfect for SEO professionals importing structured data (Schema markup) or analysts working with REST APIs.
No Coding Required: You don’t need to write Apps Script code to handle JSON.
Performance: Optimized for speed and reliability within the Sheets environment.

License

This project is released under the MIT License. It is completely open-source and free to use, modify, or distribute.

Google Sheets™ is a trademark of Google LLC.

This site is open source. Improve this page.