Set API Key from Cell Value

[Anonymous]

Hi -- very new to Power Query. Like started this morning. So I hope this is the right place to ask the question. 🙂  

In Excel I've created a query that gets data from a website using an API call, and it is authenticated via an API Key. That part works well.  I'd like to have users be able to enter their API Key in a cell in the workbook and have the query use that value.

Based on some Googling, I created a custom function / query (not sure the right terminology for this) that can return the value of a cell that's a named range. I called it GetValue()

(rangeName) => 
Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

So GetValue("apiKey") will be equal to whatever is in the cell named 'apiKey'. Groovy.

Now I'd like to be able to be able to use that in the query. The original working query with the hardcoded API key is:

let
     Source = Json.Document(Web.Contents("https://app.myWebsite.com/api/" & "employees", [Headers=[Accept="application/json", #"Api-Key"="hard_coded_api_key_here"]])),
     #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
     #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Id", "LoginEmail", "FirstName", "LastName", "Roles", "CreatedDate", "LastLoginDate", "Status", "CreatedBy", "HomePhone", "OfficePhone", "CellPhone", "PhysicalAddress", "MailingAddress", "Department", "TimeZone", "EmploymentStatus", "DateOfHire", "DateOfBirth", "SocialSecurityNumber", "DriverLicense", "Ethnicity", "Race", "Gender", "ExternalIdentifier"}, {"Id", "LoginEmail", "FirstName", "LastName", "Roles", "CreatedDate", "LastLoginDate", "Status", "CreatedBy", "HomePhone", "OfficePhone", "CellPhone", "PhysicalAddress", "MailingAddress", "Department", "TimeZone", "EmploymentStatus", "DateOfHire", "DateOfBirth", "SocialSecurityNumber", "DriverLicense", "Ethnicity", "Race", "Gender", "ExternalIdentifier"}),
     #"Sorted Rows" = Table.Sort(#"Expanded Column1",{{"LastName", Order.Ascending}}),
     #"Added Custom" = Table.AddColumn(#"Sorted Rows", "FullName", each [FirstName] & " " & [LastName]),
     #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"LastName", "FirstName", "FullName", "LoginEmail", "Roles", "Id", "CreatedDate", "LastLoginDate", "Status", "CreatedBy", "HomePhone", "OfficePhone", "CellPhone", "PhysicalAddress", "MailingAddress", "Department", "TimeZone", "EmploymentStatus", "DateOfHire", "DateOfBirth", "SocialSecurityNumber", "DriverLicense", "Ethnicity", "Race", "Gender", "ExternalIdentifier"}),
     #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"CreatedDate", "Status", "CreatedBy", "DateOfBirth", "SocialSecurityNumber", "Ethnicity", "Race", "Gender", "ExternalIdentifier"})
in
#"Removed Columns"

I'd like to be able to do something like:

let
     myApiKey = GetValue("apiKey"),
     Source = Json.Document(Web.Contents("https://app.myWebsite.com/api/" & "employees", [Headers=[Accept="application/json", #"Api-Key"=myApiKey]])),
     etc.

But it doesn't work. I get a warning asking me to Edit Credential and asking how I want to connect.  I think I should select Anonymous. When I do I get the warning"Formula.Firewall: Query 'employees (2)' (step 'Removed Columns') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination." 

If I select Web API as the connection method, it asks me for the key. I put it in, but it tells me that "A web API Key can only be specified when a web API key name is provided." Not sure where I'd put that other than in the query, where it already is.

So what am I doing wrong? Note that I've also tried:

myApiKey = """" & GetValue("apiKey") """",

thinking that maybe I needed to pass the literal quotes too. And I've tried just using GetValue() directly in the source definition.

Thanks for any insights,

Dan

[dax]

Hi @Anonymous , 

I didn't see your GetValue function, you could try to use below code to see whether it work or not. In addition, you also need to pass credential when authentication windows prompt(you need to make sure whether you could use Anonymous to access it , if can't, you need to pass corresponding credential ).

let function = (apiKey as text) => let
    Source = Json.Document(Web.Contents("https://app.myWebsite.com/api/" & "employees", [Headers=[Accept="application/json", #"Api-Key"=apiKey]])),
     #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
     #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Id", "LoginEmail", "FirstName", "LastName", "Roles", "CreatedDate", "LastLoginDate", "Status", "CreatedBy", "HomePhone", "OfficePhone", "CellPhone", "PhysicalAddress", "MailingAddress", "Department", "TimeZone", "EmploymentStatus", "DateOfHire", "DateOfBirth", "SocialSecurityNumber", "DriverLicense", "Ethnicity", "Race", "Gender", "ExternalIdentifier"}, {"Id", "LoginEmail", "FirstName", "LastName", "Roles", "CreatedDate", "LastLoginDate", "Status", "CreatedBy", "HomePhone", "OfficePhone", "CellPhone", "PhysicalAddress", "MailingAddress", "Department", "TimeZone", "EmploymentStatus", "DateOfHire", "DateOfBirth", "SocialSecurityNumber", "DriverLicense", "Ethnicity", "Race", "Gender", "ExternalIdentifier"}),
     #"Sorted Rows" = Table.Sort(#"Expanded Column1",{{"LastName", Order.Ascending}}),
     #"Added Custom" = Table.AddColumn(#"Sorted Rows", "FullName", each [FirstName] & " " & [LastName]),
     #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"LastName", "FirstName", "FullName", "LoginEmail", "Roles", "Id", "CreatedDate", "LastLoginDate", "Status", "CreatedBy", "HomePhone", "OfficePhone", "CellPhone", "PhysicalAddress", "MailingAddress", "Department", "TimeZone", "EmploymentStatus", "DateOfHire", "DateOfBirth", "SocialSecurityNumber", "DriverLicense", "Ethnicity", "Race", "Gender", "ExternalIdentifier"}),
     #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"CreatedDate", "Status", "CreatedBy", "DateOfBirth", "SocialSecurityNumber", "Ethnicity", "Race", "Gender", "ExternalIdentifier"})
in
#"Removed Columns"
in
    function

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Zoe. Thanks very much for your help. The anonymous login seems to be fine since that method work when I have the API Key hard-coded into the query as shown in my first example. It also works when I use the function you wrote and manually enter the key as the function parameter when prompted. Things fall apart when I try to set that value using the GetValue() funciton.

Here's the GetValue() function, which works fine:

(rangeName) => 
     Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

 So the problem seems to be getting a variable value inserted as the API Key in the query.

[Anonymous]

One other thing I tried as a test was to create the apiKey variable within the query set to to the the string value of the key. Then I used this variable in the Source. This works:

let
     apiKey = "MyAPIKeyHere",
     Source = Json.Document(Web.Contents("https://app.myWebSite.com/api/" & "employees", [Headers=[Accept="application/json", #"Api-Key"=apiKey]])),
     etc....

 But the same thing with apiKey = GetValue("apiKey") does not work, even though I can see that it does have the right value. So I'm really stumped.

[Anonymous · Accepted solution]

I got this to work!  I had to go to the Data Source Settings and edit permissions. I set Credentials to 'Anonymous' and Privacy Level to 'Private'. I set both the Current Workbook and the web page to match. Then everything worked. I'm not really sure why because I doesn't appear that I changed anything, but I think I must have had some set to private and some to public, and they couldn't work together as a result. That's what I get for being a noob.  

Anyway, this will make it a lot easier for end users to put their API Key into a cell on a setup worksheet rather than having to go in and edit the queries. I hope someone find this helpful.

Thanks again, Zoe.