How to Use an API Without Coding

In computer science, an application programming interface (API) is a way for several programs to communicate with each other.

This article has been originally published on Medium.

If you have limited coding experience, you may have never used it yourself. However, you can be sure that the apps on your smartphone or computer are using APIs.

Even if you’re a human being, you can find many interesting APIs that can improve your productivity, bring some fun or just solve some of your daily issues.

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

Solution

Final Solution

The final solution will look like the animated GIF below. With a simple formula in spreadsheet software (Google Sheets or Excel) you’ll be able to pull out data from any API.

Example

In this article, I will be using the example of a very cool API FruityVice that provides information about fruits.

Have a look at the documentation for more information.

In the first column, you can find a list of fruits we want information about. And we call the API to get the:

• Family name in the first column
• Amount of Fat in the second column
• Amount of Sugar in the third column

Then, we just need to put a formula to call the API in cell B2 and drag it along the column to get the full scope.

Implementation

Call the API, Get the response and filter

In order 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, therefore I took it as an example:

• 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,

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

Add a function to read JSON: importJSON

In order to perform the query and parse the JSON we will import a function shared by paulgambil in his GitHub repository. (Please give him a star for this very useful function :D)

• Grab the code in the GitHub repository and copy it
• Go to your extensions menus of Google Sheets
• 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 pieces of information we want to get from the API response

Drag the formula
And after dragging to the end of the column, the function importJSON will automatically paste the values in the right cells.

You can find more productivity tips and google sheets tutorials on my Youtube Channel

Next Steps

Import tables from URL

In this short tutorial, 1 min youtube short, I share another method to extract data from an URL.

Why not Excel?

Excel has its own function to perform an API query called WEBSERVICE. However, there is no built-in function to parse the JSON response, so you have to find a way to do it with only Excel formulas (I personally can’t).

Therefore, I would prefer to use Google Sheets.

Use Cases

Now that you can query any API using Google sheets you can do:

• 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

About Me

Let’s connect on Linkedin and Twitter, I am a Supply Chain Engineer that is using data analytics to improve logistics operations and reduce costs.

If you’re looking for tailored consulting solutions to optimize your supply chain and meet sustainability goals, please contact me.