How to use filter in Microsoft Graph API to get SharePoint items?

sharepoint microsoft-graph-api sharepoint-online boomi

11,236

Solution 1

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.

Solution 2

I just solved a very similar problem in submitting an OData query to Boomi. The issue was the spaces in the filter string:

Your string: $filter=lastModifiedDateTime ge '2017-09-04' Should be: $filter=lastModifiedDateTime%20ge%20'2017-09-04'

11,236

Author by

Debmalya Ghosh

Updated on June 09, 2022

Comments

Debmalya Ghosh almost 2 years

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)(&#39;1334af71-5b7a-4276-a8d8-c3f3f129051d&#39;)/items",
> "value": [
>     {
>       "@odata.etag": "&quot;ef6e961c-a956-400e-a77d-f044d2e0b894,8&quot;",
>       "createdDateTime": "2018-05-24T13:38:10Z",
>       "eTag": "&quot;ef6e961c-a956-400e-a77d-f044d2e0b894,8&quot;",
>       "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)(&#39;1334f71-c3f3f129&#39;)/items(&#39;3&#39;)/fields/$entity",
>       "fields": {
>         "@odata.etag": "&quot;ef6e961-f044d2e0b894,8&quot;",
>         "FileLeafRef": "757391.pdf",

Debmalya Ghosh almost 6 years

Thanks for your input. I had tried this'2017-09-04' before but it's not working.
Sreeraj Sree almost 6 years

Did you try with the $expand
Debmalya Ghosh almost 6 years

For better understanding I have added sample data in Question. Please check. Here I am using this URL to pull the data. sites/{id}.sharepoint.com:/sites/{id}:/lists/{list_id}/items‌?expand=fields. By this I am able to get data mention above. After that I am tring to use Filter by date it's not working. This is the URL: sites/{id}.sharepoint.com:/sites/{id}:/lists/{list_id}/items‌?$filter=lastModifie‌dDateTime ge '2017-09-04'&$expand=value/webUrl
JollyBrackets almost 6 years

Also doesn't work for me. I'm not able to filter on any of the "meta" data, not even the ID (e.g. ?filter=id eq '15')
rgrebski almost 6 years

This one is working for me: /items?$expand=Fields&$filter=fields/Modified lt '2018-12-28T00:00:00Z'
Debmalya Ghosh almost 6 years

Not sure why it's not working for me. I tried this also tired to tweak this but not working. Same error. Not sure if dell boomi handle the this in some other way.
colonel_claypoo about 5 years

Thanks @JollyBrackets, this works for me but did you notice that 'equals' doesn't seem to work? I know the id of a list item but always get the "Invalid filter clause" error. Any ideas?