Sign up for Office 365
Learn more about Office 365
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,
Thanks for the feedback.
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)
Disappointing, is this on the roadmap to be supported?
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.
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,
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:
Recently, 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:
SharePoint Online with Excel Services (E3, E4)
Open the spreadsheet using the Excel Desktop application
In the ribbon, select ‘DATA > From Other Sources > From OData Data Feed’
In the ‘Link or File’ field, enter your SharePoint site url followed by ‘/_vti_bin/listdata.svc/’ EG: https://simpli.sharepoint.com/_vti_bin/listdata.svc, select ‘Use the sign-in information for the person opening this file’ and press ‘Next’
Select which table(s) you want to import and press ‘Finish’
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
Save this workbook to SharePoint
In a web browser, enter your SharePoint site url followed by ‘/_layouts/15/appprincipals.aspx’ EG: simpli.sharepoint.com/_layouts/15/appprincipals.aspx
If ‘Microsoft.Azure.AnalysisServices’ appears in the list, your job is done. Otherwise, continue.
In a web browser, enter your SharePoint site url followed by ‘/_layouts/15/appinv.aspx’ EG: simpli.sharepoint.com/_layouts/15/appinv.aspx
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.
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>
Click ‘create’ and the ‘Trust It’
A huge thanks to Alexander Vanwynsberghe for his post regarding the app permissions
2 out of 2 people found this post helpful.
Interesting info and thanks for sharing!
That's great ... I need to test this.. but IMO it seems kind of complicated for something that should just work automatically