#39 feat(excel): =K.EPM() Office.js Custom Functions (alongside VBA)

[Anonymous]

Originally created by: pyy3

Summary

Adds installable Office.js Custom Functions under the K namespace — a cross-platform (Windows / Mac / Excel-on-the-web), macro-free path to live =EPM() data. They complement, not replace the VBA in excel/OpenEPM.bas, which is kept intact for demos.

=EPM() (VBA, bare) and =K.EPM() (add-in, namespaced) never collide, so a single workbook can use both.

Custom Functions are always namespaced (=NS.NAME); a dotless =EPM() from an add-in is impossible without an XLL/Excel-DNA build — the VBA already provides that.

Functions (parity with OpenEPM.bas)

Function	Behaviour
=K.EPM(entity, year, period, account, [measure], [scenario], [costCenter], [department], [scenarioId])	Value lookup; defaults period_net_amount / actuals. period accepts 1-12, Q1-Q4, H1-H2, FY
=K.EPM_BUDGET / EPM_VARIANCE / EPM_DEBIT / EPM_CREDIT	Convenience wrappers
=K.EPMSAVE(amount, entity, year, period, account, scenarioId, layer, [costCenter], [department])	Budget write-back on recalc

Unlike the VBA (manual Ctrl+Shift+R refresh), Custom Functions fetch on their own — every =K.EPM* cell in a recalc pass is debounced into one konsol.api.epm_batch POST (chunked at the backend's MAX_BATCH=2000). EPMSAVE posts to konsol.api.budget_cell_save. Same-origin, cookie auth, no CORS.

Changes

• new excel-addin/src/functions.json — Custom Functions metadata
• new excel-addin/src/functions.js — implementation + debounced batcher + per-cell error mapping
• new excel-addin/src/functions.html — runtime page
• excel-addin/manifest.xml — CustomFunctions extension point, namespace K, runtime URLs
• excel-addin/package.json — deploy copies the new files
• docs/prd/PRD-EXCEL-CUSTOM-FUNCTIONS.md — spec
• fix excel-addin/src/taskpane.js — batch refresh sent measure:"amount"/scenario:"actual"; corrected to backend defaults period_net_amount/actuals

Verification

• manifest.xml well-formed; functions.json valid JSON.
• API contract pinned against konsol/api.py (epm_batch, budget_cell_save).
• ⚠️ Not runtime-verified — needs a live konsol site + an Office.js host (desktop Excel or web) to sideload manifest.xml and smoke-test recalc + write-back. No Office.js runtime in the build env (same gap as [#35]).

🤖 Generated with Claude Code

[Anonymous]

Originally posted by: pyy3

🤖 Code review (high-effort, multi-angle + verified)

Reviewed the diff across 7 finder angles with targeted verification (incl. Microsoft Office docs). Ranked by severity.

🔴 1. Cookie auth will fail for =K.EPM() — no shared runtime declared

With no <Runtimes>/shared runtime, custom functions run in the JavaScript-only runtime, which does not support cookies. So fetch(..., {credentials:"include"}) cannot carry the Frappe session cookie — every =K.EPM cell returns 401 ("Not logged in") even after the user signs in via the task pane.
Fix: declare a shared runtime and/or follow custom-functions auth guidance (token via OfficeRuntime.storage) instead of relying on the cookie. This blocks the feature on Windows desktop.

🔴 2. =K.EPMSAVE — write-storm, silent value loss, unhandled reject (functions.js epmSave)

Diverges from the VBA EPMSAVE in three ways:

• No dedup cache (VBA has pSaveCache) → re-POSTs budget_cell_save on every recalc → duplicate writes + backend hammering.
• Rejects on failure → replaces the user's typed number with #VALUE! (VBA always returns amount).
• No .catch → an offline/network error surfaces a raw TypeError: Failed to fetch rather than a friendly error.

🟠 3. One bad year poisons the whole batch (functions.js makeReq + backend epm_batch)

Non-numeric year → Number() → NaN → JSON.stringify → "year":null. Backend does int(req.get("year",0)) → int(None) outside the per-cell try/except → HTTP 500 → sendChunk's .catch rejects every cell in the chunk. One typo'd cell breaks all =K.EPM in the batch. (period is guarded; year isn't.) Fix on either side: guard NaN in makeReq, and/or wrap the int(year) in the backend per-cell try.

🟡 4. Microtask debounce may not coalesce a large recalc into one POST

Promise.resolve().then(flush) only batches cells enqueued in a single synchronous turn. If Office dispatches invocations across event-loop turns, you get multiple POSTs — undercutting the "single round-trip per recalc" claim. A setTimeout(flush, 0) spans turns more reliably.

🟡 5. Falsy-zero drops dimensions (makeReq)

A cost center / scenarioId literally "0" (or numeric 0) is omitted by if (costCenter) → query silently aggregates across all cost centers, returning a wrong larger value with no error.

🟢 6. Cleanup

• The 401/403 + !res.ok error block is duplicated between sendChunk and epmSave — extract a postJson() helper.
• functions.js uses bare relative paths while taskpane.js routes through const FRAPPE_URL. Pick one convention.

---

Checked and dropped: "won't register on web/Mac" (the non-shared form does register on all three platforms — the cookie limitation above is the real issue); "backend returns misaligned values[]" (epm_batch pads to full length, so positional alignment holds).

Not runtime-verified — none of this was exercised on a live Office.js host; [#1] in particular should be confirmed by sideloading on Excel desktop.

🤖 Generated with Claude Code

[Anonymous]

Originally posted by: pyy3

✅ Review fixes pushed (14e2a74)

• #1 (cookie auth) — manifest now declares a shared runtime (<Runtimes lifetime="long"> + SharedRuntime requirement, CustomFunctions under <AllFormFactors>, Page → shared task-pane page). taskpane.html loads functions.js so the functions register in that runtime and share the session cookie. Removed the standalone functions.html.
• #2 (EPMSAVE) — added a per-cell save cache (no more re-POST on every recalc) and best-effort write returning the typed amount on failure instead of #VALUE! (VBA parity); validates amount/year/period are finite first.
• #3 (batch poisoning) — enqueue() rejects a non-numeric year client-side so one bad cell no longer 500s the whole batch. Backend int(year)-outside-try hardening noted as a follow-up konsol PR in the PRD.
• Cleanup — extracted a shared postJson() helper (dedupes the 401/403 + !res.ok blocks) and added a FRAPPE_URL constant to match taskpane.js.

Manifest re-validated well-formed; functions.js passes node --check. Still not runtime-verified — the shared-runtime cookie flow in particular should be smoke-tested on a live Office.js host.

🤖 Generated with Claude Code

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed