Sync Google Calendar to Sheets: Events and Availability

Getting Calendar data into a spreadsheet lets you report on events, show availability, or combine with other data (e.g. orders, tasks). Google Apps Script can read Calendar events and write them to Google Sheets—on demand or on a schedule. Once in a sheet, you can filter, pivot, and build dashboards without leaving Google Workspace.

This post covers enabling CalendarApp, fetching events for a date range, writing them to a sheet with useful columns (title, start, end, location, calendar name), and a short note on using the data for reports or free/busy views.

1. Enable Calendar in Apps Script

In your script (Extensions → Apps Script), use CalendarApp. The first time you run a function that uses it, you'll be asked to authorize access to your Calendar. After that, you can get the default calendar or a specific one by ID.

2. Get events for a date range

Get the default calendar and list events between two dates. getEvents(start, end) returns all events that overlap the range (including all-day events).

const calendar = CalendarApp.getDefaultCalendar();
const start = new Date("2025-02-01");
const end = new Date("2025-02-28");
const events = calendar.getEvents(start, end);

Write events to a sheet

Each event has getTitle(), getStartTime(), getEndTime(), getLocation(), getDescription(). Loop over events, build a row for each, then write the 2D array to the sheet:

const rows = [["Title", "Start", "End", "Location"]];
events.forEach(ev => {
  rows.push([
    ev.getTitle(),
    ev.getStartTime().toISOString(),
    ev.getEndTime().toISOString(),
    ev.getLocation() || ""
  ]);
});
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.getRange(1, 1, rows.length, 4).setValues(rows);

3. Use it for reports or availability

Once events are in Sheets, you can filter by calendar, attendee, or title; build pivot tables; or combine with other data. For availability: compute free/busy from event start/end and output a simple grid (e.g. by day and hour).

Need a custom sync or dashboard?

I can build scheduled syncs, multi-calendar views, or Web Apps that show availability. Get a quote and I'll set it up for your timezone and workflow.