Export Email To GoogleSheet

function exportInboxEmailsToSheet() {
  try {
    const batchSize = 500;
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spreadsheet.getActiveSheet();

    // Create a sheet if none exists
    if (!sheet) {
      sheet = spreadsheet.insertSheet();
    }

    // Clear previous data (except headers)
    const lastRow = sheet.getLastRow();
    if (lastRow > 1) {
      sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).clear();
    }

    const scriptProperties = PropertiesService.getScriptProperties();
    let lastProcessedTimestamp = scriptProperties.getProperty('lastProcessedTimestamp');

    let start = 0;
    let threads;
    let totalEmails = 0;
    const data = [];

    do {
      // Fetch threads in batches
      threads = GmailApp.search('in:inbox', start, batchSize);

      threads.forEach(thread => {
        const messages = thread.getMessages();

        messages.forEach(message => {
          const timestamp = message.getDate();

          // Skip messages already processed
          if (lastProcessedTimestamp && timestamp <= new Date(lastProcessedTimestamp)) {
            return;
          }

          const threadCount = thread.getMessageCount();
          const fromEmail = message.getFrom();
          const toEmail = message.getTo();
          const subject = message.getSubject();
          const bodyContent = message.getPlainBody().substring(0, 1000); // Limit body to 1000 chars
          const messageId = message.getId();
          const link = 'https://mail.google.com/mail/u/0/#inbox/' + messageId;

          // Add row to data array
          data.unshift([timestamp, threadCount, fromEmail, toEmail, subject, bodyContent, link]);
          totalEmails++;
        });
      });

      // Write batch to sheet
      if (data.length > 0) {
        lastProcessedTimestamp = data[0][0];
        scriptProperties.setProperty('lastProcessedTimestamp', lastProcessedTimestamp);

        sheet.getRange(2, 1, data.length, data[0].length)
          .setValues(data.reverse());

        data.length = 0; // Clear array for next batch
        SpreadsheetApp.flush();
      }

      start += batchSize;

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

    Logger.log("Exported " + totalEmails + " new emails to the active sheet.");

  } catch (e) {
    Logger.log("Error exporting emails: " + e.toString());
    throw e;
  }
}