Process diagram
The full nightly cycle, Fundguard to archive. Below: the canonical seven steps everyone shares, then the nine-step technical view mapped onto them. Solid = automated; dashed = a deliberate human checkpoint, not a gap.
1
Reports generated
Fund accounting
2
Renamed & sorted
Intake renames & routes
3
Workbooks updated
Same Excel formulas
4
Reports created
PDF / CSV / ZIP
5
Email drafted
Full draft in shared mailbox
6
Approved
Manual clients only
7
Sent & archived
Graph send, then archive
Canonical seven-step numbering — the shared language between this document and the client summary.
This nine-step technical view maps to those seven canonical steps: 01 Fundguard / Portal Batch → Step 1 (Reports generated); 02 Blob Storage Intake + 03 Parse & Route by Client → Step 2 (Renamed & sorted); 04 Workbook Refresh → Step 3 (Workbooks updated); 05 Report Generation → Step 4 (Reports created); 06 Email Preparation → Step 5 (Email drafted); 07 Approval → Step 6 (Approved); 08 Distribution + 09 Archival → Step 7 (Sent & archived). Renaming happens at the intake/sort step (Step 2) in the new process — today it happens later, at the workbook/macro step.
Automated
Manual — by design
01Fundguard / Portal Batch — external system. Fund accounting closes for the day; the portal produces trial balances, positions, NAV listings, capstock ledgers, and performance data.
02Blob Storage Intake — Power Automate. The new SFTP → Azure Blob delivery (which we build) drops the night's raw files into storage; a native blob trigger (connected via a dedicated service account — Entra ID Integrated, not Managed Identity) fires on each new file and copies it, in original format, into a client-visible RAW data SharePoint library. Detailed design: File-Intake-Client-Organization-Design.html.
03Parse & Route by Client — Power Automate. The filename is split into Report Type / Date / Fund Code; the Clients list resolves the destination folder; the Working copy is renamed to the Trailmark standard convention and copied into Working / {Client} / with metadata (a helper Function is called only if genuinely needed). This is where renaming happens in the new process. Detailed design: File-Intake-Client-Organization-Design.html.
04Workbook Refresh — Office Scripts. Trial Balance, Positions, NAV Listing, and Capstock Ledger are imported into their reporting workbooks in the Working library — formulas stay in Excel by design (see Decisions Log). Maps to canonical Step 3.
05Report Generation — Office Scripts + Function. PDF/CSV exports via Graph content-conversion; ZIP packaging via a small Azure Function (Power Automate has no native zip action). Saved to the Working library.
06Email Preparation — Power Automate + Graph API. Recipients/subject/body from Notification Assignments; shared mailbox reporting@trailmark.us and one shared footer from Email Sender Settings; that night's NAV pulled from the dated NAV Values history into the subject/body. The system builds the full email as a draft via Graph "Create message". The per-client Send Mode flag then decides what happens next. Detailed design: Email-Generator-Automation-Design.html.
07Approval — Operations (Manual clients only). For clients set to Manual (the default), Power Automate raises a "Start and wait for an approval" task showing the email preview and a link to the draft. On Approve, the system sends via Graph; on Reject, it stops and logs. Clients set to Automatic skip this step. Maps to canonical Step 6 (Approved) — no one hand-writes or clicks send in Outlook.
08Distribution — Graph API. The system sends the message via Graph — on approval for Manual clients, or straight away for Automatic ones. Delayed-delivery timing is honored automatically when set.
09Archival — Power Automate. Completed working files are moved into the single Archive library, under year and date folders — Archive / {year} / {reporting date} / (e.g. Archive / 2026 / 2026-07-14) — one folder per night. Rename-on-collision, no silent overwrites.
Run log — every night's run is tracked (in scope)
The workflow appends a row per client per run to a SharePoint
Run Log list — arrived, generated, approved/sent, success or failure, plus a detailed-log field for anything that needs a closer look. Simple list views give the team "arrived tonight", "queued", and "sent" at a glance. This is in scope now. The only dashboard left for the future is client self-service data entry — the private-investment SharePoint form under "Looking ahead" in the client summary.
Brad's actual daily steps 2026-08-23 transcript
| # | What Brad does today (transcript) | In the new process | Covered by |
|---|
| 1 | Archives the prior night's files first thing in the morning — moves everything out of the "today" folder into a dated archive (a macro module) (L142-164) | Automatic: Power Automate moves the prior cycle's files into the Archive library's year/date folders — no macro to run | Step 7 · Archival (09) |
| 2 | Pulls the night's raw files by hand — downloaded from Fundguard or received by email into OneDrive; no SFTP today, credentials re-typed each time (L196-208, L310) | Automatic: the new SFTP → Azure Blob delivery syncs files into a client-visible RAW data SharePoint library — no logging in | Step 2 · File Intake doc |
| 3 | Runs the renaming function to rename downloaded files to Trailmark's standard convention (L144) | Automatic: the intake/sort step renames the Working copy to the standard convention; RAW data keeps the original name | Step 2 · File Intake doc |
| 4 | Updates the workbook — a module pulls the FundGuard data from the source file into the right tab and refreshes it (L438) | Automatic: Office Scripts refreshes the workbook from the SharePoint file; same Excel formulas | Step 3 · Section 02 |
| 5 | Runs data checks — does the total equal the total we expect? Flags any security "not listed" as needing client input (L396, L342-344, L404-410) | Carry the checks forward: totals-match validation + a "not listed" flag surfaced for staff. NOT YET DESIGNED in detail — flagged so it isn't lost | Open item (new) |
| 6 | Client input loop: when there's a trade / a value changed, the client returns a sheet updating column M; it flows in to import into the system (L470-472, L615-617) | IN SCOPE: sending the file out (a nightly email attachment). NOT in this build: the inbound upload of the returned file — STAYS A MANUAL STEP FOR BRAD (he uploads it by hand, the pipeline picks it up next night). Automating the inbound = future feature (SharePoint form / email intake) | Private Investment doc + client doc 'Looking ahead' |
| 7 | Flat-files the report and strips internal columns/macros so the client only sees the clean version (L396, L456) | Automatic: report generation produces the clean client copy (renamed PDF + Excel), internal columns removed | Step 4 · Report Generation (05) |
| 8 | Saves the clean copy into the "today" folder; all attachments linked via file path to one spot (L398, L482, L575) | The per-client Working folder is the "today" folder equivalent — attachments resolved from there | Step 2 · Working library |
| 9 | Saves an email copy that the email generator then uses (L398, L412) | Rolled into the email flow — the draft is built directly from the Working files, no separate save step | Step 5 · Email Generator (06) |
| 10 | Generates the email — all attachments attached, sent from reporting@trailmark.us as one email (L500-502) | Automatic: Graph "Create message" builds the draft from the shared reporting@trailmark.us mailbox with the shared footer | Step 5 · Email Generator (06) |
| 11 | Reviews and sends (manual today; his own signature still on it) (L502) | Per-client Send Mode: Manual (default) raises an approval task, and on Approve the system sends via Graph; Automatic sends without approval. Nobody hand-writes or clicks send in Outlook | Step 6 · Email Generator (06) |
| 12 | Three types of custom reports are built per client (L606) | Preserved — report generation covers the three report types per client | Step 4 · Report Generation (05) |
Every step Brad walks through, mapped to our model so nothing is dropped.
Inside the workbook refresh (diagram step 04 → canonical Step 3)
The load-bearing, most-asked-about step. In one sentence: it swaps the night's raw input data into the workbook — it does not change or re-implement any formula. The existing formulas simply recompute against the new numbers, using Excel's own engine.
A reporting workbook holds two kinds of tabs:
Input tabs — raw data, no logic
Positions, NAV Listing, Trial Balance, Capstock Ledger. These are where the night's Fundguard data gets pasted. Just data.
Formula cells — reference the input tabs
e.g. the USVC subject NAV: XLOOKUP("USVC", 'NAV Listing'!C:C, 'NAV Listing'!M:M). Report figures are formulas pointing at the input tabs. These never change.
What the refresh does, step by step same as today's macro, minus the human
| # | Action | Detail |
|---|
| 1 | Take the night's source file | e.g. eod_nav_listing___all…xlsx from the Working library. |
| 2 | Locate the right worksheet/range in it | The tab/columns that hold the data to import. |
| 3 | Clear the old data in the destination input tab | Yesterday's numbers come out. |
| 4 | Paste in the new data | Positions & Trial Balance: clear-and-replace wholesale. NAV Listing & Capstock Ledger: key-based — match existing rows by identifier, update them, append new ones (preserves history). |
| 5 | Excel recalculates | Every formula pointing at those input tabs recomputes automatically — new NAV, positions, performance. Same engine as today. |
What does NOT change — the trust point
Not a single formula is altered. Nothing is "recomputed in code." The workbook logic is untouched; only the raw input numbers are swapped, and Excel's own calculation engine produces the outputs — exactly as when Brad picks the file from a dialog box today.
Implementation nuance — the cross-file split
A single Office Script operates on ONE workbook and can't open the source file itself. So the macro's "open source file → copy into reporting workbook" becomes two moves:
Power Automate reads the source file (SharePoint/OneDrive connector), then
passes that data as a parameter into a Run script on the destination workbook, which pastes it and lets formulas recalc. Logically identical to the macro, just split into "read the file" (flow) + "paste & recalc" (script).
Chosen approach & residual risks (decision 2026-08-26):
Decision: Office Scripts + Power Automate (Option A)
Chosen over the app-only Azure Function path (Kimi's "Variant A", ClosedXML byte-manipulation). Reason: Office Scripts runs the
real Excel calculation engine — 100% formula fidelity for client-facing financial values — and, operating on the live workbook, it can
refresh charts and pivot tables (e.g. Modern Capital's Sector Pie Chart), which the byte-manipulation path cannot. It runs under a delegated service-account connection (Managed Identity is used everywhere else). The app-only variant stays documented as a considered-and-deferred future option, not the build path.
| Residual risk | Category | Mitigation |
|---|
| Data/time limits: 120s per call, 5 MB per request, 5M cells per range, 1,600 Run-script calls/user/day | Known limit | Batch reads/writes (setValues on whole ranges, chunked). The Azure container shows some raw .xlsx are 5+ MiB — over the 5 MB Office Scripts request limit — so large source data must be read in chunks or passed as a parameter (28.6 MB limit). Spike the largest in-scope report; 19 reports are far under the daily call cap. |
| Cross-file copy is per-report design work (read source in flow → parameter → script writes) | Engineering effort | Not a capability gap. Design per report; keep each parameter under the ~28.6 MB limit. |
| Service-account connection is a single stored credential; Conditional Access / MFA changes can break it | Operational | Dedicated service account, monitored connection, documented recovery. The one stored-credential exception in the architecture (Excel has no app-only option). |
| Confirm setValues actually triggers recalculation in the server-side session | Spike confirm | High confidence (Excel web = automatic calc; calculate() API as backstop). Verify in spike: write data → read a dependent formula cell → confirm it updated. |
| Chart/pivot refresh in a generated PDF (if any in-scope report has one) | Spike confirm | Largely de-risked: the main chart concern was Modern Capital's Sector Pie Chart, which lives in the PORTAL report pack that is now OUT OF SCOPE (transcript, 2026-08-23). For any remaining in-scope report with a chart/pivot, confirm Office Scripts (PivotTable.refresh + range-bound charts) reflects new data in the exported PDF. |
Bottom line: functionally covered with high confidence; every residual risk is engineering/operational, not a "will it work" capability gap. One focused spike on Modern Capital — the hardest report (largest size + a chart + full recalc) — converts "high confidence" to "confirmed" and de-risks the other 18 by extension.
Decisions log
Chronological, across every session. This is the authoritative record — if a document elsewhere contradicts this list, this list wins.
2026-07-16 · Report calculations stay in Excel
Formulas (NAV, positions, performance) stay in Excel; Office Scripts runs the same calculation engine server-side instead of rewriting formulas as code. Lower risk since these figures go out to clients as official fund values. Rewriting into code stays a possible later phase, not part of this build. Scope: this is about the workbooks that actually calculate (Trial Balance, Positions, NAV Listing, Capstock Ledger, PortalRpts_MC) — it does not apply to Email Generator, which never calculated anything itself. It only reads an already-computed NAV value and attaches pre-generated files unmodified (verified 2026-08-25).
2026-07-16 · Centralized config via SharePoint list, owned by Operations
Notification Assignments list (not Dataverse, not a workbook) — one row per report, editable by Operations without a developer. Established the pattern later reused for the Clients list.
2026-07-16 · Blob Storage ingestion — new SFTP feed we build
Nightly source reports will be delivered through a new SFTP → Azure Blob feed we set up as part of this work (blob account with hierarchical namespace + SFTP enabled, container fundguardstorage). Today there is no SFTP — files are pulled by hand from Fundguard or by email. The container stays flat at the root — every filename already encodes its own date.
2026-08-25 · Power Automate stays as orchestrator (not Logic Apps)
Briefly considered Logic Apps + Azure Functions in place of Power Automate + SharePoint lists. Client corrected this: Power Automate stays because a different team builds against it, even though the two share the same underlying engine.
2026-08-25 · SharePoint is the entire client-facing interface
Every config value and every file lives in SharePoint — nothing hardcoded in a Function or Flow. This is the client's explicit requirement for how Operations interacts with the whole system.
2026-08-25 · One shared footer, held centrally (shared mailbox already in use)
The shared mailbox reporting@trailmark.us is already used today — that isn't the change. The change is one shared footer, held centrally in a new Email Sender Settings list, replacing whoever's personal Outlook signature currently lands on the message. (An automated flow can't pull a person's personal signature anyway — that only works inside a live desktop session.) The earlier framing around per-person mailboxes/signatures is dropped.
2026-08-25 · Body Template is a Rich Text SharePoint column, not an attached Word file
Originally proposed as an attached .docx for familiar editing. Corrected: SharePoint's Multiple-Lines-of-Text (Rich Text) column already stores HTML internally, so Power Automate reads it straight into Graph's HTMLBody with no docx-to-HTML conversion step — simpler and removes a real technical risk.
2026-08-25 · New Clients list added, separate from Notification Assignments
File routing (which folder a file belongs in) and email configuration (who gets emailed) are different concerns, even though both key off the same Client identity. Kept as two lists rather than overloading one.
2026-08-25 · Design documentation branded to Workplace Engineering
All design docs carry the firm's actual brand (colors, logo) rather than a generic placeholder palette, since these are delivered to Trailmark under the Workplace Engineering name.
2026-08-25 · NAV lookup moved to Azure Function, not a native Power Automate connector action
Power Automate's native Excel Online 'Get a row'/'Run script' actions would have worked, but hard-wire the flow to today's Excel shape (Table structure, worksheet layout). Client wants a clean abstraction seam here: Power Automate calls a small GetFundNav(ticker) Function and doesn't know or care how it gets the value. Explicit reasoning: the client treats this Excel workbook as a client-communication application they intend to move away from over time, and wants new logic built so a future migration off Excel touches only the Function's internals, not the orchestration layer. Applies as a general principle for future macro migrations, not just this one lookup.
Superseded 2026-08-26 — see next entry.
2026-08-26 · NAV lookup Function reads a SharePoint list, not the Excel workbook — hard platform constraint, not a preference
Verified against official Microsoft Graph API reference pages (worksheet-range, range-update, table-range, and others): every Excel Workbook API endpoint explicitly lists Application permissions (Managed Identity/app-only) as 'Not supported' — only Delegated (signed-in user) access works. A Function using Managed Identity literally cannot call Graph's Excel range/table API, full stop, regardless of preference. SharePoint list endpoints don't have this restriction. Resolution: a new 'NAV Values' SharePoint list — a dated share-price history, a new row per fund each night (Fund / As-of date / Share price), appended from the end-of-day file — is populated by Step 4's workbook refresh, and GetFundNav(ticker) reads that list on Managed Identity instead of touching Excel directly. That night's value is what drops into the subject/body; the list is never overwritten, so a full price history builds up. Still open: exact mechanism for Step 4 to write into that list (Office Script writes it directly at the end of its run, vs. a separate small Power Automate step) — not yet decided.
| Field | Value |
|---|
| Excel Workbook API — Application permission | Not supported (confirmed on every relevant Graph endpoint) |
| Excel Workbook API — Delegated permission | Files.ReadWrite (signed-in user only) |
| SharePoint list API — Application permission | Supported |
| Resolution | NAV Values SharePoint list (dated history), Function reads that instead of the workbook |
2026-08-25 · Power Automate confirmed over Logic Apps; licensing verified as immaterial
Briefly reconsidered after realizing the Azure Blob Storage connector (required for Step 2's ingestion trigger no matter what) is Premium-tier in Power Automate. Per Microsoft's own licensing rules, automated/scheduled flows — all of ours — only require the flow owner to hold the Premium license, not every team member, and not the pricier Process license (a capacity license tied to a specific flow or machine rather than a person — built for unattended desktop RPA or volume beyond 40,000 actions/day; neither applies here). Client confirmed: stay on Power Automate. One license covers an unlimited number of flows owned by that one user — the 40,000/day limit is a combined total across all of that user's flows, not a per-flow cap. With ~19-24 report files/night and modest actions per run, the whole pipeline (Blob intake, per-report email flows, archival) sits at low hundreds of actions/day, far under the limit, even split across many flows for failure isolation. Because Premium licenses attach to a person, not a flow, ownership goes to a dedicated service account (e.g. automation@trailmark.us) rather than a named employee — so the pipeline doesn't break if that person leaves or their license lapses. If volume ever grows toward the limit, the fix is a Process license stacked on the specific high-volume flow, not more user licenses. Licensing cost is immaterial and is covered in a separate document.
| Field | Value |
|---|
| License chosen | Power Automate Premium (user license) |
| Quantity | 1 — flow owner only |
| License holder | Dedicated service account, not a named employee |
| Flow count | Unlimited flows per owner |
| Shared limit | 40,000 actions/day, combined across all that owner's flows |
| Not chosen | Process license — capacity-based, not needed at our volume |
| Cost | Covered in a separate document |
2026-08-26 · Workbook Refresh (Step 4): Office Scripts chosen, app-only Function path deferred
An independent review (Kimi) confirmed a cleaner insight: the app-only limitation is only on Graph's Workbook object-model API (/workbook/*), NOT on the file-content endpoints (GET/PUT /content, ?format=pdf) which DO support Application permissions — so an Azure Function could write data by manipulating the .xlsx bytes (ClosedXML/OpenXML) fully app-only. Valid, and it matches the 'no scripting / minimal Power Automate / Managed Identity everywhere' preference. BUT its hard problem is formula recalculation: byte manipulation has no Excel engine — ClosedXML's calc engine covers only a subset of functions (unsupported → #NAME?), a real risk for client-facing financial values, and it also can't refresh charts/pivots (Modern Capital's Sector Pie Chart would go stale). Decision: stay with Office Scripts + Power Automate — it runs the real Excel engine (100% formula fidelity) and refreshes charts/pivots. Accepts one stored service-account connection (Excel has no app-only option) as the single credential exception. Kimi's Variant A stays documented as a considered-and-deferred future option; revisit only if the service-account dependency becomes a problem AND a formula-inventory PoC proves ClosedXML covers every function used. Residual risks are all engineering/operational (see Section 02) — gated by one spike on Modern Capital.
| Field | Value |
|---|
| Chosen | Office Scripts via Power Automate (real Excel engine) |
| Deferred | App-only Azure Function + ClosedXML (Kimi Variant A) |
| Why not deferred path | Partial calc-engine coverage + no chart/pivot refresh = risk for financial values |
| Validation gate | One end-to-end spike on the largest in-scope report (Modern Capital portal is now out of scope) |
2026-08-26 · RAW data visible in SharePoint (Matt's suggestion)
The raw Fundguard files (as they land in Azure Blob via the new SFTP feed) should be surfaced in a client-visible SharePoint folder, e.g. a 'RAW data' library, in their original raw format. This is the Inbound library, reframed as a client-facing 'RAW data' folder so Operations can see exactly what arrived, unprocessed, alongside the per-client Working folders. Real raw filenames are more complex than the reportdump sample (e.g. gryphonprod1.holdings.idx_funds.unknown_fund_view.from_DATE.to_DATE.created_TIMESTAMP.ext) with multiple files per fund per day — the parser must pick the correct/newest version. Some raw .xlsx are 5+ MiB, relevant to the Office Scripts 5 MB request limit (Section 02).
2026-08-26 · Per-client Send Mode (Automatic vs Manual) — a config flag, not a blanket rule
Refines the earlier 'never auto-send' decision: whether a report's email goes out AUTOMATICALLY or waits for MANUAL approval is now a per-client field on the SharePoint list. Default = Manual (matches today's control model, the human checkpoint stays). Automatic is available for any client that opts in. The flow always builds the full draft via Graph "Create message" first, then reads the flag: Manual → raise a "Start and wait for an approval" task (preview + link to the draft), and on Approve the system sends via Graph, on Reject it stops and logs; Automatic → send without approval. No one hand-writes or clicks send in Outlook. Preserves human control by default while letting Operations flip a single client to hands-off via one list edit.
2026-08-26 · All configuration must be simple and client-manageable, out-of-the-box
Firm principle (client): every configuration surface — recipients, templates, sender, send mode, client roster, folders — must be editable by Operations using only out-of-the-box SharePoint lists and document libraries, with no custom code or developer involvement to change a setting. This constrains design choices: prefer plain list columns and folders over anything requiring a deployment to reconfigure. Reinforces 'SharePoint is the entire interface.'
Verified against source
Claims in the design were checked against the actual sample data and official Microsoft documentation, not assumed.
2026-08-23 Brad Jones transcript — THE SOURCE OF TRUTH (client-designated)
Brad walks through the real current process; his words override older analysis where they conflict. Key facts established: (1) Fundguard data reaches the pipeline via SFTP into the SFTP-enabled Azure Blob account we set up; today there is no SFTP — Brad fetches files by hand, and the migration introduces the automatic SFTP → SharePoint sync. (2) A SharePoint RUN LOG with status views ("arrived tonight / queued / sent") is IN SCOPE in this build; the only dashboard deferred to the future is client self-service data entry — the private-investment SharePoint form. (3) Sender is 'reporting@trailmark.us' — one generic mailbox already used today; Brad's own signature is still on it, which the shared-footer-from-a-list removes. (4) Modern Capital PORTAL reports are OUT OF SCOPE — 'we're not having you guys do anymore... move away from those ourselves.' (5) The private-investment loop is CONFIRMED: clients return a sheet updating column M (trades yes/no, latest values); today email+Excel, future a SharePoint form. (6) 'Not listed' trigger = a data-completeness check needing client input. See the transcript source-of-truth memory for line refs.
reportdump/ sample export
Filenames follow a parseable pattern (fund_code.abor.report_type...from_date.to_date.created_timestamp.ext), so intake can read metadata directly. The originals stay unrenamed in the RAW data library; the Working copy is then renamed to Trailmark's standard convention — renaming IS a real automated step, not something intake skips. Note the reportdump sample understated real filename complexity: production raw names are longer and there can be multiple files per fund per night (see the RAW-data decision, Section 04), so the parser must also pick the correct/newest version.
Microsoft Learn — Azure Blob Storage connector docs
The "When a blob is added or modified (properties only)" trigger is a real, documented capability of the managed connector Power Automate shares with Consumption Logic Apps.
Correction (2026-08-28): Managed Identity auth for this connector is a
Logic Apps feature — it is NOT available to Power Automate cloud flows, which authenticate via Access Key, Microsoft Entra ID Integrated (delegated service account), or a service principal. Also confirmed: the managed trigger ignores blobs that existed before it was configured — cutover needs a one-time manual backfill.
Power Automate licensing — re-verified 2026-08-25
Power Automate Premium is the tier that includes the Azure Blob Storage connector (explicitly a Premium-tier connector). Automated/scheduled flows only require the flow owner to hold the Premium license, not every team member; the Power Automate Process (bot) license offers higher capacity or desktop RPA and is not needed at our volume. Specific pricing is covered in a separate document.
Microsoft Graph API reference — Excel Workbook API, verified 2026-08-26 (nuanced — corrected from an earlier overstatement)
OFFICIAL: every Excel Workbook API reference page (range-get, range-update, worksheet-range/update, table-range) lists Application (app-only) permissions as "Not supported" — officially only Delegated (Files.ReadWrite, signed-in user) works, and the /me/ and /users/{id}/ endpoints definitely reject app-only. NUANCE (community, unofficial): developers report app-only DOES work when targeting the drive/site-scoped endpoints instead — /drives/{drive-id}/items/{item-id}/workbook/... or /sites/{site-id}/drive/... — with application permissions like Sites.Selected or Files.ReadWrite.All (grantable to a Function's Managed Identity, which would mean zero stored secrets). The developers who got it working explicitly flagged it as possibly undocumented/a bug that could be removed. So the honest position: an app-only Azure Function path is PLAUSIBLE but officially unsupported — a real risk for client-facing NAV values, since a Microsoft change could break it silently. Extra open risk: the workbook/application/calculate endpoint (forcing formula recalculation) is reported as delegated-only / 'works unexpectedly' app-only — so even if writing ranges works app-only, triggering recalc might not. This does NOT change the NAV-lookup design (that reads a SharePoint list, cleanly app-only-supported, no ambiguity). For the Step 4 workbook REFRESH, Office Scripts + Power Automate remains the settled decision; this app-only path stays a documented, DEFERRED future option — to be revisited only if the delegated service-account dependency becomes a problem — not a live pending spike. Earlier doc versions said a Managed Identity Function 'literally cannot' touch the workbook 'full stop' — that was too absolute; corrected here.
Office Scripts + Power Automate — the workbook-refresh mechanism, verified 2026-08-26 (this is the load-bearing piece of Phase 2)
Confirmed the Step 4 workbook refresh is achievable and how it authenticates. (1) The Excel Online (Business) connector's 'Run script' action requires signing in to a Microsoft 365 account — it runs under a DELEGATED (signed-in service account) connection, NOT app-only. That's exactly why it can touch Excel where a Managed Identity Function can't: delegated Files.ReadWrite works, app-only doesn't. Answers the open question of why Office Scripts sidesteps the Excel-API app-only limitation. (2) GENERALLY AVAILABLE for business licenses (only 'preview' for personal/family) — not a preview feature for Trailmark. It's the officially recommended replacement for the now-disabled in-Excel script scheduling. CONSTRAINTS that shape the design (not blockers): a script gets ONE workbook and can't fetch external data, so the cross-file copy today's macro does (open source file → copy into reporting workbook) must be re-expressed as: Power Automate reads source data, passes it as a parameter into a Run script on the destination workbook. Data/timeout limits: 5 MB per workbook request, 5 million cells per range, ~28.6 MB per parameter, 120-second per-operation timeout, 1,600 Run-script calls per user per day; large data must be written in batches. Workbook must be standard .xlsx (not ISO-strict). IMPORTANT SECURITY EXCEPTION: this connector requires a stored signed-in (service account) connection — the ONE place in the whole architecture that keeps a stored credential rather than Managed Identity, because Excel has no app-only option. Modern Capital's large portal workbook (~2.2 MB) should be spike-tested against the 5M-cell / 120s limits before committing.