LeadSync
01 Google Sheets automation · Python backend

Messy daily lead sheets in.
One clean rep sheet out.

Two Python workers poll a team's hand-built Google Sheets. They pull every lead out of free-form daily blocks, clean the fields into 12 consistent columns, and keep a sorted, industry-tagged master sheet up to date without writing duplicates.

Watch the pipeline run Read the case study
  • 2long-running workers: the extractor polls every 3 s, the sync every 10 sPOLL_SECONDS · REFRESH_SECONDS
  • 20→12labelled form fields read per lead, cleaned into 12 output columnsFORM_FIELDS · RESULT_HEADERS
  • 3×retries with 2 s → 4 s → 8 s backoff, then a 60 s cool-off on repeated 429sexecute_with_retry()
  • 55/minself-imposed request cap, set 5 below the 60/min quota the code assumesRateLimiter(max_requests=55)
  • 414US/Canada area codes mapped to 7 time zones, with no external lookuptimezone_from_phone()
  • 7-dayrolling window re-checked each pass. The first run loads full history7-day filter · first_run
  • Python 3
  • Google Sheets API v4
  • google-api-python-client
  • OAuth 2.0 (installed app)
  • python-dotenv
  • stdlib re · deque · logging
02 How it works

Inside the pipeline

Data moves through three tiers of spreadsheets. Agents type into the input sheets. google_sheets_bot.py extracts and cleans their leads into one tab per day. generator_sync.py then merges the last seven days of those tabs into the master sheet that reps work from. Follow one pass of both workers through 14 steps below. It plays on its own; click any step to inspect it, and switch to Code to see the real excerpt.

Worker A

Extract & clean

google_sheets_bot.py · polls every 3 s
per-day tabs (Dec-15, Dec-16 …) → read by Worker B every 10 s
Worker B

Merge into the master sheet

generator_sync.py · every 10 s

Worker A only reads the agents' sheets and writes per-day tabs. Worker B never touches the input sheets. Screens are mock-ups with sample data. Code shows the real excerpt from the repo.
Every step as a list — four phases, 18 steps
Phase 1

Discover the layout

google_sheets_bot.py · main(), get_all_sheets(), find_date_columns()
  1. Split INPUT_SHEET_IDS from .env on commas, so one run can cover several input spreadsheets.
  2. Call spreadsheets.get to list the tabs, then probe cell A1 on each. Tabs that return 403/404 (locked) are skipped.
  3. Read each tab once as a bulk range (A1:ZZ20000).
  4. Scan the header row for the repeating triplet No. | DATE | Running Completed Leads. Each match marks one column group.
  5. Walk each group's DATE column and try 9 date formats, normalizing every hit to Mon-D (e.g. Dec-15). A date block runs from its own row to the row before the next date.
Phase 2

Extract & normalize

extract_data_for_date(), finalize_lead(), helpers
  1. Keep only dates within 7 days of the newest date in that tab, not today's date. This filter is skipped on the first run.
  2. Read only that block's 3-column range and parse it as a small state machine: a number next to Lead: starts a new lead, and any Label: row after it becomes a field.
  3. Clean each field. Phone becomes the first number, digits only. Rates become the last number, formatted 0.00%. Rep loses any "PCI…" suffix, separators and brackets, leaving the first name. Volume becomes the sum of every dollar amount. Time zone comes from the area code.
  4. Drop any block that has neither a business name nor a phone number.
Phase 3

Upsert per-day tabs

write_leads_to_date_sheet(), ensure_sheet_exists()
  1. Skip any lead already seen in this run. The in-memory key is tab-serial-date.
  2. Make sure a tab named after the date exists. If the read fails with HTTP 400, create it with addSheet. Then check the 12-column header row and repair it if it doesn't match.
  3. Make the serial number unique as {SourceTab}-{SerialNo}. If a row with that key exists it is updated in place; otherwise a new row is appended.
Phase 4

Build the master sheet

generator_sync.py · populate_generator_sheet()
  1. Load AR_Assign_Comp_Con into a dict keyed by (normalized date, digits-only phone), with industry as the value.
  2. Read the destination header row (B10:Z10) and map columns by name. Aliases: AR→Rep, Phone→Contact Number, Rate→Rates.
  3. Use only result tabs whose name parses as Mon-D and whose headers are valid. The window is the latest tab minus 6 days; if the destination is empty, the full history is loaded.
  4. Map each row. Date Assigned is the tab name. POS/NPOS becomes Non-POS when blank, NO or NS. Industry comes from the lookup, or reads "{Timezone} - No Industry" when there is no match.
  5. Keep existing rows outside the window, merge in the new ones, dedupe on a full-row key, and sort by date, then Non-POS first, then phone.
  6. If the result matches what's already there, skip the write. Otherwise write once from B11 and colour the POS/NPOS column (POS red, Non-POS blue) with conditional formatting.

Guards

  • Quota-aware client. Every API call goes through one sliding-window limiter (a deque of timestamps, 55 requests per 60 s), plus a 0.5 s gap after each successful call.
  • Backoff on 429. Up to 3 retries at 2/4/8 s. If those fail, it waits 60 s, clears the limiter and makes one final attempt. Other errors also retry with backoff.
  • Header contracts. Worker A rewrites a date tab's header row if it drifts. Worker B ignores any result tab whose first 12 headers don't match exactly.
  • Fault isolation. An exception in one tab is logged with a traceback and the loop moves on to the next tab. Expired OAuth tokens are refreshed, or re-authorized if they were revoked.

What "normalize" means, field by field

Outputs below were produced by running the project's own functions on these sample inputs.
FieldRaw cellOutputFunction
Contact Number312-555-0148 / cell 773-555-01023125550148extract_first_phone
Rates2.9% + 0.30 / IC+ 1.75%1.75%extract_percentage (last number)
Repsara_khan | PCI done (closer)Saranormalize_name
Volume$10,084.15 ; $228,267.84 ; $41,228.50$279,580.49sum_volume_values
Timezone3125550148 · 4155550112 · 9075550101Central · Pacific · Alaskatimezone_from_phone
Date15-Dec-2025 · 12/15/2025Dec-15parse_date
03 Screens

What the team sees

This project has no app of its own; its interface is the team's spreadsheets. Screens 01–03 recreate the sheet layouts the code reads and writes. Screen 04 follows one lead through every stage. Screen 05 is a design idea.

All screens use made-up sample data. Businesses, names and numbers are fictitious (555 exchanges), and no client data appears on this page. Screens 01–04 are HTML recreations of the sheet layouts the code reads and writes. Screen 05 is a Concept UI: a mock-up, not shipped UI.
Layout the code expects · sample data
header triplet the parser searches fordate row: starts a block

01 · Input tab. This is the source data: a layout built for people typing, not for machines. Column groups repeat across the sheet, each lead is a vertical block of labels, and the cells contain whatever the agent typed. Worker A finds the No. / DATE / Running Completed Leads triplets and reads each date block as a separate range.

04 Case study

The problem, in prose

A sales team logs its leads in shared Google Sheets: one tab per agent and one block per day. Each lead is about twenty "Label: value" rows typed by hand. That layout is easy to fill in on a call, but very hard to work from. Phone cells hold two numbers. Rate cells hold a whole pricing quote. Rep names come with notes attached. Monthly volume might be three dollar figures separated by semicolons.

Before reps can call anyone, someone has to copy every new lead into a flat list, clean each field, work out the caller's time zone, look up the business's industry in another sheet, and sort the list so the right leads come first. This is repeated every day for every agent. Each copy is a chance to paste the wrong row, miss a lead or create a duplicate.

The goal was to remove that copying without changing how the agents work. The input sheets stay exactly as they are, and the automation adapts to them.

Two small Python workers do this. One turns the hand-made blocks into a clean table per day. The other merges the past week of those tables into one master sheet, tags each lead with its industry or time zone, and puts Non-POS leads first. Both are built so they can run again and again without producing duplicates.

Hard parts worth noting

Parsing a layout built for people

The input isn't a table. Groups of three columns repeat across the sheet, dates sit inside the data, and each lead is a run of labelled rows. The parser finds the header triplets, works out each date's row range from where the next date starts, then reads each block as a state machine (a number next to Lead: starts a record; any Label: row adds a field).

Tradeoff: the parser depends on agents keeping the triplet headers and the label spelling. If a block doesn't match, it is skipped rather than guessed at.

Staying inside the API quota

Polling every 3 seconds across several spreadsheets would quickly exceed Google's per-minute limit. Every call goes through one wrapper: a sliding-window limiter capped at 55/min, a 0.5 s gap after each success, and exponential backoff on 429 errors. Reads are grouped into ranges: a whole tab in one call, then one call per date block, instead of reading cell by cell.

Tradeoff: Worker A still writes one row per call, which keeps upserts simple but uses more of the quota than batching would.

Running repeatedly without duplicates

Serial numbers start at 1 in every agent's tab, so they aren't unique. Worker A builds the key {SourceTab}-{SerialNo} and updates the matching row instead of appending. Worker B rebuilds its window, keeps rows older than the window, dedupes on the whole row, and compares the result with the existing sheet; if nothing changed, it doesn't write.

These came from real bugs in the commit history: "fix the duplication issue", "fix overwriting issue for output sheet", "fix the update part not to over-ride".

Dates with no year, in many formats

Agents write 15-Dec-2025, 12/15/2025 or Dec-15, and tab names have no year. Worker A tries 9 formats. Worker B also handles Sheets date serials, and pads Jan-9 to Jan-09 so the sort order is correct (that fix is in the history). The 7-day window is measured from the newest date in the sheet, not from today, so a sheet that is a few days behind is still processed.

Known limitation: dates without a year are assumed to be in the current year, which is fragile across December → January.

Time zone without a lookup service

Reps need to know when they can call. The area code is mapped against a hard-coded table of 414 North American codes in 7 zones, so there is no network call and nothing extra to pay for. An unknown code falls back to Central.

Tradeoff: area codes that span two zones return the main one, and the table has to be updated by hand when new codes are added.

Joining two sheets with no shared ID

Industry data lives in a separate assignment sheet that has no lead ID. The join key is (normalized date, digits-only phone), and both sides are normalized the same way. When there's no match, the cell shows "Pacific - No Industry" instead of a blank, so the rep still has useful information.

The header row is also read at run time, so ops can move columns in the master sheet without asking for a code change.

05 Stack & role

What it's built with

Python 3Two standalone scripts, each a long-running poll loop. No framework.
Google Sheets API v4values.get/update, spreadsheets.get, and batchUpdate (addSheet, repeatCell, addConditionalFormatRule).
google-api-python-client 2.108Discovery-based client; every request goes through one retry and rate-limit wrapper.
google-auth-oauthlibInstalled-app OAuth flow; the token is cached in token.json and refreshed or re-authorized automatically.
python-dotenvSpreadsheet IDs come from .env, so the same code runs against test and production sheets.
Standard libraryre for field cleaning, collections.deque for the limiter, logging to file and console, datetime for windows.

Scope, stated plainly

In scope (my work)

  • Backend automation only: both Python workers, end to end.
  • Parsing, normalization, deduplication and upsert logic.
  • Quota handling, retries, header validation, OAuth handling.
  • Changes made in response to client feedback (see the commit history).

Not in scope / not shipped

  • No frontend. The team's interface is Google Sheets itself.
  • The concept monitor (screen 05) is a mock-up only.
  • The input sheet layout and the industry assignment sheet were set by the team.
  • No hosting or deployment: the workers run from a terminal.