All case studies

RCI Group: making HubSpot–SharePoint reconciliation auditable

A scheduled Azure Function reconciled SharePoint project-monitoring data with HubSpot across four business rules, then returned an action report, archived source file and run log for each completed cycle.

Client: RCI Group
Role: Backend engineer and integration architect
Completed: Oct 2025

Python
Azure Functions
HubSpot CRM API
Microsoft Graph
SharePoint and Pandas

The operating constraint

Project teams maintained workforce-interest assignments in a SharePoint workbook while HubSpot held the contact, company and interest records used by the wider organisation.

That split created a reconciliation problem rather than a simple import task. The integration had to add missing information without duplicating contacts, remove stale interests without damaging valid relationships, link existing contacts to the correct company, and surface wrong-company relationships for human investigation. The same email could also appear against more than one organisation, making a blanket overwrite unsafe.

The operational requirement was therefore twofold:

Make HubSpot reflect the latest approved project-monitoring workbook.
Leave enough evidence for a person to understand every automated decision.

The intervention

I designed and built a Python reconciliation service deployed as an Azure timer-triggered Function. The production schedule runs at 10:00, 14:00 and 18:00 UTC.

Each run follows a controlled sequence:

Verify Microsoft Graph credentials.
Select the most recently modified Excel workbook from the SharePoint input folder.
Read the project interest and flatten semicolon-separated contact lists into normalised company-and-email records.
Compare those records with HubSpot contacts, interest values and company associations.
Apply one of four explicit reconciliation scenarios.
Write every outcome to an Excel action report.
Upload the report, archive the timestamped input workbook and retain the run log in SharePoint.

Four business rules, kept visible in code

Workbook state	HubSpot state	System action
Email present; interest already belongs to the company	Correct match	Preserve the record
Email absent; interest remains on the company	Stale match	Remove the interest when the relationship is unambiguous
Email present; contact exists without the interest	Missing match	Add the interest
Email present; contact missing or linked elsewhere	Missing or conflicting relationship	Create or link the contact, or surface the conflict for investigation

The service does not treat ambiguity as permission to guess. Contacts associated with multiple organisations, or duplicated across workbook rows, are preserved and recorded for review rather than destructively edited.

Resilience at the integration boundary

All HubSpot requests pass through one saferequest gateway. It combines a token-bucket limiter with server-provided Retry-After handling, exponential backoff, jitter, and retries for network and 5xx failures. Contact reads are processed in operational batches of 25, with delays between batches and companies.

Microsoft Graph authentication uses an Azure AD confidential client and refreshes access tokens before expiry. Secrets are loaded from environment variables in deployed environments, while Application Insights and SharePoint run logs provide two levels of runtime visibility.

The evidence

These proof points are implementation guarantees visible in the delivered repository. No verified time-saving, record-volume or accuracy percentage was available, so none is claimed.

reconciliation scenarios encoded as explicit business rules: 4
scheduled reconciliation windows a day (10:00, 14:00, 18:00 UTC): 3
contacts per batch, bounded below HubSpot's API limit: 25
SharePoint control zones: input, archive, output and logs: 4

Proof point	Repository evidence	Operational meaning
4 reconciliation scenarios	`README.MD:25-34`; `main.py:571-693`	Business decisions are explicit rather than hidden inside a generic import
3 scheduled runs per day	`function_app.py:39-44`	CRM state is refreshed at defined operational windows
25 contacts per operational batch	`main.py:597-613`	HubSpot traffic is bounded below the API's hard batch limit
4 SharePoint control zones	`main.py:69-73`	Input, archive, output and logs separate source data from evidence
Central retry gateway	`main.py:279-397`	Rate limits, network failures and server errors follow one policy
Action-level output	`main.py:754-779`	Every processed contact receives a company, email and action record

flowchart TD
    A["SharePoint input workbook"] --> B["Azure Function timer"]
    B --> C["Normalise company IDs and emails"]
    C --> D["Reconcile four business scenarios"]
    E["HubSpot contacts, interests and associations"] <--> D
    D --> F["Apply safe creates, links and updates"]
    D --> G["Surface ambiguous records for review"]
    F --> H["SharePoint action report"]
    G --> H
    H --> I["Archive source workbook"]
    H --> J["Upload run log"]
    K["Application Insights"] --- B

The key design decision was to keep control flow visible. The integration does not merely move rows between systems; it records why a contact was preserved, changed, skipped or escalated.

Four business rules, kept visible in code

Resilience at the integration boundary

Facing a similar constraint?