Culture, Ethics, Logistics, & Technology (The "SEC" Way)

⌘K
  2. Home
  4. Culture, Ethics, Logistic...
  6. Internal Business Automat...
  8. Client Email Router

Client Email Router

The Client Email Router is a custom Google Apps Script and Google Sheets dashboard designed to automatically capture incoming and outgoing email communications with specific clients, clean up the formatting, and log them into designated Google Doc Work Logs.

By running quietly in the background, this tool ensures our entire team has real-time visibility into client conversations without having to forward emails, manually copy-paste thread updates, or worry about ruining document formatting.

Key Features

“No-Code” Routing Admin Spreadsheet

You no longer need to edit code to add a new client. The script dynamically reads a central Google Sheet. By simply typing a client’s email address (or their entire @domain.com) in Column A and their Google Doc ID in Column B, the script instantly knows where to route their emails.

  • Multi-Client Routing: Uses a clientMap to associate specific email addresses with dedicated Google Doc IDs, ensuring correspondence for “Client A” never ends up in “Client B’s” document.
  • Bidirectional Logging: Searches for both sent and received emails (from: or to:) within the last 24 hours.

Smart Priority Override (SEC Coms vs. Client Logs)

Because our team uses the @secmarketing.group domain for both internal communication and client communication, the script features a smart conflict resolution engine.

  • If Hunter emails Eric, it logs to the Internal SEC Marketing Group Work Log.
  • If Hunter emails a client at @koonz.com (and CCs Eric), the script recognizes the external client domain and skips the internal log entirely, routing the email strictly to the Koonz Work Log.
  • Spam & Noise Reduction: Automatically ignores “automated” emails based on a blacklist of senders (e.g., noreply, zoom.us) and subjects (e.g., invitation:, document shared with you:).
  • Reply Trimming: Strips out “quoted” text from previous replies, signatures, and “Original Message” footers to ensure only the new content is recorded.
  • HTML Cleanup: Converts messy HTML code into clean, readable plain text while handling special characters (like converting & back to &).

Universal Team Memory (No Duplicates)

The script uses a hidden database (PropertiesService) to track what it has logged. Instead of using localized Gmail IDs, it extracts the universal internet Message-ID from every email. This means if an email is sent to four different people on our team, the script will only log it into the client doc once, completely eliminating duplicate entries.

Advanced Document Formatting

Standard automation tools often blindly paste text at the top of a document, ruining tables and important staging links. This script:

  • Scans past top-of-document tables and links.
  • Uses Regular Expressions (Regex) to specifically find the most recent Date Header ( using 12 different date formats, 3-3-2026, 03.03.26, etc.) formatted as a Heading 1, 2, or 3.
  • If a date header exists, it inserts the new email directly under it. If not, it creates a new Heading 1 date section.
  • Flattens and sorts all new emails chronologically, tucking them perfectly underneath the correct dates without breaking your layouts.
  • Metadata Logging: Each entry includes a structured header containing the timestamp, Sender, Recipient, CC list, and Subject line.
  • List Reconstruction: Unlike standard “text-only” converters, this script identifies HTML list tags (<ul>, <ol>, <li>) and recreates actual bulleted lists within the Google Doc.
  • Attachment Awareness: Detects if an email has attachments and logs their filenames at the bottom of the entry.

Intelligent Content Filtering

  • Spam & Noise Reduction: Automatically ignores calendar invites, Zoom/Webex links, Read.ai summaries, and auto-replies.
  • Reply Trimming: Strips out quoted text from previous replies and email signatures so only the newest part of the message is logged.
  • List Reconstruction: Converts messy HTML bullet points into native Google Doc bulleted lists.

How It Was Implemented

Standard no-code tools (like Zapier) struggle to insert text into specific sections of a Google Doc. To solve this, we bypassed those platforms and wrote a custom JavaScript function using Google Apps Script.

Because Apps Script lives directly inside the Google Workspace ecosystem, it has native access to the GmailApp and DocumentApp APIs. This allows it to run seamlessly in the background, read specific emails, clean up the formatting, and programmatically edit our Google Docs without needing complex API authentication or external servers.

How to Use the Spreadsheet Dashboard

To tell the script where to send emails, you just need to update the main Google Sheet (the SEC Email Router Admin Hub).

Step 1: Get the Google Doc ID Open the client’s specific Work Log Google Doc. Look at the URL in your browser’s address bar. The Document ID is the long string of random letters and numbers located between /d/ and /edit.

Step 2: Update the Spreadsheet

  1. Open the SEC Email Router Admin Hub Google Sheet.
  2. In Column A (Target), type the client’s email address (e.g., john@client.com) OR their entire domain to catch anyone at their company (e.g., @client.com).
  3. In Column B (Doc ID), paste the Document ID you grabbed in Step 1.

The script will automatically begin routing emails for this new client on the very next background run.

NOTE: when creating work logs DO NOT make multiple top level documents with each top level document having its own distinct sub heading. this breaks the URL approach and the email will be logged in a strange place. Keep all distinct places to route an email separate from other work logs

Accessing and Sharing the Script

To Access the Script:

  1. Log into the Google Workspace account that hosts the script.
  2. Go to script.google.com.
  3. Click on the project named Client Email Router (or the exact name you saved it as).

To Share Access with Other Team Members: If other team members need to update the client list or edit the code, they need access to the Apps Script project.

  1. Open the project in Apps Script.
  2. In the top right corner, click the Share button (similar to sharing a normal Google Doc).
  3. Add your team member’s company email addresses and ensure they are set to Editor.
  4. Important Note: Sharing the script allows others to edit the code. However, the automated background trigger runs exclusively under the authority of the user who originally created the trigger. (hunter@secmarketing.group as of 3-26-2026)

The Custom Toolbar Menu (Manual Controls)

When you open the Google Sheet, you will notice a custom menu at the top of the screen next to “Help” called [📬 Email Router.]

  • Run Email Routing Now: Forces the script to run immediately, scanning your inbox and logging new emails without waiting for the background timer.
  • Clear Script Memory (Testing Only): Wipes the script’s hidden memory bank. Only use this if you are actively testing the script and need it to re-process an email it has already logged.
  • 📖 How It Works: Opens a pop-up dialog with the password and a direct link to this documentation page.

TEAM ROLLOUT: How to Activate the Router for Your Inbox

CRITICAL NOTE: Google Workspace has strict privacy rules. The script cannot legally read your coworkers’ inboxes automatically. Every single team member must authorize the script and set up their own background trigger. If you have just been shared the “SEC Email Router Admin Hub” Google Sheet, follow these exact steps to turn it on for your inbox. It takes less than 60 seconds:

Step 1: Open the Code Editor

  1. Open the shared SEC Email Router Admin Hub Google Sheet.
  2. In the top toolbar, click Extensions > Apps Script. (A new tab will open displaying the code. Don’t worry, you don’t need to read or edit any of it!)

Step 2: Set Up Your Hourly Trigger

  1. On the far left sidebar of the Apps Script window, click the Triggers icon (it looks like a little alarm clock).
  2. Click the blue + Add Trigger button in the bottom right corner of the screen.
  3. Set the dropdown menus exactly as follows:
    • Choose which function to run: runEmailRouting
    • Choose which deployment should run: Head
    • Select event source: Time-driven
    • Select type of time based trigger: Minute timer
    • Select minute interval: Every minute
  4. Click Save.

Step 3: Authorize the Script (The Security Check)

Because this is a custom internal tool (and not a public app sold on the Google Workspace Marketplace), Google will show you a security warning to confirm you want to let this script read your emails.

  1. A pop-up will appear saying “Authorization Required.” Click on your @secmarketing.group email address.
  2. Google will warn you that “Google hasn’t verified this app.”
  3. Click the Advanced link at the bottom left if it requires you to do so. Otherwise just press Accept/Continue.
  4. Review the permissions (reading Gmail, editing Docs) and click Allow.

You are done! The script will now quietly check your inbox every hour in the background, identify any client communications matching the Google Sheet dashboard, and log them perfectly into the client Work Logs.

PLEASE EMAIL: eric@secmarketing.group if something is broken or formatting of the emails looks strange.

How can we help?