Advanced Multi-Sheet Healthcare Data Pipeline

Advanced Multi-Sheet Healthcare Data Pipeline - monday.com & Make.com

This scenario implements a sophisticated, fully automated data ingestion pipeline designed for healthcare operations environments where patient records must be kept current across multiple systems without manual intervention.

Trigger & Initiation: The scenario activates instantly via a monday.com webhook, firing the moment a new file is uploaded to a designated file column on a monday.com board. A group-level filter ensures the trigger only fires from an authorised upload zone, preventing accidental triggers from unrelated board activity.

File Handling & Staging: Once triggered, the scenario downloads the uploaded Excel workbook from monday.com and stages it to a OneDrive folder. This staging step is necessary to make the file accessible to the Microsoft 365 Excel module, which requires a cloud-hosted file rather than a direct binary stream. Board metadata is simultaneously fetched via a GraphQL query to support dynamic column resolution downstream.

Multi-Sheet Sequential Processing: The scenario reads five distinct worksheets from the workbook in strict sequential order, two schedule sheets and three demographic sheets. Each sheet is fully read and aggregated into a single bundle before the next sheet begins processing. This sequential chaining via aggregators ensures data integrity across sheets and prevents race conditions that would arise from parallel processing.

Today's Schedule Processing: The first schedule sheet is iterated row by row. For each patient record, a GraphQL search query looks up the patient on the monday.com board by their unique identifier. A router then branches into two paths, an update path for existing records and a create path for new ones, implementing a full upsert pattern. Appointment date, time, provider, location, status, insurance, and source fields are written to the board. On completion, an Upload Complete status flag is set on each processed item, which serves as the downstream trigger for routing automation scenarios.

Upcoming Schedule Processing: The second schedule sheet follows the same iteration and lookup pattern but writes only to future appointment fields, next appointment date, time and type, leaving all other fields untouched. Unmatched patients are silently skipped.

Demographics Processing - Three-Sheet Join: The three demographic sheets are processed sequentially, each using a two-stage Data Store check to confirm whether the current patient appeared in either the today or upcoming schedule before processing. Only patients present in the schedule are processed, reducing unnecessary operations by several orders of magnitude. For matched patients, the scenario locates their parent board item and either creates a new subitem or updates an existing one with personal details including date of birth (converted from Excel serial format), phone number, address, city, gender and insurance information.

End Result: On each daily run the scenario synchronises a healthcare operations board with fresh appointment and demographic data, correctly handling new patients, returning patients, and demographic updates, all from a single multi-sheet file upload with no manual data entry required.

Skills demonstrated: Make.com advanced scenario architecture, multi-sheet Excel processing via Microsoft 365, OneDrive file staging, monday.com GraphQL API (custom queries and mutations), upsert pattern implementation, Data Store usage for cross-sheet deduplication and membership checking, sequential aggregator chaining, subitem create and update logic, Excel serial date conversion, webhook trigger configuration, and multi-path router design.