=JSONEXTRACT
The =JSONEXTRACT() function scans a text string and extracts the first valid JSON object or JSON array it finds. If valid JSON exists within the text, only the JSON portion is returned. If no valid JSON is found, the function returns nothing.
Syntax
Section titled “Syntax”=JSONEXTRACT(AnyTextContainingJSON)Parameters
Section titled “Parameters”-
AnyTextContainingJSON
- Any text value that may include embedded JSON data (object or array).
How It Works
Section titled “How It Works”-
The function scans the text from left to right.
-
The first valid JSON object or array is identified and extracted.
-
If no valid JSON structure exists, the result is empty.
Usage Notes
Section titled “Usage Notes”-
Only the first valid JSON structure is returned if multiple exist.
-
Returns nothing if the text does not contain valid JSON.
-
Supports both JSON objects and arrays.
-
Formula function names are case sensitive and must be written in ALL CAPS.
Examples
Section titled “Examples”Extracting a JSON Object from Mixed Text
=JSONEXTRACT("The API response was: {\"status\":\"success\",\"data\":{\"id\":123,\"name\":\"John\"}} and processing completed.")Returns:
{"status":"success","data":{"id":123,"name":"John"}}Extracting a JSON Array from Text
=JSONEXTRACT("Processing items: [\"item1\",\"item2\",\"item3\"] - batch completed")Returns:
["item1","item2","item3"]Common Use Cases
Section titled “Common Use Cases”-
Parsing API responses from log files
-
Extracting configuration data from mixed content
-
Processing webhook payloads
-
Cleaning scraped web data containing JSON
-
Parsing email content with embedded structured data
-
Processing chat messages with configuration snippets
-
Extracting metadata from file headers
Keywords
Section titled “Keywords”JSONEXTRACT, extract JSON, parse JSON from text, JSON parsing, API response parsing, webhook processing, structured data extraction, formula function