Keep Excel in Sync with a SharePoint List (Beginner-Friendly, No Filter Query)

This post shows you—step by step—how to sync a SharePoint list to an Excel table without using the Excel “Filter Query.” We’ll use Filter array + Condition instead, which is more reliable for beginners.

SharePoint list: tjmax
- Columns: vendorspo (single line of text), summary (single line of text)
Excel: worksheet Clients, table tblClients
- Columns: clientname, details

👉 Grab the ready-to-use Excel file (already formatted as a table):
Download: tjmax_clients.xlsx

What you’ll build

Whenever an item is created/edited in the tjmax list, the flow checks if that item’s vendorspo already exists in the Excel table’s clientname column.

If not found, it adds a new row to Excel with:
- clientname = SharePoint vendorspo
- details = SharePoint summary

You’ll also see an optional scheduled version that loops all SharePoint items daily/weekly.

Prerequisites (5 minutes)

SharePoint list tjmax
- Columns: vendorspo, summary (both Single line of text)
Excel file stored in OneDrive for Business or a SharePoint document library
- Make sure the data is a real Excel Table (not loose cells).
- Table name: tblClients
- Columns: clientname, details
- Use the provided template above or create your own.

✅ Tip: Table/column names are case-sensitive for the connector. Keep them exactly as shown: tblClients, clientname, details.

Flow overview (no Filter Query)

Trigger: SharePoint (item created or modified)
  ↓
Initialize varVendorPO = trigger ‘vendorspo’
  ↓
Compose VendorPO_Trimmed = trim(varVendorPO)
  ↓
Excel: List rows present in a table (no filter; Pagination ON)
  ↓
Filter array → keep rows where toLower(trim(clientname)) = toLower(VendorPO_Trimmed)
  ↓
Condition: length(Filter array) = 0 ?
   ├─ Yes → Excel: Add a row (clientname=VendorPO_Trimmed, details=summary)
   └─ No  → Do nothing

Part 1 — Automated Flow (recommended)

Step 1 — Create the flow

Power Automate → Create → Automated cloud flow
Trigger: SharePoint → When an item is created or modified
Site Address: your site
List Name: tjmax

(Optional) Trigger condition — fire only when vendorspo has a value:
Open the trigger ⋯ → Settings → Trigger conditions and paste:

@not(empty(triggerBody()?['vendorspo']))

Save the trigger.

Step 2 — Normalize the key (vendor PO)

Initialize variable
- Name: varVendorPO
- Type: String
- Value: Dynamic content → vendorspo
Compose — VendorPO_Trimmed

trim(string(variables('varVendorPO')))

Step 3 — Read Excel rows (no Filter Query)

Action: Excel Online (Business) → List rows present in a table
- Location: where the file lives (OneDrive for Business or SharePoint Site)
- Document Library: e.g., Documents
- File: browse to your Excel file
- Table: tblClients

Important: Turn Pagination ON in this action’s Settings, and set Threshold to something safe like 5000.
(Without pagination, Excel usually returns only the first ~256 rows.)

Step 4 — Filter array (match case/space-insensitive)

Action: Filter array

From:

body('List_rows_present_in_a_table')?['value']

Advanced mode:

@equals(
  toLower(trim(item()?['clientname'])),
  toLower(outputs('VendorPO_Trimmed'))
)

This keeps only the Excel rows whose clientname equals the SharePoint vendorspo (after trimming and lowercasing both).

Step 5 — Condition: does it exist in Excel?

Action: Condition
- Left (Expression):
```
length(body('Filter_array'))
```
- Operator: is equal to
- Right: 0

If Yes (not found) → add the row to Excel:

Action: Excel Online (Business) → Add a row into a table
- Location/File/Table: same as above (tblClients)
- clientname:
```
outputs('VendorPO_Trimmed')
```
- details:
```
coalesce(triggerOutputs()?['body/summary'], '')
```

If No → do nothing (or add a small log with a Compose).

Step 6 — Save & Test

Save your flow.
Create or edit an item in tjmax with vendorspo populated.
Confirm a new Excel row appears only when it didn’t already exist.

Reliability Tips (Excel + Flows)

Close the Excel file during runs to avoid locks.
Concurrency: In the flow’s Settings, set Concurrency to 1 (disable parallelism) if many updates could happen at once—this reduces write conflicts.
Big tables: Keep Pagination ON and consider scheduled syncs during off-hours.

Part 2 — Optional: Scheduled Catch-Up Sync

Use this when you want a daily/weekly sweep to backfill anything missed.

Step A — Create a Scheduled flow

Power Automate → Create → Scheduled cloud flow (e.g., daily 7:00 AM).

Step B — Get SharePoint items

Action: SharePoint → Get items
- Site Address / List Name: tjmax
- (Optional) Filter Query (for SharePoint, not Excel):
```
vendorspo ne null
```

Step C — Loop each item

Apply to each → value

Inside the loop:

Compose — VendorPO_Trimmed

trim(string(items('Apply_to_each')?['vendorspo']))

Excel — List rows present in a table (same as Part 1)
- Pagination ON, Threshold e.g. 5000
Filter array (same expression as Part 1)

@equals(
  toLower(trim(item()?['clientname'])),
  toLower(outputs('VendorPO_Trimmed'))
)

Condition: length(body('Filter_array')) = 0
- If Yes → Add a row:
  - clientname = outputs('VendorPO_Trimmed')
  - details = coalesce(items('Apply_to_each')?['summary'], '')

Loop Concurrency: turn OFF (set degree to 1) to avoid Excel write conflicts.

Troubleshooting & FAQs

Q: The table/columns don’t show up in the Excel actions.
A: Ensure your data is a real Excel Table and the file is closed. Verify the Table Name is exactly tblClients and headers are exactly clientname, details.

Q: I still get duplicates.
A: Confirm your filter logic is aligning with real values (no leading/trailing spaces). Keep the trim + toLower approach exactly as shown.

Q: It only reads a small portion of my rows.
A: Turn Pagination ON and set a Threshold (e.g., 5000) in List rows present in a table.

Q: Values contain special characters.
A: We’re not using OData Filter Query, so escaping isn’t needed. The Filter array approach handles raw text well.

Copy-Paste Expressions (Reference)

Trigger condition (optional)

@not(empty(triggerBody()?['vendorspo']))

VendorPO_Trimmed
```
trim(string(variables('varVendorPO')))
```

Filter array — Advanced mode

@equals(
  toLower(trim(item()?['clientname'])),
  toLower(outputs('VendorPO_Trimmed'))
)

Condition — Left
```
length(body('Filter_array'))
```

Excel Add Row — details (Automated)

coalesce(triggerOutputs()?['body/summary'], '')

Excel Add Row — details (Scheduled)

coalesce(items('Apply_to_each')?['summary'], '')

Bonus: Starter Excel File

Use this table-ready workbook to skip setup:

Sheet: Clients
Table: tblClients
Columns: clientname, details

Search This Blog

Power of Automation