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:

usaussie/google-meet-attendance-tracker