Aggregating Google Meet Attendance Reports with Appscript & DataStudio

Sample metrics from a dashboard that uses data from this script/process (Meetings, Total Duration, Total Attendees, Unique Attendees)

UPDATE: December 8th, 2020 — The code now supports moving the processed files into a Shared Drive as well as regular Google Drive, thanks to the moveTo() method replacing the now-deprecated addFile() method.

Google Meet (meet.google.com) recently added a feature where it will send the meeting creator/owner a CSV for meeting details, which includes:

— Attendee Name, Attendee Email, Attendance Duration, Time Joined,Time Exited

I wanted to aggregate this information for all my meetings though, so i could see how much time i’m spending, who I’m spending it with, which people attend my meetings most often etc. I mean, who cares, right…but for me….someone who works in analytics, and this kind of thing intrigues me….especially if i can have it work automatically with very little overhead.

Appscript enters the game!

Like my previous posts on Appscript for Google Chat, and Google Drive, I figured it would be a quick way to tackle this. Turns out….yep, it was.

Overview

  1. Host a Google Meet
  2. Get the attendance CSV emailed to you
  3. Save CSV to a distinct Google Drive folder (as of December 7th, this CAN be in a Shared Drive)
  4. Appscript trigger runs on a schedule every X minutes
  5. CSV file is parsed and contents are migrated to a central Google Sheet
  6. Additional fields are derived to make analysis and dashboarding easier later, making the final field/column list:
  • File_Name (CSV File Name)
  • File_Id (Google Drive File ID of the CSV File)
  • Meeting_Name (Derived from the File Name, removing the extra stuff)
  • Meeting_Date (Derived from the File Name)
  • Name (Attendee Name)
  • Email (Attendee Email)
  • Duration (string, like 1 hr 3 min)
  • Time Joined (time of day joined)
  • Time Exited (time of day exited)
  • Date_Time_Joined (Derived/calculated based on meeting date & join time)
  • Date_Time_Exited (Derived/calculated based on meeting date & exit time)
  • Duration_Seconds (Derived from duration field, normalized to seconds)
  • Meeting_Owner_Email (CSV File owner according to Google Drive)

7. Processed CSV files are either trashed or moved to a “Processed” folder, to prevent re-processing (config option in the script determines what happens).

8. Now all you have to do is attach a Data Studio dashboard to the Google Sheet, and you’re done.

Every time you get an attendance report, just move the CSV into your Drive folder, and then in a little while the dashboard will be updated accordingly.

Before you stop reading….some gotchas

  1. The code was thrown together in about an hour, and I know for a fact that it could be more efficient. But…it works for now, and I’m okay with that ;-)
  2. The process hinges upon the file name, and the column order remaining the same in the CSV attachments. If those change, the appscript will need a little fine-tuning. But it should be easy to diagnose.
  3. Email addresses of external participants are truncated in the CSV from Google. If jordanhenderson@champions.com attended a Google Meet hosted by bar@internaldomain.com, then the CSV row would contain something like this for that attendee: jord********@***.com

Github repo for the appscript code:

--

--

--

Cloud stuff, data, analytics; Google, Internet2 Advisory Boards & working groups. Higher Ed IT since 2002. @techupover and @usaussie on twitter

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Create an Application-Wide Menu in Angular Using Material UI

Unified Navigation

Handling Huge React Components Using Object Composition Pattern

Top tips for optimizing React + d3 based charts Apps

Form Handler Porting

Tips For Going From Xamarin to React-Native: Lessons From the Trenches

Perform Associated Query through Array Field on MongoDB Collections

Data structure Linked Lists (Using Javascript)

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Nick Young

Nick Young

Cloud stuff, data, analytics; Google, Internet2 Advisory Boards & working groups. Higher Ed IT since 2002. @techupover and @usaussie on twitter

More from Medium

Bulk Create Certificates from Google Slide using FormPublisher

Google Apps Scripts Code Snippets Toolkit

Sync Airtable and Google Sheets using On2Air

Integrate Excel sheets using Power Automate