Forum Discussion
Issue with getting data via API with bearer token
Thank you Xiaoxin Sheng ( Anonymous ),
The logic you have provided is what I am trying to perform as the token has a 30 min lifespan and I can't get users of the report to manually enter a token each time.
To call a token I have to send a POST request with the username, password and grant_type embedded as content. The API does not accept a URI with parameters to return a token.
This works to return my token:
GetJson = Web.Contents(url,
[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("username=uXXXXXX&password=pXXXXXX&grant_type=password")
]
),
FormatAsJson = Json.Document(GetJson),
// Gets token from the Json response
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessTokenThis will give me a variable (AccessTokenHeader) with the access token. If there is another way to do this please let me know.
Hi Anonymous,
Can you share some detail content of the api which you want to invoke?
>>The logic you have provided is what I am trying to perform as the token has a 30 min lifespan and I can't get users of the report to manually enter a token each time.
You can write a custom function to get token which your account, then invoke this method before operation other api.
Regards,
Xiaoxin sheng
Hi Anonymous,
Thanks for following this through. The instructions provided for the API are as follows:
1. Create an OAuth2 Session
Create a session and get a token (that you need to pass in your Web API request) using your user credentials by doing a “HTTP POST“ request on the URL.The base URL used for all operations is formatted as follows: https://{deployedAPIServer}/api/{resource}
The “https://{deployedAPIServer}/token/" Microsoft OWIN oauth token endpoint with parameters:
"username", with value: the user login
"password", with value: the user password
"grant_type" : password (Resource Owner Password Credentials)
The content-type of the request should be: application/x-www-form-url encoded
Link to image of C# snippet provided in instructions
If the credentials are valid, the response would be a JSON object:
{"access_token":"..base64 encoded string","token_type":"bearer","expires_in":seconds}"
2. Call a Secure API
Call a secure rest API with token.
Set the returned token in your request header as a "Bearer" authentication and make the post, get, delete, .. HTTP call.Link to image of C# snippet provided in instructions
Does this help?
Hi Anonymous,
Sorry for slow response.
I haven't found a way to use power query invoke the sign in credentials, perhaps you can refer to below steps:
1. Package the sample code to a webservice.
Sample : (just for reference)
Public string GetToke(string UserName, string Password,string deployedAPIServer) { string token=""; using (HttpClient client= new HttpClient()) { Client.DefaultRequestHeaders.Add("Accept","application/json"); FormUrlEncodedConten credentialsContent =new FormUrlEncodedConten(new Dictionary<string,string> {{ "username",UserName},{"password",Password},{"scope","read"},{"grant_type","password"}}; Task<HttpResponseMesage> requestTask= client.PostAsync("https://{"+deployedAPIServer+"}/api/token",credentialsContent); string jwt= requestTask.Result.Conten.ReadAsStringAsync().Result; token= (string)jObject["access_token"]; }; return token; }
2. At query editor side, use web.content to invoke above service and store the access token.Sample:
let username="xxxx", passowrd="xxxxx", apiurl="xxx.xxx.xxx", AccessToken= Web.Contents(WebServiceURL& "?GetToke("+username+&","&+passowrd+&","&+apiurl+&")") in AccessToken
3. Use token to operation other functions.Some useful links:
Understanding the Username-Password OAuth Authentication Flow
Regards,
Xiaoxin sheng
Hi
i had the same issue which i was able to figure it with some tweak.
Like a post request with a get with bearer token to get the data
hope this helps
let
url = "http://###########/Portal/api/v1/Token",
url1 = "http://#########/Portal/api/v1/myapi?FromDate=2017/09/29 01:00:00&ToDate=2017/09/29 23:00:00&",GetJson = Web.Contents(url,
[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("grant_type=password&username=###########&password=###########&client_id=###########&client_Secret=###########")
]
),
FormatAsJson = Json.Document(GetJson),
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,GetJson1 = Json.Document(Web.Contents(url1 , [Headers=[Authorization= AccessTokenHeader ]]))
inGetJson1
Hi aexley,
Have you tried with Bearer (in capital letters)? I spent hours with that and finally that was the stupid problem.
Now, my big problem is that this solution does not work me on the Power BI service.Good luck!
Hi
i had the same issue but with some tweaking i was able to get the post and get token together and work.
my issue was i needed token registeration and use the token as bearer token to get the api call
--------------------
hope this helps for some one.
------------------
let
url = "http://###########/Portal/api/v1/Token",
url1 = "http://#########/Portal/api/v1/myapi?FromDate=2017/09/29 01:00:00&ToDate=2017/09/29 23:00:00&",GetJson = Web.Contents(url,
[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("grant_type=password&username=###########&password=###########&client_id=###########&client_Secret=###########")
]
),
FormatAsJson = Json.Document(GetJson),
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,GetJson1 = Json.Document(Web.Contents(url1 , [Headers=[Authorization= AccessTokenHeader ]]))
inGetJson1
Hope this helps
let
url = "http://###########/Portal/api/v1/Token",
url1 = "http://#########/Portal/api/v1/myapi?FromDate=2017/09/29 01:00:00&ToDate=2017/09/29 23:00:00&",GetJson = Web.Contents(url,
[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("grant_type=password&username=###########&password=###########&client_id=###########&client_Secret=###########")
]
),
FormatAsJson = Json.Document(GetJson),
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,GetJson1 = Json.Document(Web.Contents(url1 , [Headers=[Authorization= AccessTokenHeader ]]))
inGetJson1
