Forum Discussion

JeffRobson's avatar
JeffRobson
Frequent Visitor
5 years ago
Solved

Power Query REST API with Cookies

I'm trying to extract some data from a REST API service that uses cookies to store the authentication credentials.

 

 

 

let
    Source = Json.Document(Web.Contents("https://app.siliconexpert.com/ProductAPI/search/authenticateUser?login=username&apiKey=password")),
    Status = Source[Status],  
    Source1 = Json.Document(Web.Contents("https://app.siliconexpert.com/ProductAPI/search/listPartSearch?partNumber=%5b%7b%22partNumber%22:%22" & PartNumber & "wt%22%7d,%7b%22partNumber%22:%22" & PartNumber & "%22%7d%5d&mode=beginwith&pageNumber=1")),
    Status1 = Source1[Status]
in
    Status1

 

 

 

The first call works fine, says I'm authenticated & sets 3 cookies on my machine.

 

However, the second call then says I'm not authenticated & fails.

 

I've had a look through many articles on this subject but can't figure out how to capture the cookies then pass them back to the API so it knows I'm authenticated in subsequent queries.

 

Can anyone assist please?

 

API documentation at https://www.siliconexpert.com/apidocs/

 

Thanks in advance 🙂

Need Help
  • JeffRobson's avatar
    JeffRobson
    5 years ago

    I ended up solving my own problem with a Python workaround.

     

    The API documentation included some Python code which I then modified slightly to produce the results I needed.

     

    I setup a new query using the Python Script connector with the code below as the source:

     

     

     

    from pip._vendor import requests
import pandas as pd
 
headers = {'Content-Type': 'application/x-www-form-urlencoded'}
 
s = requests.Session()
 
# Put your username and password
username = 'login'
password = 'password'
data = 'login=' + username + '&apiKey=' + password
url = "https://app.siliconexpert.com/ProductAPI/search/authenticateUser"
r = s.post(url, data=data, headers=headers)
 
# default is fmt=json
data = 'fmt=xml&partNumber=[{"partNumber":"bav99wt"}]'
url = "https://app.siliconexpert.com/ProductAPI/search/listPartSearch"
r = s.post(url, data=data, headers=headers)
df = pd.DataFrame(r)
print(df)

     

     

    This returned a series of rows which I then converted into a single text field using PQ, then parsed into XML & generated the data I needed.

     

    I then edited the code to insert Power Query parameters for the username, password and part number, then converted this to a function and applied it against my data set of part numbers as an invoked function.

     

    Thanks for your help. I hope this helps someone else in future!

    Jeff

4 Replies

  • JeffRobson's avatar
    JeffRobson
    Frequent Visitor
    5 years ago

    I ended up solving my own problem with a Python workaround.

     

    The API documentation included some Python code which I then modified slightly to produce the results I needed.

     

    I setup a new query using the Python Script connector with the code below as the source:

     

     

     

    from pip._vendor import requests
import pandas as pd
 
headers = {'Content-Type': 'application/x-www-form-urlencoded'}
 
s = requests.Session()
 
# Put your username and password
username = 'login'
password = 'password'
data = 'login=' + username + '&apiKey=' + password
url = "https://app.siliconexpert.com/ProductAPI/search/authenticateUser"
r = s.post(url, data=data, headers=headers)
 
# default is fmt=json
data = 'fmt=xml&partNumber=[{"partNumber":"bav99wt"}]'
url = "https://app.siliconexpert.com/ProductAPI/search/listPartSearch"
r = s.post(url, data=data, headers=headers)
df = pd.DataFrame(r)
print(df)

     

     

    This returned a series of rows which I then converted into a single text field using PQ, then parsed into XML & generated the data I needed.

     

    I then edited the code to insert Power Query parameters for the username, password and part number, then converted this to a function and applied it against my data set of part numbers as an invoked function.

     

    Thanks for your help. I hope this helps someone else in future!

    Jeff

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi JeffRobson 

    Remove the credential in the Data Source Setting and connect to api again by this code.

    I think you can try to authenicate by access token. 

    For reference:

    Using a REST API as a data source

    Using a REST API as a data source - POST Method Only

    Video: Get access token and then connect to Rest API in Power Query

    https://www.youtube.com/watch?v=N8qYRSqRz84

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

    • JeffRobson's avatar
      JeffRobson
      Frequent Visitor
      5 years ago

      Thanks for your suggestions, Rico.

       

      Unfortunately, it seems the API I'm trying to connect to doesn't support access tokens. It seems to require the cookies created during the authorization process for each API call.

       

      For example, the cURL code for a part number search is:

       

      # 1) Creating a new directory (if not exists) to save cookies.
mkdir /tmp/siliconexpert
 
# 2) Creating a new file (if not exists) to save cookies.
touch /tmp/siliconexpert/cookies
 
# 3) Authentication (if not authenticated or session expired).
#   -d (Send specified data in POST request).
#   -X (The request method to use).
#   login (Your API username).
#   apiKey (Your API password).
curl -c /tmp/siliconexpert/cookies -d "login=my_username&apiKey=my_password" -X POST https://app.siliconexpert.com/ProductAPI/search/authenticateUser
 
# 4) Calling the API.
curl -b /tmp/siliconexpert/cookies -d "partNumber=[{\"partNumber\":\"bav99wt\"},{\"partNumber\":\"bav99\"}]&mode=beginwith&pageNumber=1&fmt=xml" -X POST https://app.siliconexpert.com/ProductAPI/search/listPartSearch

       

      https://www.siliconexpert.com/apidocs/#list-part-search

       

      Unfortunately, I'm not sure how to capture the cookies then pass them to the subsequent calls.

       

      Is there a way to do this?

       

      I attempted the R solution to this detailed at https://community.powerbi.com/t5/Desktop/How-to-extract-http-response-header/m-p/452992 but there's something still not quite right as it still says I'm not authenticated & the tokens seem to fail.

       

      Do you have any other ideas?

       

      Thanks

  • Dev_powerBi's avatar
    Dev_powerBi
    New Member
    3 years ago

    Hi, 

     

    this is giving SSL certficate verfiy fail issue, any ideas to resolve