Importing External Calendars In to Gsuite using Google App Script

ETA – December 2024

Do you need to manage multiple calendars? I do. In my day to day I need to keep track of my work calendar (GSuite), my personal calendar (GSuite) and a customers Office 365 Calendar. There are two distinct problems that led me to crafting this solution.

Problem No 1 : Managing 3 calendars is just hard, I often have scheduling issues and overlap. The cognitive load is becoming more than I am willing to spend
Problem No 2: My colleagues, have no visibility in to my day to day. It may appear like my day is empty, but it doesn’t take in to account my customers Office 365 Calendar.

These problem statements manifest in time spent managing my calendar, that often means rescheduling after people have booked time with me that appears to be free, but isn’t.

So in this post, l am going to walk you through how you can merge multiple distinct online calendars together without using a paid platform like Zapier

What I will illustrate in this post, how I

• Leveraged my business GSuite calendar as my sole calendar, you could call it my SOR (Statement Of Record). This one calendar is to have the contents of all 3 calendars in there.
• Personal calendar items to appear as free in my work calendar
• Office 365 calendar events to appear in my work calendar as busy
  
  

In order to do this there are a few processes we need to take

1. Sharing Calendars (Office 365 and GSuite)
2. Google App Script
3. Adding Trigger

function copyEvents() {
  var sourceCalendar = CalendarApp.getCalendarById('ud1ac4688v30dp8aq6qu8k2guvl5kdc9@import.calendar.google.com');
  var targetCalendar = CalendarApp.getCalendarById('shane.baldacchino@v2.digital');
  var events = sourceCalendar.getEvents(new Date(), new Date(new Date().setFullYear(new Date().getFullYear() + 1)));
  
  for (var i = 0; i < events.length; i++) {
    var event = events[i];
    var startTime = event.getStartTime();
    var endTime = event.getEndTime();
    
    // Apply a -11-hour offset
    var offset = -11 * 60 * 60 * 1000; // -11 hours in milliseconds
    var newStartTime = new Date(startTime.getTime() + offset);
    var newEndTime = new Date(endTime.getTime() + offset);
    
    // Check if an event with the same title and start time already exists
    var existingEvents = targetCalendar.getEvents(newStartTime, newEndTime, {search: event.getTitle()});
    var eventExists = existingEvents.some(function(existingEvent) {
      return existingEvent.getTitle() === event.getTitle() && existingEvent.getStartTime().getTime() === newStartTime.getTime();
    });
    
    if (!eventExists) {
      targetCalendar.createEvent(event.getTitle(), newStartTime, newEndTime, {
        description: event.getDescription(),
        location: event.getLocation()
      });
    }
  }
}

Are you are working in customer land and they have their own calendar system are you facing trouble syncing with our GSuite?

It has become an issue for me, having to manage various calendars. So last night I came up with a solution and if it helps just one person I will consider it a win.  I didn’t realise to yesterday others (@Jennifer Chahine) had similar issues.

My challenge here is simply, I have meetings in my shane.baldacchino@vivaenergy.com.au mailbox that doesn’t reflect in my GSuite calendar, with the impact being meeting clashes happening far too often. IT was becoming a pain so here is how I solved this without resorting to a paid service

Step 1 – Share your calendar (Outlook / GSuite)  as an ICS and Import in to Google
Step 2 – Create a Google App Script (was new to me). I had issues with events being offset with the timezone being set as GMT +0, the code below has an offset and define a trigger to run hourly. Change the ID’s accordingly