AI Demo: Email & Newsletter Parsing

function exportNewsletters() {
  // Open the spreadsheet and sheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("Newsletter Exports");
  if (!sheet) {
    sheet = spreadsheet.insertSheet("Newsletter Exports");
    sheet.appendRow(["Email ID", "Email Date", "From", "Subject", "Email Contents"]);
    
    // Format the header row
    sheet.getRange(1, 1, 1, 5).setFontWeight("bold");
    
    // Set column widths (adjust as needed)
    sheet.setColumnWidths(1, 5, 150);
    
    // Format the date column
    sheet.getRange("B:B").setNumberFormat("yyyy-mm-dd hh:mm:ss");
  }

  // Get existing email IDs to avoid duplicates
  var existingIds = [];
  var lastRow = sheet.getLastRow();
  if (lastRow > 1) {  // Check if there's data beyond the header row
    existingIds = sheet.getRange(2, 1, lastRow - 1, 1).getValues().flat();
  }

  // Search for emails with the "newsletter" label
  var threads = GmailApp.search('label:newsletter', 0, 30);
  
  var newData = [];
  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    var message = messages[messages.length - 1]; // Get the last message in the thread
    
    var emailId = message.getId();
    if (!existingIds.includes(emailId)) {
      var date = message.getDate();
      var from = message.getFrom();
      var subject = message.getSubject();
      var content = message.getPlainBody();
      
      newData.push([emailId, date, from, subject, content]);
    }
  }

  // If we have new data, add it to the top of the sheet
  if (newData.length > 0) {
    // Sort new data by date, most recent first
    newData.sort((a, b) => b[1] - a[1]);

    // Insert new rows at the top and set values
    sheet.insertRowsAfter(1, newData.length);
    var newRange = sheet.getRange(2, 1, newData.length, 5);
    newRange.setValues(newData);
    
    // Set text wrapping to CLIP for all cells
    newRange.setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);
    
    // Set a standard row height (adjust the value as needed)
    sheet.setRowHeights(2, newData.length, 21);  // 21 is the default row height
  }
}

function createHourlyTrigger() {
  // Delete existing triggers
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === "exportNewsletters") {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }

  // Create a new trigger to run every hour
  ScriptApp.newTrigger('exportNewsletters')
      .timeBased()
      .everyHours(1)
      .create();
}