How to avoid the feeling of "this meeting could have been an email"

February 11, 2021
Tech

How much time are you and your principals spending in meetings? If I were to hazard a guess, the answer is "most".

Which is terrifying - when does the actual work happen when your calendar is back-to-back?! - but not all that surprising.

I asked myself this question last week. I didn't want a finger-in-the-air response, I wanted a response, supported by data, that I could to use to better inform weekly planning. I also wanted a response that would hammer home the point of "this meeting could have been an email".

In an ideal world, I would be able to export a google calendar into a google sheet which would form the basis of my analysis.

So I started Googling. To begin with, my results were mainly third party integrations. Now, don't get me wrong Zapier and other integrations have their place, but I was trying to export from one Google workspace app to another so was convinced there had to be a simpler way.

And then, after Googling "script to export GCal to Sheets", I found the solution: Google Apps Scripts.

Before I go any further: this will require a tiny bit of code but once you get the hang of it, you'll be golden.

Photo by Avel Chuklanov on Unsplash

What is Google Apps Script? Google Apps Script lets you automate tasks within Google Workplace, a bit like a macro in Excel.

What will the result be? Using this tool will export all your calendar events into a Google Sheet making it super easy to work out exactly where your precious working hours are disappearing to.

OK, now for the magic:

1. Open a new Google Sheet and give it a name. I like naming conventions eg 0121_Laura

2. Click on Tools > Script Editor, this will open up a new project in Apps Script. Give the project a name - I like to match the name of the Google Sheet eg 0121_Laura.

3. Delete text in lines 1-4 and paste in this text -

function listEvents() {
 var id = "...";   // enter Calendar Id, found on its Settings page
 var cal = CalendarApp.getCalendarById(id);
 var startPeriod = new Date();
 startPeriod.setHours(0, 0, 0, 0);
 var endPeriod = new Date(startPeriod);
 endPeriod.setDate(endPeriod.getDate() + 28);
 var events = cal.getEvents(startPeriod, endPeriod);

 var output = [];
 for (var i = 0; i < events.length; i++) {
   output.push([events[i].getStartTime(), events[i].getEndTime(), events[i].getTitle()]);    
 }

 var sheet = SpreadsheetApp.getActiveSheet();
 sheet.getRange(1, 1, output.length, output[0].length).setValues(output);
}

It will look like this:

4. Open up Google Calendar > Settings and click on the calendar you want to export. Scroll down, find the calendar ID and copy it. The calendar ID will likely be an email address

5. Head back to the Script editor and paste the email address into the section that says <...>. It should now look a bit like this -

6. Click Save (the floppy disk icon) and then Run. You might be prompted to give Google permission to access your calendar. You'll see a status bar appear under the script. Once the code has run, go back to your Google Sheet and - tada! - your calendar is now exported to your Sheet.

Now what?

You can now interrogate the data to get as many insights as you need/want from it. I like to do two operations before presenting the data -

1. label each meeting ie by project name, type of meeting etc and then create a pivot table to find out just how much time was spent on each project. Your analysis might look a bit like this:

2. double-check if there are any overlapping meetings. This way you can delete any meetings that weren't attended from the data and also highlight the amount of meetings that were missed because of overlaps.

And that's it. You now have a Sheet that you can use in your next 1:1 to really drive the discussion around meetings, cadences and whether or not that 30 person meeting really needed to happen.

I would love to know how you get on with this trick - let me know on Instagram or ping me an email.

Back to all articles