Organize Your Meetings: Transfer Google Calendar Events to Google Sheets

I wish there was a tool to record and track my meetings. I have found a way to handle all my meetings effectively using the Google app script.

{getToc} $title={Table of Contents} $count={false} $expanded={false}

In this blog, we will see, how to fetch Google Calendar events and save them in a Google sheet.

So what is Google Apps Script ?

It is a scripting platform developed by Google for lightweight application development in the Google Workspace platform.

Program Flow

Let’s see step by step process to fetch calendar events and then save it to sheets

Step 1: Create a new Google sheet and access the app script editor.

Create a new Google sheet
Go to Extensions >> Appscript

3. Appscipt editor will be opened on a new tab.

Step 2: Create a function to fetch all calendar events.

1. Copy and paste the below function into the app script editor.



function getMeetings() {
  // Get the spreadsheet and sheet
  const sheet = SpreadsheetApp.getActiveSheet();

// Get the start date (YYYY-MM-DD format)
const startDate = '2024-07-01'; // Replace with your desired start date

// Get today's date
const today = new Date();

// Convert dates to all-day event format
const startTime = new Date(startDate);
const endTime = new Date(today.getFullYear(), today.getMonth(), today.getDate() + 1); // Add a day for inclusive end

// Replace 'YOUR_CALENDAR_ID' with your actual calendar ID (it's your Gmail id)
const calendar = CalendarApp.getCalendarById('YOUR_CALENDAR_ID');

// Get events between the specified start date and today
const events = calendar.getEvents(startTime, endTime);

  // Clear existing data (optional)
  sheet.clearContents();
  
// Change formatting as per the need.
sheet.getRange("A1:C1").setValues([["Title", "Start Time", "End Time"]]).setFontFamily("Nunito").setFontWeight("bold").setFontSize(14);
  // Process and add events data to sheet
  events.forEach(event => {
    const title = event.getTitle();
    // Event might not have a start/end time if it's an all-day event
    const start = event.getStartTime() || startTime;
    const end = event.getEndTime() || endTime;
    const data = [title, start, end];
    sheet.appendRow(data);
  });
}

The above code snippet fetches all the calendar events from a specific start date to today. Replace your YOUR_CALENDAR_ID with your calendar ID found in the calendar setting.

Step 3: Save and Run the code

1. Click on the play button to run the code.
2. If the code execution is successful, you will see the calendar events fetched in the sheet.

Step 4: To automate the process of fetching the events.

1. To automate this process you can add time-based triggers, where the script will be executed automatically based on time set.
2. The trigger menu is in the editor's left panel, as shown in the image below.

trigger menu is in the editor's left panel

3. Click on Triggers, a new page will open.

4. Click on add Trigger button (present at bottom right), and a dialog box will open as below

5. Select the event source as Time-driven and the type of time based on your specific need, here I have chosen an hour and interval as every hour.

6. Change the Failure notification settings as per convenience.

7. Click on Save

Final Outcome:

Bonus ✨

To fetch specific months event google sheet, add the below script



// Get the desired month (1 = January, 12 = December)
const desiredMonth = 7; // Change this to the month you want (e.g., 7 for July)

// Calculate the start and end date for the month
const startDate = new Date(YEAR, desiredMonth - 1, 1); // Year needs to be defined
const endDate = new Date(YEAR, desiredMonth, 0); // 0 represents the last day of previous month

// Convert dates to all-day events for compatibility with getEvents
const startTime = new Date(startDate.getFullYear(), startDate.getMonth(), startDate.getDate());
const endTime = new Date(endDate.getFullYear(), endDate.getMonth(), endDate.getDate() + 1); // Add a day for inclusive end

// Rest of the code...

// ... (rest of the script to get events and write to sheet)

To fetch events from a date range



// Get the start and end date (YYYY-MM-DD format)
const startDate = '2024-07-01'; // Replace with your desired start date
const endDate = '2024-07-15'; // Replace with your desired end date

// Convert dates to all-day event format
const startTime = new Date(startDate);
const endTime = new Date(endDate.getFullYear(), endDate.getMonth(), endDate.getDate() + 1); // Add a day for inclusive end

// Replace 'YOUR_CALENDAR_ID' with your actual calendar ID
const calendar = CalendarApp.getCalendarById('YOUR_CALENDAR_ID');

// Get events between the specified dates
const events = calendar.getEvents(startTime, endTime);

// ... (Rest of the code to process and write events to sheet)

To fetch events for a specific date



// Get the desired date (YYYY-MM-DD format)
const desiredDate = '2024-07-10'; // Replace with your desired date

// Convert desired date to all-day event format
const startDate = new Date(desiredDate);
const endDate = new Date(startDate.getFullYear(), startDate.getMonth(), startDate.getDate() + 1); // Add a day for inclusive end

// Replace 'YOUR_CALENDAR_ID' with your actual calendar ID
const calendar = CalendarApp.getCalendarById('YOUR_CALENDAR_ID');

// Get events for the specific date
const events = calendar.getEvents(startDate, endDate);

// ... (Rest of the code to process and write events to sheet)

Conclusion

Following the above steps you can successfully fetch the calendar events and save the day.

Please share. Thanks and Have a good day!