Extract API data in Google Sheets

After browsing this great list of Free APIs maintained by thousands of contributors, let’s focus on the art world!

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:

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.

Extracting API data for yourself

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