SheetAI

SHEETAI_PJSON Function Guide

Parse JSON strings and extract specific values with intelligent dot notation and array indexing

Install SheetAI

What is SHEETAI_PJSON Function?

The SHEETAI_PJSON (Parse JSON) function helps you parse JSON strings and extract specific values using dot notation and array indexing. It's perfect for working with API responses, complex data structures, or any JSON data you need to process in Google Sheets. Works seamlessly with SHEETAI_API for advanced data extraction.

Basic Syntax

The basic syntax is:

=SHEETAI_PJSON(jsonString, keyPath)

Parameters:

jsonString- The JSON string to parse (required)
keyPath- Dot-notation path to extract specific value (optional, returns entire JSON if omitted)

Examples

Simple Object Parsing

=SHEETAI_PJSON("{\"name\":\"John\", \"age\":30}", "name")

Output: John

Nested Object Access

=SHEETAI_PJSON("{\"user\":{\"profile\":{\"email\":\"john@example.com\"}}}", "user.profile.email")

Output: john@example.com

Array Element Extraction

=SHEETAI_PJSON("{\"items\":[\"apple\", \"banana\", \"orange\"]}", "items[1]")

Output: banana

Complex Nested Structure

=SHEETAI_PJSON("{\"data\":{\"users\":[{\"name\":\"Alice\",\"age\":25},{\"name\":\"Bob\",\"age\":30}]}}", "data.users[1].name")

Output: Bob

Parse Entire JSON

=SHEETAI_PJSON("{\"status\":\"success\", \"code\":200}", "")

Output: {"status":"success", "code":200}

Working with API Response

=SHEETAI_PJSON(A2, "response.data.url")

Where A2 contains a JSON string from an API call

Key Path Navigation Guide

Master the art of navigating JSON structures with these patterns:

PatternJSON StructureResult
name{"name":"John"}"John"
user.email{"user":{"email":"a@b.com"}}"a@b.com"
items[0]{"items":[1,2,3]}1
data.list[2].value{"data":{"list":[{},{},{"value":10}]}}10

Combining with SHEETAI_API

Use SHEETAI_PJSON to further process API responses from SHEETAI_API:

Step 1: Get API Response

=SHEETAI_API("https://api.example.com/data", "GET", "", "", "")

Step 2: Parse Specific Field (assuming above result is in A2)

=SHEETAI_PJSON(A2, "results[0].title")

Key Features

  • Intelligent JSON parsing
  • Dot notation for nested objects
  • Array indexing support
  • Deep nested access
  • Automatic type handling
  • Error handling and validation
  • Works with any JSON string

Common Use Cases

  • Parse API responses
  • Extract data from webhooks
  • Process complex JSON structures
  • Navigate nested objects
  • Access array elements
  • Transform JSON data
  • Data validation and extraction

Tips for Best Results

  • Always validate your JSON string is properly formatted
  • Use proper quote escaping (double quotes for quotes inside JSON strings)
  • Test your keyPath with simple examples first
  • Array indices start at 0 (first element is [0])
  • Use empty string as keyPath to get the entire parsed JSON
  • Check for undefined errors if a key doesn't exist in the JSON
  • Combine with SHEETAI_API for powerful data extraction workflows

💡 Pro Tip

When working with large or complex JSON responses, first use SHEETAI_PJSON without a keyPath to see the entire structure, then use specific keyPaths to extract the values you need.

⚠️ Common Errors

  • Invalid JSON format: Check for missing quotes, commas, or brackets
  • Key path not found: Verify the path exists in your JSON structure
  • Array index out of bounds: Make sure you're not accessing a non-existent array element
  • Quote escaping issues: Use double quotes for quotes inside JSON strings in Google Sheets