How to Use an API Without Coding

A No-Code method to connect to any API using Google Sheets for users without any experience in coding

How to Use an API Without Coding

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 your smartphone or computer apps use APIs.

You do not need to code to understand and use an API.

In this article, I will share two methods for connecting to and automatically getting data from APIs without writing code using Google Sheets.

If you prefer to watch, have a look at the video version of this tutorial

How do you connect to an API without coding?

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.

Demo — (Image by Author)

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.

(Image by Author)

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.

Implementation with Google Sheets

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.

API Call — (Image by Author)

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,

API JSON Response — (Image by Author)

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.

Follow the steps — (Image by Author)

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

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

Full Process — (Image by Author)

🏫 Discover 70+ case studies using python to automate tasks 💻, for supply chain sustainability🌳and business optimization 🏪 in this: Cheat Sheet

Next Steps

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.

Example of GPT agent I designed for Supply Chain Analytics — (Image by Author)

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.

Workflow of the GPTs for Analytics — (Image by Author)

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,

Create GPTs to Automate Supply Chain Analytics

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

About Me

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

For consulting or advice on analytics and sustainable supply chain transformation, feel free to contact me via Logigreen Consulting.

If you are interested, have a look at my website

Samir Saci | Data Science & Productivity

💌 New articles straight in your inbox for free: Newsletter
📘 Boost your Productivity with Data Analytics: Productivity Cheat Sheet