ENDEFRESNLTR
⚡ Automated 🏢 Pharmaceutical & Healthcare Distribution 👥 38 Regional Depots · 45,000+ Daily Stock Rows ⏱️ 3 Weeks (Scheduled Cloud Flows + SFTP + OpenXML Edge)

Power Automate Image to PDF

Eliminated Power Automate’s notorious Excel Online "File Locked for 6 Minutes", mandatory Excel Table formatting, and 256-row pagination ceilings by processing raw 45,000-row distributor `.xlsx` files directly in Zetaleap Cloud memory and generating formatted executive `.xlsx` templates.

42 sec
Down from 37 Min Run
0
Excel File Lock Errors
45K+
Daily Rows Processed
AES-256
Encrypted PDF Rebates

⚠️ Legacy Challenge & Bottlenecks

38 regional pharmaceutical depots uploaded raw ERP `.xlsx` stock sheets every hour without defined Excel Tables (`ListObjects`). Native Excel Online connectors refused to read table-free sheets, locked SharePoint files for 6 minutes during concurrent runs, and throttled `Apply to each` loops for 35+ minutes.

  • ✕Excel Online Business connector failing whenever `.xlsx` lacks a named Table object
  • ✕SharePoint `423 Locked` errors blocking subsequent scheduled runs for up to 6 minutes
  • ✕37-minute execution time iterating row-by-row over multi-thousand-line spreadsheets
  • ✕Broken formulas when populating distributor rebate summary templates

✅ Power Platform + Zetaleap Solution

Scheduled Cloud Flows pull raw depot spreadsheets via `/api/sftp/*` and pass Base64 `.xlsx` binaries to `POST /api/automation/xlsx-to-json` (`A1`), extracting thousands of typed rows in `<80ms` with zero SharePoint file locks. Rebate statements are populated via `POST /api/automation/office-template` (`G1` `{{#items}}` row expansion + Tag Doctor validation) and converted to locked PDF statements (`UB-2`).

  • ✓Cut hourly 38-depot stock aggregation runtime from 37 minutes down to 42 seconds (-98%)
  • ✓Zero SharePoint `423 File Locked` incidents across 850+ scheduled monthly flow runs
  • ✓Automated 380 monthly distributor rebate `.xlsx` + `AES-256` password-protected PDF statements
  • ✓Pre-flight Template Tag Doctor (`G1 inspect`) prevented broken `{{Tag}}` deployments

⚡ 5-Stage End-to-End Execution Pipeline

01
Parallel 38-Depot SFTP Fetch GET /api/sftp/* (Concurrency=20)
Fetches raw ERP .xlsx exports across 38 regional depot folders on an hourly cron schedule.
02
Table-Free .xlsx -> Typed JSON POST /api/automation/xlsx-to-json (A1)
Parses sparse multi-sheet OpenXML workbooks in memory without requiring Excel Tables or Graph locks.
03
Dataverse Stock & Variance Sync POST /api/automation/xml-json-query (CF-6)
Filters critical low-stock SKUs (`Items[?qty<=reorderPoint]`) and updates Dataverse inventory.
04
Corporate Excel Template Expansion POST /api/automation/office-template (G1)
Expands `{{#items}}` rows into branded distributor rebate workbooks while preserving numeric types.
05
PDF Render & AES-256 Password Lock POST /api/studio/office-to-pdf + /pdf-security
Converts statement to PDF and locks it with distributor-specific password before email dispatch.

🛡️ Enterprise Security, DLP & ALM Governance

  • ◆Two-layer atomic Zetaleap Cloud quota & burst concurrency guards handling 38 parallel depot branches safely
  • ◆256-bit AES PDF encryption protecting confidential distributor rebate margins
  • ◆Cron expression validation & holiday calendar awareness (`POST /api/edge/cron-schedule`)
  • ◆Live API latency & HTTP status monitoring via `/profile` Developer Console (`GET /api/quota/logs`)

🧩 Technology & Connector Stack

Engineered with governed Microsoft Cloud services and drop-in Zetaleap Swagger 2.0 Custom Connectors:

Power Automate Scheduled FlowsZetaleap Table-Free .xlsx (A1)Zetaleap Excel Template & Doctor (G1)Zetaleap SFTP SSH VaultZetaleap PDF AES-256 Lock (UB-2)Dataverse Batch Upsert
Swagger 2.0 Connectors Scope Similar Architecture →

🚀 Try the Live Zetaleap API Studios Featured in This Case Study

More Enterprise Case Studies

View All Case Studies (8) →