6

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"
    ]
}
Improve this question
5
  • 1
    Yes it's possible with apps script. But for more detailed response: What specifically do you want the output of the function to look like? If you have JSON saved in A1 do you want the items expanded into B1 etc., and which of them? Actually you can probably also just use Regexes to extract the data. But let me know what you want output to look like first and I'll help you.
    – Cornelius Roemer
    Commented Oct 3, 2019 at 9:02
  • @CorneliusRoemer Thank you for your reply but my concern is same functionality of ImportJSON. The default syntax is =ImportJSON(url, query, parseOptions), in this function query is the power. For further detail please check this link GitHub
    – Saad Bin Khalid
    Commented Oct 3, 2019 at 11:26
  • I don't know the functionalities of ImportJSON and the GitHub link doesn't help much. Please describe what you want the function to do so that it's understandable without reference to ImportJSON and we can see what we can do. If the JSON structure stays constant, there's no reason why you can't just use a regex like below to do the job. If you want to write a general function, you should probably learn how to code it yourself and ask for specific help once you are on your way.
    – Cornelius Roemer
    Commented Oct 3, 2019 at 13:19
  • @CorneliusRoemer I am sorry, that link doesn't help you much; actually, ImportJSON parse JSON nodes based on provided information (query / xpath, etc.) moreover this array function can extract data in two dimensional, means it can extract multiple queries / xpath into multiple columns in single cell formula.
    – Saad Bin Khalid
    Commented Oct 8, 2019 at 6:41
  • @CorneliusRoemer , I get an idea from your following answer and tried to make my own function for doing this task thank you.
    – Saad Bin Khalid
    Commented Oct 8, 2019 at 6:42

1 Answer 1

Reset to default
3

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

Improve this answer
2
  • I used this formula in A2 =TRANSPOSE(SPLIT(REGEXEXTRACT(A1,"node_name.+\[(.+)\]"),"{")) to extract and split node of JSON in A1 cell (node_name have 19 child). This formula splits child of node_name into multiple rows then use =REGEXEXTRACT($A2,B$1&".+?\:(.+?)\,") in B2:B20, where B1 contain sub-node (attribute of child) and so on.
    – Saad Bin Khalid
    Commented Oct 8, 2019 at 7:05
  • Sorry if mentioned comments are confusing actually I don’t want to reply answer to my own question.
    – Saad Bin Khalid
    Commented Oct 8, 2019 at 7:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.