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
In this post I will show you how can make sense of multiple calendars whilst providing the right degree of visibility to others.
What I will cover in this post is how I
- Leveraged my business GSuite calendar as my sole calendar, you could call it my SOR (Statement Of Record). This one calendar has the contents of all 3 calendars in there.
- Imported my personal GSuite (or GMail) calendar items to appear as free in my work calendar
- Imported an Office 365 calendar events to appear in my work calendar as busy
This isn’t actuality that hard and if you have this challenge and use GSuite as your primary email platform I would encourage you to follow along, because its easier than you think and whats more it’s free and hopefully you will learn a thing along the way. In order to do this there are a few processes we need to perform. Lets break it down in to 3 distinct tasks.
- Sharing Calendars (Office 365 and GSuite)
- Google Apps Script
- Adding Trigger
Sharing Calendars
In order to bring both my personal GSuite and customers Office 365 in to my work GSuite calendar the first thing we need to do is share these calendars.
Gsuite or GMail. Click on your calendar and hit the three dots and select Settings and Sharing. Scroll down and note down the Calendar ID (shane@baldacchino.net) in my case. In the Share with specific people or groups add in your primary GSuite calendar’s email address. This calendar will need to be imported in to your primary GSuite (but you can hide it from the list of calendars), once imported extract the calendar ID which will be in the form user@domain.com. In my case shane@baldacchino.net
Office 365. Share your calendar by logging in to https://outlook.office365.com. Once logged in click on the gear icon (top right). Click on Calendar, Shared calendars. Select your calendar in the drop down box and define which items you wish to share. In this scenario I am sharing all events and not just my free / busy information.
This calendar will need to be imported in to your primary GSuite, once imported extract the calendar ID which will be in the form GUID@import.calendar.google.com
Google Apps Script
Do you know about Google Apps Script, I will be honest I had never used it. Google
Apps Script is a cloud-based JavaScript platform powered by Google Drive that lets you integrate with and automate tasks across Google products. To me, it is like AWS Lambda for Google Drive. Automations can be executed by multiple triggers including a time-based schedule which will be using. In terms of cost, quotas by Google are quite generous and the free tier includes up to 10,000 calendar entries created per day free of charge. See https://developers.google.com/apps-script/guides/services/quotas for more details.
This is exactly what we need to do. Under the context of your master account I will craft a Google Apps Script to import and merge calendar items in to my primary GSuite Account
My code is as follows but you can extend this script by modified the array sourceCalendarIds with more calendars. I have 2 to import, perhaps you have more. Edit the array sourceCalendarIds as per your calendar id’s.
function copyEvents() {
var sourceCalendarIds = [
'ud1ac4688v30dp8aq6qu8k2guvl5kdc9@import.calendar.google.com',
'shane@baldacchino.net'
];
var targetCalendarId = 'shane.baldacchino@v2.digital';
var targetCalendar = CalendarApp.getCalendarById(targetCalendarId);
var oneYearFromNow = new Date(new Date().setFullYear(new Date().getFullYear() + 1));
if (!targetCalendar) {
console.error('Target calendar not found: ' + targetCalendarId);
return;
}
sourceCalendarIds.forEach(function(sourceCalendarId) {
var sourceCalendar = CalendarApp.getCalendarById(sourceCalendarId);
if (!sourceCalendar) {
console.error('Source calendar not found: ' + sourceCalendarId);
return;
}
console.log('Processing events from source calendar: ' + sourceCalendarId);
var events = sourceCalendar.getEvents(new Date(), oneYearFromNow);
for (var i = 0; i < events.length; i++) {
var event = events[i];
var startTime = event.getStartTime();
var endTime = event.getEndTime();
var newStartTime = new Date(startTime.getTime());
var newEndTime = new Date(endTime.getTime());
// 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) {
var transparency = sourceCalendarId === 'shane@baldacchino.net' ? 'transparent' : 'opaque';
try {
targetCalendar.createEvent(event.getTitle(), newStartTime, newEndTime, {
description: event.getDescription(),
location: event.getLocation(),
guests: event.getGuestList().map(function(guest) { return guest.getEmail(); }).join(','),
visibility: event.getVisibility(),
status: 'confirmed',
transparency: transparency // Mark the event as free or busy
});
console.log('Event created: ' + event.getTitle());
} catch (e) {
console.error('Failed to create event: ' + event.getTitle() + ' - ' + e.message);
}
}
}
});
}
Execute this code and if you have set up permissions accordingly it should execute without error, more so if you look in your calendar
Adding A Trigger
Rather than manually running this manually as per above, we want to execute with some degree of automation. So how can we do this? Google Apps Script supports two types of triggers.
Simple Triggers – these are triggers run automatically without any manual setup. Here are some examples:
- onOpen(e): Runs when a user opens a Google Docs, Sheets, Slides, or Forms file.
- onEdit(e): Runs when a user changes a value in a Google Sheets file.
- onSelectionChange(e): Runs when a user changes the selection in a Google Sheets file.
- onInstall(e): Runs when a user installs an Editor add-on from within Google Docs, Sheets, Slides, or Forms.
- doGet(e): Runs when a user visits a web app or a program sends an HTTP GET request to a web app.
- doPost(e): Runs when a program sends an HTTP POST request to a web app.
Installable Triggers – These triggers offer more flexibility and must be set up manually.
- onOpen(e): Runs when a user opens a Google Sheets, Docs, or Forms file.
- onEdit(e): Runs when a user edits a cell in a Google Sheets file.
- onFormSubmit(e): Runs when a Google Form is submitted.
- onInstall(e): Runs when an add-on is installed.
- time-driven: Runs at a specific time or on a recurring interval (similar to a cron job)
For my needs a simple time based execution is more than fine. My needs are rather simple and I am executing once per day. You can adjust accordingly.
Summary
Why manage multiple calendars when you can have a single aggregated view? Unleash your inner innovator and get familiar with Google App Script. This is an easy to follow guide that will not only provide you a single pane view of your events but provide visibility to those who leverage your calendar. My code is designed to import two calendars, but this can be easily extended. Why pay a SaaS service when the tools are there for you.
Thanks
Shane Baldacchino