cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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 🙂

5 REPLIES 5
Syndicate_Admin
Administrator
Administrator

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

Syndicate_Admin
Administrator
Administrator

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



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 😄

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (2,387)