How to use filter in Microsoft Graph API to get SharePoint items?
Here I am trying to filter data based on created date. At 1st I tried in Graph Explorer and it's working.
https://graph.microsoft.com/v1.0/me/messages?$filter=createdDateTime ge 2017-09-04&$select=subject,lastModifiedDateTime
Now trying to implement same in Dell Boomi. This is resource path to pull all the items: sites/{id}.sharepoint.com:/sites/{id}:/lists/{list_id}/items
it's working fine.
After that I am adding filter condition:
sites/{id}.sharepoint.com:/sites/{id}:/lists/{list_id}/items?$filter=lastModifiedDateTime ge 2017-09-04&$select=email,displayName
Here is getting error. This is the error message:
<HTML><HEAD><TITLE>Bad Request</TITLE>
<META HTTP-EQUIV="Content-Type" Content="text/html; charset=us-ascii"></HEAD>
<BODY><h2>Bad Request</h2>
<hr><p>HTTP Error 400. The request is badly formed.</p>
Can some one help on this, how to fix this issue? Here is the Sample data.
> { "@odata.context":
> "https://graph.microsoft.com/v1.0/$metadata#Collection(microsoft.graph.list)('1334af71-5b7a-4276-a8d8-c3f3f129051d')/items",
> "value": [
> {
> "@odata.etag": ""ef6e961c-a956-400e-a77d-f044d2e0b894,8"",
> "createdDateTime": "2018-05-24T13:38:10Z",
> "eTag": ""ef6e961c-a956-400e-a77d-f044d2e0b894,8"",
> "id": "3",
> "lastModifiedDateTime": "2018-06-18T10:24:27Z",
> "webUrl": "https://{id}.sharepoint.com/sites/{id}/Doc%20Interfaces/757391.pdf",
> "createdBy": {
> "user": {
> "email": "[email protected]",
> "id": "173abc",
> "displayName": "abc"
> }
> },
> "lastModifiedBy": {
> "user": {
> "email": "[email protected]",
> "id": "234xyz",
> "displayName": "xyz"
> }
> },
> "parentReference": {
> "id": "03fe-16595a0da875"
> },
> "contentType": {
> "id": "0x01"
> },
> "[email protected]": "https://graph.microsoft.com/v1.0/$metadata#Collection(microsoft.graph.list)('1334f71-c3f3f129')/items('3')/fields/$entity",
> "fields": {
> "@odata.etag": ""ef6e961-f044d2e0b894,8"",
> "FileLeafRef": "757391.pdf",
Answer
Short Answer
Use the auto generated SharePoint list item fields Created or Modified
/items?expand=fields&$filter=fields/Modified gt '2018-01-01'
Important Note
To perform filter queries on these fields, you will have to either:
- Index these columns (see how here)
- Or Set the 'Prefer: HonorNonIndexedQueriesWarningMayFailRandomly' header on your request (not recommended by Microsoft)
Explenation
It seems like filtering on the values that are returned by the graph endpoint (such as lastModifiedDateTime, createdDateTime, etc.) is not supported, since requests like /items&$filter=lastModifiedDateTime ge '2018-01-01' will return a "Invalid filter clause" error.