One master pipeline, thirty client trackers
A Google Apps Script automation for a PR agency that publishes each opportunity from the team's master pipeline to the right client's tracker, and keeps it up to date from first pitch to secured coverage.
An automation for a PR agency that publishes each opportunity from the team's master pipeline to the right client's tracker, and keeps it up to date until coverage is secured.
- Industry
- Public relations
- Client
- Boutique PR agency, about 20 staff; identity confidential
- Built with
- Google Apps Script and Google Sheets, replacing a Make.com workflow
- Timeline
- Built December 2025 to January 2026, then supported in daily use
A boutique PR agency of about 20 staff; identity confidential. Built December 2025 to January 2026 in Google Apps Script, replacing a Make.com workflow. All names shown are invented.
The challenge and my role
A boutique PR agency tracked every media opportunity in one master spreadsheet: client, outlet, contact, owner, dates and next steps. Each client also had their own tracker showing the work on their account, split into Pipeline and Secured tabs. An existing Make.com workflow was supposed to keep the two aligned, but it wasn't working well, so staff were finding the right client file, copying rows across and checking they matched.
I analyzed the spreadsheets and the workflow, replaced the Make.com setup with Google Apps Script running inside the agency's own Google Workspace, rolled it out to the live 2026 trackers, trained the team and supported it in daily use.
Every opportunity lived in one master sheet, and each client had their own tracker. A Make.com workflow meant to keep them aligned wasn't working well, so staff were copying rows by hand. I replaced it with Apps Script, rolled it out, trained the team and supported it in daily use.
Two views
Internal and client-facing
The team needs every client in one place. Each client should see only their own work, in the layout they're used to.
All clients in one place for the team; each client sees only their own.
Long-lived records
An opportunity changes for weeks
Next steps get updated again and again, and a pitch eventually moves from Pipeline to Secured. Appending a new row each time would bury the client in duplicates.
Updated many times, then moved from Pipeline to Secured.
Shared spreadsheets
Many people, many files
About 20 staff working at once, client names typed slightly differently, headers moved, and the occasional file nobody had shared edit access to.
About 20 staff, varied client names, moved headers, missing access.
One opportunity, start to finish
How a single pitch travels from the agency's master pipeline to a client's tracker, and stays the same record the whole way. Names are invented.
1 · Enter
A team member logs the pitch
- Record ID
- 01482
- Client
- Fernwood Bakery
- Destination
- Pipeline
- Record ID
- 01482
- Destination
- Pipeline
Dropdowns keep client, owner and type consistent. The record gets a permanent ID.
2 · Publish
It appears in the client's tracker
- Ready to Sync
- Ticked
- Lands in
- Pipeline tab
- Status
- synced
The team member decides when it's ready. The script finds the client's file, tab and columns.
Ready to Sync is ticked; the script finds the client's file, tab and columns.
Can't finish? "Sync Failed", with the reason logged.
3 · Update
Next steps change, in place
- Matched by
- Record ID
- Rows added
- 0
- Row updated
- 1
- Matched by
- Record ID
- Rows added
- 0
The same client row is updated each time, however many times the pitch moves on.
4 · Secure
Coverage lands, the record moves
- Destination
- Secured
- Client sees
- Secured tab
- Pipeline copy
- Moved, not duplicated
- Destination
- Secured
- Client sees
- Secured tab
The client's view follows the record's current status.
When a sync can't finish, it says why
At step 2, a record that can't be published shows "Sync Failed" and the reason is logged with its row: an unrecognized client name, or a client file the script can't open. A data-entry mistake and a sharing problem need different fixes, so they're reported differently.
Why every record needed its own ID
The obvious way to match a master row to its client copy is by date, client, outlet and contact. Looking at the agency's real data showed that combination isn't unique: one editor can be working on two separate stories for the same client and outlet on the same day.
Matching on those fields would update the wrong story or merge two into one. So each new master record gets a sequential Record ID, copied into the client tracker on its first sync. Every later update finds its row by that ID, whatever else has been edited.
Date, client, outlet and contact looked like a natural key, but the real data showed one editor working on two stories for the same client and outlet on the same day. A permanent Record ID on both copies keeps them apart.
| Record ID | Original Date | Client | Outlet | Contact | Next Steps |
|---|---|---|---|---|---|
| 01482 | 3/12/2026 | Fernwood Bakery | City Table Weekly | J. Rivera | Sent founder bio for spring openings round-up |
| 01483 | 3/12/2026 | Fernwood Bakery | City Table Weekly | J. Rivera | Pitched sourdough class for weekend events list |
- Record ID
- 01482
- Date · Client
- 3/12 · Fernwood Bakery
- Outlet · Contact
- City Table Weekly · J. Rivera
- Next steps
- Sent founder bio for spring round-up
- Record ID
- 01483
- Date · Client
- 3/12 · Fernwood Bakery
- Outlet · Contact
- City Table Weekly · J. Rivera
- Next steps
- Pitched sourdough class for events list
Illustration with invented names.
What the team works with
The agency kept working in the spreadsheet it already knew. Three columns were added at the front: the Record ID, a Sync Status, and a Ready to Sync checkbox that publishes a record when it's ready. Dropdowns on the key fields keep entries consistent enough to route.
The same spreadsheet as before, with a Record ID, a Sync Status and a Ready to Sync checkbox at the front, and dropdowns on key fields.
| Record ID | Sync Status | Ready to Sync | Original Date | Client | Destination Tab | Owner | Type | Outlet | Next Steps |
|---|---|---|---|---|---|---|---|---|---|
| 01480 | synced | 3/11/2026 | Kestrel Bikes | Secured | Priya S. | Digital | Ride Monthly | Story live; sent thank-you note | |
| 01482 | syncing required | 3/12/2026 | Fernwood Bakery | Pipeline | Marcus L. | City Table Weekly | Editor asked for photos by Friday | ||
| 01484 | Sync Failed | 3/12/2026 | Quill Sleep | Pipeline | Elena R. | Podcast | Rested Hour | Booking recording date |
- 01480 · Kestrel Bikessynced
- 01482 · Fernwood Bakerysyncing required
- 01484 · Quill SleepSync Failed
The KPI summary for leadership
The same master pipeline feeds an executive summary: each staff member against each client, counting open opportunities.
Open opportunities by staff member and client over a rolling 30 days. Counts records, not updates, and leaves out anything secured.
- Counts records, not updates. Editing next steps five times is still one opportunity.
- Excludes anything already secured.
- Uses a rolling window, 30 days by default, based on when the opportunity was first logged.
Leadership can see who is carrying which accounts, and which clients have little in motion.
| Staff member | Total | Fernwood | Kestrel | Quill | Morrow | Bluepine | Harbor |
|---|---|---|---|---|---|---|---|
| Priya S. | 14 | 1 | 7 | 0 | 3 | 2 | 1 |
| Marcus L. | 11 | 5 | 0 | 2 | 0 | 4 | 0 |
| Elena R. | 9 | 0 | 1 | 6 | 2 | 0 | 0 |
| Sam T. | 6 | 2 | 0 | 0 | 1 | 3 | 0 |
| Jordan P. | 4 | 0 | 2 | 1 | 0 | 1 | 0 |
| Total | 44 | 8 | 10 | 9 | 6 | 10 | 1 |
| Staff member | Total | Fern. | Kest. | Quill | Harb. |
|---|---|---|---|---|---|
| Priya S. | 9 | 1 | 7 | 0 | 1 |
| Marcus L. | 7 | 5 | 0 | 2 | 0 |
| Elena R. | 7 | 0 | 1 | 6 | 0 |
| Total | 23 | 6 | 8 | 8 | 1 |
Rollout and support
This was a migration of a workflow the agency was using every day, timed with the move to a new reporting year. The 2025 tabs stayed intact while new 2026 Pipeline and Secured tabs were prepared in every client tracker.
A live workflow migrated at the start of a new reporting year, with 2025 tabs kept intact.
Build on copies
Development and testing on working copies, never the live files.
Never the live files.
Prepare the 2026 trackers
New tabs with the expected columns, links in a Client Sheet Links tab, edit access checked.
Tabs, links and edit access.
Go live
Connected to the live master pipeline, with missing links and access resolved with the agency.
Connected to the live pipeline.
Train the team
A written user guide and live screen-shared demonstrations.
User guide and live demos.
Collect questions in one placeCollect questions
A shared questions log, so answers were written once and reused.
One shared log, answers reused.
Support daily use
Diagnosing permissions, renamed headers and missing client mappings, then resyncing affected records.
Permissions, headers, mappings, resyncs.
Example
One client tracker had a column labeled "Notes" instead of "Next Steps", so next steps weren't reaching that client. Fixing the header and resyncing existing records filled in what was missing. Reliable automation here depended on the script and the spreadsheets working together.
What changed
~5,900
records published through the system from January to July 2026
records, Jan to Jul 2026
~30
client trackers kept in step with one master pipeline
client trackers
~30 hrs
of manual copying and checking avoided each month, estimated
a month saved, est.
| Area | Before | With the automation |
|---|---|---|
| Client updates | Rows copied into each client's file by hand | Published from the master pipeline with one checkbox |
| Record identity | Guessed from date, client, outlet and contact | A permanent Record ID on both copies |
| Status changes | Moved between tabs manually | Follows the record from Pipeline to Secured |
| Failures | Noticed when a client asked | Flagged on the row, with the reason logged |
| Tooling | A separate Make.com workflow | Runs inside the agency's own Google Workspace |
Client updates
Before: copied into each file by hand
Now: published with one checkbox
Status changes
Before: moved between tabs manually
Now: follow the record to Secured
Failures
Before: noticed when a client asked
Now: flagged on the row, reason logged
The record count comes from the master pipeline. The time figure is an estimate, based on roughly 850 new records a month, plus their later updates, at a minute or two each to copy and check by hand; it wasn't measured in a formal time study. Further phases were proposed for heavier simultaneous use and a structured input form; the agency chose to stay with the current system.
The time figure is an estimate from record volume, not a formal time study. Further phases were proposed; the agency chose to stay with the current system.
The same pattern, other industries
Any firm that keeps one internal list of work and owes each client a view of their part runs into the same problem.
One internal list of work, and each client owed a view of their part.
Accounting firmsAccounting
Client task and document lists
One firm-wide tracker of returns and requests, with each client seeing what's outstanding for them.
Law firms
Matter status for clients
Internal matter tracking published as a client-facing status sheet, without exposing internal notes.
Marketing agenciesMarketing
Campaign and content trackers
A master content calendar feeding each client's approval sheet.
Staffing agenciesStaffing
Candidate pipelines by employer
One recruiting pipeline, with each employer seeing only their own candidates and stages.
Property managementProperty
Owner maintenance reports
Work orders tracked centrally, with each owner's open and completed jobs in their own sheet.
ConsultanciesConsulting
Deliverable trackers
One project register, with client-specific status pages that stay current.
Technical detail
- Google Apps Script
- Google Sheets
- Data validation
- Caching
- Replaced Make.com
How a record is synced
Read the master row and check its required fields. Find the client's spreadsheet through the links table. Find the destination tab and its columns by header name. Look for an existing copy by Record ID. Then create, update or move the client row, and record the outcome in the Sync Status column, or log the failure with its row and reason.
Matching client names
The same client could be labeled slightly differently in the pipeline and the links table. Names are normalized, special characters handled and close matches accepted, so routing tolerates small inconsistencies while the links table stays the single place to configure destinations.
Surviving everyday spreadsheet edits
Client trackers are maintained by people, so the script looks for the header row within the first 25 rows rather than assuming a fixed position. Inserting a row above the table no longer breaks the sync.
Performance and "ghost rows"
After launch, caching and optimizations reduced delays. Empty rows had been showing "syncing required" with nothing to send; that was fixed so the status column only flags real work. Very heavy simultaneous use remains subject to Google Sheets and Apps Script limits.
Adding a new client
A documented checklist: add the client to the dropdown options, add their spreadsheet link to the links table, prepare the year's Pipeline and Secured tabs with the expected column names, and confirm the right accounts have edit access. No code changes needed.
How a record is synced
Check required fields, find the client's file through the links table, find the tab and columns by header name, look for an existing copy by Record ID, then create, update or move it and record the outcome.
Matching client names
Names are normalized and close matches accepted, so small spelling differences don't stop a record from reaching its client.
Surviving spreadsheet edits
The script finds the header row within the first 25 rows, so inserting rows above a table doesn't break it.
Performance and ghost rows
Caching reduced delays, and empty rows no longer show "syncing required". Heavy simultaneous use remains subject to Google Sheets limits.
Copying the same rows into several spreadsheets?
Tell me where your team keeps the master list and who needs to see which part of it. We can discuss keeping it in the tools you already use.
Tell me where your master list lives and who needs to see which part.
See other case studies