Wednesday, September 30, 2020

Quick Tip: Avoid limitations when reading SharePoint list items using Microsoft Graph

Microsoft introduced in latest version of CSOM a new tenant property Tenant.DisableCustomAppAuthentication which can be used to disable the capability to grant permissions to app authentication in SharePoint. Unfortunately, new Office 365 tenants have this property set to true by default. I'm not sure if that is a glitch, a bug or intentional. Does Microsoft want us to use Graph instead of SharePoint REST in the future? Anyway, from now on I want to use Graph whenever possible.

I’m developing a Microsoft Teams bot currently which uses SharePoint REST to retrieve data from SharePoint lists, create webhooks etc. Since accessing SharePoint lists and sites is already possible from Microsoft Graph we decided internally to replace the existing SharePoint REST implementation with Graph APIs. Unfortunately, I faced a limitation while retrieving data from a SharePoint list which I want to share with you in this blog post.

The goal

I tried to retrieve a SharePoint list item based on its ID. Along with that, I also tried to read additional content such as the item’s fields. This is what the request looks like:

https://graph.microsoft.com/v1.0/sites/1b3c1c82-8d58-4348-b13f-02898a1c662f/
lists/221395e9-cfca-4ac6-ba33-8b678fbc7836/items/407
?$select=Id&$expand=Fields($select=Title,_ModerationStatus,_ModerationComments)

The limitation

I was retrieving data from a SharePoint list which has content approval settings turned on. During my tests, if I tried to read those items that have an approval status other than Approved (e.g.: Rejected or Pending), the API threw the following error:

System.Exception: {
   "error":{
      "code":"itemNotFound",
      "message":"The specified list was not found",
      "innerError":{
         "date":"2020-09-30T14:33:44",
         "request-id":"56d110a8-4b07-4f37-857d-0dff276742bd",
         "client-request-id":"56d110a8-4b07-4f37-857d-0dff276742bd"
      }
   }
}

This error message is definitely misleading since I was able to retrieve other items from the same list.

The workaround

By turning the content approval settings off, I was able to retrieve all items from that list without limitations. However, that wasn’t the solution I was looking for 😀 I tried the same request using the Microsoft Graph Explorer and succeeded. I ended up with the conclusion that the API permission Sites.Manage.All was missing from my AAD app configurations. According to this Microsoft Graph documentationSites.Read.All should be enough to read SharePoint list items. Anyway, after adding the missing permission to my AAD app, I was finally able to read all list items from that list.

I hope it helps!