SpreadJS 14
Formula Reference / Formula Functions / Web Functions / FILTERJSON
In This Topic
    FILTERJSON
    In This Topic

    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.

    JavaScript
    Copy Code
    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.

    JavaScript
    Copy Code
    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"