Biomathematicus

Science, Technology, Engineering, Art, Mathematics

Being able to share a calendar is a fundamental aspect of coordination in many aspects of modern life. Outlook offers a mechanism to export calendars here. But this does not solve the problem of being able to customize what is shared between Outlook and Google Calendar.

Here is the solution in search of a problem: You need to create a VBA (Visual Basic for Applications) script that exports calendar entries from Outlook into a format compatible with Google Calendar. After that, you need a python file to import a CSV file into Google Calendar. Functionality aside, this is simply an interesting programming problem.

Here’s a step-by-step guide and example scripts to achieve this:

VBA Script:

  1. Enable Developer Mode in Outlook and Excel: You need to enable the Developer tab in both applications to access the VBA editor.
  2. Save the file as an .xlsm (macro-enabled) file.
  3. Access the VBA Editor: In Outlook or Excel, press ALT + F11 to open the VBA editor.
  4. Insert a Module: In the VBA editor, right-click on VBAProject (Outlook) or VBAProject (Excel) in the left pane, choose Insert, and then click Module.

The following script includes functions to extract calendar events from Outlook between 01/01/2024 and 08/31/2024, and format and export these events to a new Excel workbook.

Option Explicit

Sub ExportCalendarEntriesToExcel()
    Dim olApp As Object
    Dim olNamespace As Object
    Dim olFolder As Object
    Dim olItems As Object
    Dim olItem As Object
    Dim i, j As Long
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set olApp = CreateObject("Outlook.Application")
    Set olNamespace = olApp.GetNamespace("MAPI")
    Set olFolder = olNamespace.GetDefaultFolder(9) ' 9 is the Calendar folder
    Set olItems = olFolder.Items
    
    ' Optional: Sort or filter items
    olItems.Sort "[Start]"
    olItems.IncludeRecurrences = True
    
    ' Create a new Excel workbook
    Set wb = Application.Workbooks.Add
    Set ws = wb.Sheets(1)
    
    ' Define the headers for Google Calendar format
    With ws
        .Cells(1, 1).Value = "Subject"
        .Cells(1, 2).Value = "Start Date"
        .Cells(1, 3).Value = "Start Time"
        .Cells(1, 4).Value = "End Date"
        .Cells(1, 5).Value = "End Time"
        .Cells(1, 6).Value = "All Day Event"
        .Cells(1, 7).Value = "Description"
        .Cells(1, 8).Value = "Location"
    End With
    
    i = 2 ' Start filling from the second row
    j = 0
    For Each olItem In olItems
        On Error GoTo NextObject:
        DoEvents
        j = j + 1
        If (olItem.Start >= CDate("1/5/2024") And olItem.End <= CDate("8/31/2024")) Then
            With ws
                .Cells(i, 1).Value = olItem.Subject
                .Cells(i, 2).Value = Format(olItem.Start, "mm/dd/yyyy")
                .Cells(i, 3).Value = Format(olItem.Start, "hh:mm AM/PM")
                .Cells(i, 4).Value = Format(olItem.End, "mm/dd/yyyy")
                .Cells(i, 5).Value = Format(olItem.End, "hh:mm AM/PM")
                .Cells(i, 6).Value = IIf(olItem.AllDayEvent, "True", "False")
                .Cells(i, 7).Value = "Body" 'olItem.Body
                .Cells(i, 8).Value = "Location" 'olItem.Location
NextObject:
                i = i + 1
            End With
        End If
        If j > 10000 Then
            Exit For
        End If
        Debug.Print (j)
    Next olItem
    
    ' Optionally, save the workbook
    'wb.SaveAs "Path to save the file", FileFormat:=xlCSV
    
    ' Clean up
    wb.Close SaveChanges:=True
    Set olItem = Nothing
    Set olItems = Nothing
    Set olFolder = Nothing
    Set olNamespace = Nothing
    Set olApp = Nothing
    
End Sub

This VBA script does a simple extraction and assumes that Outlook and Excel are installed on the same machine. Also, this script may require adjustments based on the version of Microsoft Office you are using.

Python Script:

To programmatically import a CSV file into Google Calendar, you need to use the Google Calendar API. However, the API itself does not support direct CSV imports. You’ll need to parse the CSV file, extract the event details, and then use the API to create events in Google Calendar.

1. Setup Google API

First, you need to set up access to the Google Calendar API:

  • Go to the Google Developers Console.
  • Create a new project.
  • Enable the Google Calendar API for your project.
  • Create credentials (OAuth 2.0 Client ID) for your application.
  • Download the credentials file (rename it to credentials.json).

2. Install Required Libraries

Install the Google Client Library and other necessary packages:

pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib pandas

3. Python Script to Import CSV and Create Calendar Events

Create a Python script that reads the CSV file, parses its contents, and creates events in Google Calendar using the API.

import datetime
import pandas as pd
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build

# Function to authenticate and create a service
def google_calendar_service():
    SCOPES = ['https://www.googleapis.com/auth/calendar']
    creds = None
    # The file token.json stores the user's access and refresh tokens, and is
    # created automatically when the authorization flow completes for the first
    # time.
    try:
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    except:
        pass
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        # Save the credentials for the next run
        with open('token.json', 'w') as token:
            token.write(creds.to_json())
    return build('calendar', 'v3', credentials=creds)

# Read CSV and parse events
def import_events_from_csv(csv_path):
    df = pd.read_csv(csv_path)
    service = google_calendar_service()
    for index, row in df.iterrows():
        event = {
            'summary': row['Subject'],
            'location': row['Location'],
            'description': row['Description'],
            'start': {
                'dateTime': datetime.datetime.strptime(f"{row['Start Date']} {row['Start Time']}", '%m/%d/%Y %I:%M %p').isoformat(),
                'timeZone': 'America/Los_Angeles',
            },
            'end': {
                'dateTime': datetime.datetime.strptime(f"{row['End Date']} {row['End Time']}", '%m/%d/%Y %I:%M %p').isoformat(),
                'timeZone': 'America/Los_Angeles',
            },
        }
        event = service.events().insert(calendarId='primary', body=event).execute()
        print(f"Event created: {event.get('htmlLink')}")

# Example CSV path
csv_path = 'path_to_your_csv_file.csv'
import_events_from_csv(csv_path)

IMPORTANT:

  • This script uses OAuth 2.0 for authentication. It will open a new browser window or tab to request access permissions the first time it runs. Run it in debug mode, or you might miss the authentication window.
  • Make sure to adjust the timezone in the event creation code as per your local timezone.

This solution provides a way to programmatically create events in Google Calendar from a CSV file, integrating the necessary steps to handle authentication and event creation through the Google Calendar API.

biomathematicus