How to extract data from an API using the ImportJSON tool
If you want to learn how to extract data from an API yo are in the right place!
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 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.
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:
- Simply, install the tool into your Google Sheets from the Google Workspace Marketplace following the step-by-step instructions indicated by the add-on.
- From there, you can access the tool by clicking “Add-ons” and activating ImportJSON within any given spreadsheet.
- Within the tool, you’ll have access to guidance that can give you further insights into how to leverage the tool within your web data research.
Now that you’ve got the squared away, it’s time to explore the wealth of information that is available via APIs and where to find it.
The advantage of scraping from APIs is the variety of information they provide, which can have a major impact within a number of industries. Many websites have their own APIs, particularly if their function is to share information, like for example Wikipedia. Of course this does not apply to all websites, so the easiest way to discover available APIs is through a resource that’s already done the work for you.
This useful list will give you an idea of the possibilities with information broken up by sector. From stock prices to patent databases to census data and more, it’s an exhaustive list that holds a lot of answers, which you can use to enrich your business decisions.
All we need to do now is find the data points that are interesting to your work and access them with ImportJSON. To see how it’s done, let’s try one out together.
First, scroll through the list and pick a sector that impacts your work.
Let’s say we work in the art world and want to see the works of art that are currently on display in specific museums around the world. ImportJSON is designed to help you do 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 there, which will help us to better understand the trends in our industry.
With the ImportJSON add-on enabled in GoogleSheets, we copy/paste the url to the first API of published artworks in their collection. By inserting this into the JSON function, we now have access to all of the published works currently in the Art Institute of Chicago. And it took us less than 3 minutes to retrieve.
=IMPORTJSON(url to scrape)
Now you have all the data in your spreadsheet but maybe you were looking for a concrete set of fields, this is not a problem with IMPORTJSON as you can easily filter and manipulate your data.
Let’s say you only want to retrieve the title, artist, place of origin and a small explanation of the artwork. Then, what you need to do is to incorporate them to the formula, following this pattern:
=IMPORTJSON(url to scrape, fields to display)
Remember to name the fields exactly as they appear in the API. To do it easier, you can copy paste the name of the fields in the spreadsheet and introduce them in the formula by indicating the cells where they are placed:
Once you have the data you want, you can modify how it’s displayed in the spreadsheet by using the TRANSPOSE function ahead of the IMPORTJSON function:
That was easy! Now you can apply this same process to all kinds of APIs that are rich with data from government, public, and company sources depending on what you’re looking for.
One important thing to keep in mind when using this process is that to extract the data easily, the APIs you use with ImportJSON must be open to extract files, meaning they cannot require an authkey. You can verify this under the Auth column; if it says no then you’re good to go. If the APIs you use require an authkey, then you can still use ImportJSON to extract files but you would need to use a cURL request that can complicate a bit the task if you don’t know how to write it. However many APIs just give the cURL text in their documentation. If that’s the case, then you just need to copy/paste it.
Give it a go then let us know how it worked for you. We’d really love to help you succeed with our tool, so we’re here to help you with any questions or concerns about extracting API data into GoogleSheets.
And if you find a particularly interesting open API, we’d also love to hear about it! Leave us a comment or reach out via our contact page.