Forum Discussion
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.
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.
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.
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.