Portfolio
  • Integration and data sync
  • Google Apps Script
  • Google Sheets

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.

Master pipelineSource of truth~20 staff edit hereIDs, dropdowns, sync statusSync scriptClient A trackerPipeline · SecuredClient B trackerPipeline · SecuredClient C trackerPipeline · Secured… about 30 in allKPI summaryFor agency leadership
MasterpipelineSyncClient AClient BClient C… about 30
The agency edits one spreadsheet. Each client sees only their own work.
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. 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. 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. 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. 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.

Two different stories, identical matching fields
Record IDOriginal DateClientOutletContactNext Steps
014823/12/2026Fernwood BakeryCity Table WeeklyJ. RiveraSent founder bio for spring openings round-up
014833/12/2026Fernwood BakeryCity Table WeeklyJ. RiveraPitched sourdough class for weekend events list
Illustration with invented names. Only the Record ID tells these two apart.
  • 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.

Master pipeline (illustration)
Record IDSync StatusReady to SyncOriginal DateClientDestination TabOwnerTypeOutletNext Steps
01480synced3/11/2026Kestrel BikesSecuredPriya S.DigitalRide MonthlyStory live; sent thank-you note
01482syncing required3/12/2026Fernwood BakeryPipelineMarcus L.PrintCity Table WeeklyEditor asked for photos by Friday
01484Sync Failed3/12/2026Quill SleepPipelineElena R.PodcastRested HourBooking recording date
Illustration with invented names, using the real column structure and status values. The failed row's reason (here, a client file without edit access) goes to an automation log with its row number.
  • 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.

Open opportunities, last 30 days (illustration)
Staff memberTotalFernwoodKestrelQuillMorrowBluepineHarbor
Priya S.14170321
Marcus L.11502040
Elena R.9016200
Sam T.6200130
Jordan P.4021010
Total4481096101
A smaller version with invented staff, clients and counts. The real summary covers every staff member and about 30 clients; here, one client clearly needs attention.
Open opportunities, last 30 days (illustration)
Staff memberTotalFern.Kest.QuillHarb.
Priya S.91701
Marcus L.75020
Elena R.70160
Total236881
Smaller illustration with invented staff, clients and counts.

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.

  1. Build on copies

    Development and testing on working copies, never the live files.

    Never the live files.

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

  3. Go live

    Connected to the live master pipeline, with missing links and access resolved with the agency.

    Connected to the live pipeline.

  4. Train the team

    A written user guide and live screen-shared demonstrations.

    User guide and live demos.

  5. Collect questions in one placeCollect questions

    A shared questions log, so answers were written once and reused.

    One shared log, answers reused.

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

What changed: before and with the automation
AreaBeforeWith the automation
Client updatesRows copied into each client's file by handPublished from the master pipeline with one checkbox
Record identityGuessed from date, client, outlet and contactA permanent Record ID on both copies
Status changesMoved between tabs manuallyFollows the record from Pipeline to Secured
FailuresNoticed when a client askedFlagged on the row, with the reason logged
ToolingA separate Make.com workflowRuns 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