Automating Gmail to Google Sheets: Build an Email Lead & Invoice Extractor with Apps Script

Introduction

Every business receives daily operational emails: customer quote requests, inbound website leads, payment receipts, and vendor invoices. Manually copying contact names, phone numbers, and pricing figures into Google Sheets consumes hundreds of team hours annually. With Google Apps Script, you can construct an autonomous, serverless automation pipeline that extracts, parses, and logs email data on a scheduled clock trigger.

Architectural Overview

Our automation workflow follows four programmatic stages:

1. Search Gmail inbox for unread messages matching a specific search operator (e.g., label:leads is:unread).
2. Iterate through threads and extract metadata (Date, Sender, Subject, Body text).
3. Parse specific fields using regular expressions (Regex) or line splits.
4. Append structured records into Google Sheets and apply a processed label in Gmail to prevent duplicate logging.

The Production Apps Script Code

Open your Google Sheet, click Extensions > Apps Script, and paste the following tested script:

function parseInboundLeadsToSheets() {
  const SHEET_NAME = "Inbound_Leads";
  const GMAIL_QUERY = 'label:inbound-lead is:unread';
  const PROCESSED_LABEL_NAME = "Automated_Processed";
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(SHEET_NAME);
  
  // Create sheet with headers if not present
  if (!sheet) {
    sheet = ss.insertSheet(SHEET_NAME);
    sheet.appendRow(["Timestamp", "Sender Name", "Sender Email", "Phone", "Requested Service", "Message Snippet", "Thread ID"]);
    sheet.setFrozenRows(1);
    sheet.getRange("A1:G1").setFontWeight("bold").setBackground("#f1f5f9");
  }
  
  // Ensure processed label exists
  let processedLabel = GmailApp.getUserLabelByName(PROCESSED_LABEL_NAME);
  if (!processedLabel) {
    processedLabel = GmailApp.createLabel(PROCESSED_LABEL_NAME);
  }
  
  const threads = GmailApp.search(GMAIL_QUERY, 0, 25);
  if (threads.length === 0) {
    Logger.log("No new unread leads found.");
    return;
  }
  
  const rowsToAdd = [];
  
  for (let i = 0; i < threads.length; i++) {
    const messages = threads[i].getMessages();
    const latestMessage = messages[messages.length - 1];
    
    if (!latestMessage.isUnread()) continue;
    
    const rawSender = latestMessage.getFrom();
    const date = latestMessage.getDate();
    const body = latestMessage.getPlainBody();
    const threadId = threads[i].getId();
    
    // Parse sender name and email address
    let senderName = rawSender;
    let senderEmail = rawSender;
    const emailMatch = rawSender.match(/<([^>]+)>/);
    if (emailMatch) {
      senderEmail = emailMatch[1];
      senderName = rawSender.replace(/<[^>]+>/, "").trim();
    }
    
    // Extract Phone Number via Regex
    const phoneMatch = body.match(/(?:phone|tel|mobile)[:s]*([+0-9s-()]{7,20})/i);
    const phone = phoneMatch ? phoneMatch[1].trim() : "Not Provided";
    
    // Extract Service Requested
    const serviceMatch = body.match(/(?:service|interest|product)[:s]*([^

]+)/i);
    const service = serviceMatch ? serviceMatch[1].trim() : "General Inquiry";
    
    const snippet = body.substring(0, 150).replace(/[\r\n]+/g, " ");
    
    rowsToAdd.push([date, senderName, senderEmail, phone, service, snippet, threadId]);
    
    // Mark as read and label
    latestMessage.markRead();
    threads[i].addLabel(processedLabel);
  }
  
  if (rowsToAdd.length > 0) {
    sheet.getRange(sheet.getLastRow() + 1, 1, rowsToAdd.length, rowsToAdd[0].length).setValues(rowsToAdd);
    Logger.log("Successfully appended " + rowsToAdd.length + " new leads.");
  }
}

Step 2: Automating with Time-Driven Triggers

To run this function autonomously without opening Google Sheets:

1. In the Apps Script editor, click the Triggers icon (clock symbol) on the left sidebar.
2. Click + Add Trigger.
3. Choose parseInboundLeadsToSheets as the target function.
4. Select event source: Time-driven.
5. Select type: Minutes timer > Every 10 minutes.
6. Click Save and authorize requested Gmail and Sheets permissions.

Best Practices & Quotas

• Batch Append: Notice how our script collects rows in an array (rowsToAdd) and calls setValues() once. Never call appendRow() inside a loop, as each network round-trip slows execution and burns API quotas.
• Daily Quotas: Standard @gmail.com accounts have a limit of 20,000 read operations per day, whereas Google Workspace accounts have 50,000. Filtering via is:unread ensures minimal quota consumption.