Connecting to Channel Advisor API

Question

I am completely new to APIs and have done some reading. After not being able to connect to the API via a Web data source (https://api.channeladvisor.com/oauth2/token), I am trying to connect to Channel Advisor's API via Power Query (Excel, but it doesn't work in Power BI, either). I am getting errors when I try to call the custom function I made from another query.

I found this post online, and I was able to use some of the code snippets posted as follows.

STEP 1: I created a custom function called "GetAccessToken"

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

That part works.

STEP 2: I created a query calling the custom function to access Channel Advisor

Then I created another query that is supposed to forge the connection, but either I get a 400 error, or I get a simple table that says "error | invalid client":

let
    Source = Json.Document(Web.Contents("https://api.channeladvisor.com/v1/orders",
                       [Headers=[#"Authorization"="bearer " & GetAccessToken(), 
                                 #"accept" = "text/plain", 
                                 #"Content-Type"="application/json"], 
                                  
                        ManualStatusHandling = {404, 400}]))

in
    Source

I have not been able to find any comprehensive instructions anywhere on how to fix this, or what I need to do to get the result that I am looking for.

Update: I was able to successfully connect to the API in Postman. When the initial token expires, the token that "GetAccessToken" procures when run as a straight query works fine. I redid the permissions in Power Query for the umpteenth time, and now all of a sudden it returns data. I'll keep poking and report back.

score 0 · Accepted Answer · answered Mar 07 '23 at 19:39

Since my initial issue was that I found bits and pieces to this, but no complete answer, I have decided to post the solution here. I hope this is allowed.

My goal was to connect to Channel Advisor orders.

STEP 1: CREATE THE CUSTOM FUNCTION

Prerequisite: create an application in Channel Advisor's developer console and record the access token and refresh token.

Recommended: test the code first before creating the function (leave out the () => and see if it works, then add it in and name it. In my case, the function is called GetAccessToken.)

This is the M code:

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

This gets the current access token (= bearer token in Channel Advisor "speak") based on the refresh token.

STEP 2: CREATE THE CONNECTION QUERY

This is the initial query:

let
    Source = Json.Document(Web.Contents("https://api.channeladvisor.com/v1/orders",
                       [Headers=[#"Authorization"="bearer " & GetAccessToken(), 
                                 #"accept" = "text/plain", 
                                 #"Content-Type"="application/json"], 
                                  
                        ManualStatusHandling = {404, 400}]))
in
    Source

And this is the output from that query:

Click on the word "List" in the table, next to the word "value". This inserts a step "Navigation" into the query. It also opens up the list an a tab called "List Tools" at the top. Click on "Convert | To Table" on the ribbon in the "List Tools" tab:

It will present you with a dialog box. I simply clicked "OK":

The last step is to expand the resulting column:

Uncheck "Use original column name as prefix" and click "OK". Voila, here is your data.

OF NOTE: Make sure that both data sources are set to connect anonymously. Otherwise, you will get an error. Also, try things in Postman first if you run into trouble.

The screenshots here are screenshots of screenshots, so if they're a bit fuzzy, that is the reason. I didn't have the time/energy to retake them. :)

PS. [By default, the API only returns 100 items.](https://knowledge.channeladvisor.com/kc?id=kb_article_view&sys_id=3fccd3491bd5d1d42d9eea40604bcb70#concept-8903__section-6037) — Dolunaykiz, Mar 09 '23 at 12:47

Connecting to Channel Advisor API

1 Answers1