Forum Discussion
Does power query work with Cookies
Hello Team
Does Power Query work with cookies?
There is no information on the portal about working with cookies:
https://docs.microsoft.com/en-us/power-query/handlingauthentication
I've built a connector using the OAuth flow. Just like how StartLogin, Finish Login, Refresh and Logout are available, is there anything thats available for handing cookies?
I've tried creating a function to handle cookies:
CookieRequest = () =>
let
HTTPHeader = [Headers = [#"Authorization"= "Bearer "&TokenMethod()[access_token], #"Content-Type" = "application/json"]],
Headers = HTTPHeader[Headers],//Response will be Authorization(Bearer Token) and Content-Type(application/Json)
Authorization = Headers[Authorization],//Authorization will have the Bearer Token
Cookies= Web.Contents("https://domain/CloudAssessmentService/api/cookie", [Headers=[Authorization=Authorization]])
in
Cookies;
TokenMethod is a function, I've created to get the access_token. When I run this function with the combination of TokenMethod in PowerBI Desktop, I get the cookies result(its just the domain name). However, when I use this function in my connector in visual studio, it doesn't work. I get the old error which is "we found extra characters at the end of JSON input". This error is returned because the authention isn't complete and it returns the HTML page to login(Once cookies are returned successfully the login completes).
Cookie is a part of the Headers. I've got this working in Postman. I have a screenshot but don't seem to have an option to upload it here.
Is there anything different, I can do with this code:
let
Cookies= Web.Contents("https://Domain/CloudAssessmentService/api/cookie", [Headers =[Authorization= "Bearer "&TokenMethod("password", "token", "code")[access_token], #"Content-Type" = "application/json"]]),CookiesResponse = Cookies,
apidata = Json.Document(Web.Contents("https://domain/api/software", [Headers =[Authorization= "Bearer "&TokenMethod("password", "token", "code")[access_token], Cookie="CookiesResponse"]]))
in
apidata
7 Replies
"does PQ work with cookies" is too ambiguous. Remember that all Power Query does is receive the results of a web.contents() call, including any possible session details. You then have to write your own Power Query code to interpret the results, store the credentials, and use them in subsequent calls.
In that sense - yes, Power Query works with cookies.
Thanks for the reply.
I'm succcessfully getting our cookies(tested in power query editor). If I may, I have another question. I'm trying to call one of our APIs using a similar function as before:
[DataSource.Kind = "Software" , Publish="Software.Publish"]
shared Software.Contents = (url as text) =>
let
GetSoftwareAccessToken = TokenMethod, //#"TokenMethod", //Returns the access_token, refresh_token, token_type and expiry time
HTTPHeader = [Headers = [#"Authorization"= "Bearer "&TokenMethod[access_token], #"Content-Type" = "application/json"]],//Returns the Authorization as Bearer Token and Content-Type as application/json in the form of RECORD
Headers = HTTPHeader[Headers],//Returns the Authorization as Bearer Token and Content-Type as application/json as values
Authorization = Headers[Authorization],
apidata = Web.Contents("https://domain/api/software", [Headers =[Authorization= "Bearer "&TokenMethod[access_token]]])
in
apidata;Now when I test this in visual studio, it returns an error:
"We cannot apply field access to the type Function"
However, the same function gets me the result in BYTES in Power Query Editor. When hitting our API, the only requirement is to have the cookies as before step and then send "Bearer Token" as Header with our API call which is what I'm sending in the apidata above.
Please ignore the previous reply. I've found out why that issue was occuring.
I'm trying to send the cookies along with the Bearer Token as Authorization to make an API call:
let
Cookies= Web.Contents("https://Domain/CloudAssessmentService/api/cookie", [Headers =[Authorization= "Bearer "&TokenMethod("password", "token", "code")[access_token], #"Content-Type" = "application/json"]]),CookiesResponse = Cookies,
apidata = Json.Document(Web.Contents("https://domain/api/software", [Headers =[Authorization= "Bearer "&TokenMethod("password", "token", "code")[access_token], Cookie="CookiesResponse"]]))
in
apidata
The cookies doesn't seems to be going through with the API call(apidata), it asks me for sign in details which shouldn't be the case. When I send Bearer Token and Cookie as header it should get me the data from the API call.
Am I doing it the wrong way to send the cookies as a header?
