The Challenge of Manual Email Logging
For sales, support, and administrative teams, manually copying customer details from inbound contact emails into spreadsheets is time-consuming, prone to typos, and creates significant response lag. By deploying a serverless Google Apps Script trigger that runs continuously in the background of your Google Workspace account, inbound inquiry emails can be parsed, extracted, and appended to a centralized Google Sheet in real time.
Core Architecture of the Automation Pipeline
The automated script operates through four sequential phases:
- Targeted Search: Queries Gmail using specific search operators (e.g.
label:leads is:unread) to locate new customer inquiries without scanning your entire inbox. - Regex Data Extraction: Uses regular expression capture groups to isolate customer names, email addresses, phone numbers, and budget requirements from the email body.
- Spreadsheet Insertion: Formats the extracted fields and executes an atomic
sheet.appendRow()call. - State Management: Marks the email thread as read and applies an archive label (e.g.
Processed-Sheets) to prevent duplicate row insertions.
Production Google Apps Script Code
Open your target Google Sheet, navigate to Extensions > Apps Script, and paste the following clean V8 JavaScript code:
/**
* Inbound Gmail Lead Parser for Google Sheets
* Evaluates unread emails matching specific lead search criteria.
*/
function parseInboundLeadsToSheet() {
const SPREADSHEET_ID = SpreadsheetApp.getActiveSpreadsheet().getId();
const targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("InboundLeads") || SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Search for unprocessed lead emails
const searchQuery = "label:website-inquiries is:unread";
const threads = GmailApp.search(searchQuery, 0, 20);
if (threads.length === 0) {
Logger.log("No new unread lead inquiries found.");
return;
}
// Create or retrieve audit label
let processedLabel = GmailApp.getUserLabelByName("Processed-Sheets");
if (!processedLabel) {
processedLabel = GmailApp.createUserLabel("Processed-Sheets");
}
const rowsToAppend = [];
for (let i = 0; i < threads.length; i++) {
const messages = threads[i].getMessages();
const latestMessage = messages[messages.length - 1];
const body = latestMessage.getPlainBody();
const dateReceived = latestMessage.getDate();
const sender = latestMessage.getFrom();
const subject = latestMessage.getSubject();
// Regular Expression Parsing Patterns
const nameMatch = body.match(/Name:\s*([^\r\n]+)/i);
const emailMatch = body.match(/Email:\s*([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,})/i);
const phoneMatch = body.match(/Phone:\s*([+0-9\s-()]{7,20})/i);
const messageMatch = body.match(/Message:\s*([\s\S]+?)(?=\n\n|--|$)/i);
const clientName = nameMatch ? nameMatch[1].trim() : sender;
const clientEmail = emailMatch ? emailMatch[1].trim() : extractRawEmail(sender);
const clientPhone = phoneMatch ? phoneMatch[1].trim() : "Not Provided";
const clientMessage = messageMatch ? messageMatch[1].trim() : subject;
rowsToAppend.push([
dateReceived,
clientName,
clientEmail,
clientPhone,
clientMessage,
latestMessage.getId(),
"New Lead"
]);
// Mark as processed
threads[i].addLabel(processedLabel);
threads[i].markRead();
}
// Batch append rows to minimize spreadsheet API quota
if (rowsToAppend.length > 0) {
const startRow = targetSheet.getLastRow() + 1;
targetSheet.getRange(startRow, 1, rowsToAppend.length, rowsToAppend[0].length).setValues(rowsToAppend);
Logger.log(`Successfully logged ${rowsToAppend.length} new leads.`);
}
}
function extractRawEmail(senderString) {
const match = senderString.match(/<([^>]+)>/);
return match ? match[1] : senderString;
}
Step-by-Step Deployment Instructions
- Inside your Google Sheet, create a tab named
InboundLeads. - Add the following headers in Row 1:
Date Received,Client Name,Email,Phone,Message,Message ID,Status. - Open Extensions > Apps Script, paste the code, and click Save.
- Click Run and complete the standard Google OAuth authorization prompt granting permissions to access Gmail and Spreadsheets.
- Click the clock icon (Triggers) in the left sidebar, click Add Trigger, set the event source to Time-driven, and select an interval (e.g. Every 10 minutes).
Apps Script Quotas & Performance Limits
Google Workspace enforces daily quotas on Apps Script execution:
- Consumer Gmail accounts: Maximum 6 minutes runtime per execution; 100 email read/search operations per day.
- Google Workspace Business / Enterprise accounts: Maximum 30 minutes runtime per execution; 500 email operations per day.
Frequently Asked Questions
Will this script mark all my personal emails as read?
No. The script strictly processes emails matching the explicit searchQuery (e.g. label:website-inquiries is:unread). Other inbox messages remain untouched.
What happens if a customer sends a message with irregular formatting?
Our code includes robust fallbacks. If the regex does not locate a formal "Name:" field, it gracefully falls back to the sender's email display name rather than failing.