Aggregate your Google Calendar Data with Appscript, Google Sheets & Data Studio
This one’s simple….. I was curious how much time I was spending in meetings…..how many people I usually meet with, who I meet with the most, and which meetings are the most frequent.
So…just like the quick appscript things I threw together for Google Meet, Chat, and Drive…I spent about 90 mins last night putting this together:
High level overview — AppScript code to query your google calendar and pull data into a Google Sheet, which can then be analyzed or visualized in Google Data Studio.
I was able to aggregate the last 7 years of my Google Calendar data in about 30 mins (grabbing about a year’s worth of data at a time using the backfill() function i included).
The basic premise of the approach is that it will look at “yesterday’s” calendar data, and pull in the event name, location, guests, guest status etc, and throw all that into a google sheet. After that, it’s a simple task of putting a Data Studio dashboard on top to help make sense of what’s been collected.
All in all it took about 90 mins to write, run and build the dashboard. I know for a fact that the method could be made more efficient, but this is a starting point, and in a shareable state.
The best part about it, is it doesn’t require 3rd party access to my google calendar data. I only need to allow my own script, hosted on google, access to my google calendar, and my google sheet.
Hopefully this helps others if they’re interested in analyzing their calendar info. Pairing this with the Google Meet Attendance aggregator appscript i wrote about earlier, it would provide a really nice overview when the data is combined.