Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

dax's avatar
dax
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.