Refresh token api call

[Anonymous]

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!

[rainer1 · Accepted solution]

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!

[rainer1 · Accepted solution]

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 · Accepted solution]

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]

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. 

[Anonymous]

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]

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]

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