appScriptCalendar.js

/**
 * Export unique attendees from ONE calendar over the last 5 years to a CSV in Google Drive.
 *
 * Setup:
 *  - Apps Script -> Services -> add "Calendar API" (Advanced Google Services)
 */
function exportAttendeesToCsv() {
  const YEARS_BACK = 3;

  // IMPORTANT: set this to the calendar you want to read.
  // Examples:
  //   "primary"
  //   "your-calendar-id@group.calendar.google.com"
  const CALENDAR_ID = "ashish@setfive.com";

  // Optional behavior flags
  const INCLUDE_SELF = false;        // include you if you're listed as attendee
  const EXPAND_RECURRING = true;     // expands recurring events into instances
  const ONLY_ACCEPTED = false;       // if true, only include accepted attendees

  const now = new Date();
  const timeMax = now.toISOString();
  const start = new Date(now);
  start.setFullYear(start.getFullYear() - YEARS_BACK);
  const timeMin = start.toISOString();

  // email -> {email, name, meetingCount, firstSeen, lastSeen}
  const people = new Map();

  let pageToken = null;
  let eventsProcessed = 0;

  do {
    const resp = Calendar.Events.list(CALENDAR_ID, {
      timeMin,
      timeMax,
      singleEvents: EXPAND_RECURRING,
      orderBy: EXPAND_RECURRING ? "startTime" : undefined,
      maxResults: 2500,
      pageToken: pageToken || undefined,
      showDeleted: false,
    });

    const events = resp.items || [];
    for (const ev of events) {
      eventsProcessed++;

      const evStart = (ev.start && (ev.start.dateTime || ev.start.date)) || "";
      const attendees = Array.isArray(ev.attendees) ? ev.attendees : [];

      for (const a of attendees) {
        if (!a || !a.email) continue;
        if (!INCLUDE_SELF && a.self) continue;

        if (ONLY_ACCEPTED && a.responseStatus && a.responseStatus !== "accepted") {
          continue;
        }

        const email = String(a.email).trim().toLowerCase();
        if (!email) continue;

        const name = (a.displayName || "").trim();
        upsertPerson_(people, email, name, evStart);
      }
    }

    pageToken = resp.nextPageToken || null;
  } while (pageToken);

  const rows = [["email", "name", "meetingCount", "firstSeen", "lastSeen"]];
  const sorted = Array.from(people.values()).sort((a, b) => a.email.localeCompare(b.email));
  for (const p of sorted) {
    rows.push([p.email, p.name, String(p.meetingCount), p.firstSeen, p.lastSeen]);
  }

  const csv = rows.map(r => r.map(escapeCsv_).join(",")).join("\n");

  // Optional: add UTF-8 BOM for Excel compatibility
  const csvWithBom = "\uFEFF" + csv;

  const filename =
    `calendar_attendees_${sanitizeForFilename_(CALENDAR_ID)}_last_${YEARS_BACK}_years_${formatDateStamp_(now)}.csv`;

  const blob = Utilities.newBlob(csvWithBom, "text/csv;charset=utf-8", filename);
  const file = DriveApp.createFile(blob);

  Logger.log(`CSV char length: ${csv.length}`);
  Logger.log(`Blob bytes: ${blob.getBytes().length}`);
  Logger.log(`Drive file size: ${file.getSize()}`);
  Logger.log(`CSV created: ${file.getUrl()}`);
}

function upsertPerson_(people, email, name, evStart) {
  const seen = evStart || "";

  const existing = people.get(email);
  if (!existing) {
    people.set(email, {
      email,
      name: name || "",
      meetingCount: 1,
      firstSeen: seen,
      lastSeen: seen,
    });
    return;
  }

  if (!existing.name && name) existing.name = name;
  existing.meetingCount += 1;

  if (seen) {
    if (!existing.firstSeen || seen < existing.firstSeen) existing.firstSeen = seen;
    if (!existing.lastSeen || seen > existing.lastSeen) existing.lastSeen = seen;
  }
}

function escapeCsv_(value) {
  const s = String(value ?? "");
  if (/[",\n]/.test(s)) return `"${s.replace(/"/g, '""')}"`;
  return s;
}

function formatDateStamp_(d) {
  const pad = (n) => String(n).padStart(2, "0");
  return `${d.getFullYear()}-${pad(d.getMonth() + 1)}-${pad(d.getDate())}_${pad(d.getHours())}${pad(d.getMinutes())}`;
}

function sanitizeForFilename_(s) {
  return String(s).replace(/[^a-zA-Z0-9._-]+/g, "_").slice(0, 80);
}