SharePoint Patterns

Three ways in — pick the right one

Almost everything in SharePoint is reachable three ways, and choosing well saves you most of the pain. Reach for the first-party SharePoint connector actions (Get items, Create item, Create file…) for the common 80%; drop to Send an HTTP request to SharePoint when the connector can’t express what you need; and prefer Microsoft Graph for cross-service work and lighter throttling.

The REST/OData surface

The SharePoint REST service lives under _api/ on every site. List data hangs off _api/web. Address a list by title, by GUID, or by its server-relative URL — title is readable but breaks if the list is renamed, so GUID is the stable choice for production.

GET https://contoso.sharepoint.com/sites/Ops/_api/web/lists/getbytitle('Orders')/items
GET https://contoso.sharepoint.com/sites/Ops/_api/web/lists(guid'2b1c…')/items
GET https://contoso.sharepoint.com/sites/Ops/_api/web/GetList('/sites/Ops/Lists/Orders')/items
Accept: application/json;odata=nometadata

JSON light — control how much metadata you get back

The Accept header decides how chatty the response is. Use the lightest level your code needs — nometadata is far easier to parse, verbose is required when you write items because the entity type lives in the metadata.

OData querying: $select, $filter, $expand, paging

Push shaping into the query string so the server returns only the rows and columns you need. Selecting two columns instead of forty is the single cheapest performance win, and a filter on an indexed column is what keeps a large list usable (see Large lists below).

Expand a lookup or person column

GET …/_api/web/lists/getbytitle('Orders')/items
  ?$select=Title,Status,AssignedTo/Title,AssignedTo/EMail,Category/Title
  &$expand=AssignedTo,Category
  &$top=100
Accept: application/json;odata=nometadata

Creating, updating & deleting items

Reads are plain GETs. Every write is a `POST` — you signal update vs delete through the X-HTTP-Method header, not the verb. Two things trip people up: the request-digest token and the item’s entity type name.

Get the form digest first (raw REST only)

POST https://contoso.sharepoint.com/sites/Ops/_api/contextinfo
Accept: application/json;odata=nometadata
// → body.FormDigestValue  →  send as X-RequestDigest on subsequent writes

Create an item

POST …/_api/web/lists/getbytitle('Orders')/items
Accept: application/json;odata=verbose
Content-Type: application/json;odata=verbose
X-RequestDigest: <form digest>

{
  "__metadata": { "type": "SP.Data.OrdersListItem" },
  "Title": "INV-1043",
  "Status": "Open",
  "Amount": 1299.5
}

Update (MERGE) and delete

POST …/_api/web/lists/getbytitle('Orders')/items(42)
X-HTTP-Method: MERGE
IF-MATCH: "*"
Content-Type: application/json;odata=verbose
X-RequestDigest: <form digest>

{ "__metadata": { "type": "SP.Data.OrdersListItem" }, "Status": "Closed" }

POST …/_api/web/lists/getbytitle('Orders')/items(42)
X-HTTP-Method: DELETE
IF-MATCH: "*"
X-RequestDigest: <form digest>

Writing the awkward field types

Plain text, number, choice, and date columns take their literal value. The columns that cause support tickets are person, lookup, multi-value, and managed-metadata — each wants a particular shape.

Large lists & the 5,000-item threshold

SharePoint Online enforces a list view threshold of 5,000 items — the most items a single database operation may touch at once. Data above 5,000 is fine and fully stored; what breaks is any *view, sort, or filter* that would have to scan more than 5,000 rows. The fix is column indexing, applied before you cross the line.

Index the columns you filter and sort on

An indexed column lets the server jump straight to matching rows. The golden rule: a view over the threshold works only if its first filter is on an indexed column and that filter returns ≤ 5,000 rows. Index proactively — you can add an index only while the list is under 20,000 items, and a list may hold at most 20 indexes.

Page through a big list

GET …/_api/web/lists/getbytitle('BigList')/items
  ?$select=Title,Status&$filter=Status eq 'Open'   // Status indexed
  &$top=2000
Accept: application/json;odata=nometadata

// response → "odata.nextLink": "…/items?$skiptoken=Paged=TRUE&p_ID=2000&…"
// GET that nextLink for the next page; stop when it is absent.

Throttling: 429, 503 & Retry-After

SharePoint Online throttles to protect the service. Applications get HTTP 429 (Too Many Requests) or 503 (Server Too Busy), each with a Retry-After header telling you how many seconds to wait. Honouring it is the fastest route out — throttled requests still count against your quota, so blind retries dig the hole deeper.

HTTP/1.1 429 Too Many Requests
Retry-After: 31
RateLimit-Limit: 1200
RateLimit-Remaining: 0
RateLimit-Reset: 31

• Honour Retry-After exactly — it is SharePoint telling you the minimum safe delay. The HTTP action’s Default retry policy already respects it.
• Watch the preview RateLimit-Remaining header and slow down *before* you hit zero, rather than waiting for the 429.
• Reduce concurrency and avoid request spikes — cap Apply to each parallelism; high fan-out multiplies throttling.
• Prefer Microsoft Graph over CSOM/REST — it costs fewer resource units, so it throttles later.
• Use delta queries with a token to scan content (1 resource unit) instead of re-reading whole lists.

Decorate your traffic

SharePoint prioritises well-decorated traffic. App-only and custom callers should send a registered App ID and a User-Agent in Microsoft’s naming convention so the service can identify (and favour) you. Undecorated traffic — no App ID, no compliant User-Agent — is throttled first.

User-Agent: ISV|CompanyName|AppName/Version      // building a product for others
User-Agent: NONISV|CompanyName|AppName/Version   // internal line-of-business app

$batch — combine requests

The SharePoint Online REST service (and SharePoint 2016+) supports the OData $batch endpoint: pack many reads — and writes, grouped in change sets — into one HTTP call. Fewer round-trips means less latency and fewer throttling hits, since each sub-request is still costed individually but you pay one network trip.

POST https://contoso.sharepoint.com/sites/Ops/_api/$batch
Content-Type: multipart/mixed; boundary="batch_a1"
X-RequestDigest: <form digest>

--batch_a1
Content-Type: application/http

GET /sites/Ops/_api/web/lists/getbytitle('Orders')/items?$top=1 HTTP/1.1
Accept: application/json;odata=nometadata

--batch_a1--

Permissions: inheritance, roles & unique scopes

Sites, lists, folders, and items are all securable objects. By default each inherits permissions from its parent. To give an object its own permissions you break inheritance, which copies the parent’s assignments down and then lets you add or remove them independently.

The model: role definitions + role assignments

You never grant a *right* to a user directly. A role definition (a.k.a. permission level) is a named bundle of rights; a role assignment binds a principal (user or group) to a role definition on a particular scope. Manage access by adding and removing assignments — and by managing groups, not individuals.

Break inheritance & assign a role over REST

// 1. Break inheritance — keep existing assignments, reset child scopes
POST …/_api/web/lists/getbytitle('Orders')/breakroleinheritance(copyRoleAssignments=true,clearSubscopes=true)
X-RequestDigest: <form digest>

// 2. Look up the role definition id for "Contribute"
GET …/_api/web/roledefinitions/getbyname('Contribute')   // → Id, e.g. 1073741827

// 3. Grant principal 18 (a SharePoint group) Contribute on the list
POST …/_api/web/lists/getbytitle('Orders')/roleassignments/addroleassignment(principalid=18,roledefid=1073741827)
X-RequestDigest: <form digest>

• breakroleinheritance(copyRoleAssignments, clearSubscopes) — pass copyRoleAssignments=false to start from an empty ACL (then add yourself back), clearSubscopes=true to discard any unique permissions already set below.
• resetroleinheritance — re-inherit from the parent and drop the object’s unique permissions.
• Resolve principal Ids from …/_api/web/sitegroups/getbyname('…') or …/_api/web/siteusers; resolve role-definition Ids from …/roledefinitions.
• Remove access with roleassignments/removeroleassignment(principalid=…,roledefid=…).

Document libraries & files

A document library is just a list whose items are files (base template 101). So everything above applies — plus a file/folder REST surface for the binary side. Reach a folder by its server-relative URL, and a file by its server-relative URL or through its parent folder’s Files collection.

GET  …/_api/web/GetFolderByServerRelativeUrl('/sites/Ops/Shared Documents')/Files
GET  …/_api/web/GetFileByServerRelativeUrl('/sites/Ops/Shared Documents/q3.pdf')

POST …/_api/web/GetFolderByServerRelativeUrl('/sites/Ops/Shared Documents')/Files/Add(url='q3.pdf',overwrite=true)
X-RequestDigest: <form digest>
<binary file content in the body>

Set metadata on an uploaded file

A file is also a list item. To set its columns, reach it as a list item via ListItemAllFields and MERGE the fields — exactly like a normal item update.

POST …/_api/web/GetFileByServerRelativeUrl('/sites/Ops/Shared Documents/q3.pdf')/ListItemAllFields
X-HTTP-Method: MERGE
IF-MATCH: "*"
Content-Type: application/json;odata=verbose
X-RequestDigest: <form digest>

{ "__metadata": { "type": "SP.Data.Shared_x0020_DocumentsItem" },
  "Title": "Q3 report", "Department": "Finance" }

Large files — upload in chunks

The simple Files/Add body is capped (~2 MB on the classic Content path; the connector’s Create file can’t chunk at all). For anything over ~10 MB, upload the file as a sequence of slices with one upload session, identified by a GUID: StartUpload on the first chunk, ContinueUpload for the middle, FinishUpload to commit. REST supports files up to 2 GB this way.

// create an empty file first, then:
POST …/GetFileByServerRelativeUrl('/sites/Ops/Shared Documents/big.zip')/StartUpload(uploadId=guid'4f2…')
POST …/GetFileByServerRelativeUrl('…/big.zip')/ContinueUpload(uploadId=guid'4f2…',fileOffset=10485760)
POST …/GetFileByServerRelativeUrl('…/big.zip')/FinishUpload(uploadId=guid'4f2…',fileOffset=20971520)
// each call body = that slice of bytes; the response returns the new offset

Power Automate connector patterns

Most flows never need raw REST — the SharePoint connector covers it. The trick is filtering on the server (not pulling everything and filtering in the flow) and knowing the connector’s sharp edges.

Get items — filter on the server with a Filter Query

Status eq 'Open'
Status eq 'Open' and Amount gt 1000
startswith(Title, 'INV')
Country/Title eq 'New Zealand'                 // lookup column → child field
Start_x0020_Date gt '@{formatDateTime(utcNow(),'yyyy-MM-dd')}'

// Order By:  Modified desc      Top Count: 5000      Pagination: On (Settings)

• Set Top Count and turn on Pagination (Settings) for lists over 5,000 — otherwise a Filter Query that matches nothing in the first 5,000 rows returns empty.
• Filter on an indexed column; use Limit Columns by View so you only pull the fields you need.
• Use a trigger condition on “When an item is created or modified” to stop re-runs (e.g. only fire when a status equals a value) and avoid infinite loops when the flow writes back.
• For person columns, get the Id with the Resolve person action before Create/Update item.
• Cap Apply to each concurrency when each iteration calls SharePoint — it is the easiest way to throttle yourself.

When the connector can’t — Send an HTTP request to SharePoint

This standard (non-premium) action runs any SharePoint REST call as the flow’s connection, handling the digest and auth for you. Use it for permissions, folders, batch, or admin endpoints the connector doesn’t expose. Add Accept: application/json;odata=nometadata to get easily-parsed JSON light back.

// single object
body('Send_an_HTTP_request_to_SharePoint')['Id']

// a collection → loop body(...)['value'], then per row:
items('Apply_to_each')['Title']

Field notes & gotchas

• Index before 5,000, not after. You can only add an index while the list is under 20,000 items, and a view over threshold needs its first filter on an indexed column. Plan indexes the day you create the list.
• Always use internal names in $filter, $select, $orderby, and Filter Query — display names lie after a rename, and spaces are _x0020_.
• Person/lookup columns write by Id, not email or claims. Resolve the Id first; multi-value fields take a { "results": [...] } wrapper.
• Every write is a POST with X-HTTP-Method MERGE/PUT/DELETE — and needs a request digest unless you’re using the connector or OAuth.
• Match your Parse JSON to your Accept header — nometadata gives a flat value array; verbose nests under d.results.
• Unique permissions are a scaling cost. Secure folders or use groups; never put per-item ACLs on a large list.
• Honour Retry-After and decorate your traffic — undecorated, spiky callers get throttled first; prefer Graph and delta queries at volume.