oData connection refresh in Excel Web App

This question has suggested answer(s) This question has suggested answer(s)

I'm trying to make the data connection refresh work in an excel workbook published to SharePoint on-line.

 

I am consuming an oData feed from a SharePoint list, it works great when opening the workbook in the client but a data refresh when viewing (or editing) in the browser always fails.

 

1. Is this supposed to be possible?

2. What are the pre-requisites?

3. Is there a step by step article somewhere?

 

Using Office 365 - 2013

 

Thanks for any help,

Ed

All Replies
  • Hi Ed,

    Currently, it's not available to refresh data connection directly when an Excel file is selected and displayed in the browser using Excel Services. As a workaround, we recommend opening the file in Excel  2013 to refresh the data, and then save the file back to the SharePoint document library where it resides.

    For detailed information about it, you can refer to the article:
    Project Server 2013 known issues (Excel reports using OData connections can't be refreshed in Excel Services)
    http://office.microsoft.com/en-us/help/project-server-2013-known-issues-HA102919020.aspx.

    Thanks,
    Ida Qiu

  • Disappointing, is this on the roadmap to be supported?

  • Disappointing, is this on the roadmap to be supported?

  • Hi Ed,

    Regarding whether the feature is supported in the future, I sincerely suggest you continue to pay attention to official information about Project Server 2013. Moreover, for more efficient and professional suggestions, I also recommend you post the question to Project Forum for better reply.

    Thanks,
    Ida Qiu

  • Hi Ida,

    I do not understand what this has to do with Project Server, except for the fact that project server users seem to suffer from the same issue. It's fundamentally a SharePoint on-line issue. I'm looking to publish graphs through published excel sheets but currently there isn;t a way to have these show the actual updated information unless someone manually opens them in the Excel client and publishes again.

    Thanks for your reply, regards,

    Ed


  • Hi Ed,
     
    I'm sorry to confuse you. As for the article mentioned above, regarding the issue, it provides a workaround to resolve it temporarily and applies to Project Web App for Project Server 2013. At the same time, regarding the Project service's related issue, there will be subsequent updates in the Project Forum as soon as possible.
     
    Moreover, if there are any updates about the issue, we also post the newest and related information in the thread for your reference. I appreciate your continued patience. Thanks for your understanding.
     
    Best Regards,
    Ida Qiu
  • Any update on this? I'm On SPO 2013 with Plan 2.  I have an excel web app with a excel 2013 worksheet hosted on SPO.  The worksheet is pointing to secure store for the user/pw (a cloud account also w/ Plan 2) that has access to a list. My connection is odata to the SPO list (on the same site).

     

    Basically cartwheels to get this all wired up, and it says it can't refresh the data from the SPO Excel Webpart.

     

    On my client everything works great, though if I log off and try to connect as the cloud user from the worksheet on my client it says it's can't use/find the data.

  • I feel the new Power BI tools should provide a solution now/soon:

    www.microsoft.com/.../Office.aspx

    HTH,

    Ed

  • Re​​​cently, we ​needed to connect a SharePoint list to an Excel spreadsheet and be able to refresh the data using Excel Online. This is how we got it working:

    Requireme​​nts​​

    • ​​​SharePoint Online with Excel Services (E​​​​3, E4)

    Creating the Spr​ea​dsheet​​​

    1. Open the spreadsheet using the Excel Desktop application

    2. In the ribbon, select ‘DATA > From Other Sources > From OData Data Feed’

    3. In the ‘Link or File’ field, enter your SharePoint site url followed by ‘/_vti_bin/listdata.svc/’ EG: https://simpli.sharep​oint.com/_vti_bin/listdata.svc, select ‘Use the sign-in information for the person opening this file’ and press ‘Next’

    4. Select which table(s) you want to import and press ‘Finish’

    5. Select how you want to view this data in your workbook (select ‘table’ if you’re not sure) and press ‘OK’ – this will import the list data into the spreadsheet

    6. Save this workbook to SharePoint

    Giving app permi​​ssio​​​ns to ‘Microsoft Analysis Services’

    1. In a web browser, enter your SharePoint site url followed by ‘/_layouts/15/appprincipals.aspx’ EG: simpli.sharepoint.com/_layouts/15/appprincipals.aspx

    2. If ‘Microsoft.Azure.AnalysisServices’ appears in the list, your job is done. Otherwise, continue.

    3. In a web browser, enter your SharePoint site url followed by ‘/_layouts/15/appinv.aspx’ EG: simpli.sharepoint.com/_layouts/15/appinv.aspx

    4. In the App ID field, add 00000009-0000-0000-c000-000000000000 and click Lookup. The title and App domain should tell you we’re working with the Azure AnalyisisServices. 

    5. Copy and paste the following in the Permission Request XML Field:
      <AppPermissionRequests><AppPermissionRequest Scope = "http://sharepoint/projectserver/reporting" Right="Read"></AppPermissionRequest><AppPermissionRequest Scope = "http://sharepoint/content/tenant" Right="FullControl"></AppPermissionRequest></AppPermissionRequests>

    6. Click ‘create’ and the ‘Trust It’

    A huge thanks to Alexander Vanwynsberghe for his post​ regarding the app permissions

    1 out of 1 people found this post helpful.

  • Interesting info and thanks for sharing!

    1 out of 1 people found this post helpful.

  • That's great ... I need to test this.. but  IMO it seems kind of  complicated for something that should just work automatically

    1 out of 1 people found this post helpful.