Solved

Authentication error when connecting Excel to IFS via OData feed


Badge +2

Hi all,

 

I am getting an authentication issue when trying to connect Excel to IFS via OData Feed. 

Steps are as follows.

 

  1. Data → Get Data → From Other Sources → From OData Feed
  1. Add the URL

 

  1. Connect using basic credentials.

 

Could anyone advice on how to solve this issue?

 

Thank you!

 

Best Regards,

Apsara

icon

Best answer by Amila Samarasinghe 26 May 2023, 14:31

View original

13 replies

Userlevel 6
Badge +14

Hi @apshlk 

 

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,

Badge +2

Hi Amila,

Thank you for your suggestion. I will check.

BR,

Apsara

Userlevel 3
Badge +8

Did you manage to get it to work ?

Userlevel 7
Badge +30

As @Amila Samarasinghe says, this is because of using basic authentication. I think it is still supported if you do not have another option. Have a look at our documentation:

https://docs.ifs.com/techdocs/23r1/030_administration/010_security/040_iam_settings/050_authentication_for_integrations/030_direct_access_grants/#basic_authentication_leveraging_ropc_in_the_ifs_proxy

 

Userlevel 7
Badge +20

In 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

Userlevel 7
Badge +20

In 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 :(

 

Userlevel 6
Badge +14

Hi @dsj @apshlk 

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

Userlevel 7
Badge +20

wow, this is a breakthrough!

I didn’t think it’s possible to add the token fetching inside a function itself, bravo.

Thanks for sharing @Amila Samarasinghe 

Badge +2

Thank you very much @Amila Samarasinghe 

Userlevel 1
Badge +3

Hi Amila,

What was the IAM client type you used?

I’m having some credentials problems!

br.petri

Userlevel 6
Badge +14

Hi @Petri P 

It was a normal IAM client with Service Accounts enabled and a service account user connected.

BR,

Amila

Userlevel 1
Badge +3

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

 

Userlevel 1
Badge +3

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

Reply