AppSScript Code

Copy 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>