An implementation plan for the build team: the nightly reporting automation expressed as concrete Power Automate flows — each flow's steps as a diagram, the exact actions and connectors it uses, and how it authenticates. Technical claims verified against Microsoft Learn (2026-08-28).
Power Automate and Azure resources authenticate differently, and the two are easy to conflate — so this comes first.
Steps 1–2. Trigger: a file lands in Azure Blob from the SFTP delivery. Outcome: the original sits in RAW data; a renamed copy sits in Working / {client}.
| Step | Action / connector | Notes |
|---|---|---|
| Trigger | Azure Blob — When a blob is added or modified (properties only) | Root folder only; ignores pre-existing blobs (one-time cutover backfill) |
| Get file | Azure Blob — Get blob content | By path from the trigger |
| Keep original | SharePoint — Create file | RAW data library, original name + format |
| Resolve client | SharePoint — Get items | Clients list, match by identifier prefix → target folder |
| Rename & sort | SharePoint — Create file | Working / {client}, renamed to the standard convention |
| Log | SharePoint — Create item | Run Log: arrived / sorted |
Auth: dedicated service account (Microsoft Entra ID Integrated) for Blob and SharePoint — not Managed Identity.
Steps 3–4. The night's data refreshes the reporting workbook using the real Excel engine, the fund's NAV is captured to a dated list, and the clean client report is produced.
| Step | Action / connector | Notes |
|---|---|---|
| Refresh workbook | Excel Online (Business) — Run script | Office Scripts drives the real Excel engine. Limits: 120s sync timeout, 5MB/request, 28.6MB parameter, 1,600 runs/user/day |
| Capture NAV | SharePoint — Create item | NAV Values: new dated row (Fund, As-of date, Share price) |
| Produce report | Graph — content conversion (?format=pdf) / Office Script export | Internal columns removed; clean PDF + Excel |
| Save report | SharePoint — Create file | Working / {client} |
| Log | SharePoint — Create item | Run Log: generated |
Auth: the Run script step runs under a dedicated delegated service account (the one credentialed step); everything else via the service-account connections.
Steps 5–7. The system builds the full email as a draft, holds it for approval (or sends automatically), sends on approval, then archives the night's files and writes the run log.
| Step | Action / connector | Notes |
|---|---|---|
| Get config | SharePoint — Get items | Notification Assignments, Email Sender Settings, NAV Values |
| Build draft + attach | Azure Function (Graph SDK) | Create message in reporting@trailmark.us + attach report; ≤3MB single POST, 3–150MB upload session (LargeFileUploadTask); returns id + webLink |
| Branch | Condition | Send Mode: Manual / Automatic |
| Approval (Manual) | Approvals — Start and wait for an approval | Preview + link to the draft; Approve / Reject |
| Send | Microsoft Graph | POST /users/reporting@trailmark.us/messages/{id}/send |
| Reject cleanup | Microsoft Graph | Delete the draft message |
| Archive | SharePoint — Move / Copy file | Working / {client} → Archive / {year} / {date} |
| Run Log | SharePoint — Create item | approved/sent, success/failure, detailed log |
Auth: the draft build + send run in the Azure Function on Managed Identity (Mail.ReadWrite + Mail.Send, App RBAC-scoped to the one mailbox); orchestration, approval and Run Log run in Power Automate under the service account.
Two pieces run as Azure resources rather than Power Automate cloud flows — documented here so the whole picture sits in one place.
Every flow also appends to the Run Log SharePoint list (one row per client per run: arrived / generated / approved-or-sent / success-or-failure + detailed log), which the status views and dashboard read from.