Email to Google Sheet data parsing involves extracting information like sender, subject, date, or message body from incoming emails using Google Apps Script. The script scans Gmail, parses relevant details using filters or keywords, and logs them into a Google Sheet, automating lead capture, support tracking, or order management.
Step 1: Click on the Copy button to copy the code snippet.
Step 2: Paste the copied code into your project’s script editor.
function fetchEmailData() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Replace with your desired search criteria
var searchQuery = 'label:Hello Mohit';
// Clear existing data
sheet.getDataRange().clearContent();
// Set the headings in the first row
sheet.getRange("A1:L1").setValues([
["From", "To", "CC", "Subject", "Time", "Enquiry from", "Name", "Firm Name", "Address", "EmailID", "Mobile", "Message"]
]);
var threads = GmailApp.search(searchQuery);
var data = [];
// Loop through all threads
for (var i = 0; i < threads.length; i++) {
var messages = threads[i].getMessages();
// Loop through all messages in a thread
for (var j = 0; j < messages.length; j++) {
var email = messages[j];
var body = email.getPlainBody();
var subject = email.getSubject();
var from = email.getFrom();
var to = email.getTo();
var cc = email.getCc();
var time = email.getDate();
// Extract fields from the email body
var Enquiryfrom = extractValueFromBody(body, "Enquiry from :");
var Name = extractValueFromBody(body, "Name :");
var FirmName = extractValueFromBody(body, "Firm name :");
var Address = extractValueFromBody(body, "Address :");
var EmailID = extractValueFromBody(body, "EmailID :");
var Mobile = extractValueFromBody(body, "Mobile :");
var Message = extractValueFromMessage(body, "Message-");
// Add the data to the array
data.push([from, to, cc, subject, time, Enquiryfrom, Name, FirmName, Address, EmailID, Mobile, Message]);
}
}
// Write the data to the sheet
sheet.getRange(2, 1, data.length, 12).setValues(data);
}
// Helper function to extract value from email body based on a field
function extractValueFromBody(body, field) {
var startIndex = body.indexOf(field);
if (startIndex !== -1) {
startIndex += field.length;
var endIndex = body.indexOf("\n", startIndex);
var value = body.substring(startIndex, endIndex).trim();
// Remove leading and trailing whitespace
value = value.replace(/^\s+|\s+$/g, '');
return value;
}
return "";
}
// Helper function to extract the message from email body
function extractValueFromMessage(body, field) {
var startIndex = body.indexOf(field);
if (startIndex !== -1) {
startIndex += field.length;
var value = body.substring(startIndex).trim();
return value;
}
return "";
}
