Retrieving JSON data from a REST API in Excel with Power Query
In this previous post I explained how to discover the last updated pages from a website using RAW Labs. In this one, I am showing how to use Power Query in Excel to retrieve this data from 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 )
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 :
This example illustrates the case of a private RAW Labs API endpoint with a Bearer authentication token set in the HTTP header, but this works as well for a public API with or without a secret key.
RAW Labs API return lists of records within brackets […. ], 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.
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.
