How to Convert Your Google Calendar Events To Microsoft Excel

 
2021_Q1_Google Calendar blog_Feb_Image.png

Skill Level: Intermediate

If you’re just beginning your macro-building journey, check this Quick Start Guide out first!

Using a calendar to track and organize life is a great tool.  Google’s calendar is very flexible with multiple calendars, repeat events, reminders and more.  Recently I needed to do some analysis on my calendar to tabulate how many events and activities I had done.  I soon realized there was no easy way to summarize or list view the calendar.  I wanted to get the information exported to Excel so I could complete my analysis.  After some searching on the Internet, I was surprised to find there was no easy solution.

Google does allow you to search and maintains your calendar forever.  The only mechanism to get the data out is through the Import/Export feature.  You can export to an ics file for each calendar you have in your account.  However, the ics file format is not friendly to anyone!

If you search the Internet, you will find there are monthly services and software tools you can purchase to convert an ics file to a usable format.  The idea of paying for such a tool did not make sense since the ics file was fundamentally just a text file.  I embraced my passion for Excel and created a macro to do the work for me.  In the overview below, you can follow my steps to get your calendar and create an Excel spreadsheet.  Then, create a macro and insert the code provided for the function and subroutine.  You can feel free to customize and optimize from there!  Enjoy!

STEP 1 – Export your Google calendar

Open your Google calendar and click the gear icon for Settings.  Next click on the left menu option for Import & Export.  From the screen, select one or more calendars to export and click the “Export” button.  You will get an ics file for each calendar and need to process each one separately.

 

STEP 2 – Open in Excel

Launch Excel and choose FILE > OPEN.  Go to the “Browse” file selection window and change the file filter to look at All Files.  The file filter is in the bottom right corner if you aren’t familiar with that screen.

Browse to your ics file.  Select and open the file.  Excel will launch the text conversion wizard.  Just leave the default options and click Next until you get to Finish.  The resulting Excel workbook should have one sheet and the data will be in column A.  It will likely be a long sheet (lots of rows) if your calendar had a lot of events.  I tested with over 20,000 rows and the macro processed in under a minute.

 

STEP 3 – Create a macro

With your workbook open, go to the “View” ribbon can select to “View Macros”.  From the popup window, you can create a new macro and name it.  Copy and paste the macro code provided below.  You will create a function and a subroutine.  Then, run the macro!

The macro will present a popup with the total number of rows found in the ics input.This is a simple check in case you end up with a blank row or something unusual.As the macro runs, the status bar at the bottom left of Excel will update as each event is processed.The result will be in the “Output” worksheet.

 
' Function to check if a sheet exists
Function WorksheetExists(sName As String) As Boolean
    WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)")
End Function

Sub Convert_ics()
'
' Convert Google exported ics file to Excel column sheet
' Created 2-7-2021 by Infotect Design Solutions
'
Dim OutputSheetName As String
Dim ColonPos As Integer

' Main variables
OutputSheetName = "Output"
CurrCell = ""
EndCalendar = "END:VCALENDAR"
EventBegin = "BEGIN:VEVENT"
EventEnd = "END:VEVENT"
EventDateStartPre = "DTSTART"
EventDataEndPre = "DTEND"
EventDescPre = "DESCRIPTION:"
EventSummPre = "SUMMARY:"
EventTracker = 0
Set CalSheet = Sheets(1)
CalCol = "A"    ' The ics sheet will have only one column when converted into Excel
CalLastRow = 0
RowIndex = 1
CalCounter = 0

' Output sheet tracking variables
Ocurrcol = 1
Ocurrrow = 2

' Output sheet - clear it or create it
' TIP - If you want to tweak the output, rename the tab after running the macro to preserve
If WorksheetExists(OutputSheetName) Then
    Sheets(OutputSheetName).Select
    Cells.Clear
Else
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = OutputSheetName
    Sheets(OutputSheetName).Select
End If

' Setup output column headers
Range("A1").Select
ActiveCell.FormulaR1C1 = "Begin_Date"
Range("B1").Select
ActiveCell.FormulaR1C1 = "End_Date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Description"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Summary"
Range("A2").Select
    
' Macro assume first sheet is ICS output and data is in column A
Sheets(1).Select
Range("A1").Select

With ActiveSheet
    CalLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    MsgBox CalLastRow
End With
    
CalRange = "A1:A" & CalLastRow
    
For RowIndex = 1 To CalLastRow
    CurrCell = Cells(RowIndex, CalCol)
    If CurrCell = EventBegin Then
        EventTracker = 1 ' start processing an event
    Else
        If EventTracker = 1 Then
            ' The following IF statements must be in the order of the events in the ics sheet
            If Left(CurrCell, Len(EventDateStartPre)) = EventDateStartPre Then   ' Get event start date and time
                ColonPos = InStr(CurrCell, ":")
                Sheets(OutputSheetName).Cells(Ocurrrow, Ocurrcol).Value = Right(CurrCell, Len(CurrCell) - ColonPos)
                Ocurrcol = Ocurrcol + 1
            End If
            If Left(CurrCell, Len(EventDataEndPre)) = EventDataEndPre Then  ' Get event end date and time
                ColonPos = InStr(CurrCell, ":")
                Sheets(OutputSheetName).Cells(Ocurrrow, Ocurrcol).Value = Right(CurrCell, Len(CurrCell) - ColonPos)
                Ocurrcol = Ocurrcol + 1
            End If
            If Left(CurrCell, Len(EventDescPre)) = EventDescPre Then    ' Get event description
                Sheets(OutputSheetName).Cells(Ocurrrow, Ocurrcol).Value = Right(CurrCell, Len(CurrCell) - Len(EventDescPre))
                Ocurrcol = Ocurrcol + 1
            End If
            If Left(CurrCell, Len(EventSummPre)) = EventSummPre Then    ' Get event summary
                Sheets(OutputSheetName).Cells(Ocurrrow, Ocurrcol).Value = Right(CurrCell, Len(CurrCell) - Len(EventSummPre))
                Ocurrcol = Ocurrcol + 1
            End If
            If CurrCell = EventEnd Then
                EventTracker = 0 ' end processing an event
                Ocurrrow = Ocurrrow + 1
                Ocurrcol = 1
                CalCounter = CalCounter + 1
                Application.StatusBar = "Calendar items processed " & CalCounter
            End If
        End If
    End If
    
Next RowIndex
        
End Sub

Thanks to everyone who shares the Excel VBA talents on the Internet, and I hope this helps contribute to the community!

Infotect Design Solutions has over 20 years’ experience helping companies achieve their IT success. Infotect was founded to bridge the gap between an organization's technology needs and the availability of reliable, value-based consulting services. If you would like to see if we are right for your company, or get your company’s security score, please call us at (813) 630-2400 or email us at Startfresh@infotectdesign.com.

-Blog written by Steve Williams

President & Founder of Infotect Design Solutions

 
 
Guest UserCo-Managed IT, Infotect, Infotech, Information Technology, IT support, Partnership19 Comments