There are thousands of APIs, tools used by developers to connect data from one service to another. Many APIs have data that could be useful in everyday situations, and you don’t necessarily have to be a programmer to connect to the data. However, it can be helpful to have it in a format that is familiar and easy to use.
The trusty spreadsheet—whether it’s Excel, Google Sheets, Numbers, or any tool that can accept comma separated values (CSV) files—is the data tool of the non-programmer. We’ve grown accustomed to viewing our data as columns and rows. Most APIs produce a different data format, called JSON (or XML). It’s full of curly braces and doesn’t look very user-friendly. However, you can convert JSON to CSV or Excel to get the data in a more familiar format.
Find Your Data Source
Before you can convert your JSON, you need to have the data. Perhaps you already know where you’ll get your data, but if you’ve never used an API before, that can be intimidating. Once you know what to look for, though, accessing many APIs is as intuitive as loading a webpage.
First, to discover the API, there are a couple of approaches that work well once you know who has the data you want:
- Look in the footer or header of the website for the words “API,” “developer,” “partners,” or “integrations”
- Google it by searching for “website name API”
- Look in a directory like ProgrammableWeb.
Next, you’ll need to navigate the API documentation. The best APIs will have a “getting started” guide or similar tutorial for first-timers. It can still be intimidating, but you’re looking for descriptions of the type of data you want. For example, if you use a CRM and you’re wanting to get your contacts via API, look for sections of the documentation that reference “getting contacts” or “listing contacts.”
For our Halloween celebration map of events and our holiday light displays map, we went to Eventful and found “Developer API” in its footer. Within its API documentation, we saw an endpoint (which is like a web address URL) for /events/search. Using their examples, we could put together the API call right in the web browser: http://api.eventful.com/json/events/search?app_key=SECRETKEY&keywords=halloween&page_size=250&date=2018103000-2018110100
When you replace “SECRETKEY” with your API key, that returns a bunch of text. It’s in JSON format, but if you don’t know what to look for, it might not seem very useful.
Understand the JSON Results
Much of the time, JSON is returned as a giant wall of text, which is one reason it looks incredibly confusing to non-programmers (and many programmers can’t make much sense of it, either). One quick trick to make JSON more readable is to either “pretty print” it or get an extension such as JSONView for Chrome that automatically displays JSON with colors and indentation to make it easier to understand the data within.
Take the example above. It still may be daunting, but hopefully, some of it looks familiar. For example, can you tell the total number of results? Almost 3,000—as referenced by the total_items.
Most JSON data is stored as key/value pairs. That is, there is a way to reference a value (total_items in our example) and the value itself (2869). All related key/value pairs are stored within a single object, as denoted by the curly brackets { and }. Each pair is separated by a comma. There can be objects within objects by using more curly brackets as the value.
In addition, there’s one more important type of value, highly relevant to converting from JSON to CSV: an array or list. This is many values in a row, often many objects in a row. A list occurs between two sets of square brackets, [ and ].
Our example shows a primary object defined by the { on the first line, followed by a few key/value pairs, then e key called “events” whose value is an object. Within that object, there is a single key, “event” which includes a list value (the [ shows us this is a list) and the values within the list are yet more objects. There’s a lot to unpack there and understand, and it might be helpful to see the full JSON file displayed using JSONView.
Even if it’s confusing, the JSON will always have a structure you can figure out using these basic building blocks: objects with key/value pairs, where values can be additional objects or lists of values.
Convert JSON to Excel or CSV
Once you understand the type of data that can be converted, it’s time to make your JSON data usable in Excel. That means you need to convert the JSON either directly to an Excel document, or more likely to a text document that Excel can read, such as CSV.
Let’s look back at our Eventful data again. We need to extract the list of events, which are a series of objects. The key/value pairs in all of the event objects will all contain the same keys. The values will obviously be different since they describe individual events.
Therefore, in spreadsheet terms: the keys become the header row.
You can copy all the key/values, or just the ones you want. For example, we can see the “latitude” key in the screenshot from the previous section. That is a useful value for making a map!
If there’s only a small amount of data, you could make quick progress by copying and then pasting your data from JSON to a spreadsheet. However, most of the time there’s a lot of data. In our Eventful example, there were almost 3,000 events! Use a tool like this to convert file formats automatically.
Optional: Create a Map with Your Data
Many APIs and data sources include location data, such as addresses, city names, or latitude/longitude coordinates. Once your data is in spreadsheet format (as a CSV, Excel, Google Sheets, and more) you can easily create a map like this:
View 2018 Tour de France Route in a full screen map
Create a latitude and longitude map by copy-pasting your spreadsheet data in the cases where you have geographic coordinates. Otherwise, you can automatically geocode location names using our Google Map creator.
