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

Solution 1:

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")