FETCHING EMAILS INTO GSHEET (1K+ EMAILS PER DAY)

[Axel Rigo Aliponga]

Hi is there any way to fetch all the unread emails without hitting the premium quota everyday even though we have the google workspace already I've tried my code but I always get the summaries of failure due to premium quota I've reached it is there any way I can improve my code  Hi I want to ask a question or recommendation regarding with the script I have I have receive a I've reached the premium quota for the call even though I have the google workspace and I receive usually 1k+ emails per day how is it possible to have this kind of code work


function myFunction() {

  var userEmail = Session.getActiveUser().getEmail();

  var allowedEmail = "";
abc...@example.com
  if (userEmail !== allowedEmail) {

    throw new Error("You are not authorized to run this script.");

  }

  // Your script code here, runs only if email matches

  Logger.log("Authorized user: " + userEmail);

}

function exportUnreadEmailsByIdinTPEU() {

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inbox");

 

  // Ensure the sheet and its header row exist.

  if (!sheet) {

    SpreadsheetApp.getActiveSpreadsheet().insertSheet("Inbox");

    const newSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inbox");

    newSheet.appendRow(["Date", "From", "Subject", "Body Snippet", "Email Link", "Booking Number(s)"]);

    // Reassign the sheet variable to the newly created sheet

    sheet = newSheet;

  } else {

    ensureHeader(sheet);

  }

  // Get and parse the list of processed message IDs from script properties.

  const props = PropertiesService.getScriptProperties();

  const processedIdsString = props.getProperty('processedMessageIds');

  let processedMessageIds = [];

 

  // FIX: Added a try...catch block to handle potential JSON parsing errors

  try {

    processedMessageIds = processedIdsString ? JSON.parse(processedIdsString) : [];

  } catch (e) {

    Logger.log("Error parsing processedMessageIds from properties. Resetting. Error: " + e.message);

    processedMessageIds = [];

  }

 

  // Get or create the label to mark processed emails

  const processedLabelName = "Processed_by_Script";

  const processedLabel = GmailApp.getUserLabelByName(processedLabelName) || GmailApp.createLabel(processedLabelName);

  // Search for all unread threads in the inbox.

  const threads = GmailApp.search('in:inbox is:unread');

  for (let t = 0; t < threads.length; t++) {

    const thread = threads[t];

    const messages = thread.getMessages();

    // Loop through all messages in the thread to find the unread ones

    for (let m = 0; m < messages.length; m++) {

      const msg = messages[m];

      const messageId = msg.getId();

      // Only process the message if it is unread and not already in our database

      if (msg.isUnread() && !processedMessageIds.includes(messageId)) {

        const date = msg.getDate();

        const from = msg.getFrom();

        const subject = msg.getSubject();

        const body = msg.getPlainBody().replace(/\s+/g, ' ');

        const content = subject + " " + body;

        // UPDATED: Regex to find booking numbers. Removed the extra ')?' at the end.

        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;

        const codes = content.match(pattern) || []; // Use .match directly and provide a default empty array

        // Append the email details to the sheet, including the found codes

        sheet.appendRow([

          date,

          from,

          subject,

          body.substring(0, 100),

          `${messageId}`,
https://mail.google.com/mail/u/0/#inbox/
          codes.join(", ")

        ]);

       

        // Add the message ID to our list of processed IDs

        processedMessageIds.push(messageId);

       

        // Mark the message as read to prevent it from being picked up as unread again

        //msg.markRead();

        // Break the loop after processing the first unread message in the thread

        break;

      }

    }

   

    // Apply the label to the entire thread after it has been processed

    processedLabel.addToThread(thread);

  }

 

  // Save the updated list of processed IDs back to script properties.

  props.setProperty('processedMessageIds', JSON.stringify(processedMessageIds));

}

/**

 * Helper function to ensure the header row exists in the spreadsheet.

 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet The sheet to check.

 */

function ensureHeader(sheet) {

  const headers = ["Date", "From", "Subject", "Body Snippet", "Email Link", "Booking Number(s)"];

  const range = sheet.getRange(1, 1, 1, headers.length);

  const existingHeaders = range.getValues()[0];

 

  const isHeaderPresent = existingHeaders.join() === headers.join();

 

  if (!isHeaderPresent) {

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

  }

}