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,

Hi Amila,

Thank you for your suggestion. I will check.

BR,

Apsara

Did you manage to get it to work ?

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

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

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_Responsenaccess_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 =s#"Authorization"="Bearer "&token,#"Content-Type"="application/json"]
    ]

    )),

    values = dataavalue],
    #"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

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 

Thank you very much @Amila Samarasinghe 

Hi Amila,

What was the IAM client type you used?

I’m having some credentials problems!

br.petri

Hi @Petri P 

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 @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

This has been fixed.

Thanks !

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 ​@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.