BLOG

Retrieving JSON data from a REST API in Excel with Power Query

November 12, 2021
   
Solution
Posted by Georges Lagardère

When you have created your data API through RAW, it’s likely that you will want to show users the data in their favourite data analysis tool.

Here we show how to use Power Query in Excel to retrieve data from any REST API, in this case it is the last updated web pages from a website virgingalactic.com as an example.

First, you need to create a new empty spreadsheet in Excel. Then, from the Data menu create a Blank Query ( “Data | Get Data | From Other Sources | Blank Query” )

Creating a Blank Query

This will open the Power Query Editor. From there, go to the View tab and open the Advanced Query Editor ( “View | Advance Query Editor” ). There, you can replace the default empty query with your own query, for example this one:

https://api.raw-labs.com/demos/1/public/sitemap/recentpages?website=https://virgingalactic.com

This example is a public API, i.e. with no API Key or token. In the case of a private API endpoint you would need to set the Bearer authentication token in the HTTP header, or pass in an API key as a paramter in Excel.

Our API above returns list of records within brackets (click …. ), you can expand the columns and get the field names with this button:

From there you can choose which fields you want to load (‘loc’ and ‘lastmod’ in this example)and once done, select “Close & Load” from the File menu. The API endpoint will be contacted and the selected data loaded and the fields displayed in your spreadsheet.

10 last updated pages on virgingalactic.com

I hope you found this useful, and can quickly get going with using API-based data from us at RAW Labs (and indeed any other platform) – from inside the world’s favourite data analysis tool.

Don’t forget to check out our other blog posts too.

Georges Lagardere Sales Director RAW Labs

Georges Lagardère, VP Customer Experience, RAW Labs.

Why not follow us on LinkedIn, or Twitter.

Learn More

Blog

Reference Data Standard Library

Standard Libraries are a commonly known construct in programming. Same for data; we show how to use RAW to surface and standardise data into APIs

Blog

Hello world, meet RAW

here we show how to use RAW Data Product Platform to create a simple Hello World output

Blog

Tutorial 1. First flight with RAW

First Tutorial with RAW where we take reader through a first query, first API and passing parameters to that API

Leave a Reply

Your email address will not be published.

Success

Thank you for your interest. Expect to hear from us soon.

Error

Email address must contain the @ symbol