Export Email To GoogleSheet

Copy Code

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

    if (!sheet) {
      sheet = spreadsheet.insertSheet(); 
    }
   
   
    var lastRow = sheet.getLastRow();
    if (lastRow > 0) {
      sheet.getRange(2, 1, lastRow, sheet.getLastColumn()).clear(); 
    }
   
    var data = [];
    var start = 0;
    var threads;
    var totalEmails = 0;
   

    var scriptProperties = PropertiesService.getScriptProperties();
    var lastProcessedTimestamp = scriptProperties.getProperty('lastProcessedTimestamp');
   
    do {
      threads = GmailApp.search('in:inbox', 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 timestamp = message.getDate();
         
        
          if (lastProcessedTimestamp && timestamp <= new Date(lastProcessedTimestamp)) 
          { continue; 
          }
           var threadCount = message.getThread().getMessageCount(); var fromEmail = message.getFrom(); var toEmail = message.getTo(); var subject = message.getSubject(); var bodyContent = message.getPlainBody().substring(0, 1000); 
      
           var messageId = message.getId(); var link = 'https://mail.google.com/mail/u/0/#inbox/' + messageId; var row = [timestamp, threadCount, fromEmail, toEmail, subject, bodyContent, link]; data.unshift(row); 
  
           totalEmails++; } } 

           if (totalEmails > 0) {
        lastProcessedTimestamp = data[0][0]; 
        scriptProperties.setProperty('lastProcessedTimestamp', lastProcessedTimestamp);
       
  
        sheet.getRange(2, 1, data.length, data[0].length).setValues(data.reverse()); 
        data = [];
        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;
  }
}