Connecting a JSON API to Excel

Dale Mandeville Updated by Dale Mandeville

Prerequisites:

You'll need a URL that returns JSON. Contact the Wheelhouse team or your administrator to request a URL that returns the data you need.

If your data source requires authentication, you'll need to follow the instructions in the "Entering Credentials in Excel" toward the end of this article.

Connecting to the Data Source:

Once you have the URL, follow these steps to connect your JSON to Excel.

  1. Open a new spreadsheet in Excel. Select the Data tab.
  2. Select From Web
  3. Enter the URL of the JSON
  4. Click OK - Microsoft Power Query will open.
  1. From the Open As menu, select JSON
  2. Select To Table
  3. Click OK in the dialog.
  4. Click the expand column button
  5. Uncheck the column name prefix
  6. Click OK
  7. Click Close & Load
  8. Your data is linked directly to Excel! Click the refresh button to import the latest data instantly.
  9. Here is where you can get creative! Starting with this data, you can create charts, pivot tables, and do any other analysis you would like to do.

Entering Your Credentials in Excel

If your data source requires basic authentication, you'll need to enter your credentials in Excel. This only needs to be done once per computer or when your credentials change.

  1. Select the Data section of the ribbon
  2. Select "Queries & Connections"
  3. Right-click on one of the queries
  4. Select Edit
  1. Select "Data Source Settings"
  1. Select Edit Permissions
  2. Select Edit
  3. Select Basic
  4. Enter User Name (email)
  5. Enter your Wheelhouse Password
  6. Click Save
  1. Close all windows until you get back to your spreadsheet. Click Refresh All to update your data.

How did we do?

Contact