Manaz Afzal Book a consultation

Work snippet

Google Sheets work register to Jobber jobs

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

Discovery findings

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.

Findings from the review
IDFindingEvidenceRisk if left
F1The sheet is the sales and scheduling register, but Jobber is updated in batchesA 7-hour lag on some rows, and three rows never copiedThe crew arrives without a Jobber job, or a job exists only in the sheet
F2Two people edit the same row, and the last paste into Jobber winsConflicting phone numbers on two jobsThe wrong contact is called
F3There is no row id, so a re-run of a script created five duplicate jobs on a trialFive Jobber jobs, one sheet rowThe trial was stopped
F4Cancelled rows stay green in the sheet after Jobber was cancelledFour rows, still marked activeThe register overstates the week
F5Address columns are free text, with unit numbers often in the notes12 jobs last month needed a call to find the unitTime is lost on site

Approach

  1. Give every row a stable id and required columns

    People own the content; the flow owns the write-back columns.

  2. Create or update from Ready only

    Incomplete rows are held, not pushed.

  3. Write the job number and status back

    The register cannot stay green after a Jobber cancel.

  4. Refuse a second job for the same row id

    A replay is safe.

Current state and future state

Register to Jobber, current and future

Current state

Google SheetThe register
Copy and pasteWhen someone has time
JobberSometimes later, sometimes twice
StatusDiverges

Future state

Google SheetRow id, required fields
Make or ZapierValidate, map, key
Jobber jobCreated or updated
Write-backJob number and status

Future-state modules

Google SheetsRegister with row idsMake or ZapierValidate and syncJobberJob create / updateWrite-backNumber and status

How it is built

Module-level build
  1. Module ARegister shape

    A stable row id, required columns, and a status that a person sets to Ready before a job is created.

  2. 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.

  3. Module CCreate or update

    A Ready row creates a Jobber job, or updates it if the row id already has a job number.

  4. Module DWrite-back

    Job number, scheduled window and Jobber status are written back to the row, in columns the flow owns.

  5. 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

Column map, register to Jobber
Sheet columnJobber fieldOwnerRule
row_idCustom field, idempotency keyFlow, once issuedNever reused
customer_name, email, mobileClientPerson, then flow matchesMatch on email or mobile before creating a client
site_line1, unit, suburb, postcodeProperty / service addressPersonUnit required when the suburb is a known strata area, else held
work_descriptionJob title and instructionsPersonCopied as written
requested_date, windowSchedule requestPersonA request, confirmed in Jobber
status (Ready, Hold, Cancelled)Whether to create, wait, or cancelPersonThe flow does not set Ready
jobber_job_no, jobber_status, last_syncWrite-back columnsFlow onlyPeople do not edit these

Worked exhibits

A fortnight of the register, walkthrough figures
Rows marked Ready64
Jobs created or updated57
Held for a person6incomplete address or contact
Duplicates blocked0row id already had a job
Cancels written both ways3sheet and Jobber agreed
A ready row and the Jobber job it created
Google Sheets · row 18
row_idREG-0184
statusReady
siteUnit supplied
jobber_job_noJ-22041
jobber_statusScheduled
last_syncSame morning
Jobber · job 22041
clientMatched on mobile
propertySame unit and suburb
scheduleRequested window, confirmed
custom fieldREG-0184
notesCopied from the description

Exceptions and runbook

Exception runbook
CaseRuleWho actsFirst action
Row marked Ready, site incompleteWrite Hold and the missing column, do not create a jobThe person who owns the rowComplete the cells, set Ready again
Two editors, conflicting contact detailsThe later Ready values are sent, and a note is written in a change columnOperationsConfirm the contact with the customer if the phone changed
Jobber cancelled, sheet still ReadyWrite-back sets the sheet status to CancelledFlowA person must set Ready again to reopen, which is a new decision, not a silent undo
Row id missingThe flow issues an id on first sight, and does not create a job until the next Ready passFlow, then the row ownerSo a half-pasted row cannot become a job

How to read the exhibits

  1. The fortnight of figures shows register entries and holds.
  2. 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.
  3. Ready is always a human decision.

Test plan

PASS / FAIL checks
IDCheckMethodPASS whenFAIL when
T1Happy pathSet a complete row to ReadyOne Jobber job, number written backNo job, or a job with a blank address
T2UpdateChange the description on a row that already has a job numberThe same Jobber job is updatedA second job
T3HoldSet Ready with no unit in a strata suburbStatus becomes Hold, no jobA job is created
T4Cancel both waysCancel in Jobber, and separately cancel in the sheetBoth ends show CancelledOne end stays active
T5ReplayRun the flow twice on the same Ready rowOne jobTwo jobs

Scope

AreaIncluded
DiscoveryCurrent register, Jobber usage and a fortnight of rows, with written findings
Register shapeRow ids, required columns and flow-owned write-back columns
Sync flowCreate, update, hold and cancel, with idempotent keys
Write-backJob number, window and status
Test planThe PASS and FAIL checks in this write-up
Handover and supportSOPs, walkthrough, 14 days of fixes, optional monthly hour block
Not includedA full rewrite of the commercial process, or moving the register off Google Sheets

Rollout, handover and monthly support

MilestoneDeliverableTimingShare of fee
M1 Discovery and register designFindings and the column book, signedWeek 125%
M2 Flow in a copyCreate, update and write-back testedWeek 235%
M3 Holds, cancels and testsAll checks passedWeek 325%
M4 HandoverSOPs, 14 days of fixesWeek 415%
Handover and monthly support

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.

Related work snippets

Next step

Bring me the work that repeats.

I scope it in writing, test it against your current process on your own data and hand over everything I build.

Book a consultation