cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TrysHisBest
New Member

Channel Advisor API Connection with auth key creation

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 🙂

1 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

View solution in original post

10 REPLIES 10
v-jingzhang
Community Support
Community Support

Hi @TrysHisBest 

 

Do you send a Post request to get the key? If so, you need to put request body in Content parameter of Web.Contents function. 

 

Here is an example for Post request. Create a blank query, open its Advanced Editor, remove the code there and paste below code to it.

let
    url = "https://xxxxxxxxxxxxxxxxxxxxxxxx",
    headers = [#"Content-Type" = "application/x-www-form-urlencoded", #"Accept" = "*/*"],
    postBody = [
        grant_type = "xxxxxxxxxx", 
        username = "xxxxxxxxxxxxxxxx",
        password = "xxxxxxxxxxx"
        ],
    response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary(Uri.BuildQueryString(postBody))
        ]))
in
    response

 

Once you get the key, you can then use it in another Web.Contents statement to get data for your report. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Hi Jing,

 

I've tried this and had this come back as the first response?

DataSource.Error: Web.Contents failed to get contents from 'https://api.channeladvisor.com/oauth2/token' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.channeladvisor.com/oauth2/token
Url=https://api.channeladvisor.com/oauth2/token

After that i tried ammending to add in the refresh token but met with the same response above?

 

 

let
    url = "https://api.channeladvisor.com/oauth2/token",
    headers = [#"Content-Type" = "application/x-www-form-urlencoded", #"Accept" = "*/*"],
    postBody = [
        grant_type = refresh_token,
        refresh_token ="xxxxx"
        username = "xxxxxxxxxxxxxxxx",
        password = "xxxxxxxxxxx"
        ],
    response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary(Uri.BuildQueryString(postBody))
        ]))
in
    response



Hi @TrysHisBest 

 

You need to modify the headers and body content according to your POST request. Can you provide a screenshot of the correct request in Postman (remove sensitive info)? 

 

I think grant_type value should also be wrapped by double quotes. 

grant_type = "refresh_token",

 

Jing

Hi @v-jingzhang ,

 

Really do apprecaite the help.

 

TrysHisBest_0-1638261921022.png

 

TrysHisBest_1-1638261977814.png


This is what i made in postman and getting the auth key from. works no problem

Then have a seperate tab to use a GET request to get the data i need from a different url with the Auth02 key.

TrysHisBest_2-1638262112539.png

 

 

Again thanks very much 🙂

Hi @TrysHisBest 

 

Your username and password are passed in Authorization Header with Basic Auth type, not in Body, so they should be in Headers parameter rather than Content parameter of Web.Contents().

 

I did some google searching for passing basic Authorization username and password, and found a blog and a thread:

Using Power Query to get data from an API that uses a Username and Password combination

Passing username and password in Basic authentication (microsoft.com)

 

Both suggest to put username and password in username:pasword format, then encode the string with base64 encoding. Then put the encoded string into Headers. For encoding use the link https://www.base64encode.org/

 

Based on above, you could try below code

let
    url = "https://api.channeladvisor.com/oauth2/token",
    headers = [#"Authorization"="Basic xxxxxxxxxxxxx"],
    postBody = [
        grant_type = "refresh_token", 
        refresh_token = "xxxxxxxxxxxxxxxx"
        ],
    response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary(Uri.BuildQueryString(postBody))
        ]))
in
    response

 

If you can get correct token by using above code, then you can transform the query to a custom function so that it can be called by other queries. Just add () => before let:

() =>
let
    url = "https://api.channeladvisor.com/oauth2/token",
    headers = [#"Authorization"="Basic xxxxxxxxxxxxx"],
    postBody = [
        grant_type = "refresh_token", 
        refresh_token = "xxxxxxxxxxxxxxxx"
        ],
    response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary(Uri.BuildQueryString(postBody))
        ]))
in
    response

 

Then create another query for the GET request which calls the function. Assume the earlier custom function query is called GetAccessToken.

let
    Source = Json.Document(Web.Contents("https://xxxxxxxxxxxxxxxxxxxx", [Headers=[#"Authorization"="Bearer "&GetAccessToken()]]))
in
    Source

 

Hope this helps.

 

Jing

@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 

TrysHisBest_0-1638434265416.png

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!

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

View solution in original post

@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
Super User
Super User

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

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 😄

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors