CODEWITHSUNDEEP
excelrestpowerqueryget-transform
kirvis
kirvis

Reputation: 79

Pass parameter from sheet in Powerquery headers

I am querying a REST API in Get & Transform (FKA PowerQuery) in Excel 2016, and I want to parse the API key from a sheet in Excel with parameters.

If I hardcode the API key in the query as part of the headers, all works fine, but if I import it from the sheet and try to use the imported value in the query, I get an authentication failure.

This does work:

let
        Source = Json.Document(Web.Contents("https://app.url.io/api/", [Headers=[Authorization="Basic APIKEY"]]))
in
        Source

This also works:

let
    API_key = "APIKEY",
    Source = Json.Document(Web.Contents("app.url.io/api/", [Headers=[Authorization="Basic "&API_key]]))
in
    Source

And this does not work:

let
    //API key from parameter table
    API = Excel.CurrentWorkbook(){[Name="API_key"]}[Content],
    API_key = API{0}[Column1],
    //
    Source = Json.Document(Web.Contents("https://app.url.io/api/", [Headers=[Authorization="Basic "&API_key]]))
in
    Source

Any ideas on what goes wrong here?

Upvotes: 1

Views: 636

Answers (1)

kirvis
kirvis

Reputation: 79

After doing some digging, I found a better solution: I am now using the credentials dialog with Basic authentication, with the API key as username and a blank password. This is safer since the API key is not stored on a sheet anymore nor is it stored in the query.

Thanks!

Upvotes: 0

Related Questions