Extracting Google Calendar Events to Sheets: A Simple Apps Script Solution

by Harri Lammi

In my previous post about streamlining credit card reports with Claude.ai, I noticed a challenge with calendar event processing. Recently I discovered a better approach by using the Google Calendar API through Apps Script. This method allows me to pull filtered calendar events directly into the same spreadsheet as my exported credit card bill. In this post, I'll share the solution developed with Claude.ai for extracting and processing relevant calendar events.

The Solution

I maintain a Google spreadsheet for processing my credit card bill. It has some custom functions and frequently used explanations, making it an ideal place to add the Apps Script. The script extracts events from my calendar within specified start and end dates, excluding events that have no participants or contain certain titles.

For this implementation, I used Claude.ai by simply requesting it to create a Google Sheets script that would import my calendar events. After a few iterations of refining the filtering logic, I was pleasantly surprised when Claude suggested implementing event name filtering.

The result was a script that adds a custom menu to Google Sheets, allowing me to refresh the calendar data whenever needed. Let's dive into the technical implementation details in the next section.

Step-by-Step Setup Guide

Step 1: Create a Calendar Sheet

I created a dedicated "Calendar" sheet to store the imported calendar events. At the top of the sheet, I added three filters in a two-column layout: labels in column A and their corresponding input fields in column B. These filters include:

• Start date: defines the beginning of the date range

• End date: sets the end of the date range

• Excluded titles: a comma-separated list of event titles to filter out (e.g. “weekly,coffee break”)

This makes it easy to adjust the filter criteria before refreshing the calendar data.

Step 2: Add the Apps Script

Under the “Extensions” menu there’s Apps Script selection. It opens the script editor, where I copy-pasted the following script developed with Claude.ai. Remember to save the script. 

function getCalendarEvents() {
  // Get active spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Read dates from cells B1 and B2
  var startDateCell = sheet.getRange("B1").getValue();
  var endDateCell = sheet.getRange("B2").getValue();
  
  // Read excluded titles from cell B3
  var excludeTitlesCell = sheet.getRange("B3").getValue();
  var excludeTitles = excludeTitlesCell ? excludeTitlesCell.split(",").map(title => title.trim().toLowerCase()) : [];
  
  // Ensure dates are Date objects
  var startDate = new Date(startDateCell);
  var endDate = new Date(endDateCell);
  
  // Validate dates
  if (isNaN(startDate.getTime()) || isNaN(endDate.getTime())) {
    SpreadsheetApp.getUi().alert('Invalid dates! Please check cells B1 and B2.');
    return;
  }
  
  // Get default calendar
  var calendar = CalendarApp.getDefaultCalendar();
  
  // Get events for the date range
  var events = calendar.getEvents(startDate, endDate);
  
  // Create header row
  var headers = ["Date", "Starts", "Ends", "Title", "Description", "Location", "Participants"];
  sheet.getRange("A5:G5").setValues([headers]);
  
  // Process events and add them to the spreadsheet
  var data = [];
  for (var i = 0; i < events.length; i++) {
    var event = events[i];
    var guests = event.getGuestList();
    
    // Skip events without participants
    if (guests.length === 0) {
      continue;
    }
    
    // Check if event title should be excluded
    var eventTitle = event.getTitle().toLowerCase();
    if (excludeTitles.some(title => eventTitle.includes(title))) {
      continue;
    }
    
    var guestEmails = [];
    // Collect participant emails
    for (var j = 0; j < guests.length; j++) {
      guestEmails.push(guests[j].getEmail());
    }
    
    // Compile event data
    var row = [
      event.getStartTime().toDateString(),
      event.getStartTime().toTimeString(),
      event.getEndTime().toTimeString(),
      event.getTitle(),
      event.getDescription(),
      event.getLocation(),
      guestEmails.join(", ")
    ];
    data.push(row);
  }
  
  // Clear previous results (from row 6 onwards)
  var lastRow = sheet.getLastRow();
  if (lastRow > 5) {
    sheet.getRange(6, 1, lastRow - 5, headers.length).clearContent();
  }
  
  // Add data to spreadsheet starting from row 6
  if (data.length > 0) {
    sheet.getRange(6, 1, data.length, headers.length).setValues(data);
  }
  
  // Format table
  sheet.autoResizeColumns(1, headers.length);
}

// Add menu to Sheets view
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Calendar')
      .addItem('Get Events', 'getCalendarEvents')
      .addToUi();
}

Step 3: Security and Permissions

1. Close the script editor and reload your Google Sheet

2. You'll see a new "Calendar" menu item

3. Click Calendar > Get Events

4. Grant the requested permissions (the script needs access to your calendar and spreadsheet)

Step 4: Using the Tool

1. In cell B1, enter your start date (e.g., "2024-11-01")

2. In cell B2, enter your end date (e.g., "2024-11-30")

3. In cell B3, optionally enter titles to exclude (e.g., "weekly,status meeting,coffee break")

4. Click Calendar > Get Events

Once you run the script, magic should happen and your filtered calendar events will appear below the filter inputs, organized with all relevant details.

Next steps

I quickly noticed that jumping between two sheets (expenses and events) is more work than switching between Chrome and the calendar app. So the next step is to think of an efficient way to generate explanations from event descriptions and guest lists, and match them with the relevant expense rows. The explanation generation requires AI capabilities, and I'm considering using Gemini for this task.

A small step forward toward a fully automated process!