Google-sheets – Parse JSON from Google Spreadsheet Cell

google sheetsgoogle-appsgoogle-apps-scriptjson

I am working on an API with google spreadsheet and app script to retrieve JSON response; I am using ImportJSON function (custom function by Brad Jasper and Trevor Lohrbeer) to retrieve and parse JSON response and at this point everything works fine.

Now, I want to:
1- retrieve JSON response
2- save it in a cell
3- parse JSON from cell

Retrieving and saving is possible with ImportData function and some custom function to save / convert it into value. But ImportJSON is not able to parse JSON so is there any other custom function which can do same functionality from cell not from url?
Following is sample of my JSON response.

{
    "status": true,
    "message": "Successfully received Data.",
    "is_child": true,
    "child_items": [
        "AabUf_Hw",
        "95Uw3NK4",
        "gfbRCgrM",
        "n8TZGk3k",
        "8Fu0IHpg"
    ]
}

Best Answer

You can build a custom parser for any fixed JSON string using regular expressions. For example, if you want to extract the child items into columns you can do the following (assuming your JSON string is in A1):

=SPLIT(REGEXREPLACE(INDEX(REGEXEXTRACT(A1,"(\[(\n|\s|""|\w|,)+\])"),1),"(\[|\]|\n|\s|"")",""),",")

You can adapt this formula to suit whatever extraction you're trying to perform.

enter image description here

Related Topic