+2Hi all,
I am getting an authentication issue when trying to connect Excel to IFS via OData Feed.
Steps are as follows.
Could anyone advice on how to solve this issue?
Thank you!
Best Regards,
Apsara
Best answer by Amila Samarasinghe
Hi
I actually managed to fetch data to excel using power query. I found below guide and also did some twerking to the code to achieve this.
https://www.myonlinetraininghub.com/connecting-to-an-oauth-api-like-paypal-with-power-query
This is the code I used
let
api_url = "https://servername.ifs.com/",
token_path = "auth/realms/#realm_name#/protocol/openid-connect/token",
ClientID = "client id", //client ID and secret from IAM client
Secret = "secret",
EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(ClientID & ":" & Secret), BinaryEncoding.Base64),
Token_Response = Json.Document(Web.Contents(api_url,
[
RelativePath = token_path,
Headers = [#"Content-Type"="application/x-www-form-urlencoded",#"Authorization"=EncodedCredentials],
Content=Text.ToBinary("grant_type=client_credentials")
]
)),
token = Token_Response[access_token],
path = "main/ifsapplications/projection/v1/DocumentRevisionsHandling.svc/DocIssueSet?&$skip=0&$top=25", //projection URL
data = Json.Document(Web.Contents(api_url,
[
RelativePath = path,
Headers =[#"Authorization"="Bearer "&token,#"Content-Type"="application/json"]
]
)),
values = data[value],
#"Converted to Table" = Table.FromList(values, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"luname", "keyref", "DocClass", "DocNo", "DocSheet", "DocRev", "Title"}, {"Column1.luname", "Column1.keyref", "Column1.DocClass", "Column1.DocNo", "Column1.DocSheet", "Column1.DocRev", "Column1.Title"})
in
#"Expanded Column1"Below is the fetched data from excel
BR
+14Hi
In IFS Cloud, basic authentication is not supported. Also please see below idea. Seems this is not possible in IFS Cloud yet.
OData Access from Excel | IFS Community
BR,
+32As
+22In my opinion it’s highly likely that it’s possible to connect IFS Cloud to excel as Odata feed. Seems we need to implement the OAuth2 flow for power query as mentioned in MS documentation
https://learn.microsoft.com/en-us/power-query/handling-authentication#implementing-an-oauth-flow
Cheers!
Damith
+22In my opinion it’s highly likely that it’s possible to connect IFS Cloud to excel as Odata feed. Seems we need to implement the OAuth2 flow for power query as mentioned in MS documentation
https://learn.microsoft.com/en-us/power-query/handling-authentication#implementing-an-oauth-flow
Cheers!
Damith
Edit on my last comment.
Did some search around this and it seems it’s not possible to extend the power query within excel. So that option is not useful :(
+14Hi
I actually managed to fetch data to excel using power query. I found below guide and also did some twerking to the code to achieve this.
https://www.myonlinetraininghub.com/connecting-to-an-oauth-api-like-paypal-with-power-query
This is the code I used
let
api_url = "https://servername.ifs.com/",
token_path = "auth/realms/#realm_name#/protocol/openid-connect/token",
ClientID = "client id", //client ID and secret from IAM client
Secret = "secret",
EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(ClientID & ":" & Secret), BinaryEncoding.Base64),
Token_Response = Json.Document(Web.Contents(api_url,
[
RelativePath = token_path,
Headers = [#"Content-Type"="application/x-www-form-urlencoded",#"Authorization"=EncodedCredentials],
Content=Text.ToBinary("grant_type=client_credentials")
]
)),
token = Token_Response[access_token],
path = "main/ifsapplications/projection/v1/DocumentRevisionsHandling.svc/DocIssueSet?&$skip=0&$top=25", //projection URL
data = Json.Document(Web.Contents(api_url,
[
RelativePath = path,
Headers =[#"Authorization"="Bearer "&token,#"Content-Type"="application/json"]
]
)),
values = data[value],
#"Converted to Table" = Table.FromList(values, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"luname", "keyref", "DocClass", "DocNo", "DocSheet", "DocRev", "Title"}, {"Column1.luname", "Column1.keyref", "Column1.DocClass", "Column1.DocNo", "Column1.DocSheet", "Column1.DocRev", "Column1.Title"})
in
#"Expanded Column1"Below is the fetched data from excel
BR
+22wow, this is a breakthrough!
I didn’t think it’s possible to add the token fetching inside a function itself, bravo.
Thanks for sharing
Hi Amila,
What was the IAM client type you used?
I’m having some credentials problems!
br.petri
+14Hi
It was a normal IAM client with Service Accounts enabled and a service account user connected.
BR,
Amila
Hi Amila,
Thanks for the info. What I do wrong. still get some credentials problem? I have also a service user. So no user id’s nor passw’s needed for the call header . The ClientId and secret enough. I get the token alright (not the Data). I have given the permission to the projection for server user. The projection is made with QueryDesigner but got the same when trying similar calls that oyu have used.
Br.petri
Hi Amila,
Thanks for the info. What I do wrong. still get some credentials problem? I have also a service user. So no user id’s nor passw’s needed for the call header . The ClientId and secret enough. I get the token alright (not the Data). I have given the permission to the projection for server user. The projection is made with QueryDesigner but got the same when trying similar calls that oyu have used.
Br.petri
Now I got data to excel, don’t know why credentials error first, maybe some refresh problem.
br.petri
Hi
I am also trying to get this solution works. I used the same example you have published here, and it seemed to work for me but now it is giving me below error.
Permissions also set as below.
Same endpoint works from Postman tool.
Appreciate if you could share some input on this.
thanks !
/Damith
Hi All,
Can one of you who solved the issue please outline the solution. Seems like it is a common problem and I am facing it now. But can’t find the solution anywhere.
Regards,
Malik
Firstly, let me thank
I had issues getting this working, but I wanted to share how I got it working, just in case it helps someone else.
I followed all the steps and was still getting issues, but it was a 401 error, that isn’t mentioned here.
It turned out that Service Account User associated with the IAM Client I was using hadn’t been given any permissions in IFS.
Once I gave it permissions to view the data and the relevant companies and sites, I had no issues with the query.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
