Excel Online - Consuming SharePoint Lists & Data Refresh

Dear Office 365 Team,

I set up a data connection to a SharePoint list using this guide: blogs.technet.com/.../excel-online-consuming-sharepoint-lists.aspx
If I press "Refresh All Connections" inside my Excel sheet an error message appears:

"Connection: PoC
Error: A connection could not be made to the data source with the DataSourceID of '68d644a7-eb58-4ccd-ad0c-d15bb1f19216', Name of 'PoC'.
An error occurred while processing table 'PoC'.
The current operation was cancelled because another operation in the transaction failed."

I also tried the following guide: http://www.sptechcon.com/news/sharepoint-dashboard-excel which ended up in the same problem.

Please help me :-).

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Hi Julian,

Since our forum focuses on Office 365 online services, it is suggested you post the issue in Excel IT Pro Discussions Fourm for dedicated support. Thanks for your understaning.

Meanwhile, you may refer to the following link for troubleshooting:
http://social.technet.microsoft.com/wiki/contents/articles/3870.troubleshoot-powerpivot-data-refresh.aspx

Best Regards,
Vincent

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Dear Vincent,

this is an issue I have with SharePoint Online in combination with Excel Online (using the Office Web Apps or the Excel WebPart).

If I refresh the data connection on my local Excel it does work like it is supposed to be.

Unfortunately I could not find any useful information in the troubleshooting guide - most of the tips seems to be for an On-Prem. environment.

Thank you for your help and best regards

Julian

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi Julian,

Since Excel Online app cannot connect to external data source directly, could you let us know whether you edit the Excel file in Excel Online with Excel desktop app(Excel 2013)? In addition, could you let us know the following information for narrowing down this issue?
1. I noticed from the second link in your initial post that view list permission is needed when refreshing data connection, could you verify if you can view the connected list with your account?
2. According to the blog below, in order to refresh Excel Web App, you must first perform one refresh in Excel 2013 client and save the report which will update the internal model of the workbook so it is supported by Excel Web App, have you done the same?
https://blogs.office.com/2013/03/29/project-online-and-excel-web-app-cloud-data-improves-reporting/
3. To identify if this issue is related to specific list, please try to connect to other lists and see if the issue happens.

Best Regards,
Vincent

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hey Vincent,

thank you for your fast reply.

I edit the file using Excel Online (or the Excel WebPart). If I try to refresh the data connection using the Excel desktop app (2013) it does work like it is supposed to.

The account I am using is Site Collection administrator in the given site and has also view list permissions (anyway that is a good hint).

When I did my tests, I set up the data connection, did a refresh and uploaded the file to SharePoint. So the internal model of the workbook should be fine.

I tried the same procedure with different lists, site collections and even tenants. It is always the same :-(.

Best regards and thank you for your help.

Julian

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

hi julian,

i followed your scenario and reproduced this issue. when i click learn more about data refresh from the error message, it brought me to the following link:
https://support.office.com/en-us/client/results?shownav=true&lcid=1033&ns=xlwaenduser&version=15&ver=15&apps=wdwaenduser%2cxlwaenduser%2cppwaenduser%2conwaenduser&helpid=excelrefreshfailed 

please pay attention to the important part, which says:
depending on how your particular environment is configured, you might or might not be able to refresh the data in a workbook that you are viewing in a browser window. for example, the ability to refresh external data depends, in large part, on how excel services is configured. for more information, contact a sharepoint administrator.

in addition, i did some research and found a relevant article explaining that this is an expected behavior:
http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2013/01/31/powerpivot-for-sharepoint-browser-refresh-fails-data-refresh-not-supported-in-office-web-apps.aspx 

as it says, excel web app runs in one of two modes: sharepoint view mode and office web apps server view mode. when we open excel file in excel online, you will observe some characters like wopiframe.aspx from the url which indicate excel web app is running in office web server view mode. when office web app sever view mode is used to view workbooks, the feature of refresh odata connections is not available.

given the situation, i would like to suggest choosing edit in excel and refresh the data connection in local excel app as a workaround.



best regards,
vincent

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Dear Vincent,

thank you for your suggestion. 

If I use my local Excel client to do the refresh it does indeed work - but unfortunately this is not a solution for my problem, as I want a data refresh using Excel Online.

According to this article: Use external data in workbooks in SharePoint Online using a SharePoint list as an OData feed is a supported scenario.

Some—but not all—of the data sources that you can use in Excel are supported in SharePoint Online. When a data source is supported, it means that you can typically refresh the data in the workbook in a browser window. You can use the following kinds of data sources in workbooks in SharePoint Online:

  • SharePoint lists that are available as OData feeds

Thank you for your help

Julian

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi Julian,

I follow the same steps and can confirm that it should work. From your side, may I know whether the issue occurs to any workbooks even the one created as simple as the demo in the guide?

Meanwhile, I’d like to confirm whether the connected list and the Excel workbook are stored in the same tenant. If not, the issue as which you run into will occur.

If all your workbooks stored in the same tenant as the connected list cannot be refreshed, could you follow the steps below and let me know the outcome?

1. Close all your Office applications on your computer.
2. Go to Control Panel > User Accounts > Credential Manager > Windows Credentials.
3. Remove all Office 365 related credentials, e.g. MicrosoftOffice15_Data:orgid:*** Email address is removed for privacy *** or MicrosoftOffice16_Data:ADAL:xxx.
4. Follow the blog to create a new list and a workbook connecting to it.
5. Create a new library in the same site as the list.
6. Save and upload the workbook to the library.
7. Open it in Excel Online and see if you can refresh the data.

Thanks for your time and effort.

Lingyu Sun

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi Julian,

Is there any update?

Thanks,
Lingyu Sun

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hey Lingyu Sun,

this did the trick for me :-).

Thank you for your help!

Best regards

Julian

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Dear Lingyu Sun,

unfortunately I need to reject my answer.

Following your steps it used to work one time.

Now I tried it again and it does not work anymore...

I receive the same error as always.

Best regards

Julian

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated April 22, 2019 Views 1,450 Applies to: