Skip to main content
Solved

Authentication error when connecting Excel to IFS via OData feed


Forum|alt.badge.img+2
  • Do Gooder (Employee)
  • 2 replies

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

Best answer by Amila Samarasinghe

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

View original
Did this topic help you find an answer to your question?

17 replies

Amila Samarasinghe
Superhero (Partner)
Forum|alt.badge.img+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,


Forum|alt.badge.img+2
  • Author
  • Do Gooder (Employee)
  • 2 replies
  • May 23, 2023

Hi Amila,

Thank you for your suggestion. I will check.

BR,

Apsara


Forum|alt.badge.img+8
  • Sidekick (Partner)
  • 27 replies
  • May 24, 2023

Did you manage to get it to work ?


Mathias Dahl
Superhero (Employee)
Forum|alt.badge.img+32
  • Superhero (Employee)
  • 2798 replies
  • May 25, 2023

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

 


dsj
Superhero (Partner)
Forum|alt.badge.img+22
  • Superhero (Partner)
  • 824 replies
  • May 25, 2023

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


dsj
Superhero (Partner)
Forum|alt.badge.img+22
  • Superhero (Partner)
  • 824 replies
  • May 26, 2023
dsj wrote:

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

 


Amila Samarasinghe
Superhero (Partner)
Forum|alt.badge.img+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


dsj
Superhero (Partner)
Forum|alt.badge.img+22
  • Superhero (Partner)
  • 824 replies
  • May 26, 2023

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 


Forum|alt.badge.img+2
  • Author
  • Do Gooder (Employee)
  • 2 replies
  • May 31, 2023

Thank you very much @Amila Samarasinghe 


Forum|alt.badge.img+3
  • Do Gooder (Employee)
  • 6 replies
  • August 14, 2023

Hi Amila,

What was the IAM client type you used?

I’m having some credentials problems!

br.petri


Amila Samarasinghe
Superhero (Partner)
Forum|alt.badge.img+14

Hi @Petri P 

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

BR,

Amila


Forum|alt.badge.img+3
  • Do Gooder (Employee)
  • 6 replies
  • August 16, 2023

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

 


Forum|alt.badge.img+3
  • Do Gooder (Employee)
  • 6 replies
  • August 23, 2023
Petri P wrote:

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


Forum|alt.badge.img+4
  • Do Gooder (Partner)
  • 8 replies
  • May 21, 2024

Hi @Amila Samarasinghe,

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


Forum|alt.badge.img+4
  • Do Gooder (Partner)
  • 8 replies
  • May 22, 2024

This has been fixed.

 

Thanks !


Forum|alt.badge.img+10
  • Hero (Partner)
  • 145 replies
  • December 20, 2024

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


Forum|alt.badge.img
  • Do Gooder (Customer)
  • 2 replies
  • February 6, 2025

Firstly, let me thank ​@Amila Samarasinghe for putting in the hard work and getting this code onto the internet.

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.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings