Apps Scripts Code

Copy Code

// =====================
// RUN ON SHEET OPEN
// =====================
function onOpen() {
    menu();
    listTotalEmails();
  }
  
  // =====================
  // CUSTOM MENU
  // =====================
  function menu() {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu('📧 Gmail Tools')
      .addItem('📨 Total Emails', 'listTotalEmails')
      .addItem('📥 Unread Emails', 'countUnreadEmails')
      .addItem('🔍 Filter by Keyword', 'filterAndSortEmails')
      .addItem('📬 Send Gmail Summary', 'sendEmailSummary')
      .addItem('🧹 Clean Mail (Old + Spam)', 'showSidebar')
      .addItem('🔄 Reload Old/Spam Emails', 'reloadOldAndSpamEmails')
      .addItem('📤 Forward Emails by Keyword', 'forwardEmailsByKeyword')
      .addToUi();
  }
  
  // =====================
  // 1. TOTAL EMAILS
  // =====================
  function listTotalEmails() {
    const threads = GmailApp.search("");
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = ss.getSheetByName("Total Emails") || ss.insertSheet("Total Emails");
    sheet.clearContents();
    sheet.appendRow(["Sender", "Subject", "Date"]);
  
    threads.forEach(thread => {
      const msg = thread.getMessages()[0];
      sheet.appendRow([msg.getFrom(), msg.getSubject(), msg.getDate()]);
    });
  
    beautifySheet(sheet);
    SpreadsheetApp.getUi().alert(`📬 Total Emails Found: ${threads.length}`);
  }
  
  // =====================
  // 2. UNREAD EMAILS (WITH OPEN LINK)
  // =====================
  function countUnreadEmails() {
    const unreadEmails = GmailApp.search("is:unread");
    SpreadsheetApp.getUi().alert(`You have ${unreadEmails.length} unread email(s).`);
    writeUnreadEmailsToSheet(unreadEmails);
  }
  
  function writeUnreadEmailsToSheet(unreadEmails) {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = ss.getSheetByName("Unread Emails") || ss.insertSheet("Unread Emails");
    sheet.clearContents();
    sheet.appendRow(["Sender", "Subject", "Date", "Open"]);
  
    unreadEmails.forEach(thread => {
      const msg = thread.getMessages()[0];
      const sender = msg.getFrom();
      const subject = msg.getSubject();
      const date = msg.getDate();
      const threadId = thread.getId();
      const url = `https://mail.google.com/mail/u/0/#inbox/${threadId}`;
      const openLink = `=HYPERLINK("${url}", "Open")`;
  
      sheet.appendRow([sender, subject, date, openLink]);
    });
  
    beautifySheet(sheet);
  }
  
  // =====================
  // 3. FILTER EMAILS BY KEYWORD
  // =====================
  function filterAndSortEmails() {
    const ui = SpreadsheetApp.getUi();
    const keyword = ui.prompt("Enter keyword to filter unread emails:").getResponseText().trim();
    if (!keyword) return ui.alert("❌ Please enter a valid keyword.");
  
    const filteredEmails = GmailApp.search(`is:unread ${keyword}`);
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Filtered Emails") || SpreadsheetApp.getActiveSpreadsheet().insertSheet("Filtered Emails");
    sheet.clearContents();
    sheet.appendRow(["Sender", "Subject", "Date"]);
  
    filteredEmails.forEach(thread => {
      const msg = thread.getMessages()[0];
      sheet.appendRow([msg.getFrom(), msg.getSubject(), msg.getDate()]);
    });
  
    beautifySheet(sheet);
    ui.alert(`✅ Found ${filteredEmails.length} unread emails with keyword "${keyword}".`);
  }
  
  // =====================
  // 4. GMAIL SUMMARY EMAIL
  // =====================
  function sendEmailSummary() {
    const totalEmails = GmailApp.search("").length;
    const unreadEmails = GmailApp.search("is:unread").length;
  
    const oldDate = new Date();
    oldDate.setDate(oldDate.getDate() - 30);
    const oldEmails = GmailApp.search(`before:${Utilities.formatDate(oldDate, Session.getScriptTimeZone(), "yyyy/MM/dd")}`).length;
  
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    const file = DriveApp.getFileById(spreadsheet.getId());
    const sheetUrl = spreadsheet.getUrl();
    file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  
    const summary = `
  📊 Gmail Summary Report
  -------------------------------
  📨 Total Emails: ${totalEmails}
  📥 Unread Emails: ${unreadEmails}
  🗑️ Emails Older Than 30 Days: ${oldEmails}
  📎 Sheet: ${sheetUrl}`;
  
    MailApp.sendEmail(Session.getActiveUser().getEmail(), "📬 Gmail Summary Report", summary);
    SpreadsheetApp.getUi().alert("✅ Summary email sent.");
  }
  
  // =====================
  // 5. CLEAN MAIL - OLD + SPAM
  // =====================
  function listOldAndSpamEmails() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Clean Mail") ||
                  SpreadsheetApp.getActiveSpreadsheet().insertSheet("Clean Mail");
    sheet.clearContents();
    sheet.appendRow(["Sender", "Subject", "Date", "Type"]);
  
    const threadMap = {};
  
    const oldDate = new Date();
    oldDate.setDate(oldDate.getDate() - 30);
    const oldThreads = GmailApp.search(`before:${Utilities.formatDate(oldDate, Session.getScriptTimeZone(), "yyyy/MM/dd")}`);
    oldThreads.forEach(thread => {
      const msg = thread.getMessages()[0];
      const key = msg.getFrom() + msg.getSubject() + msg.getDate();
      threadMap[key] = thread.getId();
      sheet.appendRow([msg.getFrom(), msg.getSubject(), msg.getDate(), "Old"]);
    });
  
    const spamThreads = GmailApp.search("in:spam");
    spamThreads.forEach(thread => {
      const msg = thread.getMessages()[0];
      const key = msg.getFrom() + msg.getSubject() + msg.getDate();
      threadMap[key] = thread.getId();
      sheet.appendRow([msg.getFrom(), msg.getSubject(), msg.getDate(), "Spam"]);
    });
  
    beautifySheet(sheet);
    PropertiesService.getDocumentProperties().setProperty("threadMap", JSON.stringify(threadMap));
  }
  
  function deleteEmailsByType(type) {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Clean Mail");
    const data = sheet.getDataRange().getValues();
    const map = JSON.parse(PropertiesService.getDocumentProperties().getProperty("threadMap") || "{}");
  
    for (let i = data.length - 1; i > 0; i--) {
      if (data[i][3] === type) {
        const key = data[i][0] + data[i][1] + data[i][2];
        const id = map[key];
        if (id) GmailApp.getThreadById(id).moveToTrash();
        sheet.deleteRow(i + 1);
      }
    }
  
    SpreadsheetApp.getUi().alert(`✅ Deleted all "${type}" emails.`);
  }
  
  function deleteOldEmails() {
    deleteEmailsByType("Old");
  }
  
  function deleteSpamEmails() {
    deleteEmailsByType("Spam");
  }
  
  function reloadOldAndSpamEmails() {
    listOldAndSpamEmails();
    SpreadsheetApp.getUi().alert("✅ Old and Spam emails reloaded.");
  }
  
  // =====================
  // 6. FORWARD EMAILS
  // =====================
  function forwardEmailsByKeyword() {
    const ui = SpreadsheetApp.getUi();
    const keyword = ui.prompt("Enter keyword to find unread emails:").getResponseText().trim();
    const email = ui.prompt("Enter email to forward to:").getResponseText().trim();
    if (!keyword || !email.includes("@")) return ui.alert("❌ Invalid input.");
  
    const threads = GmailApp.search(`is:unread ${keyword}`);
    threads.forEach(thread => {
      const msg = thread.getMessages()[0];
      GmailApp.sendEmail(email, `FWD: ${msg.getSubject()}`, msg.getBody(), { htmlBody: msg.getBody() });
    });
  
    ui.alert(`✅ Forwarded ${threads.length} email(s) to ${email}`);
  }
  
  // =====================
  // 7. SIDEBAR UI
  // =====================
  function showSidebar() {
    const html = HtmlService.createHtmlOutputFromFile("Sidebar").setTitle("🧹 Gmail Cleanup");
    SpreadsheetApp.getUi().showSidebar(html);
  }
  
  // =====================
  // 8. FORMATTING FUNCTION
  // =====================
  function beautifySheet(sheet) {
    const range = sheet.getDataRange();
    const numRows = range.getNumRows();
    const numCols = range.getNumColumns();
  
    sheet.setFrozenRows(1);
  
    const header = sheet.getRange(1, 1, 1, numCols);
    header.setFontWeight("bold").setFontColor("white").setBackground("#4A90E2").setHorizontalAlignment("center");
  
    range.setBorder(true, true, true, true, true, true);
  
    for (let r = 2; r <= numRows; r++) {
      const color = r % 2 === 0 ? "#F9F9F9" : "#FFFFFF";
      sheet.getRange(r, 1, 1, numCols).setBackground(color);
    }
  
    for (let c = 1; c <= numCols; c++) sheet.autoResizeColumn(c);
  }