Flows with Excel and SharePoint

💡 Key concepts

• Excel Online (Business): Works only with Tables. Key actions: List rows present in a table, Add a row into a table, Update a row (by key column), Run script (Office Scripts). Turn on pagination in settings, because List rows returns a limited number of rows (256) by default.
• Excel dates come back as serial numbers unless you set DateTime Format = ISO 8601 in List rows. Otherwise convert: addDays('1899-12-30', int(item()?['ForecastDate']), 'yyyy-MM-dd').
• SharePoint lists often work better than Excel files as a multi-user "database" for trackers (action items, milestone updates, data calls): versioning, permissions, views, and a reliable trigger.
  • Triggers: When an item is created, When an item is created or modified, When a file is created in a folder.
  • Filter Query (OData) in Get items: Status eq 'Open' and DueDate lt '2026-11-01'.
  • Trigger conditions (in trigger settings) keep flows from running unnecessarily, e.g. @equals(triggerOutputs()?['body/Status/Value'], 'Late').
• Concurrency control on Apply to each, which affects speed and write conflicts.
• File locks: Excel files open in the desktop app can block flow writes. That's another reason to use lists for multi-user data.

🛠 Hands-on exercise: Milestone Slip Notifier

Data: SharePoint list Milestone Tracker with columns: Title (MilestoneName), ProgramID (text), BaselineDate (date), ForecastDate (date), ActualDate (date), Owner (person), Status (choice: On Track / At Risk / Late / Complete), SlipDays (number), LastNotified (date).

1. Create the list and import ~30 rows from milestones.
2. Flow: When an item is created or modified → compute slip days (div(sub(ticks(ForecastDate), ticks(BaselineDate)), 864000000000)) → update the SlipDays column.
3. Condition: if SlipDays > 30 and the forecast date changed, email the Owner and post in a Teams channel with a formatted card.
4. Add a trigger condition so the flow ignores its own SlipDays update. (Infinite loops are a classic beginner mistake. Explain why it happens.)

📦 Mini-project: Monthly Data Call Collector

A flow triggered when a file is created in the SharePoint status_drops library that (1) checks that the file name matches PRG-###_Status_YYYY-MM.xlsx, (2) logs the submission (ProgramID, month, submitter, timestamp) to a Data Call Log list, (3) replies to the submitter with a confirmation, and (4) on the 6th business day, emails a summary of programs that haven't submitted. (Do this in a second scheduled flow comparing the Programs table with the log.)