Easily extract Details from Gmail to Excel
I’m receiving so many appointments to my Gmail, and I was wondering if I could get this into a Google Sheet with ease? The answer is, Yes!
Managing email data can be tedious if you have to copy/paste hundreds of records, but Google Apps Script provides a simple way to automate the process.
Let’s see how to extract specific Gmail email details (like title, name, email, and mobile number) and save them into an Excel file (Google Sheets, which can later be downloaded as Excel).
Lezzgoooo!
Step 1: Open Google Sheets
Open Google Sheets.
Create a new sheet or open an existing one where you want to store the email data.
Rename the first row with headers such as – Title, Name, Email, Contact
Step 2: Open the Script Editor
Click on Extensions in the Google Sheets menu.
Select Apps Script. This opens the Apps Script editor.
Step 3: Write the Script
In the Apps Script editor, delete any placeholder code.
Copy and paste the following code
function extractEmailsToSheet() {
const labelName = "Appointments"; // Change to your Gmail label
const sheetName = "Appointments"; // Change to your sheet name
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
if (!sheet) {
Logger.log(`Sheet with name "${sheetName}" not found!`);
return; // Stop execution if sheet is not found
}
// Get the Gmail label
const label = GmailApp.getUserLabelByName(labelName);
if (!label) {
Logger.log(`Label with name "${labelName}" not found!`);
return;
}
// Get threads with the label
const threads = label.getThreads();
threads.forEach(thread => {
const messages = thread.getMessages();
messages.forEach(message => {
const body = message.getPlainBody();
// Parse content from email body
const titleMatch = body.match(/Title::\s*(.*)/);
const nameMatch = body.match(/Patient Name::\s*(.*)/);
const emailMatch = body.match(/Email::\s*(.*)/);
const mobileMatch = body.match(/Mobile Number:\s*:\s*(.*)/);
const title = titleMatch ? titleMatch[1].trim() : "";
const name = nameMatch ? nameMatch[1].trim() : "";
const email = emailMatch ? emailMatch[1].trim() : "";
const mobile = mobileMatch ? mobileMatch[1].trim() : "";
// Append data to Google Sheet
sheet.appendRow([title, name, email, mobile]);
// Remove label from thread (apply to thread, not individual message)
thread.removeLabel(label);
});
});
}
Step 4: Save, Create a Trigger and Authorize the Script
On the left hand side panel, go to Triggers. Create a tigger and set the event to time-based and set the rest of the events as per your need.
Apps Script will prompt you to authorize the script. Click Review Permissions and grant the required permissions.
(If you get a popup saying Google doesn’t trust this app, there should be an Advance button. Click on it and grant permission.)
Click on the save icon and name your project (e.g., Gmail Extractor).
Click the Run ▶ button at the top.
You should see your sheet getting populated with the details.
Step 5: Customize the Search Query
The GmailApp.search('is:unread') line filters emails. You can modify it to extract emails based on your needs, such as:
By label: 'label:Work'
By keyword: 'subject:invoice'
By date: 'after:2024/01/01 before:2024/12/31'
Refer to Gmail search operators for more options.
Step 6: Run the Script
After authorization, click the Run button again.
The script will fetch the email data and add it to your Google Sheet.
Step 7: Download as Excel
In Google Sheets, click File > Download > Microsoft Excel (.xlsx).
The sheet will be downloaded as an Excel file.
Step 8: Script to Remove Duplicates
To ensure your spreadsheet has unique entries based on the email and name columns, you can add a new function to the Apps Script. This script will check for duplicate rows and delete them.
Add This Script to Remove Duplicates
Open the same Apps Script editor where you wrote the email extraction script.
Add the following function to the script
Here is the complete code with the duplicate removing feature,
function extractEmailsToSheet() {
const labelName = "Appointments"; // Change to your Gmail label
const sheetName = "Appointments"; // Change to your sheet name
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
if (!sheet) {
Logger.log(`Sheet with name "${sheetName}" not found!`);
return; // Stop execution if sheet is not found
}
// Get the Gmail label
const label = GmailApp.getUserLabelByName(labelName);
if (!label) {
Logger.log(`Label with name "${labelName}" not found!`);
return;
}
// Get existing data to check for duplicates and similarities
const existingData = sheet.getDataRange().getValues();
const existingEntries = new Set();
const potentialDuplicates = []; // To store rows with similar data
// Populate the Set with existing email and name combinations
for (let i = 1; i < existingData.length; i++) { // Start from 1 to skip headers
const email = existingData[i][2]; // Assuming Email is in the 3rd column
const name = existingData[i][1]; // Assuming Name is in the 2nd column
if (email && name) {
existingEntries.add(`${email}_${name}`.toLowerCase());
}
}
// Get threads with the label
const threads = label.getThreads();
threads.forEach(thread => {
const messages = thread.getMessages();
messages.forEach(message => {
const body = message.getPlainBody();
// Parse content from email body
const titleMatch = body.match(/Title::\s*(.*)/);
const nameMatch = body.match(/Patient Name::\s*(.*)/);
const emailMatch = body.match(/Email::\s*(.*)/);
const mobileMatch = body.match(/Mobile Number:\s*:\s*(.*)/);
const title = titleMatch ? titleMatch[1].trim() : "";
const name = nameMatch ? nameMatch[1].trim() : "";
const email = emailMatch ? emailMatch[1].trim() : "";
const mobile = mobileMatch ? mobileMatch[1].trim() : "";
// Skip adding the row if it's a duplicate
const key = `${email}_${name}`.toLowerCase();
if (!existingEntries.has(key)) {
sheet.appendRow([title, name, email, mobile]); // Append to the sheet
existingEntries.add(key); // Add to Set to avoid adding again
} else {
// Check for potential similarity (e.g., same email but different mobile number)
const potentialRow = [title, name, email, mobile];
for (let i = 1; i < existingData.length; i++) { // Check existing data for similarity
const existingRow = existingData[i];
if (
email.toLowerCase() === (existingRow[2] || "").toLowerCase() &&
name.toLowerCase() === (existingRow[1] || "").toLowerCase() &&
(mobile !== existingRow[3] || title !== existingRow[0]) // Variations in mobile or title
) {
potentialDuplicates.push({ rowIndex: i + 1, newRow: potentialRow });
}
}
}
// Remove label from thread (apply to thread, not individual message)
thread.removeLabel(label);
});
});
// Highlight similar rows in yellow
potentialDuplicates.forEach(entry => {
const { rowIndex, newRow } = entry;
// Highlight existing row
const range = sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn());
range.setBackground("yellow");
// Highlight new row (if already added to sheet)
const lastRowIndex = sheet.getLastRow();
const lastRowRange = sheet.getRange(lastRowIndex, 1, 1, sheet.getLastColumn());
lastRowRange.setBackground("yellow");
Logger.log(`Potential duplicate found: Row ${rowIndex} and newly added data.`);
});
}
I hope this helps and if you found this article to be helpful. Please share on your socials.Conclusion
With this simple Google Apps Script, you can effortlessly extract Gmail details and organize them in an Excel-ready format.
This method saves time and reduces manual work, whether you’re tracking invoices, organizing unread emails, or managing project correspondence.
Remember to test this and if you encounter problems a simple Google Search or use Claude to ask for a solution.
Here are a few things to do in testing this: Replace Appointments with the appropriate Gmail label and Google Sheet name, Run the script on your data, and verify that flagged rows (similar data) are highlighted in yellow for manual review.
