【SharePoint】SharePoint Rest API for Select、Filter、Sort、Paging（SharePoint rest api操作SharePoint list ）

前言

很多时候我们需要对SharePoint的custom list或document library进行相关操作，接下来将了解一下如何通过rest api对SharePoint进行查询、条件搜索、排序、分页等操作（适用于SharePoint 2010/2013/2016/2019/online)

 示例

1、select list all items for all field（获取自定义列表list name所有items，包含所有列）

示例：weburl/_api/web/lists/getbytitle('list name')/items

2、select list all items for specific field（获取自定义列表指定列的所有items）

语法：$select=Field1, Field2, Field3

示例：weburl/_api/web/lists/getbytitle('list name')/items?$select=ID,Title

3、order（排序） 

语法：$orderby=(Column Internal Name order)

升序: weburl/_api/web/lists/getbytitle('list name')/items?$select=ID,Title&$orderby= Title asc

降序: weburl/_api/web/lists/getbytitle('list name')/items?$select=ID,Title&$orderby= Title desc

4、filter（筛选）

语法：$filter=(Column Internal Name operator value)

示例：

Filter by Title	weburl/_api/web/lists/getbytitle('list name')/items?$filter= Title eq ‘parth'
Filter by ID	weburl/_api/web/lists/getbytitle('list name')/items?$filter=ID eq 2
Filter by Date	weburl/_api/web/lists/getbytitle('list name')/items?$filter=Start_x0020_Date le datetime'2016-03-26T09:59:32Z'
Multiple Filters	weburl/_api/web/lists/getbytitle('list name')/items?$filter=( Modified le datetime'2016-03-26T09:59:32Z') and (ID eq 2)
Title name starts with the letter P	weburl/_api/web/lists/getbytitle(‘'list name')/items?$filter=startswith(Title,‘P’)
Return all items from the 'list name' list modified in May	weburl/_api/web/lists/getbytitle(‘'list name')/items? $filter=month(Modified) eq 5

5、Operator（运算符）

Supported	Not supported
Numeric comparisons Lt Le Gt Ge Eq Ne	Arithmetic operators  (Add, Sub, Mul, Div, Mod) Basic math functions  (round, floor, ceiling)
String comparisons startsWith substringof Eq Ne	endsWith replace substring tolower toupper trim concat
Date and time functions day() month() year() hour() minute() second()	DateTimeRangesOverlap operator Querying as to whether a date time falls inside a recurrent date time pattern

6、Top（获取列表中前几条数据）

语法：$top Count

示例：weburl/_api/web/lists/getbytitle('list name')/items?$top 5

7、*expand（扩展：当获取 person or lookup字段时，经常用到）

lookup字段（city为lookup表）示例：weburl/_api/web/lists/getbytitle('list name')/items?$select=ID,Title,city/Id&$expand= city/Id

person字段（Author为用户字段）示例：weburl/_api/web/lists/getbytitle('list name')/items?$select=Author/Title&$expand=Author/Id