Forum Discussion
Refresh token api call
Hi Anonymous,
I stumbled over a similar problem as you last year. I solved my api connection by buidling a custom data connector.
https://github.com/Microsoft/DataConnectors
With the connector you have a lot of possibilitys for authentication handling:
see: https://docs.microsoft.com/en-us/power-query/handlingauthentication
The custom Connector can be attached to the data gatway from there the api calls are made.
This is not a really easy way for sure but may be a solution.
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!Hi Rainer and everyone,
Thank you for the quick response. Having said this, I was able to solve this one following the below manner.
Regards,
AnandStep one: Create parameters for client_id, client_secret, oauth token, search url, username, password
Step two: a new query with the followinglet //App Details client_id = #"API Key", //<===parameter client_secret = #"API Secret", //<===parameter //Authentication //URI's token_uri = #"Token URL", //<===parameter resource = #"Search URL", //<===parameter //User Details username = username, //<===parameter password = password, //<===parameter tokenResponse = Json.Document(Web.Contents(token_uri, [ Content= Text.ToBinary(Uri.BuildQueryString( [ client_id = client_id ,client_secret=client_secret ,username=username ,password=password ,resource=resource ,grant_type = "password" ])) ,Headers= [Accept="application/json"] , ManualStatusHandling={400} ])), access_token = tokenResponse[access_token], token = "Bearer " & tokenResponse[access_token], GetJsonQuery = Web.Contents("[search url]", [ Headers = [#"Authorization"=token] ] ), FormatAsJsonQuery = Json.Document(GetJsonQuery) in FormatAsJsonQuery
Hi Rainer and everyone,
Thank you for the quick response. Having said this, I was able to solve this one following the below manner.
Regards,
Anand
Step one: Create parameters for client_id, client_secret, oauth token, search url, username, password
Step two: a new query with the following
let
//App Details
client_id = #"API Key", //<===parameter
client_secret = #"API Secret", //<===parameter
//Authentication
//URI's
token_uri = #"Token URL", //<===parameter
resource = #"Search URL", //<===parameter
//User Details
username = username, //<===parameter
password = password, //<===parameter
tokenResponse = Json.Document(Web.Contents(token_uri,
[
Content=
Text.ToBinary(Uri.BuildQueryString(
[
client_id = client_id
,client_secret=client_secret
,username=username
,password=password
,resource=resource
,grant_type = "password"
]))
,Headers=
[Accept="application/json"]
, ManualStatusHandling={400}
])),
access_token = tokenResponse[access_token],
token = "Bearer " & tokenResponse[access_token],
GetJsonQuery = Web.Contents("[search url]",
[
Headers = [#"Authorization"=token]
]
),
FormatAsJsonQuery = Json.Document(GetJsonQuery)
in
FormatAsJsonQuery
Hi Anonymous ,
I tried making API call using the provided query after creating the required parameters.
Only change I did is -
grant_type = "client_credentials"
I am getting below error -
P.S - I tried using other methods as well, I am getting same error whichever query I use. Please suggest what am I doing wrong.
Thanks in advance.
Hi,
I've seen a similar issue earlier. However, if you refresh all your data the token will get a refresh too helping you to authenticate automatically.
hope this helps.
//A!
The refresh is not working as it giving error for credentials. Do you know any other reason it could go wrong.
I have gone thru it, cant seem to find any issue.
Any suggestions are welcome.
EHPAdvocate I
Anonymous did you get this sorted? I've just managed to implement OAuth 2 flow with refresh token in Power BI. Here's my code (you might not need to reference resource variable, and the verifier variable is an optional GUID & GUID that's passed forwards and back). NB credit for structure of the code goes to mattmasson.
https://github.com/microsoft/DataConnectors/issues/280#issuecomment-589651327
ManagementInformationConnector = [ TestConnection = (redirect_uri) => { ManagementInformationConnector.Contents(azureEnvironment, "jobs") }, Authentication = [ OAuth = [ StartLogin = StartLogin, FinishLogin = FinishLogin, Refresh = Refresh ] ], Label = Extension.LoadString("DataSourceLabel") ]; StartLogin = (resourceUrl, state, display) => let codeVerifier = Text.NewGuid() & Text.NewGuid(), authorizeUrl = "https://login.microsoftonline.com/" & environmentVariables[AzureTenantId]{0} & "/oauth2/authorize?" & Uri.BuildQueryString([ client_id = environmentVariables[AzureClientId], resource = environmentVariables[AzureResource], response_type = "code", response_mode = "query", redirect_uri = redirect_uri, code_challenge_method = "plain", code_challenge = codeVerifier, state = state, login = "login" ]) in [ LoginUri = authorizeUrl, CallbackUri = redirect_uri, WindowHeight = windowHeight, WindowWidth = windowWidth, Context = codeVerifier ]; FinishLogin = (context, callbackUri, state) => let parts = Uri.Parts(callbackUri)[Query], result = if (Record.HasFields(parts, {"error", "error_description"})) then error Error.Record(parts[error], parts[error_description], parts) else TokenMethod(parts[code], "authorization_code", context) // code is the authorization code itself in result; TokenMethod = (authCode, grant_type, optional verifier) => let codeVerifier = if (verifier <> null) then [code_verifier = verifier] else [], codeParameter = if (grant_type = "authorization_code") then [ code = authCode ] else [ refresh_token = authCode ], query = codeVerifier & codeParameter & [ client_id = environmentVariables[AzureClientId], client_secret = environmentVariables[ApiManagementClientSecret], grant_type = grant_type, redirect_uri = redirect_uri ], tokenResponse = Web.Contents("https://login.microsoftonline.com/" & environmentVariables[AzureTenantId]{0} & "/oauth2/token", [ Content = Text.ToBinary(Uri.BuildQueryString(query)), Headers = [ #"Content-type" = "application/x-www-form-urlencoded", #"Accept" = "application/json" ] ]), parts = Json.Document(tokenResponse) in // check for error in response if (parts[error]? <> null) then error Error.Record(parts[error], parts[message]?) else parts; Refresh = (resourceUrl, refresh_token) => TokenMethod(refresh_token, "refresh_token");