Fetching Emails or Exporting Emails to Gsheet

[Axel Rigo Aliponga]

Hi Everyone,

So I have an on going app script right now which should fetch all the emails inside the inbox but for some reason the emails that has RE: usually doesnt get included some are included any possible scenario that is the same with mine.

[Keith Andersen]

Can you share your script?

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

On Wed, Sep 17, 2025, 2:39 AM Axel Rigo Aliponga <arali...@gmail.com> wrote:

Hi Everyone,

So I have an on going app script right now which should fetch all the emails inside the inbox but for some reason the emails that has RE: usually doesnt get included some are included any possible scenario that is the same with mine.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/2e11c9e7-d338-4ee7-aa51-ca8deaa69eedn%40googlegroups.com.

[Axel Rigo Aliponga]

function exportUnreadInboxEmailsToSheet() {

  try {

    var batchSize = 500;

    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

    var sheetName = "Inbox";

    var sheet = spreadsheet.getSheetByName(sheetName);

    if (!sheet) {

      sheet = spreadsheet.insertSheet(sheetName);

    }

    // Define headers

    var headers = [

      "Date",

      "From",

      "To",

      "Subject",

      "Body (first 300 chars)",

      "Link",

      "Booking Numbers"

    ];

    var firstRow = sheet.getRange(1, 1, 1, headers.length).getValues()[0];

    var isHeaderMissing = headers.some((header, index) => header !== firstRow[index]);

    if (isHeaderMissing) {

      sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

    }

    // Regex for booking numbers

    const pattern = /\b(?:(?=[A-Z0-9]{12})(?=[A-Z0-9]*[A-Z])(?=[A-Z0-9]*\d)[A-Z0-9]{12}|(?=[A-Z0-9]{16})(?=[A-Z0-9]*ANEX)(?=[A-Z0-9]*[A-Z])(?=[A-Z0-9]*\d)[A-Z0-9]{16}|(?=[A-Z0-9]{13})(?=[A-Z0-9]*[A-Z])(?=[A-Z0-9]*\d)[A-Z0-9]{12}W|(?=ANEX[A-Z0-9]{12}W)(?=[A-Z0-9]*[A-Z])(?=[A-Z0-9]*\d)[A-Z0-9]{17})\)?\b/g;

    var data = [];

    var start = 0;

    var totalEmails = 0;

    var scriptProperties = PropertiesService.getScriptProperties();

    var processedIds = scriptProperties.getProperty('processedMessageIds');

    var processedIdSet = processedIds ? new Set(processedIds.split(',')) : new Set();

    var myEmail = Session.getActiveUser().getEmail().toLowerCase();

    var threads;

    do {

      threads = GmailApp.search('in:inbox is:unread', start, batchSize);

      for (var i = 0; i < threads.length; i++) {

        var messages = threads[i].getMessages();

        for (var j = 0; j < messages.length; j++) {

          var message = messages[j];

          var messageId = message.getId();

          var fromEmail = message.getFrom().toLowerCase();

          // Skip if already processed

          if (processedIdSet.has(messageId)) {

            continue;

          }

          // Skip if sent by me

          if (fromEmail.indexOf(myEmail) !== -1) {

            continue;

          }

          // Mark as processed

          processedIdSet.add(messageId);

          // Gather info

          var timestamp = message.getDate();

          var toEmail = message.getTo();

          var subject = message.getSubject();

          var bodyContent = message.getPlainBody().substring(0, 300);

          var link = 'https://mail.google.com/mail/u/0/#inbox/' + messageId;

          // Extract booking numbers from subject and body using regex

          var subjectMatches = subject.match(pattern) || [];

          var bodyMatches = bodyContent.match(pattern) || [];

          var allMatches = [...new Set([...subjectMatches, ...bodyMatches])];

          var bookingNumbers = allMatches.length > 0 ? allMatches.join(", ") : "";

          // Push data

          data.push([

            timestamp,

            fromEmail,

            toEmail,

            subject,

            bodyContent,

            link,

            bookingNumbers

          ]);

          totalEmails++;

        }

      }

      // Write to sheet in batches

      if (data.length > 0) {

        var lastRow = sheet.getLastRow();

        sheet.getRange(lastRow + 1, 1, data.length, data[0].length).setValues(data);

        data = [];

        SpreadsheetApp.flush();

      }

      start += batchSize;

    } while (threads.length === batchSize);

    // Store updated processed IDs (limit to recent 10,000 to avoid size issues)

    var processedIdArray = Array.from(processedIdSet);

    if (processedIdArray.length > 10000) {

      processedIdArray = processedIdArray.slice(processedIdArray.length - 10000);

    }

    scriptProperties.setProperty('processedMessageIds', processedIdArray.join(','));

  } catch (e) {

    throw e;

  }

}

// Optional helper to reset processed IDs when you want to reprocess all

function resetProcessedMessageIds() {

  PropertiesService.getScriptProperties().deleteProperty('processedMessageIds');

[Axel Rigo Aliponga]

Hi this is my app script that I've been making, Recently I changed it to messageID but it captures all the replies wether it was old and not only the new one 

function exportUnreadInboxEmailsToSheet() {

  try {

    var batchSize = 500;

    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

    var sheetName = "Inbox";

    var sheet = spreadsheet.getSheetByName(sheetName);

    if (!sheet) {

      sheet = spreadsheet.insertSheet(sheetName);

    }

    // Define headers

    var headers = [

      "Date",

      "From",

      "To",

      "Subject",

      "Body (first 300 chars)",

      "Link",

      "Booking Numbers"

    ];

    var firstRow = sheet.getRange(1, 1, 1, headers.length).getValues()[0];

    var isHeaderMissing = headers.some((header, index) => header !== firstRow[index]);

    if (isHeaderMissing) {

      sheet.getRange(1, 1, 1, headers.length).setValues([headers]);

    }

    // Regex for booking numbers

    const pattern = /\b(?:(?=[A-Z0-9]{12})(?=[A-Z0-9]*[A-Z])(?=[A-Z0-9]*\d)[A-Z0-9]{12}|(?=[A-Z0-9]{16})(?=[A-Z0-9]*ONEY)(?=[A-Z0-9]*[A-Z])(?=[A-Z0-9]*\d)[A-Z0-9]{16}|(?=[A-Z0-9]{13})(?=[A-Z0-9]*[A-Z])(?=[A-Z0-9]*\d)[A-Z0-9]{12}W|(?=ONEY[A-Z0-9]{12}W)(?=[A-Z0-9]*[A-Z])(?=[A-Z0-9]*\d)[A-Z0-9]{17})\)?\b/g;