cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Set API Key from Cell Value

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

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

Re: Set API Key from Cell Value

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.

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Set API Key from Cell Value

Hi @DanBrill , 

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.

 

Highlighted
Frequent Visitor

Re: Set API Key from Cell Value

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.

Highlighted
Frequent Visitor

Re: Set API Key from Cell Value

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.

Frequent Visitor

Re: Set API Key from Cell Value

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.

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors