cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ranand1482
Resolver I
Resolver I

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 @ranand1482,

 

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

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 @ranand1482,

 

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

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

Hi @ranand1482 , 

 

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. 

 

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. 

EHP
Frequent Visitor

@raimon 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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors