K2 OData Feed with Microsoft Excel not showing any table in excel

  • 27 February 2020
  • 7 replies
  • 29 views
S

I did all the steps as given by the K2 Article How to: Use the K2 OData Feed with Microsoft Excel 

But i am stuck at one place on excel

when you do the Data Connection Wizard

- I did properly Connect to Data Feed Configuration

- Next Select Table appears to be empty.

13309i7970E1F278944854.pngCan anybody help me with this. Thank you in advance.

7 replies

K
Badge +7

Hello  @Sabina,


 


To resolve this problem it is necessary to perform the following:


1- Go to K2 Management > Categories > SharePoint 2013 > Libraries > Select the SmarObject used in the OData (SharePoint Document Library)


2- Scroll down to "SmartObject OData API" section


3- Change the Primary key of the SmartObject to ID


4- Click on 'Refresh Now' and then try to load the data in Excel or PBI


 


-AAD consent when setting up the API will need to be completed by the global admin account with MFA turned off.


 


 


-Then when connecting to the OData feed from Excel, you will need to initially connect with an account that also has MFA(Multi-Factor Authentication)off.


 


I heope this helps.


 


 

D

Hi  @Sabina ,


 


When creating the Smartbox SmartObject in K2 Designer. Did you include at least three properties/items in the Smartbox SmartObject?


Did you ensure you did not include the word "OData" in the SmartObject name?


 


After this, when enabling the OData API in K2 Management, did you select the SmartObject that you have created to be the only data source? And be sure to configure all the other settings as shows on the screenshot attached.


Be sure to click "Apply Changes" after configuring the settings.


 


Did you ensure you copied the correct OData URL when configuring the OData settings?


 


If you have done all these steps correctly and it still won't work, please provide me with more details so I can try to reproduce the problem on my side.


Details to provide:


K2 version you are using


Microsoft Excel version you are using


Any other relevant information about your issue.


 


Kind regards,


Dian


S

Hi @Dian , I have done everything as per the article

but still no luck.

Below are the information you asked

 

K2 Version - 5.1

Excel - Microsoft Excel 2016 MSO (16.0.7726.1042) 32 bit

Thanks for your time.

H

Hi there  @Sabina 


 


I realise that from the original post you are using k2 cloud user guide update 11 to configure a smartObject for OData API instead of using K2 five user guide 5.1 since you are using k2 version 5.1 . Can you please maybe try to configure it using the link below after making your smartObject


https://help.k2.com/onlinehelp/k2five/userguide/5.1/default.htm#K2-Management-Site/Integration/SmartObjectODataService.htm


 


When done with the configuration of a smartObject for OData API using the documentation of K2 five user guide 5.1 then you can review the following link on how to use the k2 OData Feed with microsoft excel


https://help.k2.com/onlinehelp/k2five/userguide/5.1/default.htm#How-Tos/ODataFeedService/ODataFeedWithExcel.htm


 


Let me know if this help

S

Hi  @HulisaniN,

 

Thnaks for your reply

 

Its not different article almost the same.

I found one issue is that when i open this url in browser

https://[k2_site_name]/api/odata/op/refresh

it gives me the following screen

Regards,

 

Sabina

H

Hi  @Sabina ,


 


For the 404 error please try the following steps:



  • Have you check your IIS on the website's binding and the scheme (HTTP/HTTPS)?


Similar issue


https://community.k2.com/t5/K2-Blackpearl-Forum-Posts/can-not-open-k2-workspace-with-a-404-error/m-p/81500#M24174


 


Also you can do the following steps to solve the error



1. Click on "Start>Control Panel"
2. Open "Administrative Tools"
3. Open "Internet Information Services (IIS) Manager"
4. Expand the node for the relevant web server
5. Click on the "Web Service Extensions" node
6. Select the ASP.NET Web Service Extension from the list on the right hand side
7. Click on the "Allow"
8. Close the "Internet Information Services (IIS) Manager" console
9. just to be sure, do an IIS Reset :D


 


here is the link i got it from.


 


I really hope one of this links help you

D

Hey  @Sabina ,


 


I've read through all of the considerations to keep in mind when trying to expose a SmartObject as OData in this article (the one that you follwed when doing your initial configuration)


And I thought the following three to be the most likely cause of your issue:



  • Category and SmartObject Security affect whether SmartObjects can be published as OData endpoints. To publish a SmartObject via the OData API, the Application Pool user account that hosts the K2 sites requires at least “View” rights on the SmartObject (or on the parent category/folder, if the security on the SmartObject is set to “Inherit Permissions”).

  • The OData API can only expose SmartObjects that have a List method. To change the default list method see the SmartObject Default List topic.

  • You can only use the SmartObject OData Service API environments where AD or AAD is the default identity provider. You cannot use the API in K2 environments that use SQLUM


 


If none of these are the cause of your problem, please also read through the Authentication part, just after the Considerations part (that should also be read) of this article


 


As per your last message relating to the error message you receive when entering your url in a browser, I get the same type of error: 



however as you can see on the image, in this case the SmartObject is actually referenced in the XML. 


So this is why I think your problem could possibly be related to your identity provider or possibly the authentication you are using.


 


One more thing, could you please ensure that the following directory:


C:[K2 Installation Path]WebServicesAPISmartObjectService.OData


ex: C:Program FilesK2WebServicesAPISmartObjectService.OData


Does exist with the follwing contents:



 


Kind regards,


Dian

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings