In computer science, an application programming interface (API) is a way for several programs to communicate.
You may have never used it if you have limited coding experience.
However, you can be sure that your smartphone or computer apps are using APIs.
You do not need to code to understand and use an API.
In this article, I will share two methods to connect and automatically get data from APIs without writing code using Google Sheets.
💌 New articles straight in your inbox for free: Newsletter
📘 Boost your Productivity with Data Analytics: Productivity Cheat Sheet
Final Solution
The final solution will look like the animated GIF below. With a simple formula in spreadsheet software (Google Sheets or Excel) you can pull out data from any API.
Example
In this article, I will use the example of a very cool API FruityVice, that provides information about fruits.
Have a look at the documentation for more information.
You can find a list of fruits for which we want information in the first column.
We call the API to get the following:
- Family name in the first column
- Amount of Fat in the second column
- Amount of Sugar in the third column
Then, we need to put a formula to call the API in cell B2 and drag it along the column to get the full scope.
Call the API, Get the response and filter
To call the API, you need to send a get request to the API following the syntax below.
It does not require any API key
- The blue part is the address of the website.
You can go there to check the documentation - The red part will give you access to the API
- The orange part is where you need the unique input parameter: the fruit name in English.
It will return a response in JSON format,
{
“genus”: “Musa”,
“name”: “Banana”,
“id”: 1,
“family”: “Musaceae”,
“order”: “Zingiberales”,
“nutritions”: {
“carbohydrates”: 22,
“protein”: 1,
“fat”: 0.2,
“calories”: 96,
“sugar”: 17.2
}
}We need to understand the way the API response is structured to extract the information we want,
What is interesting for us is,
- “family”: of the fruit
- “nutritions/fat”: the fat quantity
- “nutritions/sugar”: the quantity of sugar
If someone ask you: “What is an API?”.
You have the answer now!
Add a function to read JSON: importJSON
To perform the query and parse the JSON, we will import a function shared by Paulgambil in his GitHub repository.
- Grab the code in the GitHub repository and copy it
- Go to your extensions menus of Google Sheets
- Please create a new script and name it importJSON.gs
And now your function importJSON is available.
Parse the API JSON response
Write the query
You can now start to write the formula in the cell B2,
=importJSON("https://www.fruityvice.com/api/fruit/"&A2;"/family,/nutritions/fat,/nutritions/sugar"; "noHeaders,Truncate")- We take the fruit name in the cell A2
/family, nutritions/fat, nutritions/sugar: specify the three information we want to get from the API responseDrag the formula
And after dragging to the end of the column, importJSON will automatically paste the values in the right cells.
Create No-Code Analytics Products with “GPTs”
OpenAI recently released a new feature of ChatGPT called GPTs.
I have explored this feature to deploy advanced analytics solutions that do not require coding skills for usage.
I have named it “The Supply Chain Analyst”.
This custom GPT agent is designed to automate advanced analytics tasks for the supply chain and interact with users using natural language.
Agents can use core modules and prompts designed by Data Scientists to answer users’ questions.
- User: “I would like to extract count the number of restaurants in Manhattan using API XYZ.”
- Agent: “Looking at the documentation of API XYZ […]”
- Agent: “Creating a Python script with requests to perform the query.”
- Agent: “There are 1,234 restaurants in the area of Manhattan.”
💡 If you want to know more about the design of custom GPTs,
Import tables from URL
In this short tutorial, 1 min YouTube short, I share another method to extract data from a URL.
Why not Excel?
Excel has a function to perform an API query called WEBSERVICE.
However, there is no built-in function to parse the JSON response, so you must find a way to do it only with Excel formulas.
Therefore, I prefer to use Google Sheets.
Use Cases
Now that you can query any API using Google Sheets, you can do the following:
- Calculate distances between locations with Distance Matrix or Geocoding APIs
- Translate any word using Google Translate API
- Get any image with pictures from stock services like Pixabay
Let’s connect on Linkedin and Twitter; I am a Supply Chain Engineer using data analytics to improve logistics operations and reduce costs.
If you are interested, have a look at my website
