JSON: manually parse data and create tables

Overview

Right, you’ve read about what JSON is and how to easily create JSON objects or arrays in dashdash. But most of the time you’re probably going to be using integration functions to get data from the web and into a spreadsheet. And that’s what we’re going to talk about today: how to parse JSON manually and get it into your spreadsheets :nerd_face:.

Manually parsing JSON responses

When a function returns data to a cell, you remember that you’ll see something like this:

image

When you open the data explorer by clicking on the ![Data Icon|16x16] icon, in the All data tab you see the entire response from the integration - but how do you get something into a cell? Well, we’ve made it ridiculously easy.

Just hover over the key-value pair of the JSON you want, click copy, and then paste it into a cell. Boom! The value appears in the cell :slight_smile:

Try it yourself! Just paste in the function below into cell A1 and get the name in the first object.

=SEARCH_COMPANIES_GAUSS(">500","London","retail")

:memo: Note: If you copy a JSON object or array and paste it into a cell, you’ll again see a {data} cell as a cell can only display a singular value.

PARSE()

If we look at the formula in the cell where we just pasted in the JSON value, you’ll see a formula like this:

=PARSE(A1,"[1].['name']")

dashdash has a cool function called PARSE() that looks through the JSON and extracts the value that you want. How it works is simple:

PARSE(json, [query])

Parameter Mandatory Description
json Yes The JSON that you want to extract information from. Usually, you just refer to the data cell that contains the JSON. For example: A1.
query No The JsonPath query use to extract the data you want. For example: "[0].['name']" or "[*].['name', 'domain']".

The first parameter is pretty easy to understand, right? But, what the hell is JsonPath!? Don’t be scared - just read on and it’ll all make sense :slight_smile:.

JsonPath

JsonPath is a language that we use to navigate JSON to extract information. The basics are thankfully simple, though the more advanced elements do take a little bit of head-scratching sometimes.

If we have a look at the example before, =PARSE(A1,"[0].['name']"), let’s analyze what’s going on here.
What we’re saying here is that: Go to the first item (and object or array) in the JSON, [0], and then extract the name in that item, .['name']. That full stop is really important by the way - it tells JsonPath that it needs to go into the item to get the value.

Now, try changing the 0 to 1. You should magically see that the value changes in that cell. Pretty cool right?

What about if instead of 1, you put in *? You get a data cell again right? Let’s click in and see what happened:

As you can see, we got all the names for all the companies in the cell - that’s pretty damn amazing. The * is called an operator and basically tells the computer to go into everything.

But why do you need to know all this? Well, what if you want to create your own tables from the data that you get back? Well, that’s exactly why we’d use JsonPath. We have a pretty exhaustive article on JsonPath that you should definitely have a look at if you need more examples or explanations.

Creating tables from JSON data

Say we want to create a table that has the name, domain, and LinkedIn URL for all the companies in the data cell. Well, we’d use our INSERT_DATA(), OVERWRITE_DATA(), or UPDATE_DATA() functions in combination with JsonPath and PARSE.

First, let’s extract the data we want from JSON. In cell B1, paste in this formula:

=PARSE(A1, "[*].['name', 'domain','linkedin']")

Now, in B1 you should again see a data cell - let’s have a look in the data explorer at what we got back:

Brilliant! We extracted the name, domain, and LinkedIn URL for all the companies. Now, all that you need to do is get it into a table. And that couldn’t be easier. In cell C1, paste in this formula:

=OVERWRITE_DATA(B1,A3:3)

And just like that - you’ve made your own custom table :muscle:!

:bulb: You can also put the PARSE() function straight into the _DATA functions, for example:

=OVERWRITE_DATA(PARSE(A1, "[*].['name', 'domain','linkedin']"),A3:3)

Summary

As you can see, it’s ridiculously easy to create your own custom tables from JSON data using JsonPath and our _DATA() functions :hugs:.

And of course, there’s a lot more you can do with JsonPath and our data table functions - make sure to read up on them. If you’re stuck on something, you can always leave a comment and we’ll be on it quicker than a dashdash function execution :wink:.

So, you’re now armed with all the JSON tools and knowledge you need to build some truly amazing things. So go on now, build and impress us :rocket: !

2 Likes