API calls from Excel

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

API calls from Excel

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:

image.png

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

image.png

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: github.com/VBA-tools/VBA-JSON/releases, then import JsonConverter.bas into the project. In the VBA Editor, go to File → Import.

image.png

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

  • Microsoft XML, v6.0
  • Microsoft Scripting Runtime

image.png

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 people from the Star Wars API (swapi.dev).

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

image.png


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”.

image.png

Then we click on “Advanced”. Here we put the url, and if credentials are needed, they can be entered as a header.

image.png

image.png

Hope it was helpful!