HPSCAT vendor data ingestion pipeline

Automated data pipeline that pulls vendor volume data from the HPSCAT API and uploads it to SharePoint via Microsoft Graph. Replaced a manual monthly process across 11 manufacturer vendors.

What it pulls

Vendor volume data for 11 manufacturers: ALPHA BAKING, BUSH BROTHERS, DOLE, JM SMUCKER, ES FOODS, HIDDEN VALLEY / CLOROX, CHICKEN OF THE SEA, Baker Boy, BAKE N JOY, SPECIALTY FOOD SERVICE, Dessert Holdings.

Why PowerShell, not Power Automate

HPSCAT requires static-IP whitelisting. Power Automate's egress IPs are dynamic and can't be reliably whitelisted, so I run the pipeline as PowerShell from office-network sources whose IP ranges (Grand Rapids and Charlotte) are whitelisted by HPSCAT.

Variants

• HPSCAT-SingleVendor: one-vendor refresh, used for ad hoc pulls.
• HPSCAT-MultiVendor: the standard monthly run across all 11 vendors.
• HPSCAT-MultiDay: multi-day windows when a single day is short.
• HPSCAT-MultiVendor-Backfill: historical backfill across vendors and date ranges.

SharePoint integration

Uploads land in document libraries via Microsoft Graph with structured folder layout. Permissions and folder structure are managed automatically so downstream BI work doesn't need to chase access.

Operational shape

• Scheduled runs with structured logging and alerting on failure.
• Idempotent retries: rerunning the same window produces the same result.
• Field mapping and validation to handle vendor-specific schema quirks.