[]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

FILTERJSON

This function parses data from a JSON string.

Syntax

FILTERJSON(json_string)

Arguments

This function has the following argument:

Argument

Description

json_string

[required] JSON string data in a valid format.

Remarks

If the JSON string has invalid format, it returns #VALUE! error. A few examples with their expected results are shown below:

JSON String

Formula

Result

{"a": 123}

FILTERJSON("{""a"": 123}")

[object Object]

"123"

FILTERJSON("123")

123

[1,2, "string"]

FILTERJSON([1,2,"string"])

#VALUE!

{a: 123}

FILTERJSON("{a: 123}")

#VALUE!

'[1, "string", {"a": 123}]'

FILTERJSON("[1, ""string"", {""a"": 123}]")

1 | string | [object Object]

Data Types

Accepts string data. Returns scalar value, an object, or an array of objects.

Examples

You can parse the data from a JSON string.

var json_string = `{
    "store": {
        "book": [
            {
                "category": "reference",
                "author": "Nigel Rees",
                "title": "Sayings of the Century",
                "price": 8.95
            },
            {
                "category": "fiction",
                "author": "Evelyn Waugh",
                "title": "Sword of Honour",
                "price": 12.99
            },
            {
                "category": "fiction",
                "author": "Herman Melville",
                "title": "Moby Dick",
                "isbn": "0-553-21311-3",
                "price": 8.99
            },
            {
                "category": "fiction",
                "author": "J. R. R. Tolkien",
                "title": "The Lord of the Rings",
                "isbn": "0-395-19395-8",
                "price": 22.99
            }
        ],
        "bicycle": {
            "color": "red",
            "price": 19.95
        }
    },
    "expensive": 10
}`;
sheet.setValue(4,0,json_string);
sheet.setFormula(5,0,'=FILTERJSON(A5)');
sheet.setFormula(6,0,'=PROPERTY(A6,"store.book.0.title")'); // "Sayings of the Century"

You can use FILTERJSON function with a nested WEBSERVICE function to parse a URL containing JSON data. The parsed data can be displayed by using PROPERTY function.

sheet.setFormula(0,0,'=FILTERJSON(WEBSERVICE("https://cors-demo.glitch.me/allow-cors"))'); // WEBSERVICE get the json data.
sheet.setFormula(1,0,'=PROPERTY(A1,"message")'); // "You are handling CORS like a pro!"

spread.options.allowDynamicArray = true; // allow dynamicArray
sheet.setFormula(2,0,'=FILTERJSON(WEBSERVICE("https://restcountries.eu/rest/v2/name/Japan"))'); // WEBSERVICE get the json data.
sheet.setFormula(3,0,'=PROPERTY(A3,"name")'); // "Japan"