The Trap of Unstructured Data
Many businesses receive critical operational data entirely via email. A web form on your site might send you an email every time a customer requests a quote. A server monitoring tool might send you an email every time a hard drive reaches 90% capacity.
The problem is that this data is “unstructured” and trapped inside an inbox. To track leads or monitor uptime properly, a human usually has to open Gmail, copy the customer’s name from the email body, open a Google Sheet, and paste it into a row.
By combining Google Apps Script with the GmailApp class, you can build a script that runs in the background every five minutes, reads the content of new emails automatically, extracts the relevant data points, and writes them directly into a Google Sheet.
Step 1: Create a Gmail Label
You never want a script scanning every single email in your inbox. You only want it to scan specific, structured alerts.
- Open Gmail.
- Create a filter that catches the specific emails you want to parse (e.g.,
subject:"New Lead Request"). - Set the filter to automatically apply a new label called “ToParse”.
Step 2: Prepare the Destination Sheet
- Create a new Google Sheet.
- Name the first tab “Leads”.
- Set up headers in Row 1: (A1: Date, B1: Customer Name, C1: Phone Number).
Step 3: Write the Parsing Script
Let’s assume the body of the email always looks exactly like this:
Hello, you have a new lead.
Name: John Doe
Phone: 555-0199
In your Google Sheet, click Extensions > Apps Script. Delete the default code and paste the following:
function parseEmailsToSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Leads");
// 1. Find all unread threads in Gmail that have our specific label
var label = GmailApp.getUserLabelByName("ToParse");
var threads = label.getThreads();
// 2. Loop through each thread
for (var i = 0; i < threads.length; i++) {
var messages = threads[i].getMessages();
// 3. Loop through each message in the thread
for (var j = 0; j < messages.length; j++) {
var message = messages[j];
// We only process it if it hasn't been read yet
if (message.isUnread()) {
var body = message.getPlainBody();
var date = message.getDate();
// 4. Use Regular Expressions (Regex) to extract the data
var nameMatch = body.match(/Name:\s*(.*)/);
var phoneMatch = body.match(/Phone:\s*(.*)/);
// If Regex finds a match, grab the exact captured text
var customerName = nameMatch ? nameMatch[1] : "Not Found";
var phoneNumber = phoneMatch ? phoneMatch[1] : "Not Found";
// 5. Append the extracted data as a new row in the spreadsheet
sheet.appendRow([date, customerName, phoneNumber]);
// 6. Mark the email as read so we don't process it again next time
message.markRead();
// Optional: Remove the label to keep the inbox fully clean
threads[i].removeLabel(label);
}
}
}
}
Understanding the Magic: Regular Expressions (Regex)
The core of email parsing relies on Regex.
The line body.match(/Name:\s*(.*)/) tells the script: “Scan the entire email body. Look for the exact word ‘Name:’. Then, look for any blank spaces (\s*). Then, grab absolutely every character that comes after that until the end of the line ((.*)).”
If your web form outputs data differently, you will need to adjust the Regex to match your specific formatting.
Step 4: Authorize and Automate
- Click the Run button to test the script. Google will require authorization to read your Gmail.
- Once authorized, check your sheet. If you have unread emails labeled “ToParse”, they will instantly appear as neat rows of data.
- To fully automate this, click the Clock icon (Triggers) on the left side of the Apps Script editor.
- Add a trigger to run the
parseEmailsToSheetfunction Time-driven, every 5 minutes.
Conclusion
By connecting the Gmail API directly to Google Sheets via Apps Script, you transform your inbox from a passive reading environment into an automated data ingestion pipeline, entirely eliminating the need for manual data entry.