Work snippet
Google Sheets work register to Jobber jobs
Concept DesignIllustrative integration walkthrough. Business details, figures and records are worked examples.
An installer that plans its work in a shared Google Sheet but books crews in Jobber. Marking a row Ready now creates the Jobber job through Make or Zapier, later edits to the row flow through to that same job, the job number comes back into the sheet, and a row with a gap waits rather than half-creating a job.
The problemJobs are typed twice, once in the sheet and once in Jobber.
What changesA Ready row becomes one Jobber job, kept in step with the sheet.
- Area
- Spreadsheet register to the job system
- Tools
- Google Sheets · Jobber · Make or Zapier
- Typical scope
- US$1,800-3,200
- Timeline
- About 4 weeks · fixed milestones
The problem
Every job was entered twice, cancellations never left Jobber, and the first test of an automation produced five jobs from a single row because rows carried no ID.
- Some rows never become Jobber jobs
- Two editors can leave the wrong phone on the job
- A re-run can create duplicates
- Cancels in Jobber do not come back to the sheet
What discovery would examine
A review of a work register kept in Google Sheets, the current copy-and-paste into Jobber, and a fortnight of rows for a small installation business.
| ID | Finding | Evidence | Risk if left |
|---|---|---|---|
| F1 | The sheet is the sales and scheduling register, but Jobber is updated in batches | A 7-hour lag on some rows, and three rows never copied | The crew arrives without a Jobber job, or a job exists only in the sheet |
| F2 | Two people edit the same row, and the last paste into Jobber wins | Conflicting phone numbers on two jobs | The wrong contact is called |
| F3 | There is no row id, so a re-run of a script created five duplicate jobs on a trial | Five Jobber jobs, one sheet row | The trial was stopped |
| F4 | Cancelled rows stay green in the sheet after Jobber was cancelled | Four rows, still marked active | The register overstates the week |
| F5 | Address columns are free text, with unit numbers often in the notes | 12 jobs last month needed a call to find the unit | Time is lost on site |
Approach
- Give every row a stable id and required columns
People own the content; the flow owns the write-back columns.
- Create or update from Ready only
Incomplete rows are held, not pushed.
- Write the job number and status back
The register cannot stay green after a Jobber cancel.
- Refuse a second job for the same row id
A replay is safe.
Current state and future state
Current state
Future state
Future-state modules
How it is built
- Module ARegister shape
A stable row id, required columns, and a status that a person sets to Ready before a job is created.
- Module BValidation
Address parts, a contact method, and a requested date, or the row is held with a reason in a column the flow writes.
- Module CCreate or update
A Ready row creates a Jobber job, or updates it if the row id already has a job number.
- Module DWrite-back
Job number, scheduled window and Jobber status are written back to the row, in columns the flow owns.
- Module ECancel and close
A Cancelled status in the sheet requests a Jobber cancel; a Jobber cancel writes back so the row cannot stay green.
Field mapping
| Sheet column | Jobber field | Owner | Rule |
|---|---|---|---|
| row_id | Custom field, idempotency key | Flow, once issued | Never reused |
| customer_name, email, mobile | Client | Person, then flow matches | Match on email or mobile before creating a client |
| site_line1, unit, suburb, postcode | Property / service address | Person | Unit required when the suburb is a known strata area, else held |
| work_description | Job title and instructions | Person | Copied as written |
| requested_date, window | Schedule request | Person | A request, confirmed in Jobber |
| status (Ready, Hold, Cancelled) | Whether to create, wait, or cancel | Person | The flow does not set Ready |
| jobber_job_no, jobber_status, last_sync | Write-back columns | Flow only | People do not edit these |
Worked exhibits
Exceptions and runbook
| Case | Rule | Who acts | First action |
|---|---|---|---|
| Row marked Ready, site incomplete | Write Hold and the missing column, do not create a job | The person who owns the row | Complete the cells, set Ready again |
| Two editors, conflicting contact details | The later Ready values are sent, and a note is written in a change column | Operations | Confirm the contact with the customer if the phone changed |
| Jobber cancelled, sheet still Ready | Write-back sets the sheet status to Cancelled | Flow | A person must set Ready again to reopen, which is a new decision, not a silent undo |
| Row id missing | The flow issues an id on first sight, and does not create a job until the next Ready pass | Flow, then the row owner | So a half-pasted row cannot become a job |
How to read the exhibits
- The fortnight of figures shows register entries and holds.
- Write-back columns are owned by the flow, so a person cannot type a job number that the flow will later overwrite without a rule.
- Ready is always a human decision.
Test plan
| ID | Check | Method | PASS when | FAIL when |
|---|---|---|---|---|
| T1 | Happy path | Set a complete row to Ready | One Jobber job, number written back | No job, or a job with a blank address |
| T2 | Update | Change the description on a row that already has a job number | The same Jobber job is updated | A second job |
| T3 | Hold | Set Ready with no unit in a strata suburb | Status becomes Hold, no job | A job is created |
| T4 | Cancel both ways | Cancel in Jobber, and separately cancel in the sheet | Both ends show Cancelled | One end stays active |
| T5 | Replay | Run the flow twice on the same Ready row | One job | Two jobs |
Scope
| Area | Included |
|---|---|
| Discovery | Current register, Jobber usage and a fortnight of rows, with written findings |
| Register shape | Row ids, required columns and flow-owned write-back columns |
| Sync flow | Create, update, hold and cancel, with idempotent keys |
| Write-back | Job number, window and status |
| Test plan | The PASS and FAIL checks in this write-up |
| Handover and support | SOPs, walkthrough, 14 days of fixes, optional monthly hour block |
| Not included | A full rewrite of the commercial process, or moving the register off Google Sheets |
Rollout, handover and monthly support
| Milestone | Deliverable | Timing | Share of fee |
|---|---|---|---|
| M1 Discovery and register design | Findings and the column book, signed | Week 1 | 25% |
| M2 Flow in a copy | Create, update and write-back tested | Week 2 | 35% |
| M3 Holds, cancels and tests | All checks passed | Week 3 | 25% |
| M4 Handover | SOPs, 14 days of fixes | Week 4 | 15% |
Handover
- Written operating procedures for the happy path and each exception in the runbook
- A recorded walkthrough of the architecture, the queue and how to replay an item
- A credentials and ownership list, with every connection in your name
- Fourteen days of fixes after go-live, for defects in the agreed scope
Monthly support
A set block of hours each month, at US$35 per hour, agreed in writing before it starts. Typical use of the block: held rows, new columns, and the cancel path in both directions
- Review of the exception queue and the weekly digest
- Small mapping or rule changes, agreed before they are made
- A short written note on what was done and what is still open
Typical scope for this kind of project: US$1,800-3,200
About 4 weeks · fixed milestones. The systems health check (US$150, fixed) comes first and confirms the scope. Extra hourly work is US$35 per hour, agreed before it starts. After handover, a set block of hours each month is available at the same rate, agreed in writing, to watch the flows, handle changes and work the exception queue.
Discuss an engagement.
Outline the operational problem through the contact form. I respond with a recommended starting point, including whether the US$150 systems health check is the appropriate first step.