Using Vba to add an Calendar event in my Google calendar

K

Krassluca

New Member
Joined
Jan 13, 2025
Messages
3
Office Version
  1. 2021
Platform
  1. Windows
Hey Everyone,
I'm working on a project where I need to automatically synchronize calendar events from an Excel spreadsheet to a Google Calendar or Outlook Calendar using VBA. However, I’ve encountered a couple of challenges and would appreciate any guidance or suggestions from those of you who have worked on similar projects.

My Goal:​

  • I want to automatically transfer data from an Excel spreadsheet (which has columns like Event Name, Start Time, End Time, Location, etc.) to either Google Calendar or Outlook Calendar.
  • The process should be automated through VBA, so I don't have to manually enter events into the calendar.

The Challenges:​

  1. Google Calendar API (OAuth2 authentication):
    • I’ve tried to integrate VBA with the Google Calendar API, but I’m running into issues with authentication and API calls. I’ve followed guides on setting up OAuth2 credentials but I keep getting an error like "Defined function is invalid."
    • It seems like the function to make the API request is not working as expected, but I’m not sure where I’m going wrong.
    • I need help understanding how to authenticate using OAuth2, and how to send data (events) from Excel to Google Calendar.
    • Example of Code I’m Using:
Hi everyone,

I'm working on a project where I need to automatically synchronize calendar events from an Excel spreadsheet to a Google Calendar or Outlook Calendar using VBA. However, I’ve encountered a couple of challenges and would appreciate any guidance or suggestions from those of you who have worked on similar projects.

My Goal:​

  • I want to automatically transfer data from an Excel spreadsheet (which has columns like Event Name, Start Time, End Time, Location, etc.) to either Google Calendar or Outlook Calendar.
  • The process should be automated through VBA, so I don't have to manually enter events into the calendar.

The Challenges:​

  1. Google Calendar API (OAuth2 authentication):
    • I’ve tried to integrate VBA with the Google Calendar API, but I’m running into issues with authentication and API calls. I’ve followed guides on setting up OAuth2 credentials but I keep getting an error like "Defined function is invalid."
    • It seems like the function to make the API request is not working as expected, but I’m not sure where I’m going wrong.
    • Also if there is a simple road with just using my google calendar Id it would also help
    • I need help understanding how to authenticate using OAuth2, and how to send data (events) from Excel to Google Calendar.
    • Example of Code I’m Using:
    • Excel Formula: 
      [/Private Sub CreateNewEvent()
    Dim objGoogle As GoogleOAuth2
    Set objGoogle = GetSession
    Dim objStartDate As EventDateTime
    Set objStartDate = New EventDateTime
    objStartDate.DateTimeValue = JsonConverter.ConvertToIso(CDate(#4/26/2023 11:00:00 PM#)) 'Start time
    Dim objEndDate As EventDateTime
    Set objEndDate = New EventDateTime
    objEndDate.DateTimeValue = JsonConverter.ConvertToIso(CDate(#4/27/2023 6:00:00 AM#)) 'End time
    Dim objEvent As EventItem
    Set objEvent = New EventItem
    With objEvent
        .Summary = "Clander" 'Name of event to be created
        Set .StartDateTime = objStartDate 'Start time
        Set .EndDateTime = objEndDate 'End time
    End With
    Debug.Print "Id of the new event is: " & objGoogle.EventsResource.Insert(CalendarId:="primary", EventItem:=objEvent).ID
End Sub

Public Function GetSession() As GoogleOAuth2
    Dim objGoogleOAuth2 As GoogleOAuth2
    Set objGoogleOAuth2 = New GoogleOAuth2
    With objGoogleOAuth2
        .ApplicationName = "krassluca2@gmail.com"
        .ClientID = "Client-Id"
        .ClientSecret = "Client-Secret"
        .Scopes = Array("https://www.googleapis.com/auth/calendar.events")
        .AuthorizeOAuth2
    End With
    Set GetSession = objGoogleOAuth2
End Function

Public Sub LogOut()
    Dim objGoogleOAuth2 As GoogleOAuth2
    Set objGoogleOAuth2 = New GoogleOAuth2
    With objGoogleOAuth2
        .ApplicationName = "GoogleCalendar"
        .ClientID = "Client-Id"
        .LogOut Refresh_Token
    End With
End Sub
Public Sub ListEvents()
    Dim objSession As GoogleOAuth2
    Set objSession = GetSession()
    If Not objSession Is Nothing Then
        Dim colEvents As InstancesAndListResponse
        Dim strCalendarId As String
        strCalendarId = "primary"
        On Error Resume Next
        Set colEvents = objSession.EventsResource.List(CalendarId:=strCalendarId)
        If Err.Number <> 0 Then
            MsgBox Err.Description, vbExclamation, "Error"
        End If
        On Error GoTo 0
        If Not colEvents Is Nothing Then
            Dim objEvent As EventItem
            Dim i As Long
            If colEvents.Items.Count > 0 Then
                For i = 1 To colEvents.Items.Count
                    Set objEvent = colEvents.Items.Item(i)
                    Debug.Print objEvent.ID
                    Debug.Print objEvent.Summary
                Next
                If colEvents.NextPageToken <> vbNullString Then Call LoopThroughEventsByPageToken(objSession, colEvents.NextPageToken)
            End If
        End If
    End If
End Sub

Private Sub LoopThroughEventsByPageToken(ByVal Session As GoogleOAuth2, ByVal PageToken As String, Optional ByVal CalendarId As String = "primary")
    If Not Session Is Nothing And PageToken <> vbNullString Then
        Dim colEvents As InstancesAndListResponse
        On Error Resume Next
        Set colEvents = Session.EventsResource.List(CalendarId:=CalendarId, PageToken:=PageToken)
        If Err.Number <> 0 Then
            MsgBox Err.Description, vbExclamation, "Error"
        End If
        On Error GoTo 0
        If Not colEvents Is Nothing Then
            Dim objEvent As EventItem
            Dim i As Long
            If colEvents.Items.Count > 0 Then
                For i = 1 To colEvents.Items.Count
                    Set objEvent = colEvents.Items.Item(i)
                    Debug.Print objEvent.ID
                    Debug.Print objEvent.Summary
                Next
                Dim strPageToken As String
                strPageToken = colEvents.NextPageToken
                While strPageToken <> vbNullString
                    Call LoopThroughEventsByPageToken(Session, strPageToken)
                Wend
            End If
        End If
    End If
End Sub]
[/LIST]
[/LIST]
thanks for the help!!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
I've not had to use the Google API for this, and there are other on this forum who are far more knowledgeable than I am on the topic, but I would make two points that you're welcome to disregard if not applicable.

1. Outlook Calendar - if you have the desktop version of Outlook, then adding events to that calendar is trivial (assuming you are not using this brand new version of Outlook that has VBA disabled). If that's a solution, that should be very straight forward.
2. ICS files - from memory, Google Calendar supports ICS files which might be a way of getting the event detail into the calendar on a semi-automated basis. It avoids the headache of having to use the API, but then it isn't fully automated. ICS files, from memory, are very easy to generate.

Just my two cents.
 
Upvote 0
Krassluca said:
I’ve tried to integrate VBA with the Google Calendar API, but I’m running into issues with authentication and API calls. I’ve followed guides on setting up OAuth2 credentials but I keep getting an error like "Defined function is invalid."
Click to expand...

Post your code which causes that error.

Also post your code for the GoogleOAuth2 AuthorizeOAuth2 method and the EventsResource.Insert method.
 
Upvote 0
Krassluca said:
Hey Everyone,
I'm working on a project where I need to automatically synchronize calendar events from an Excel spreadsheet to a Google Calendar or Outlook Calendar using VBA. However, I’ve encountered a couple of challenges and would appreciate any guidance or suggestions from those of you who have worked on similar projects.

My Goal:​

  • I want to automatically transfer data from an Excel spreadsheet (which has columns like Event Name, Start Time, End Time, Location, etc.) to either Google Calendar or Outlook Calendar.
  • The process should be automated through VBA, so I don't have to manually enter events into the calendar.

The Challenges:​

  1. Google Calendar API (OAuth2 authentication):
    • I’ve tried to integrate VBA with the Google Calendar API, but I’m running into issues with authentication and API calls. I’ve followed guides on setting up OAuth2 credentials but I keep getting an error like "Defined function is invalid."
    • It seems like the function to make the API request is not working as expected, but I’m not sure where I’m going wrong.
    • I need help understanding how to authenticate using OAuth2, and how to send data (events) from Excel to Google Calendar.
    • Example of Code I’m Using:
Hi everyone,

I'm working on a project where I need to automatically synchronize calendar events from an Excel spreadsheet to a Google Calendar or Outlook Calendar using VBA. However, I’ve encountered a couple of challenges and would appreciate any guidance or suggestions from those of you who have worked on similar projects.

My Goal:​

  • I want to automatically transfer data from an Excel spreadsheet (which has columns like Event Name, Start Time, End Time, Location, etc.) to either Google Calendar or Outlook Calendar.
  • The process should be automated through VBA, so I don't have to manually enter events into the calendar.

The Challenges:​

  1. Google Calendar API (OAuth2 authentication):
    • I’ve tried to integrate VBA with the Google Calendar API, but I’m running into issues with authentication and API calls. I’ve followed guides on setting up OAuth2 credentials but I keep getting an error like "Defined function is invalid."
    • It seems like the function to make the API request is not working as expected, but I’m not sure where I’m going wrong.
    • Also if there is a simple road with just using my google calendar Id it would also help
    • I need help understanding how to authenticate using OAuth2, and how to send data (events) from Excel to Google Calendar.
    • Example of Code I’m Using:
    • Excel Formula: 
      [/Private Sub CreateNewEvent()
    Dim objGoogle As GoogleOAuth2
    Set objGoogle = GetSession
    Dim objStartDate As EventDateTime
    Set objStartDate = New EventDateTime
    objStartDate.DateTimeValue = JsonConverter.ConvertToIso(CDate(#4/26/2023 11:00:00 PM#)) 'Start time
    Dim objEndDate As EventDateTime
    Set objEndDate = New EventDateTime
    objEndDate.DateTimeValue = JsonConverter.ConvertToIso(CDate(#4/27/2023 6:00:00 AM#)) 'End time
    Dim objEvent As EventItem
    Set objEvent = New EventItem
    With objEvent
        .Summary = "Clander" 'Name of event to be created
        Set .StartDateTime = objStartDate 'Start time
        Set .EndDateTime = objEndDate 'End time
    End With
    Debug.Print "Id of the new event is: " & objGoogle.EventsResource.Insert(CalendarId:="primary", EventItem:=objEvent).ID
End Sub

Public Function GetSession() As GoogleOAuth2
    Dim objGoogleOAuth2 As GoogleOAuth2
    Set objGoogleOAuth2 = New GoogleOAuth2
    With objGoogleOAuth2
        .ApplicationName = "krassluca2@gmail.com"
        .ClientID = "Client-Id"
        .ClientSecret = "Client-Secret"
        .Scopes = Array("https://www.googleapis.com/auth/calendar.events")
        .AuthorizeOAuth2
    End With
    Set GetSession = objGoogleOAuth2
End Function

Public Sub LogOut()
    Dim objGoogleOAuth2 As GoogleOAuth2
    Set objGoogleOAuth2 = New GoogleOAuth2
    With objGoogleOAuth2
        .ApplicationName = "GoogleCalendar"
        .ClientID = "Client-Id"
        .LogOut Refresh_Token
    End With
End Sub
Public Sub ListEvents()
    Dim objSession As GoogleOAuth2
    Set objSession = GetSession()
    If Not objSession Is Nothing Then
        Dim colEvents As InstancesAndListResponse
        Dim strCalendarId As String
        strCalendarId = "primary"
        On Error Resume Next
        Set colEvents = objSession.EventsResource.List(CalendarId:=strCalendarId)
        If Err.Number <> 0 Then
            MsgBox Err.Description, vbExclamation, "Error"
        End If
        On Error GoTo 0
        If Not colEvents Is Nothing Then
            Dim objEvent As EventItem
            Dim i As Long
            If colEvents.Items.Count > 0 Then
                For i = 1 To colEvents.Items.Count
                    Set objEvent = colEvents.Items.Item(i)
                    Debug.Print objEvent.ID
                    Debug.Print objEvent.Summary
                Next
                If colEvents.NextPageToken <> vbNullString Then Call LoopThroughEventsByPageToken(objSession, colEvents.NextPageToken)
            End If
        End If
    End If
End Sub

Private Sub LoopThroughEventsByPageToken(ByVal Session As GoogleOAuth2, ByVal PageToken As String, Optional ByVal CalendarId As String = "primary")
    If Not Session Is Nothing And PageToken <> vbNullString Then
        Dim colEvents As InstancesAndListResponse
        On Error Resume Next
        Set colEvents = Session.EventsResource.List(CalendarId:=CalendarId, PageToken:=PageToken)
        If Err.Number <> 0 Then
            MsgBox Err.Description, vbExclamation, "Error"
        End If
        On Error GoTo 0
        If Not colEvents Is Nothing Then
            Dim objEvent As EventItem
            Dim i As Long
            If colEvents.Items.Count > 0 Then
                For i = 1 To colEvents.Items.Count
                    Set objEvent = colEvents.Items.Item(i)
                    Debug.Print objEvent.ID
                    Debug.Print objEvent.Summary
                Next
                Dim strPageToken As String
                strPageToken = colEvents.NextPageToken
                While strPageToken <> vbNullString
                    Call LoopThroughEventsByPageToken(Session, strPageToken)
                Wend
            End If
        End If
    End If
End Sub]
[/LIST]
[/LIST]
thanks for the help!!
Click to expand...
What version of Excel are you using? It looks like you have to use at least Excel 2019 to run this macro.
 
Upvote 0
Dan_W said:
I've not had to use the Google API for this, and there are other on this forum who are far more knowledgeable than I am on the topic, but I would make two points that you're welcome to disregard if not applicable.

1. Outlook Calendar - if you have the desktop version of Outlook, then adding events to that calendar is trivial (assuming you are not using this brand new version of Outlook that has VBA disabled). If that's a solution, that should be very straight forward.
2. ICS files - from memory, Google Calendar supports ICS files which might be a way of getting the event detail into the calendar on a semi-automated basis. It avoids the headache of having to use the API, but then it isn't fully automated. ICS files, from memory, are very easy to generate.

Just my two cents.
Click to expand...
So I saw a video on how to use Google Drive to automatically save a CSV file and let google read and integrate it into the calendar. As for the Outlook problem, I just found a code for shared calenders. But the code is for my boss and I can't test it because I don't have a shared calendar.
VBA Code: 
Private Sub Outlook_Appointment_in_Shared_Calendar()

    Dim olApp As Outlook.Application
    Dim outNameSpace As Namespace
    Dim outSharedName As Outlook.Recipient
    Dim outCalendarFolder As MAPIFolder
    Dim olAppItem As Outlook.AppointmentItem
    Dim SharedMailboxEmail As String

    SharedMailboxEmail = "DSSTest@companyname.com"

    Set olApp = GetObject("", "Outlook.Application")
    On Error GoTo 0
    If olApp Is Nothing Then
    On Error Resume Next
    Set olApp = CreateObject("Outlook.Application")
    On Error GoTo 0
        If olApp Is Nothing Then
            MsgBox "Outlook is not available!"
            Exit Sub
        End If
    End If
    If Not IsOutlookRunning Then
        CreateObject("WScript.Shell").Run "outlook.exe", 3, False
        Set outApp = CreateObject("Outlook.Application")
    Else
        Set outApp = GetObject(, "Outlook.Application")
    End If
    Set outNameSpace = olApp.GetNamespace("MAPI")
    'Start at Namespace and get the DSSTest/DSSTest@companyname.com data file folder (whichever works)
    'Either
    Set outCalendarFolder = outNameSpace.Folders("DSSTest")
    'or
    Set outCalendarFolder = outNameSpace.Folders("krassluca2@gmail.com")
  
    'Get the calendar within DSSTest/DSSTest@companyname.com
    Set outCalendarFolder = outCalendarFolder.Folders("Calendar")
    'Confirm correct calendar
    Debug.Print outCalendarFolder.Name, outCalendarFolder.FolderPath

    'Create new appointment in DSSTest calendar
    Set olAppItem = outCalendarFolder.Items.Add(olAppointmentItem)
        LastRow = Sheet2.Range("A99999").End(xlUp).Row  'Last Row Of Data in Col. A
        If LastRow < 3 Then Exit Sub
        For MtgRow = 3 To LastRow
    With olAppItem
        ' set default appointment values
        .ReminderSet = True
        .BusyStatus = olBusy
        .MeetingStatus = olMeeting
        On Error Resume Next
        .Subject = Sheet2.Range("A" & MtgRow).Value 'Subject
        .Location = Sheet2.Range("B" & MtgRow).Value 'Location
        .Start = Sheet2.Range("C" & MtgRow).Value & " " & Format(Sheet2.Range("D" & MtgRow).Value, "hh:mm AM/PM") 'Start Date & Time
        .End = Sheet2.Range("E" & MtgRow).Value & " " & Format(Sheet2.Range("F" & MtgRow).Value, "hh:mm AM/PM")  'End Date & Time
        .Body = Sheet2.Range("G" & MtgRow).Value 'body
        .ReminderMinutesBeforeStart = Sheet2.Range("I" & MtgRow).Value 'Reminder Minutes
        .BusyStatus = Sheet2.Range("J" & MtgRow).Value ' tentative
        .AllDayEvent = Sheet2.Range("H" & MtgRow).Value
        .RequiredAttendees = Sheet2.Range("M" & MtgRow).Value
        .OptionalAttendees = Sheet2.Range("L" & MtgRow).Value
        .BusyStatus = olBusy
        On Error GoTo 0
        .Display ' saves the new appointment to the default folder
    End With
        Next MtgRow
End Sub
 

Attachments

  • screenshot_Excel.png
    screenshot_Excel.png
    45.4 KB · Views: 1
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,225,478
Messages
6,185,228
Members
453,283
Latest member
Shortm88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top