Sync Google Calendar Events to a Google Sheet Using Apps Script

Whether you're tracking meeting time, generating reports, or building a personal time log, syncing Google Calendar events to a Sheet is a powerful combination. Apps Script's CalendarApp makes this straightforward.

Basic: List this week's events

function syncCalendarToSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clearContents();

  // Header row
  sheet.appendRow(['Title', 'Start', 'End', 'Duration (mins)', 'Location', 'Description']);

  const calendar = CalendarApp.getDefaultCalendar();
  const now = new Date();
  const weekStart = new Date(now);
  weekStart.setDate(now.getDate() - now.getDay()); // Start of current week (Sunday)
  weekStart.setHours(0, 0, 0, 0);

  const weekEnd = new Date(weekStart);
  weekEnd.setDate(weekStart.getDate() + 7);

  const events = calendar.getEvents(weekStart, weekEnd);

  events.forEach(event => {
    const start = event.getStartTime();
    const end = event.getEndTime();
    const durationMins = Math.round((end - start) / 60000);

    sheet.appendRow([
      event.getTitle(),
      start,
      end,
      durationMins,
      event.getLocation() || '',
      event.getDescription() || ''
    ]);
  });

  Logger.log(`Synced ${events.length} events.`);
}

Sync a specific calendar by name

If you have multiple calendars, you can target one by name:

function syncNamedCalendar() {
  const calendarName = 'Work';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clearContents();
  sheet.appendRow(['Title', 'Start', 'End', 'Duration (mins)']);

  const calendars = CalendarApp.getCalendarsByName(calendarName);
  if (calendars.length === 0) {
    Logger.log(`Calendar "${calendarName}" not found.`);
    return;
  }

  const calendar = calendars[0];
  const start = new Date();
  start.setDate(1); // First day of current month
  start.setHours(0, 0, 0, 0);

  const end = new Date(start.getFullYear(), start.getMonth() + 1, 0); // Last day

  const events = calendar.getEvents(start, end);
  events.forEach(event => {
    const s = event.getStartTime();
    const e = event.getEndTime();
    sheet.appendRow([
      event.getTitle(),
      s,
      e,
      Math.round((e - s) / 60000)
    ]);
  });
}

Add colour-coded status based on event type

function syncWithColours() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const calendar = CalendarApp.getDefaultCalendar();
  const start = new Date();
  const end = new Date();
  end.setDate(end.getDate() + 7);

  sheet.clearContents();
  sheet.appendRow(['Title', 'Start', 'End', 'All Day?']);

  const events = calendar.getEvents(start, end);
  events.forEach((event, i) => {
    const row = i + 2;
    sheet.appendRow([
      event.getTitle(),
      event.getStartTime(),
      event.getEndTime(),
      event.isAllDayEvent() ? 'Yes' : 'No'
    ]);

    // Highlight all-day events in yellow
    if (event.isAllDayEvent()) {
      sheet.getRange(row, 1, 1, 4).setBackground('#fff2cc');
    }
  });
}

Automate with a daily trigger

function createDailySyncTrigger() {
  ScriptApp.newTrigger('syncCalendarToSheet')
    .timeBased()
    .everyDays(1)
    .atHour(6)
    .create();
}

Run createDailySyncTrigger() once and your sheet will refresh every morning with the latest calendar data.

Tips

CalendarApp.getEvents() returns events in chronological order.
All-day events return midnight as their start time — check isAllDayEvent() when duration calculations look wrong.
For shared/team calendars, use CalendarApp.getCalendarById(calendarId) with the calendar's ID found in its settings.