JSON, short for JavaScript Object Notation is a syntax for fetching, exchanging and storing data. It's considered an easier alternative to XML and its use is widespread on the web. There's an implementation or library in just about any language to deal with JSON style data, and it sees prevalent use in APIs all over the internet.

SeoTools interacts with structured JSON data through the JsonPathOnUrl function. It allows you to both GET and POST data to a JSON compatible endpoint, and in the case of the former it'll return the data in an array.

One of my favorite APIs to play around with is Forecast.io, if you want to follow along with the examples in this post I suggest you sign up for an account - it's completely free.

The Anatomy of JSON

Before we dive in to some examples lets brush up on what JSON actually is, and how it's structured. I think you'll be able to get a hang of it just by looking at its syntax.

{"StarWars":[
    {"firstName":"Han", "lastName":"Solo"},
    {"firstName":"Luke", "lastName":"Skywalker"},
    {"firstName":"JarJar", "lastName":"Binks"}
]}

If you've got any kind of background with JavaScript I'm sure this looks familiar to you. But if it doesn't, lets break it down even further.

Name of the Object

Property Name : Property Value

Property Name : Property Value

Property Name : Property Value

Like XML, it's just data that's structured in a way that makes it handable.

JsonPathOnUrl

Now that we've got an idea of what JSON is, lets take a look at the implementation in SeoTools. You'll find the JsonPathOnUrl function under the HTTP Ribbon. It'll bring up the following panel:

  1. API endpoint (i.e api.forecast.io).
  2. JSON path (i.e $currently).
  3. HttpSettings.

If we take a look at the Forecast.io API documentation we can see that their API is pretty simple. If we want to fetch the timezone our coordinates are in, we simply send a GET request formatted like so:

https://api.forecast.io/forecast/APIKEY/LATITUDE,LONGITUDE

This will return entire next weeks forecast - that's a lot of data we're not particularly interested in, we only want the timezone, remember?

This is where JSON paths come in. Let's tell SeoTools that we're only interested in inserting the property temperature into our selected cell. We accomplish this by entering $timezone into the JSON path field.

Pretty straight forward, right?

Let's try another example, but this time we want to fetch the temperature. This is going to be slightly trickier, because the temperature property is nested inside of the currently property. So how do we tell SeoTools to fetch just the temperature?

$currently.temperature

Pretty straightforward, don't you think?

If you'd like more in-depth examples, check out the the cookbook JsonPathOnUrl.xls that's included in the latest SeoTools release.

HTTPSettings

If you want to send data instead of fetching data, SeoTools will do that to. Hit the button under HTTP settings and select GET from the uppermost dropdown menu. You can now push data straight from Excel to a JSON API, pretty neat, huh?

You're also able to specify parameters that you might want to pass along your GET request. You do this in the Request form section.