Overview

Built an automated reporting pipeline that connects JotForm submissions directly to a live Microsoft Excel dashboard via Power Automate webhooks. Every time a form is submitted, the data is instantly appended to a structured spreadsheet, and dynamic Excel charts update automatically — turning raw form responses into a real-time reporting dashboard with zero manual effort.

Problem Being Solved

The business had staff manually copying JotForm submission data into spreadsheets, then rebuilding charts and reports each time. This was time-consuming, error-prone, and meant reports were always out of date. A fully automated pipeline was needed.

Solution Architecture

Webhook & Power Automate Flow

• JotForm webhook configured to fire an HTTP POST on every form submission
• Power Automate HTTP trigger receives the payload and parses the JSON fields
• Flow maps each form field to the correct Excel column
• New row is appended to the Excel table stored in SharePoint/OneDrive
• Timestamp and submission metadata are automatically added alongside form data

Excel Data Layer

• Excel Table (not just a range) used as the data source — enables dynamic expansion
• Structured columns defined for each form field with consistent data types
• Lookup formulae (XLOOKUP, SUMIF, COUNTIF) aggregate data into summary tables
• Conditional formatting applied to highlight anomalies or threshold breaches
• PivotTables built on top of the raw data for flexible slicing and filtering

Charts & Visualisation

• Charts bound to Excel Tables — they extend automatically as new rows are added
• Bar and line charts for trend analysis over time
• Pie/donut charts for categorical breakdowns
• Dashboard sheet with all key visuals consolidated for management reporting
• No manual refresh needed — opening the file shows the latest data

Outcome & Impact

• Eliminated hours of weekly manual data entry across the team
• Reports are always current — updated within seconds of each submission
• Reduced risk of human error in data transcription
• Management can access live dashboard at any time without requesting a report
• Scalable — adding new form fields requires only a column addition and formula update

Skills & Tools Used

• Power Automate — HTTP trigger, JSON parsing, SharePoint/Excel connector
• JotForm — Webhook configuration and payload structure
• Microsoft Excel — Tables, PivotTables, dynamic charts, advanced formulae
• SharePoint / OneDrive — File hosting for real-time Excel access
• JSON data mapping and field transformation in Power Automate