Call Any API Into Excel Without VBA Using Power Query

If you’re anything like me, you have an awful lot of tools at your disposal. You love what they do, you enjoy using their interface (or hate it in some cases), but the bulk of the work you do with them is exporting their data into spreadsheets so you can put reports or analyses together. This is all well and good, but what if you’re in a hurry and just want to get the data into your spreadsheet right away? Interfaces are slow sometimes. You need to do more clicks and you don’t necessarily want the pretty graphs, you just want the information right now.

Most tools offer what’s called an API – a way to get data from the software without using the interface. A lot of people use these to build their own tools or just to save time, and, if you know what you’re doing, you can use them to bypass the interface and get your information straight into Excel.

Excel has its own programming language called VBA, which you can use to create scripts to do this for you, but what if you’re not that great with VBA and don’t have time to learn? There’s a solution.

Today I’m going to show you how to use a free Excel add-in to call pretty much any API’s data straight into a spreadsheet without using VBA. Sorry if today’s post seems a little basic, but this method really can save you time if you’re in a hurry and don’t have time to figure out a script.

Power Query for Excel

I talked about Power Query in a recent post about my favourite Excel add-ins, and this one really has been a life saver on a number of occasions. You can get it for free here.

Power Query lets you extract data from pretty much any API without needing to play with VBA, although I’d argue that if you’re going to be doing this a lot, you should at least learn the basics as it’ll save you time in the long run.

Even if you do know your way around VBA, I’d really recommend this add-in – there’s so much you can do with it that it’s well worth playing with.

Anyway, here’s an example of how you can use it to query an API without even opening that scary VBA tab.

Using The SEMRush API And Power Query

The first thing you absolutely have to do before you try to do any API work with this is to get an understanding of the terminology it uses. Look at the API’s documentation and figure out what it is you want it to do and the things you need to put in the query to make it do that.

Let’s use an example with the SEMRush (aff) API.

In many cases, API’s are, in basic terms, a URL with specific parameters in their string which tell the tool what information we want from it. If we take a look at the documentation for the SEMRush API, you’ll see something like this (click the link for full documentation).

Let’s say we want to look at the PPC keywords targeted at Crazyegg’s homepage in the UK over the last month. According to the API documentation, we need a URL string that looks like this (obviously I’ve removed my API key from the URL string – get your own. Make sure you take out the line breaks, too).

http://uk.api.semrush.com/?action=report&type=url_adwords&key=APIKEYGOESHERE&
display_limit=5&
export=api&export_columns=Ph,Po,Nq,Cp,Co,Tr,Tc,Nr,Td,Tt,Ds&
url=http://www.crazyegg.com/

Now we’ve got that (I really recommend putting these together in Notepad or something similar, just so you can keep an eye on it), we need to pop it into Power Query to get it to make the call.

That’s really easy. Once you’ve installed it and you’ve got your URL to hand, go to the ‘From a URL’ tab as seen below.

Now paste your call URL in there. If you’ve done it right, you should get the following result in a new Excel tab.

Kind of ugly, isn’t it? But everything you need is there.

Making It Usable

Forgive me if I’m delving into Excel basics here, but a lot of API’s make their call on semicolon delimitations rather than cells, which means that your beautiful spreadsheet can end up looking quite messy. Here’s a quick way to fix that.

Just select the data, copy it to your chosen sheet, go to the Data tab and click “Text to Columns” to change the delimitation from comma to semicolon. There we go, shiny data in separate columns. Now you’re ready to paste this into your report.

Once you’ve got the hang of this and the language of your chosen API, this will take a lot less time to do than exporting the data and copying it over manually.

Try It Yourself

That’s just one example. I’ve made this process work with a wide range of API calls and I’ve worked this into a number of our monthly reports to save a bit of time. Have a go yourself and I’d love to hear from you if you’ve got any more cool ideas for things that can be done with this brilliant add-in.