Build a Gmail-to-Sheet lead inbox

Enquiries to [email protected] arrive all day, and an inbox is a poor place to track them. They get read, half-actioned, and buried — and at the end of the month nobody can say how many leads came in or where they went. The useful version of that mailbox is a list, not a thread pile.

This script keeps a Leads sheet in sync with the inbox. It picks up every new enquiry thread, pulls out the sender, subject and a short summary, and appends it as a structured row — so the team works from a sheet they can sort, filter and report on, while Gmail just feeds it.

What you’ll need

• A Gmail label leads/new, applied automatically to inbound enquiries by a Gmail filter (for example, anything sent to [email protected]).
• A Leads sheet with a header row of six columns: receivedAt, from, name, subject, summary, threadUrl.
• The Leads sheet’s file ID, copied from its URL, set as LEADS_SHEET_ID in the config block.
• The script will create the leads/captured label itself on first run.

The script

// The spreadsheet that holds the captured leads.
const LEADS_SHEET_ID = '1abcLeadsSheetId';

// Gmail label applied by a filter to new enquiry threads.
const NEW_LABEL = 'leads/new';

// Label this script adds once a thread has been logged, so it is
// never logged twice.
const CAPTURED_LABEL = 'leads/captured';

// How many threads to process per run.
const BATCH_SIZE = 50;

/**
 * Reads new enquiry threads and appends each one as a row in the
 * Leads sheet, then marks it captured so it is not logged again.
 */
function captureLeads() {
  // 1. Get the inbound label and the "already captured" label,
  //    creating the latter the first time it is needed.
  const newLabel = GmailApp.getUserLabelByName(NEW_LABEL);
  if (!newLabel) {
    Logger.log('Label "' + NEW_LABEL + '" not found — set up the filter first.');
    return;
  }
  const seenLabel = GmailApp.getUserLabelByName(CAPTURED_LABEL)
    || GmailApp.createLabel(CAPTURED_LABEL);

  // 2. Pull a batch of labelled threads.
  const threads = newLabel.getThreads(0, BATCH_SIZE);
  if (!threads.length) {
    Logger.log('No new leads to capture.');
    return;
  }

  const sheet = SpreadsheetApp.openById(LEADS_SHEET_ID).getSheets()[0];

  // 3. Skip anything already captured, then build a row per thread.
  const newRows = threads
    .filter((t) => !t.getLabels().some((l) => l.getName() === CAPTURED_LABEL))
    .map((t) => {
      const msg = t.getMessages()[0];

      // Split "Jane Doe <[email protected]>" into name and email.
      const fromRaw = msg.getFrom();
      const [, name = fromRaw, email = ''] =
        fromRaw.match(/^"?(.*?)"?\s*<(.+?)>$/) || [];

      // First few lines of the body as a short summary.
      const summary = msg.getPlainBody()
        .split('\n').slice(0, 3).join(' ').slice(0, 200);

      const url = 'https://mail.google.com/mail/u/0/#inbox/' + t.getId();

      // Mark the thread captured so it is not picked up next run.
      t.addLabel(seenLabel);

      return [new Date(), email, name, t.getFirstMessageSubject(), summary, url];
    });

  // 4. Append all new rows in one write.
  if (newRows.length) {
    sheet.getRange(sheet.getLastRow() + 1, 1, newRows.length, 6)
      .setValues(newRows);
  }
  Logger.log('Captured ' + newRows.length + ' new leads.');
}

How it works

1. captureLeads looks up the leads/new label. If the filter has not been set up yet that label will not exist, so it logs a message and stops. It then gets the leads/captured label, creating it on the first run.
2. It pulls up to BATCH_SIZE threads carrying the leads/new label. If there are none, it logs and exits without touching the sheet.
3. It filters out any thread that already has the leads/captured label — a safety net so a thread is never logged twice even if labels overlap.
4. For each remaining thread it reads the first message and splits the From header with a regex, separating the display name from the email address. The fallbacks handle a bare address with no display name.
5. It takes the first three lines of the plain-text body as a summary, capped at 200 characters, and builds a direct Gmail link from the thread ID.
6. It adds the leads/captured label to the thread, then returns a six-value row matching the sheet’s columns.
7. All the new rows are appended in a single setValues write, which is far faster than writing them one at a time.

Example run

A new email arrives, the filter labels it leads/new, and the next run appends a row:

receivedAt	from	name	subject	summary	threadUrl
2025-09-16 09:42	[email protected]	Jane Doe	Quote for a brand refresh	Hi, we’re after a quote for a full brand refresh ahead of a Q4 launch…	https://mail.google.com/…#inbox/18f…

The thread is now labelled leads/captured, so the next run skips it and only brand-new enquiries are added.

Trigger it

Run this on a time-based trigger so the sheet stays close to live:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose captureLeads, an event source of Time-driven, and a Minutes timer of Every 5 minutes.
4. Save and approve the authorisation prompt.

Watch out for

• The leads/captured label is what prevents duplicates. If you remove it from a thread, the next run will log that thread again.
• Auto-responders and heavy senders will flood the sheet if the Gmail filter is loose. Keep the filter tight — match the support address and exclude obvious no-reply senders.
• Only the first message of each thread is read. A long back-and-forth still produces one row, captured from the opening email — which is usually what you want for a lead, but worth knowing.
• The From regex assumes the standard Name <email> format. An unusual header falls back to using the whole raw string as the name with an empty email — check the sheet occasionally for blank email cells.
• getThreads is capped at BATCH_SIZE per run. If more than 50 enquiries arrive between runs, the rest wait for the next trigger — fine at five-minute intervals, but raise the batch size for a busier inbox.