API calls from Excel

How to make API calls from Excel and transform JSON response to a table.

Nobody can deny that Microsoft Excel is still a powerful tool even if you know Python, SQL, Javascript… It provides a super-friendly interface, has many functionalities and is a very popular software.

On the other hand, being able to make API calls and process the response provides a new world of endless possibilities. Nowadays many companies give access to their data via certain endpoints.

Why not put these 2 tools together? In this article we’ll explain how to do it.

There are 2 main ways in Excel to do it:

Via Visual Basic script
Via making a “query” from the data menu

API Call from Visual Basic script

The first thing is enable the “developer” menu. This can be done in File → Options → Customize Ribbon:

Once this is done we have to open the VBA editor.

In order to process the JSON response of the API call, we need to add the JsonConverter module, which can be found in the following url: https://github.com/VBA-tools/VBA-JSON/releases, then import JsonConverter.bas into the project. In the VBA Editor, go to File → Import.

Then we also need to import 2 references into the project from the “Tools” menu.

Microsoft XML, v6.0
Microsoft Scripting Runtime

Next we have to create a new module to write the code that will make the api call. Here I present 2 examples:

Get the users from https://jsonplaceholder.typicode.com/

Get the people from the Star Wars API (https://swapi.dev/).

If you want to save the excel file, remember to use the “xlsm” extension, which allows macros.

API Call from “New Query”, in “Data” tab

Excel 2016 has a built-in feature that allows to make API calls. Previous versions can also make it, but installing the PowerQuery plugin. To make an API call we must go to the “Data” tab and click on “New Query” → “From Other Sources” → “From Web”.