Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 38 ยท Connectors and Data

SharePoint Lists and Excel Tables in Power Automate

Read and write SharePoint list items and Excel Online tables from flows: Get items, filter queries, Create item, Update item, column types, Excel limits and patterns.

SharePoint lists and Excel files are where most small business data lives, so they are where most flows begin. Both have connectors that are included with Microsoft 365. They behave differently, and each has traps that are worth knowing before you build.

SharePoint list actions

The key actions of the SharePoint connector:

Action What it does
Get items Returns rows, with Filter Query, Order By, Top Count and selected columns
Get item Returns one row by ID
Create item Adds a row
Update item Changes columns of a row by ID
Delete item Removes a row
Create file / Get file content / Get file properties Document library work
Send an HTTP request to SharePoint Calls the REST API for anything missing

In each action choose the Site Address and the List Name from the drop-downs. If the list is not offered, choose Enter custom value and paste the site URL and the list's GUID or name.

Get items settings

  • Filter Query: OData such as Status eq 'Open' and DueDate le '2025-03-31'. Use the internal column name (spaces become _x0020_; the internal name is fixed when the column is created, even if you rename the title later).
  • Order By: Created desc.
  • Top Count: number of rows to return, up to 5,000.
  • Limit Columns by View and Select parameters reduce the data returned.
  • Pagination: in Settings turn it on and set a threshold (for example 5000) to read beyond 100 rows, which is the default page size.

Filtering on a column that is not indexed in a large list (over 5,000 items) fails with a list view threshold error. Index the columns you filter on.

Column types and their quirks

Column type How it comes back Tip
Single line, number, date Plain values Dates are UTC text
Choice Object with Value Use Status/Value in expressions
Person or Group Object with Email, DisplayName, Claims Use Person/Email
Lookup Object with Id, Value Filter with Field/Id eq 3
Yes/No true or false Compare with equals(..., true)
Managed metadata Object with a term label Filter is limited
Attachments Separate actions Use Get attachments

When creating a Person column value, pass the email in the Claims field using an expression such as triggerBody()?['email'], or pick from the drop-down.

Python Stand-in: handle a choice column object and a person column

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Create item and Update item

Both actions show the list's columns as fields. Required columns have a red asterisk. Update item needs the Id of the item. A common pattern in an approval flow is to update a status column at the end.

TEXT
Update item
   Id:       triggerOutputs()?['body/ID']
   Status Value: Approved
   Approver: items('Apply_to_each')?['responder']?['email']

For a column you do not want to change, leave the field empty rather than typing a blank value, or you may erase the stored value.

Excel Online (Business)

The Excel Online (Business) connector works with tables inside .xlsx files stored in OneDrive or SharePoint. You must format the data as a table first (Insert > Table), because the connector cannot read a loose range.

Main actions:

  • List rows present in a table
  • Add a row into a table
  • Update a row (needs a key column and its value)
  • Delete a row
  • Get tables, Create table
  • Run script to call an Office Script for bulk changes

Excel limits and tips:

  • The default page returns 256 rows; enable pagination for more.
  • The file must not be open for editing in the same moment by a desktop program, or you may see locks.
  • Update a row matches by Key Column, so that column's values must be unique.
  • Do not use Excel as a database for busy flows. Concurrent writes can fail or collide. Use SharePoint lists or Dataverse for anything with several writers.
  • Dates in Excel are serial numbers. Convert with addDays('1899-12-30', int(item()?['Date'])).
Python Stand-in: Excel serial date to a real date

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Patterns worth copying

  • New item notifications: trigger When an item is created, then post to Teams with the Title and a link to the item.
  • Status workflow: trigger When an item is modified with a trigger condition on Status, then run the matching branch.
  • Copy to a log: after the main work, Create item in an audit list with who, what and when.
  • Daily digest: scheduled flow, Get items with a filter query, Select, Create HTML table, send mail (see Arrays, objects and JSON).

A stand-in for the digest filter:

Python Stand-in: due-soon filter for a digest

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Common mistakes

  • Filtering on the display name of a column instead of the internal name.
  • Forgetting pagination, so only the first 100 rows are processed.
  • Reading Status when it is an object; use Status/Value.
  • Triggering on modified and updating the same item from the flow (see Automated flows and triggers).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How many items does Get items return by default without pagination?

    The default page is 100 rows; turn on pagination and set a threshold for more.
  2. 2. How do you read a Choice column's text in an expression?

    Choice columns return an object whose Value property holds the text.
  3. 3. What must be true of an Excel file for the connector to read it?

    The Excel Online connector works with named tables, not loose ranges.
  4. 4. Why filter on indexed columns in large lists?

    Filtering on non-indexed columns of lists over 5,000 items can fail with a threshold error.

Summary

  • SharePoint actions read and write list items; use internal column names, indexed filters and pagination.
  • Choice, person and lookup columns come back as objects.
  • Excel needs real tables, has paging limits and is poor for concurrent writes.
  • Common patterns: notifications, status workflows, audit logs and digests.

Next: Dataverse tables and OData queries