Automated Calendar Event Tracking in Google Sheets using Google Apps Script

Published: at 12:00 AM

Table of contents

Open Table of contents

Introduction

A fun little recent script that I worked on - a Google Apps Script to write my Google Calendar events to a Google Sheet.

Why Even Do This?

No particular reason, and this was just for experimenting with the Google Apps Script. However, put some thought into it and one would quickly realize the potential that integrating the GSuite applications with Apps Script possesses.

Imagine a Project Management scenario - how much time is spent in planning for meetings, attending them, transcribing notes and action items, sending out meeting notes and following up on them? There is a case to be made of how Apps Scripts can automate a good chunk of this by building custom workflows that integrate

  1. Google Slides for decks
  2. Google Meet for the meetings
  3. Google Docs for notes
  4. Google Sheets for action items
  5. GMail for emails

This fun little project provided a good initiation into Apps Scripts’s much bigger capabilities.

Here’s How It Works

This little script grabs events from my calendar and plops them right into a Google Sheet. It even checks to see if I already have events recorded and only pulls in new stuff — so I don’t end up with duplicate entries.

The Script

Link to the full script - Github. The script is modular, with functions that handle specific actions.

1. formatDate(date)

function formatDate(date) {
  var year = date.getFullYear(); // Extract the full year (e.g., 2024)
  var month = String(date.getMonth() + 1).padStart(2, '0'); // Month is zero-based, so add 1; pad with 0 if single digit
  var day = String(date.getDate()).padStart(2, '0'); // Extract the day of the month; pad with 0 if single digit
  return `${year}-${month}-${day}`; // Format as "YYYY-MM-DD"
}

This function ensures uniform date formatting for entries in the sheet.

2. calculateDurationInMinutes(startTime, endTime)

// Function to calculate the duration in minutes given start and end times
function calculateDurationInMinutes(startTime, endTime) {
  var start = new Date(startTime); // Convert start time to Date object
  var end = new Date(endTime); // Convert end time to Date object
  var differenceInMs = end - start; // Calculate the difference in milliseconds
  return differenceInMs / (1000 * 60); // Convert milliseconds to minutes
}

This function helps track the duration of each event for better time analysis.

3. getStartDate(sheet, defaultDate)

// Function to determine the start date for event retrieval based on the sheet's last row
function getStartDate(sheet, defaultDate) {
  var lastRow = sheet.getLastRow(); // Get the last row with data in the sheet
  
  if (lastRow > 1) { // Check if there is existing data (assumes headers in the first row)
    var lastDate = sheet.getRange(lastRow, 3).getValue(); // Get the latest "Start Time" from column 3 in the last row
    var startDate = new Date(lastDate); // Convert last date to a Date object
    startDate.setDate(startDate.getDate() + 1); // Set start date to one day after the last date
    startDate.setHours(0, 0, 0, 0); // Set time to 12:00 AM (start of the day)
    return startDate; // Return the calculated start date
  } else {
    return new Date(defaultDate); // If no data, use the specified default date
  }
}

This function helps prevent duplicate entries by calculating a dynamic start date.

4. writeEventDetailsToSheet(sheet, row, eventDetails)

// Function to write the details of a single event to the sheet at a specified row
function writeEventDetailsToSheet(sheet, row, eventDetails) {
  // Set values in the sheet for each event detail in the specified columns
  sheet.getRange(row, 1).setValue(eventDetails.dateOfEvent); // Column 1: Date of Event
  sheet.getRange(row, 2).setValue(eventDetails.title); // Column 2: Event Title
  sheet.getRange(row, 3).setValue(eventDetails.start_time); // Column 3: Start Time
  sheet.getRange(row, 4).setValue(eventDetails.end_time); // Column 4: End Time
  sheet.getRange(row, 5).setValue(eventDetails.duration); // Column 5: Duration in minutes
  sheet.getRange(row, 6).setValue(eventDetails.colorOfEvent); // Column 6: Event Color
  sheet.getRange(row, 7).setValue(eventDetails.description); // Column 7: Event Description
}

This function logs each event’s details in a consistent and organized manner.

5. getEvents()

// Main function to retrieve events from Google Calendar and log them into the spreadsheet
function getEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); // Access the active spreadsheet
  var sheet = ss.getSheetByName("sheet_name"); // Select the sheet by name "sheet_name"

  // Determine the start date for retrieving events based on existing sheet data
  var startDate = getStartDate(sheet, "November 10, 2024 00:00:00"); // Default start date if no data in sheet
  
  // Set the end date to the current day at 11:59 PM for retrieving today's events
  var endDate = new Date();
  endDate.setHours(23, 59, 59, 999); // Set end time to the end of the day

  var cal = CalendarApp.getCalendarById("[email protected]"); // Get Google Calendar by its ID
  var events = cal.getEvents(startDate, endDate); // Fetch events within the specified date range

  var lastRow = sheet.getLastRow(); // Get the last row in the sheet to start appending events
  var firstUpdateRow = lastRow + 1; // Row where new event data will begin

  // Loop through each event to extract details and log them in the sheet
  events.forEach((event, i) => {
    // Create an object to store details of the current event
    var eventDetails = {
      title: event.getTitle(), // Event title
      start_time: event.getStartTime(), // Event start time
      end_time: event.getEndTime(), // Event end time
      location: event.getLocation(), // Event location
      description: event.getDescription(), // Event description
      colorOfEvent: event.getColor(), // Color ID of the event
      duration: calculateDurationInMinutes(event.getStartTime(), event.getEndTime()), // Duration in minutes
      dateOfEvent: formatDate(event.getStartTime()) // Formatted event date as "YYYY-MM-DD"
    };
    
    // Write the current event's details to the sheet in the appropriate row
    writeEventDetailsToSheet(sheet, firstUpdateRow + i, eventDetails);
  });

  Logger.log("Events have been added to the Spreadsheet"); // Log completion message
}

This main function orchestrates the entire process of fetching and logging events, leveraging helper functions for formatting, duration calculation, start date selection, and sheet writing.

How to Use It

To deploy this code in a Google Sheet and have it automatically pull events from Google Calendar, follow these steps:

Step 1: Open Google Apps Script

  1. Open your Google Sheet where you want to log the calendar events.
  2. Go to Extensions > Apps Script. This opens the Google Apps Script editor.

Step 2: Add the Script Code

  1. In the Apps Script editor, delete any code in the script editor and paste the entire code from above.
  2. Make sure to replace "[email protected]" in CalendarApp.getCalendarById("[email protected]"); with your own Google Calendar ID (you can use your email if it’s the default calendar or the specific calendar ID if using a different one).

Step 3: Save the Project

  1. Click File > Save, name the project (e.g., “Calendar Event Logger”), and save it.

Step 4: Set Permissions

  1. Click Run > getEvents to test the script for the first time.
  2. You’ll be prompted to authorize access. Click Review Permissions, sign in with your Google account, and grant the necessary permissions.
    • Since the script interacts with Google Calendar and Google Sheets, you’ll need to allow access to both.
  3. After authorizing, the script will run and start adding events to your “work_tracker” sheet (or the sheet name you specified).

Step 5: Add a Trigger to Automate the Script

To have this code run automatically on a schedule (e.g., daily), set up a trigger.

  1. In the Apps Script editor, click on the clock icon on the left sidebar (or go to Triggers > Manage triggers).
  2. Click + Add Trigger in the bottom right corner.
  3. For Choose which function to run, select getEvents.
  4. For Select event source, choose Time-driven.
  5. Set up the frequency (e.g., daily, hourly) depending on how often you want the script to log events.
  6. Click Save.

Step 6: Test and Verify

  1. Go back to your Google Sheet to check if events are logged as expected in the “work_tracker” sheet.
  2. Manually run getEvents again if you want to check functionality before the trigger runs automatically.

Optional: Customizing Event Columns

To customize the columns or adjust other settings (e.g., colors), edit the writeEventDetailsToSheet function in the code.

A Few Other Things

Permissions

The script requires access permissions to Google Calendar and Google Sheets. I believe in the practice of the principle of least privilege - an information security concept in which a user is given the minimum levels of access – or permissions – needed to perform his/her job functions.

While this is managed in the Script by limiting the Scripts access to only the Google Sheet that it is deployed for, it currently has complete access to the Calendar with edit access - ideally read only should be enough unless you want to also write back to the Calendar - there is way to do this via Calendar API settings.

Things To Try

While this is a good starting point, what would be exciting is if we could pull the project that the calendar event was for without having to explicitly mention that in the event name.

Color IDs could work - I am big fan of time blocking my calendar and the way I organize that is by given a custom color to each of my events, each color is mapped to a project label.

Unfortunately, the Google Calendar API does not provide for fetching the color labels, just the color IDs, and the background and foreground colors. A solution could be to maintain a dictionary of color ID and project label mapping in the script.

Go ahead, give the script a try and let me know what you think.

The Complete Script

/*** @OnlyCurrentDoc */

  
// Function to format a date as "YYYY-MM-DD" for consistent display

function formatDate(date) {

  var year = date.getFullYear(); // Extract the full year (e.g., 2024)

  var month = String(date.getMonth() + 1).padStart(2, '0'); // Month is zero-based, so add 1; pad with 0 if single digit

  var day = String(date.getDate()).padStart(2, '0'); // Extract the day of the month; pad with 0 if single digit

  return `${year}-${month}-${day}`; // Format as "YYYY-MM-DD"

}

// Function to calculate the duration in minutes given start and end times

function calculateDurationInMinutes(startTime, endTime) {

  var start = new Date(startTime); // Convert start time to Date object

  var end = new Date(endTime); // Convert end time to Date object

  var differenceInMs = end - start; // Calculate the difference in milliseconds

  return differenceInMs / (1000 * 60); // Convert milliseconds to minutes

}

// Function to determine the start date for event retrieval based on the sheet's last row

function getStartDate(sheet, defaultDate) {

  var lastRow = sheet.getLastRow(); // Get the last row with data in the sheet

  if (lastRow > 1) { // Check if there is existing data (assumes headers in the first row)

    var lastDate = sheet.getRange(lastRow, 3).getValue(); // Get the latest "Start Time" from column 3 in the last row

    var startDate = new Date(lastDate); // Convert last date to a Date object

    startDate.setDate(startDate.getDate() + 1); // Set start date to one day after the last date

    startDate.setHours(0, 0, 0, 0); // Set time to 12:00 AM (start of the day)

    return startDate; // Return the calculated start date

  } else {

    return new Date(defaultDate); // If no data, use the specified default date

  }

}

// Function to write the details of a single event to the sheet at a specified row

function writeEventDetailsToSheet(sheet, row, eventDetails) {

  // Set values in the sheet for each event detail in the specified columns

  sheet.getRange(row, 1).setValue(eventDetails.dateOfEvent); // Column 1: Date of Event

  sheet.getRange(row, 2).setValue(eventDetails.title); // Column 2: Event Title

  sheet.getRange(row, 3).setValue(eventDetails.start_time); // Column 3: Start Time

  sheet.getRange(row, 4).setValue(eventDetails.end_time); // Column 4: End Time

  sheet.getRange(row, 5).setValue(eventDetails.duration); // Column 5: Duration in minutes

  sheet.getRange(row, 6).setValue(eventDetails.colorOfEvent); // Column 6: Event Color

  sheet.getRange(row, 7).setValue(eventDetails.description); // Column 7: Event Description

}

// Main function to retrieve events from Google Calendar and log them into the spreadsheet

function getEvents() {

  var ss = SpreadsheetApp.getActiveSpreadsheet(); // Access the active spreadsheet

  var sheet = ss.getSheetByName("google_sheet_name_placeholder"); // Select the sheet by name "work_tracker"

  // Determine the start date for retrieving events based on existing sheet data

  var startDate = getStartDate(sheet, "November 10, 2024 00:00:00"); // Default start date if no data in sheet

  // Set the end date to the current day at 11:59 PM for retrieving today's events

  var endDate = new Date();

  endDate.setHours(23, 59, 59, 999); // Set end time to the end of the day

  var cal = CalendarApp.getCalendarById("calendar_email_id_placeholder"); // Get Google Calendar by its ID

  var events = cal.getEvents(startDate, endDate); // Fetch events within the specified date range

  

  var lastRow = sheet.getLastRow(); // Get the last row in the sheet to start appending events

  var firstUpdateRow = lastRow + 1; // Row where new event data will begin

  

  // Loop through each event to extract details and log them in the sheet

  events.forEach((event, i) => {

    // Create an object to store details of the current event

    var eventDetails = {

      title: event.getTitle(), // Event title

      start_time: event.getStartTime(), // Event start time

      end_time: event.getEndTime(), // Event end time

      location: event.getLocation(), // Event location

      description: event.getDescription(), // Event description

      colorOfEvent: event.getColor(), // Color ID of the event

      duration: calculateDurationInMinutes(event.getStartTime(), event.getEndTime()), // Duration in minutes

      dateOfEvent: formatDate(event.getStartTime()) // Formatted event date as "YYYY-MM-DD"

    };

    // Write the current event's details to the sheet in the appropriate row

    writeEventDetailsToSheet(sheet, firstUpdateRow + i, eventDetails);

  });

  

  Logger.log("Events have been added to the Spreadsheet"); // Log completion message

}