Extract open data from API

Author: Matt
Published: September 9, 2023
Extract open data from API

The web is a fantastic place full of valuable, public information just waiting to be analyzed – if you know where to find it. Luckily, we know exactly where this data is and how it can be extracted from open APIs to be manipulated and analyzed. And we want to pass this skill on to you.

While we explained in this post how to get JSON data into a table with our simple process, here we want to go further into why JSON is a great place to sleuth for information. In addition to opening your eyes to the data possibilities that APIs offer, we’ll also walk you through an example of the data extraction process so that you can apply the same theory to your industry-specific research.

Where to find interesting API for your research purposes

Before we do anything else, first make sure you have the ImportJSON add-on set-up in your GoogleSheets. This tool makes it easy to tap into open-source APIs and retrieve information in seconds without any manual labor on your end. It just takes a few minutes so follow the easy process here

Now that you’ve got the squared away, it’s time to explore what kind of information is available via APIs and where to find it. This lis of free APIs gives you an idea of the possibilities with information broken up by sector.

From stock prices to census data, from entertainment data to patents, this exhaustive list holds a lot of answers that you can use to enrich your business decisions. To give you a better idea of how it’s done, let’s try one out together. 

An example of how to extract API data with ImportJSON

First, scroll through the list and pick a sector that impacts your work.

Let’s say we work in Art History and want to see the work that is currently published in specific museums around the world. ImportJSON can help you with this!

Click on Art & Design, then let’s check out the Art Institute of Chicago. Here we see a number of APIs with useful information about the current works on display, which will help us to better understand the current trends in our industry. 

With the ImportJSON add-on enabled, we copy/paste the url to the first API of published artworks in their collection. By inserting this into the JSON function in GoogleSheets, we now have access to all of the published works currently in the Art Institute of Chicago. And it took us a few seconds to retrieve.


Then, just filter out the data to display the one you are interested in. Let’s say:


One important thing to keep in mind when using this process is that the APIs you use with ImportJSON must be open. Meaning it cannot require an authkey to tap into it. You can verify this under the Auth column; if it says no then you’re good to go. 

Are you willing to test our ImportJSON add-n and try to import your first data? We’re always looking for ways to help our customers successfully use our products, so please let us know if you have any remaining questions or concerns!