Power Automate & SharePoint Ops Engine - Excel, HubSpot & Power BI
Business Systems & Microsoft 365 Integration Engineer
- Replaced fragmented Excel spreadsheets and manual weekly email summaries with one governed SharePoint/Excel unit inventory
- Built bi-directional Power Automate cloud flows syncing HubSpot CRM deals, visual availability grids, and Outlook digests
- Delivered a live Power BI executive dashboard tracking real-time unit statuses, pipeline velocity, and scheme totals
What I built
Unified fragmented weekly commercial and site operations across Microsoft Excel, SharePoint Online, Outlook, and HubSpot CRM using Microsoft Power Automate and Python/TypeScript data-hygiene scripts. Replaced manual weekly spreadsheet re-keying with a single governed SharePoint/Excel master inventory table, automated two-way HubSpot deal and enquiry syncing via REST webhooks, and built scheduled Power Automate flows that generate visual status reports and executive email digests automatically. Connected the single source of truth to a live Power BI dashboard tracking real-time unit statuses, regional pipeline totals, and conversion velocity.
Tech stack
Power Automate & SharePoint Commercial Operations Engine - Excel, HubSpot & Power BI
Business systems integration, single-source-of-truth data modeling, and automated weekly executive reporting across Microsoft 365 and HubSpot CRM.
Snapshot
| Role | Business Systems & Microsoft 365 Integration Engineer |
| Stack | Microsoft Power Automate · SharePoint Online · Microsoft Excel (Office Scripts) · Power BI · HubSpot CRM · Python / TypeScript |
| Domain | Multi-site property, field operations, and commercial sales tracking |
| Primary Users | Sales executives, regional site managers, commercial directors, and managing partners |
| Outcome | Eliminated 6+ hours/week of duplicate spreadsheet & email re-keying; 100% live unit/asset status consistency across CRM, Excel, and Power BI |
The Problem
Every week, the commercial and sales team updated multiple disconnected spreadsheets, manually cross-checked inbound email enquiries, updated HubSpot deals by hand, and typed the exact same asset/unit statuses into a weekly management summary email and visual availability tracker. Because there was no single source of truth:
- Status drift was constant: A unit marked Reserved in an email thread was still showing Available in the master Excel workbook two days later.
- No live running totals: Leadership had to wait until the end-of-week manual email compilation to see current reservations, pipeline value, and site-by-site progress.
- Fragile manual spreadsheets: Different staff members used inconsistent status labels (
Hold,Reserved,Booked - Pending) and accidentally broke VLOOKUP formulas when inserting rows.
What I Built
1. Single Governed Master Inventory & Status Data Model
Designed a single relational schema in SharePoint Lists / Dataverse paired with a synced Excel Online Master Workbook:
- Enforced strict choice-column validation for statuses (
Available,Enquiry Active,Reserved,Contract Issued,Exchanged,Completed) so free-text typos could no longer corrupt downstream reports. - Wrote Python and TypeScript data-cleaning scripts to normalize 3+ years of messy legacy Excel exports and CSV dumps (resolving duplicate unit IDs, mixed date formats, and unlinked contact records) before seeding the production list.
2. Event-Driven Power Automate Cloud Flows (HubSpot <-> M365 <-> Outlook)
Built modular Microsoft Power Automate flows connecting the entire commercial workflow:
- Inbound Lead & Enquiry Routing: Webhooks from website forms, portal enquiries, and shared Outlook mailboxes automatically parse enquiry details, deduplicate contacts in HubSpot CRM, and link the prospect directly to the relevant scheme/unit code.
- Bi-Directional Status Sync: When a sales executive updates a unit's stage in HubSpot or the governed SharePoint/Excel table, a Power Automate flow validates the transition, logs an immutable audit row (who changed what and when), and updates all connected views within seconds.
- Automated Weekly Digest & Visual Status Pack: A scheduled weekly Power Automate flow aggregates live running totals by site, highlights status changes from the past 7 days, renders an HTML/PDF visual status summary and colour-coded availability matrix, and dispatches the formatted report to leadership automatically.
3. Live Owner & Executive Dashboard in Power BI
Connected Power BI directly to the governed SharePoint/Excel dataset and HubSpot API:
- Built an executive view that non-technical directors and site managers use daily, displaying live running totals (Available vs. Reserved vs. Contracted), weekly sales velocity, lead-source attribution, and scheme-by-scheme cash/revenue forecasts.
What Did Not Work Initially (and How I Fixed It)
- Excel File-Locking & Sync Delays: In the first prototype, triggering Power Automate directly off an Excel workbook stored in OneDrive caused
409 Conflict / File Lockederrors whenever two sales reps had the spreadsheet open in desktop Excel simultaneously. Fix: I moved the primary transactional table to a SharePoint List (which supports row-level concurrent edits and instant webhook triggers) and exposed a live, synced Excel view + Power BI dashboard for staff who preferred working in spreadsheets. - Infinite Webhook Loops Between HubSpot and SharePoint: Two-way syncing initially re-triggered the HubSpot webhook whenever Power Automate wrote back a timestamp. Fix: Added a deterministic
last_sync_hashandsync_originproperty check in the Power Automate trigger condition so programmatic write-backs are ignored while genuine user edits fire immediately.
Deliverables & Handover
- Governed SharePoint & Excel master unit/asset inventory with strict status validation
- 6 production Power Automate cloud flows with dead-letter error handling and Teams/email failure alerts
- Python & TypeScript migration utility for messy legacy Excel/CSV files
- Live Power BI executive and commercial pipeline dashboard
- Plain-English maintenance guide and 1-page quick reference for sales executives and site managers