More and more companies are providing API services for their data. From free collections of public data to paid subscriptions to data services, APIs are quickly becoming the most popular way to quickly connect to different sources of data.
As API documentation tends to be seriously intimidating and is usually found in the “developer” section of the site, many people assume that using them is beyond their reach. However, you don’t need to know how to code to take advantage of APIs. In fact, it’s much easier than you think!
This article will show you how to use an API without coding directly from your Google Sheets file. What’s more, we’ll provide the perfect tool to help simplify API data extraction further – the ImportJSON add-on for Google Sheets. Whether you’re doing market research or monitoring your investments, save yourself time and suffering by learning how to easily get your data from an API without coding.
What is an API?
API stands for Application Programming Interface. This interface serves as a gateway that other computers or programs can use to request or send data. Many popular sites and platforms are creating their own APIs, so that users can comfortably interact with their data.
For example, you may need to monitor various cryptocurrencies and use this data in your spreadsheet. While this information is available from different websites, manually finding and updating the data you want is a stressful and time-consuming process. Instead, you could connect to a site’s API, and retrieve the data directly from the database.
APIs are often classified according to the type/level of access allowed: Open (available to all), Partner (subscribers), Internal (within company), Composite (combination). We use them to communicate with the database using different methods, including:
- GET – used to retrieve information
- POST – used to send/publish information
- DELETE – used to delete information
In this article, we will focus on the GET method for Open or Public APIs, which contain publicly available data and don’t require authentication.
How to get data from an API
Without technical knowledge, even figuring out how to approach an API can be daunting. In general, there are 2 main challenges users need to address when retrieving data from an API:
- Finding the URL that returns the data: The URL needed to retrieve a site’s data is often hidden amongst the rest of the documentation. Finding the right URL for the right method of API communication can be quite difficult.
- Interpreting and manipulating the results: Once you’ve found the URL you can use to request the data, you’re confronted with the second challenge: making sense of the API’s response. This tends to be in the JSON format, which is great for structuring data, but absolutely horrifying to read:
Finding a tool to transform this data into readable, useful information could be a game-changer for many. Even then, you’d still have to import the information to your spreadsheet before you could really get to work.
This is where the ImportJSON add-on comes in.
You can use ImportJSON to extract data from an API via Google Sheets and automatically get the data in a simple format that you can actually understand and use.
Let’s see how anyone can overcome both challenges, by using ImportJSON to get and interpret data from an API.
How to use an API without coding using ImportJSON
ImportJSON is a Google Sheets add-on that allows you to import data from an API and automatically transforms this data into a readable format. Once installed, you will have a powerful new formula available in Sheets.
The ImportJSON formula has three parameters, but only the first one is required:
=IMPORTJSON(input, filters, options)
The input can be a URL, which is the option we will be using, but it also accepts other inputs.
In the example below, you will also learn how to quickly add filters to your request.
Before you start, you’ll have to find a public API that you want to use. If you don’t have a specific one in mind, check out the list of Public APIs on Github. For this example, we’ll be using one of the Cryptocurrency APIs, CoinCap API. You can identify the public APIs as those that don’t need an authentication key.
Step 1. Activate add-on in Google Sheets
Open Google Sheets and go to Extensions → ImportJSON and activate the add-on. Click on Open Sidebar if it’s not already open.
Step 2. Find URL
Go to the link for the API you have selected: in this example, CoinCap. There is a sidebar on the left, which you can use to navigate the documentation.
Click where it says GET /assets in the sidebar to see the information you need to make the request. Right-click on the URL to copy it.
Step 3. Add URL to formula
Paste the URL into a cell on your spreadsheet. You may have to add the “https://” manually. A few rows below this, type in the ImportJSON formula and use the URL as the input. Press the Enter key to return the information.
As you can see, you immediately get readable results back, rather than the dense JSON text.
Step 4. Add filters to formula for final results
As you can see in the previous screenshot, there are twelve items of information for each cryptocurrency. The titles/tags are in the left-hand column, and the values are in the right-hand column. For example, you can see that the ID for the first item (0) is “bitcoin” and the rank is “1”.
You can use these tags to quickly filter the data with ImportJSON. Simply type in the tags you want in the empty cells under the URL. Add these tags to the formula, making sure to follow the correct syntax.
Just like that, the formula should now return data according to your formula. As you can see, the add-on has arranged this into an easy-to-read table.
Benefits of using ImportJSON
As you have seen, getting API data with ImportJSON is extremely quick and simple; in just a few steps, you instantly retrieve your desired data in a digestible format. However, ImportJSON’s benefits go beyond saving you from having to look at the JSON format. All you need to worry about is finding the URL for the data you want and then getting the most out of that data: everything in between is taken care of for you. Here are some more benefits of using the ImportJSON for your API processes:
- Import data directly into Sheets: get API data directly into your spreadsheet, in a usable format.
- Use different inputs: the formula accepts URLs, cURL requests, Google Drive File IDs, and JSON in text.
- Get data from multiple sources at once: you can use multiple inputs of different types in the same formula to retrieve multiple pieces of data in just one action.
- Quickly add/remove filters: add multiple filters to select the specific data you want, in the order you want it, for customized results fulfilling your exact needs.
- Use advanced options for greater flexibility: using the advanced options will give you even more control over the results by transposing the data, hiding headers, etc.
- Schedule updates: automate the latest data retrieval by choosing the frequency with which you want to refresh your data.
APIs are a fantastic way to easily collect and extract huge amounts of data quickly. Although you may initially view APIs as an extremely complex and technical process, they are not as scary as you think. Once you have the right tool, you can collect huge amounts of data with ease.
As you can see, ImportJSON is a great option for non-technical users. It’s an easy-to-use tool that transforms and filters the JSON into readable, useful pieces of data. Now that you feel more confident about how to use an API without coding, you can begin your own process of extracting your data via API by installing the ImportJSON add-on.
Want to learn more about extracting data from APIs and turning JSON data into tables? Check out these blog posts below:
