topic Re: Power Query REST API with Cookies in Developer

Power Query REST API with Cookies

JeffRobson — Fri, 30 Jul 2021 09:17:42 GMT

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 🙂

Re: Power Query REST API with Cookies

Anonymous — Mon, 02 Aug 2021 08:25:13 GMT

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.

Re: Power Query REST API with Cookies

JeffRobson — Mon, 02 Aug 2021 09:24:20 GMT

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

Re: Power Query REST API with Cookies

JeffRobson — Tue, 03 Aug 2021 08:10:06 GMT

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

Re: Power Query REST API with Cookies

Dev_powerBi — Tue, 25 Apr 2023 17:58:14 GMT

Hi,

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