Using $filter with Microsoft Graph Excel APIs

Question

With Microsoft Graph I can access rows from a table like this:

/v1.0/drives/..../workbook/worksheets/Sheet4/tables/2/rows

The documentation states:

This method supports the OData Query Parameters to help customize the response.

I am able to use the $select query parameter:

/v1.0/drives/..../workbook/worksheets/Sheet4/tables/2/rows?$select=values.

But how can I use $search or $filter query parameters? For example, I want to search the rows where column 'employeeName' contains the string "John".

Answer 1

In order to filter data from Excel you should get a workbook session id first:

POST https://graph.microsoft.com/v1.0/drives/.../workbook/createSession

BODY => {persistChanges:false}

You may change the value of persistChanges to true if you want to keep any changes you make to the worksheet. This will return an id which you will be using it as part of the headers when applying the filter:

POST https://graph.microsoft.com/v1.0/drives/.../workbook/worksheets('Sheet4')/tables(id='4')/columns('employeeName')/filter/apply

HEADER => workbook-session-id: session_Id

BODY => {  criteria: {  filterOn: "Custom",  criterion1: "=John", operator: "Or", criterion2: null }

Finally you can retrieve the rows by using:

GET https://graph.microsoft.com/v1.0/drives/.../workbook/worksheets('Sheet4')/tables('4')/range/visibleView/rows?$select=values

HEADER => workbook-session-id: session_Id

Here is some reference on how to setup the criteria

And a general reference about Excel and the Graph API

Answer 2

Microsoft Graph has some documentation about the optional query parameters here. There's also more documentation about the OData Query standards here.

Microsoft Graph only allows the $search query parameter to be used with message and person collections. Here is an example to find all messages that contain "pizza":

GET https://graph.microsoft.com/v1.0/me/messages?$search="pizza"

The $filter query parameter doesn't have this limitation. Here is an example to find all the users with names that start with "A":

GET https://graph.microsoft.com/v1.0/users?$filter=startswith(displayName,'A')