Archive past events to a log sheet

Google Calendar is a good place to see what is happening this week, but a poor place to answer questions about the past. Once an event scrolls off the visible range, working out how many client meetings Northwind ran last month — or how many hours went into them — means clicking back through the calendar one day at a time.

This script keeps a running log instead. Every night it reads yesterday’s events from the default calendar and appends one row per event to a Sheet, with the start time, title, attendee count, and duration in minutes. Over a few weeks that Sheet becomes a searchable, sortable history you can filter, total, or chart — without ever touching the calendar itself.

What you’ll need

• A Google Sheet to act as the log. The script writes to the first tab and appends below whatever is already there, so add a header row (Start, Title, Attendees, Duration (min)) once and the data lines up underneath it.
• The ID of that Sheet, taken from its URL.
• Access to the calendar you want to log. This script uses the default calendar of whoever owns the script.

The script

// The Sheet that holds the running event log. The script appends to the
// first tab — add a header row there once before the first run.
const EVENT_LOG_SHEET_ID = '1abcEventLogId';

// Milliseconds in one minute — used to convert event durations.
const MS_PER_MINUTE = 60_000;

/**
 * Reads every event from yesterday on the default calendar and appends
 * one row per event to the log Sheet. Designed to run once a day.
 */
function logYesterday() {
  // 1. Build the window for "yesterday" — midnight to one second before
  //    midnight, so an all-day pass picks up every event on that date.
  const start = new Date();
  start.setDate(start.getDate() - 1);
  start.setHours(0, 0, 0, 0);

  const end = new Date(start);
  end.setHours(23, 59, 59, 999);

  // 2. Pull every event that falls inside yesterday's window.
  const events = CalendarApp.getDefaultCalendar().getEvents(start, end);

  // 3. Nothing on the calendar yesterday — stop before opening the Sheet.
  if (!events.length) {
    Logger.log('No events yesterday — nothing to log.');
    return;
  }

  // 4. Turn each event into a flat row: start time, title, attendee
  //    count, and duration rounded to whole minutes.
  const rows = events.map((e) => [
    e.getStartTime(),
    e.getTitle(),
    e.getGuestList().length,
    ((e.getEndTime() - e.getStartTime()) / MS_PER_MINUTE).toFixed(0),
  ]);

  // 5. Append the rows below whatever is already in the log Sheet.
  const sheet = SpreadsheetApp.openById(EVENT_LOG_SHEET_ID).getSheets()[0];
  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 4).setValues(rows);
  Logger.log('Logged ' + rows.length + ' event(s) from yesterday.');
}

How it works

1. logYesterday builds a date window covering all of yesterday. It takes today’s date, subtracts one day, and snaps start to midnight and end to one millisecond before the next midnight.
2. CalendarApp.getDefaultCalendar().getEvents returns every event that falls inside that window, including ones created by other people that you were invited to.
3. If the calendar was empty yesterday, the script logs a message and stops — no point opening the Sheet to write nothing.
4. Each event is mapped to a four-column row. The duration is the end time minus the start time in milliseconds, divided by MS_PER_MINUTE and rounded to a whole number.
5. The rows are appended starting at getLastRow() + 1, so each run adds to the bottom of the log and never overwrites earlier history.

Example run

Suppose yesterday’s calendar held three events. After the nightly run, three new rows appear at the bottom of the log Sheet:

Start	Title	Attendees	Duration (min)
2026-05-24 09:30	Northwind weekly standup	6	30
2026-05-24 11:00	Client kickoff — Harbour Co	4	60
2026-05-24 15:00	Design review	3	45

A month of these rows lets you answer “how many client meetings did we run?” or “how many hours went into internal sync?” with a simple filter or SUM.

Trigger it

Run this once a day, after midnight, so it always logs a completed day:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose logYesterday, event source Time-driven, type Day timer, and set the hour to 1am to 2am.
4. Save. Approve the calendar and spreadsheet authorisation prompt the first time it runs.

Watch out for

• Duration is wall-clock time, not attended time. The script measures how long an event was scheduled for, not how long anyone actually stayed.
• All-day events have a start and end exactly 24 hours apart, so they log as a 1440-minute “meeting”. If that skews your totals, filter them out by checking e.isAllDayEvent() before mapping the row.
• The attendee count comes from getGuestList(), which excludes the event owner. Add one if you want a true headcount.
• A recurring event logs once per occurrence — that is correct, but it means a daily standup adds a row every day. That is expected, not a bug.
• The script only ever reads yesterday. If a daily run is missed, that day is not backfilled; you would need to run it manually with an adjusted window.