Channel Advisor API Connection with auth key creation

[TrysHisBest]

Afternoon All!

Gonna be honest i'm racking my **bleep** brains out with this one but i'm getting no where.

I tested a rest function in Postman and all works brilliant returning the data i need. unfortunately i want to build this into a power query now. 
to generate a Oauth2 key i need to send a refresh key with username and password in order to get a return , then use the vaule it returns to request a report.

I'm hoping some wise individual will have at least some idea how to pull this off as i cannot i've tried using the web API fuction built into PQ but its just doesn't allow me to do anything. any ideas?

Thanks in advance 🙂

[SomeDataDude]

Okay, thanks.

That clears the error on Privacy Levels. Now I run into the error: We couldn’t authenticate with the credentials provided. Please try again.

But if I run the token Inline, it runs fine. So, I think the M code is not good enough to passes the authentication. But I have no clue, what’s wrong.

[lbendlin]

Did you notice there are two spaces after "Bearer"  ?

[SomeDataDude]

Thanks for the trigger around Bearer. I put the Bearer &function in the other query. Now it is working.

Function:

() =>
let
content = "{
            ""mode"" : ""raw"",
            ""client_id"": ""************",
            ""client_secret"": ""*********""
}",

Source = Json.Document(Web.Contents("url_refreshtoken",
                       [Headers=[#"Authorization"="******", 
                                 #"accept" = "text/plain", 
                                 #"Content-Type"="application/json"], 
                                  
                        ManualStatusHandling = {404, 400},

Content=Text.ToBinary(content)])),
access_token ="Bearer " &Source[access_token]

in
access_token

Query:

let
    Source = Json.Document(Web.Contents("search_url",
                       [Headers=[#"Authorization"= GetAccessToken(), 
                                 #"accept" = "text/plain", 
                                 #"Content-Type"="application/json"], 
                                  
                        ManualStatusHandling = {404, 400}]))

in
    Source

[SomeDataDude]

Yes I did. In my current M code is it just one.

[TrysHisBest]

@v-jingzhang  You are brilliant!

I've managed to get the Access token and invoke it as a custom response! brilliant! 

Its returned 3 values 

I Just need the access_token part of the response could i reference just that that in the GET request? or do i need to alter the custom function? 

Its seems to get confused when all 3 parts are still in the Custom Function

Thanks very much for all your help getting me this far!

[v-jingzhang · Accepted solution]

Great! Cheers!

The response is in Json format, so you just need to add a step at the end of the custom function to extract access_token value. Don't forget to add a comma at the end of previous response step. 

.....................
 response = Json.Document(Web.Contents(.......................)),
 access_token = response[access_token]
in
access_token

Jing

[TrysHisBest]

@v-jingzhang  You are a legend!

Thank you very much! 

Words cannot express how thankful i am!

I'm hoping to put all of this to good use now and keep expanding beyond.

😁😁😁

[lbendlin]

Were you able to accomplish the first part in Power Query (ie getting the refresh key) ?

[TrysHisBest]

Unfortuntely not I've been trying a couple variations of Jing's code above, but unfortunately not getting far. i'm sure its me but unfamilar with post requests so sharp learning curve at the moment 😄