arrow_backBack to Projects
code

AI Job Tracking Automation Script

AI & AgentsData Engineering
Jul 1, 2026visibility4

An AI-powered workflow that automatically reads emails, parses job application emails, and updates the information on Google Sheets.

PythonLangGraphLangChainETL PipelineMCPGoogle Workspace APIsBeautifulSoup4

This was the first time I tried to code again without AI (partially). So, basically, I ideate everything, the system design and architecture, and even wrote the langgraph workflow (even though it took me 4 hours to do everything, but I learned a lot! I kind of want to feel the productive struggle again.). But for the rest, like the MCP, the Google API & Oauth integration, I need a little help with that, so I ask AI for some assistance XD.

image.pngzoom_in

- just look at those beautiful red colors! I guess reality really hits hard

Note: The color fill and color coding are not part of this script, but rather, that is a Google Sheets-specific, using background fill and conditional formatting

The Problem Statement

This is a side project I just came up with while applying for jobs. I experienced the tedious repetition of constantly checking my email for updates on my applications. Instead of going through that every day, I decided to build an automated agent (since AI Automation is one of the roles I apply for, it felt like the perfect way to prove I could do it!).

Architecture & Tech Stack

To ensure the system was scalable, robust, and cost-effective, I chose a modern AI orchestration stack:

  • LangGraph & LangChain: Used to build a reliable, state-machine-based workflow for the agent. LangGraph's nodes and edges provide deterministic control over the LLM's execution path.
  • Model Context Protocol (MCP): Implemented an MCP server for Gmail integration. This decouples the integration logic from the core agent, standardizing how the LLM accesses recent emails.
  • Google Workspace APIs: Utilized for secure OAuth authentication and interacting directly with Gmail and Google Sheets.
  • Groq: Chosen as the LLM provider for its ultra-low latency inference (and also because it is free and I'm broke atm), crucial for processing large batches of text quickly.

(update: the use of LLMs in this provider is depracated)

  • NEW! Gemma4: Since the model that I used before is deprecated, I migrated to Google AIs, specifically, the gemma-4-31b-it model. It has a generous free tier and is an improvement over the last model.

The Design

The core automation relies on a meticulously designed state graph with four primary nodes: get_email, tools, analyzer, and update_sheets.

LangGraph Architecturezoom_in

Engineering Challenges & Optimizations

Building this wasn't as simple as just plugging an LLM into an email inbox. I ran into several major engineering hurdles, mostly revolving around rate limits and dirty data.

1. Beating the Free Tier Limits (Dual-Model Architecture) Initially, feeding massive email threads to the LLM immediately hit Groq's free-tier rate limit of 12,000 Tokens Per Minute (TPM). To optimize both speed and token costs, I redesigned the workflow into a Dual-Model Architecture:

  • A Fast Model (llama-4-scout-17b) handles the initial routing. It binds to the MCP tools, fetches the emails, and determines if there are any actionable updates.
  • A Smart Model (llama-3.3-70b-versatile) is invoked only for the heavy lifting. Using structured outputs (with_structured_output), it accurately extracts a rigid Pydantic schema (company_name, job_title, status, etc.). Furthermore, the workflow dynamically filters its own memory using LangChain's RemoveMessage utility to prune intermediate tool-call messages and retain only essential context.

update on number 1, since the free models that are being used in there were deprecated now on Groq, I migrated to Google's Free AI Models, specifically the gemma4-31b-it model from the gemma4 family, and deprecated the architecture above.

2. The Hidden War on Tokens (Email Parsing) Extracting the actual job title from an email proved to be the hardest challenge.

  • Broken Plaintext Fallbacks: Relying on Gmail's native snippet or the email's text/plain fallback failed miserably because companies like LinkedIn often leave the plaintext version empty or heavily truncated.
  • Invisible Watermarks: Email marketers inject hundreds of invisible zero-width characters (like \u200c, \u200d, \xad) and tracking links into emails. These are invisible to humans but silently consume thousands of LLM tokens.
  • The Fix: I pivoted to extracting the raw text/html payload, parsing it with BeautifulSoup4 to cleanly strip all HTML tags and separate blocks with newlines. I then implemented a series of Regex rules to instantly vaporize massive tracking URLs, hunt down and destroy invisible Unicode characters, and aggressively truncate massive "Similar Jobs" recommendations in the email footer. This maxed out token efficiency and provided the LLM with perfectly clean, dense text.

3. Smart Google Sheets Upserting Rather than blindly appending rows and creating duplicates every time the script ran, I built deduplication logic to insert rows. The final node reads the existing Google Sheet, matches entries based on Company Name, Job Title, and Status and selectively executes update operations to overwrite the status (e.g., from "Applied" to "Application Viewed") while appending brand new jobs to the bottom. Furthermore, since fetching email returns the most recent emails (so, under the hood, the most recent email starts at the first index), I reversed the message so that the said email starts at the last index to avoid a race condition.

Automation Result Trackerzoom_in

Final Thoughts

Overall, this project taught me that AI is not only for conversational chatbots but also a practical, background utility that solves real-world workflow problems.

Building this automation really highlighted the power of combining modern AI orchestration tools with standard APIs. (By leveraging a dual-model approach, strict state management via LangGraph, and aggressive data-cleaning heuristics), I was able to transform a brittle manual task into a highly reliable and cost-efficient autonomous system.

Quokka AIQuokka