AppSScript Code
function fetchEmailData() {
var labelName = "Fetch Gmail data"; // replace with the name of your label
var batchSize = 500; // adjust the batch size as needed
var sheet = SpreadsheetApp.getActiveSheet();
var data = [];
data.push(["From Name", "From Email", "Reply-To Name", "Reply-To Email", "To", "Subject", "Date", "Body Name", "Body Email", "Body Office Phone", "Body Address", "Body Message"]);
var start = 0;
var threads = GmailApp.search("label:" + labelName, start, batchSize); // get the first batch of threads
while (threads.length > 0) {
for (var i = 0; i < threads.length; i++) {
var thread = threads[i];
var messages = thread.getMessages();
for (var j = 0; j < messages.length; j++) {
var message = messages[j];
var from = message.getFrom();
var fromName = "";
var fromEmail = "";
if (from) {
var parts = from.split("<"); if (parts.length > 1) {
fromName = parts[0].trim();
fromEmail = parts[1].replace(">", "").trim();
} else {
fromEmail = from.trim();
}
}
var replyTo = message.getReplyTo();
var replyToName = "";
var replyToEmail = "";
if (replyTo) {
var parts = replyTo.split("<"); if (parts.length > 1) {
replyToName = parts[0].trim();
replyToEmail = parts[1].replace(">", "").trim();
} else {
replyToEmail = replyTo.trim();
}
}
var to = message.getTo();
var subject = message.getSubject();
var date = message.getDate();
var body = message.getBody().replace(/<\/?[^>]+(>|$)/g, ""); // remove HTML tags
body = body.replace(/<\/div>
/g, “\n”); // replace div tags with newline characters // Extract “Name”, “Email”, “Office Phone”, “Address”, and “Message” fields from email body var bodyName = “”; var bodyEmail = “”; var bodyOfficePhone = “”; var bodyAddress = “”; var bodyMessage = “”; var bodyMatch = body.match(/Name:\s*([\w\s]+).*Email:\s*([^\s]+).*Office\s*Phone:\s*(.*)\s*Address:\s*(.*)\s*Message:\s*([\s\S]*)/is); if (bodyMatch) { bodyName = bodyMatch[1].trim(); bodyEmail = bodyMatch[2].trim(); bodyOfficePhone = bodyMatch[3].trim(); bodyAddress = bodyMatch[4].trim(); bodyMessage = bodyMatch[5].trim(); } data.push([fromName, fromEmail, replyToName, replyToEmail, to, subject, date, bodyName, bodyEmail, bodyOfficePhone, bodyAddress, bodyMessage]); } } start += batchSize; threads = GmailApp.search(“label:” + labelName, start, batchSize); // get the next batch of threads } var numRows = data.length – 1; // exclude header row var numCols = data[0].length; sheet.getRange(1, 1, numRows + 1, numCols).setValues(data); sheet.getRange(“B2:B”).setNumberFormat(“@”); // format email columns as plain }
