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