FannieMae-GenAI

/**

• Usage:
• • Open Google Sheets, Extensions -> Apps Script
• • Paste this code, Save, Run extractContactsFromGmail()
• • Authorize scopes when prompted
• Notes:
• • Change query to limit search (label:inbox newer_than:365d from:example@company.com etc.)
• • This scans up to THREAD_LIMIT threads (safe default). Increase with caution. */

function extractContactsFromGmail() { const SHEET_NAME = 'Gmail Contacts'; const QUERY = 'in:inbox'; // change to narrow (e.g., 'label:prospects newer_than:365d') const THREAD_LIMIT = 10000; // number of threads to search (tweak) const threads = GmailApp.search(QUERY, 0, THREAD_LIMIT);

// regex for emails & international-looking phone numbers const emailRegex = /[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}/g; const phoneRegex = /(+?\d{1,3}[\s.-]?)?((?\d{2,4})?[\s.-]?)?[\d\s.-]{5,15}\d/g;

const ss = SpreadsheetApp.getActiveSpreadsheet(); let sheet = ss.getSheetByName(SHEET_NAME); if (!sheet) sheet = ss.insertSheet(SHEET_NAME);

// header sheet.clear(); sheet.appendRow(['ThreadId','MessageId','Date','FromHeader','SenderName','SenderEmail','Subject','DetectedEmails','DetectedPhones','Snippet']);

const rows = []; threads.forEach(thread => { const threadId = thread.getId(); const messages = thread.getMessages(); messages.forEach(msg => { const msgId = msg.getId(); const from = msg.getFrom() || ''; const subject = msg.getSubject() || ''; const date = msg.getDate(); const plainBody = msg.getPlainBody() || ''; const snippet = msg.getPlainBody().slice(0,300).replace(/\r?\n/g,' ');

  // parse name and email from "From" header (e.g. "John Doe <john@example.com>")
  const fromMatch = from.match(/(.*)<\s*([A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,})\s*>/);
  let senderName = '';
  let senderEmail = '';
  if (fromMatch) {
    senderName = fromMatch[1].trim().replace(/^"|"$/g,'');
    senderEmail = fromMatch[2].trim();
  } else {
    // fallback: if only email present or only name present
    const em = from.match(emailRegex);
    if (em && em.length) senderEmail = em[0];
    senderName = from.replace(senderEmail, '').replace(/["<>]/g,'').trim();
  }

  // find any emails/phones in body or signature
  const emailsFound = Array.from(new Set((plainBody.match(emailRegex) || []).concat(senderEmail ? [senderEmail] : []))).join('; ');
  const phonesFound = Array.from(new Set(plainBody.match(phoneRegex) || [])).join('; ');

  rows.push([threadId, msgId, date, from, senderName, senderEmail, subject, emailsFound, phonesFound, snippet]);
});

});

if (rows.length) { // write in bulk for speed sheet.getRange(2,1,rows.length, rows[0].length).setValues(rows); } SpreadsheetApp.flush(); Logger.log('Done. Rows written: ' + rows.length); }