Import XML or JSON data into Excel using URL from a cell

I have an API call to make from my Excel spreadsheet that will have various parameters depending on the data entered in the spreadsheet.

I have formatted the URL properly and have it in a cell that is named ApiUrl however when I try to set up a data source in Excel, it seems to always require me to provide the URL in the setup wizard rather than referencing data in my spreadsheet (such as this pre-formatted URL).

I am certain there must be a way to do this...but I have not been able to find it.

I am using the latest Office36.

My URL can be formatted to return XML or JSON data if one format or the other gives me the capabilities I am looking for. It is also OK if I need to set up each parameter as a reference to a cell rather than the URL string being precomposed.

Once I get the data into my workbook, I can take it from there...I just need help getting data from a precomposed URL string. Thanks!

The URL is something like:

This will return XML (or with a slight modification, JSON) data formatted as described here:

I am only trying to get the "ac_monthly" array...and will then be inserting those 12 values into individual cells in my sheet.

1 Answer

You can edit your queries in Power Query.

Here is a sample code to use content from named range ("apiURL") as api URL:

let apiAddress = Excel.CurrentWorkbook(){[Name="apiURL"]}[Content]{0}[Column1], Source = Json.Document(Web.Contents(apiAddress)), #"Converted to Table" = Record.ToTable(Source)
in #"Converted to Table"

Step by step:

1. enter your link in a cell
2. assign custom name to the cell
3. open power query
4. create a new blank query: home - new source - blank query
   
5. open advanced editor
   
6. copy the code from above (adjust range name if needed)
7. close power query (select "keep changes")