API calls from Excel

Photo by Josh Calabrese on Unsplash

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:

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

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

Hope it was helpful!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Toni Cañada

Toni Cañada

40 Followers

Civil engineer passionate about business administration and coding.