The problem
The company helps older adults downsize, organize and move into senior communities. Every job is tracked in JobTread, and every client also gets a multi-sheet Excel intake workbook stored in SharePoint that field staff fill in during the home visit. We were brought in after the owner asked whether the two systems could talk to each other.
Office staff re-typed the same client, address, referral and family-contact details from JobTread into a fresh Excel form for every new job.
Each new form had to be copied from a template by hand, named by hand and filed into the correct year folder by hand.
When a phone number, move-to address or note changed in JobTread, the Excel form silently went stale unless someone remembered to update it.
Typos crept in during re-typing, so the CRM and the form disagreed on basic facts.
Field staff sometimes opened a form that had not been created yet, or opened the wrong one.
What we built
We built a small, always-on sync service instead of a scheduled export. JobTread already fires webhooks the moment a record changes, so we let those events drive everything: create the form when a job is created, re-fill it whenever the job, its location, its customer or a contact changes. We kept it one-way (CRM to Excel) so the CRM stays the single source of truth, and we only ever touch the twenty cells that come from JobTread; the rest of the form stays the field team's.
Home visit form
Filled from the CRM
Service log
- 01job.created received
- 02202 accepted
- 03Form created in year folder
- 0420 cells written
- 05sync ok, 9.8 s
Webhook receiver
Authenticated endpoint that accepts JobTread events, answers in milliseconds and queues the work
Field map
A plain configuration file that says which JobTread field lands in which Excel cell, so the mapping can change without a code change
Form builder
Uploads the client's own blank form into the correct year folder, creating the folder if it does not exist, and names the file by client and job number
Cell writer
Writes the mapped values through the Microsoft Graph workbook API inside a single session, so merged cells, formatting and the other sheets stay untouched
Tracker
Remembers which SharePoint file belongs to which job, so renamed or moved files keep syncing and deleted files are recreated
Audit log and alerts
Every sync attempt is logged, and only true failures raise an alert to our engineer
How it works
From trigger to result.
How the system works
8 stages
01CRM event
TriggerA job, customer, contact or location is created or edited.
02Webhook receiver
LogicChecks the secret token, drops duplicates, replies 202 in milliseconds.
03Tracker lookup
DataThe service decides: a new form, or an update to one it already manages.
04CRM API fetch
LogicPulls the full job with location, contacts and custom fields.
05Field mapper
LogicA configuration map turns the data into 20 cell writes.
06SharePoint file
DataTemplate uploaded to the correct year folder, tracked by item id.
07Workbook session
ResultCells written through Microsoft Graph. Formatting and other sheets untouched.
08Audit log and alerts
PersonEvery attempt logged. Only true failures email the engineer.
Our approach
How we worked.
Discovery
Read the client's field mapping, confirmed which twenty cells are CRM-owned, and verified JobTread API and webhook behaviour against a real job.
Access
Guided the client through registering a Microsoft Entra app with admin consent so the service could write to SharePoint without a user login.
Build
Wrote the service test-first (44 automated tests) with a pure mapping layer and thin API clients.
Verify
Twelve local end-to-end cases, then the same cases against the deployed service, then live JobTread events into an isolated test folder, all in the client's real tenant without touching existing files.
Launch and support
Deployed with managed secrets, an error-only alert and a written go-live checklist.
The outcome
What changed.
Office staff no longer copy, rename or file client forms; the form is waiting in the right year folder before they open SharePoint.
Changes to a client's phone, address, notes or family contact in JobTread appear in the Excel form without anyone remembering to update it.
Existing client files were never touched; only forms created by the service are ever written to.
Renaming or moving a form in SharePoint does not break the sync, and an accidentally deleted form is recreated automatically with an alert to us.
A pre-launch test surfaced a real production edge case (database idle disconnect), which we fixed before any client data was involved.
Questions
What people ask.
Can JobTread automatically create and fill an Excel form in SharePoint?
Not out of the box, but yes with a small integration. JobTread sends webhooks when a job, customer, contact or location changes. We use those events to create the Excel file from your template and write the mapped fields through the Microsoft Graph API, so the form appears filled within seconds.
Will the integration overwrite what my staff type into the form?
No. Only the cells mapped from JobTread are ever written, and the mapping is a plain config file you control. Every other cell, sheet, merged range and logo in the workbook is left alone.
What happens if someone renames, moves or deletes the file in SharePoint?
Files are tracked by their SharePoint item id, so renames and moves inside the library keep working. If a file is deleted, the service recreates it from the template, refills it and sends an alert so someone can check why it was removed.
Do we need to give you a Microsoft 365 login?
No. The service authenticates as a registered app in your Microsoft Entra tenant with admin-consented permissions. No user password is stored, and access can be revoked by your admin at any time.