AppSScript Code

Copy Code

function fetchEmailData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var searchQuery = 'label:Hello Mohit'; // Replace with your desired search criteria


  // 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 = [];


  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();


    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();


      // Extracting 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 "";
}