Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Refresh token api call

hi all,

 

am currently working with salesforce social studio reporting and they do have rest api to retrieve data and report on. This does work well with "bearer tokens" with postman and using web connection to retrieve data. However, since these tokens are associated to expire after 3600 seconds the token again has to be updated manually. I'm trying to find a manner in which this can be automated.

 

I followed the earlier provided solution to parameterize or using MS Flow (power automate) > http to post and refresh token still does not help, any suggestion will be of great help.

 

more information about social studio rest api is here

https://developer.salesforce.com/docs/atlas.en-us.api_social.meta/api_social/1-introduction-to-the-s...

 

regards,

A!

2 ACCEPTED SOLUTIONS
rainer1
Resolver III
Resolver III

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!

 

View solution in original post

Anonymous
Not applicable

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

 

View solution in original post

6 REPLIES 6
rainer1
Resolver III
Resolver III

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!

 

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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 - 

 

raimon_0-1600952414199.png

 

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. 

 

Anonymous
Not applicable

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!

Anonymous
Not applicable

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. 

EHP
Frequent Visitor

@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");

 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors