Excel Online – Owner level list permissions may not be honored through Odata connections

There may be a scenario where an Excel Workbook is consuming a SharePoint list through OData that has Item level permissions on that Library. In this scenario, owners of the list should see all items added to the list by every user, however, you may find that owners are only seeing their own items in the list or getting an error if they have no items in the list. This will only happen when using the OData connection and refreshing that Data Connection in Excel Online.

To resolve this problem, you need to make sure that the proper service permissions are set for the site collection you are specifying in your OData connection. The steps below should resolve this issue.

1. Open https://YouSiteURL.com (or where SharePoint list lives) as a site collection admin
2. Change the url to https://YouSiteURL.com/_layouts/15/appinv.aspx in the browser's address bar manually
3. Copy paste 00000009-0000-0000-c000-000000000000 into the App Id field and click Lookup      

Note: You should get Microsoft.Azure.AnalysisServices or Microsoft Power BI Reporting and Analyticsin the Title field. The App id is shared between Excel Online (“Microsoft.Azure.AnalysisServices”) and Power BI ("Microsoft Power BI Reporting and Analytics"). The display name depends on your subscriptions.

4. Copy the site collection url into the Redirect Url field (https://YouSiteURL.com)
5. Copy the following lines 'as is' into the 'Permission Request XML' field.

Note: Do NOT replace http://sharepoint/content/sitecollection, that must be copied as a literal value, it is not a placeholder

    <AppPermissionRequest Scope="http://sharepoint/content/sitecollection"  Right="FullControl"/>

6. Click Create
7. Click Trust it on the next  page
8. Reopen the excel sheet with the Odata connection to the list in this site collection and refresh with an owner user.

Comments (2)

  1. Anonymous says:

    Added to the toolbox with workarounds, thanks for sharing!

  2. Tom S. - MSFT says:

    Also see:

    Project Online and Excel Web App: Cloud data improves reporting

Skip to main content