Google Meet Attendance Reporter - Self Service Solution
Google Meet Attendance Reporter Overview
Google Meet Attendance Reporting Solution Overview:
If you use Google Workspace (or Google Workspace Essentials), you would be leveraging Google Meet to organize your video meetings and live streaming to compliment the new normal.
If your meeting had more than a few attendees, you might want to know who attended your Google Meeting (or live steam) and for how long.
Though Google Meet records attendance but this information is provided in Google Admin Console, and one needs Google Workspace Admin access to see it, which is not scalable as you don’t want to give Google Workspace admin access to all your users.
So I thought to invest sometime and create a self service solution where your Google Workspace users (without admin access) can simply fill up a Google Form, and instantly get their Google Meet attendance report.
Every solution comes with a certain set of features and limitations, and this meet attendance solution is no different, so before I show you how the solution works, and how you easily set it up for your Google Workspace users, let me quickly tell you its features and limitations.
Google Meet Attendance Reporting Solution Features:
1. Self Service : This Google Meet Attendance tracking solution provides your Google Workspace users a self service option where they simply fill up a form to instantly get attendance report, without any manual action required from Google Workspace Admins.
2. Cost Effective : As this solution takes advantage of Google Workspace native applications and services, it does not cost you anything after the initial setup. Though you can run this solution with free Cloud Identity license too (with some limitations), I would recommend you to use a dedicated Google Workspace Basic license for it (explained later in this post) costing you just $6/month to run this solution.
3. No Learning Curve : Your Google Workspace end users would simply need to fill up a Google Form providing Google Meet meeting code, and meeting type (e.g regular meeting or live stream), no special learning is required for end users to leverage this solution.
4. No Data Sharing with 3rd party : This solution is built with using Google native applications and services (e.g Google Sites, Form, Gmail, Apps Script) which you can run within your own Google Workspace environment. You do not need to provide any 3rd party application or developer access to your Google Workspace or Google Meet data.
5. Available only to Meeting organizers : This solution does check and provide Google
Meet meeting attendance report only to meeting organizer and restrict reporting to unauthorized users (e.g participants).
6. Logging & Reporting : This solution leverages Google Apps Script which provides execution reports and also notify you in case of any issues. It also uses a dedicated Google Workspace account to ensure reporting and logging can be separate from other Google Workspace Administration.
Google Meet Reporting Solution Limitations:
As this Google Meet Attendance reporting solution leverages Google Apps Script services, there are some quotas which should be kept in mind :
(i) Gmail mail sending quota of up to 2,000 emails per day if you use Google Workspace.
(ii) If you use Google Workspace Essentials which does not offer Gmail, you may leverage Apps Script’s MailApp service where quota is 1500 emails per day.
(iii) Google Document creation quota of 1500 documents per day.
Overcome Limitations:
Most of the meetings are small in size (e.g ~5 people) and their organizers might not need attendance report for such meetings. Usually organizers who organize either large meetings OR Live Streams (available in Google Workspace Enterprise plan) would need attendance report.
This solution can serve up to 1,500 meeting attendance requests everyday, however with some tweaks in this readymade provided solution, you can easily scale it to serve more number of requests (e.g using 3rd party email sender API like Sendgrid to over come MailApp or Gmail emails per day limit).
Now, let me tell you in details how this solution works (for the end users), and how Google Workspace Admins can set it in just a few minutes.
How does Meet Attendance Reporter work?
Detailed Overview of the Solution
Our Google Meeting attendance solution leverages a dedicated Google Workspace account with delegated admin access + Google Sites + Google Form + Google Apps Script, this is how it works behind the scenes :
Notes :
1. You should be a Google Workspace Administrator to make this solution to work in your Google Workspace domain.
2. Step by step instructions (and video) are provided in this blog post including the script that you can simply copy and paste.
1. Dedicated Google Workspace Account :
(1.1) We will create a dedicated Google Workspace account + assign this account a delegated administration role, so it can use Reports API.
(1.2) This dedicated account would run the script (e.g call Reports API) and send email to requestor with meeting attendance report.
2. Google Form :
(i) We will create a Google Form which would ask users to provide their Google Meeting code and Meeting type (e.g whether it was a regular google meeting or live stream).
(ii) We will restrict this Google Form to our Google Workspace organization, so only users who are in our Google Workspace tenant can fill it.
(iii) Ideally for privacy reasons, we should only be providing attendance report to the meeting organizers, for that purpose, we will collect Form filler’s email address to check whether he/she is the meeting organizer or requested meeting or not.
(iv) We would send the requestor an email automatically (and instantly) after filling up the form with Google doc link, which has their Google Meeting attendance report.
3. Google Apps Script with Trigger :
(i) Our Google Apps script would listen to the Form submission (based on apps script trigger), read the input values (e.g meeting code, meeting type, requestor email id).
(ii) Our script would then call Google Workspace Reports API (with either meeting or live stream event) with the meeting code to get meeting details.
(iii) Our script will parse the required information from the API response (e.g meeting attendee email address and attendance duration) –> Create a new Google document –> insert a table –> and populate this table with Google Meet attendance data.
(iv) Our script would check whether the requestor is indeed this meeting’s organizer or not. we’ll compare the form filler’s email address with the organizer email in API response).
(v) Our script would send an email to requestor with a Google doc link (which has meeting attendance details) if the requestor is meeting organizer, otherwise we’ll send a sorry email.
(vi) Our script would update the status in Google sheet (e.g “report sent”), so that we won’t process this request again and only work on new form submissions.
4. Google Site :
(i) Though this is optional, but you may consider embedding your Google Form in an internal Google Site where your users can go and fill it up to get Google Meet attendance.
Google Meet Attendance Reporter Setup
Step 1 : Dedicated Admin Account
Let us first create a dedicated Google Workspace account and assign it delegated Admin permission to read from Reports API
As you now understand our Google Meet attendance solution, let me show you how you can easily set it up within a few minutes.
— Let us create a new dedicated account which we would use to run the solution script.
— I would call it meetreporter@mydomain.com but you may name it whatever you want.
— We would also be using this account to send attendance report to requestors, though Apps Script offers “MailApp” service which can help you send up to 1500 emails a day without a Google Workspace license (e.g Cloud Identity free).
However, I would go with a dedicated Google Workspace license as it gives me flexibility to send up to 3000 emails a day.
— I am assigning Enterprise license, but you can assign Google Workspace Basic license too.
— Now let us create a custom delegated administration role as a best practice, which we’ll later assign to our meetreporter user we created above.
— Give this role only the reporting permission as thats all we need to report Google Meet attendance.
Now click on “Assign Role” to open Google Workspace administrator role assignment window.
— Search for the user you created above, and assign it the custom reporting role we created.
Step 2 : Create Google Form
Let us now create our Google Form which our users would fill to get Google Meet Attendance report.
— Now let us create a new Google Form
— I would call my form “Google Meet Attendance Reporter”, but you may call it anything you want.
— You should also put some context in the form heading as shown in the screenshot below.
— Lets add couple of questions to our form to get the information we need to report Google Meet attendance.
— We’ll need meeting code (to get the attendance report for it) and meeting type as regular meeting and live stream have different API endpoints.
— Your final Google Form would look the one shown in below screenshot.
— Click on the gear icon to open your Google Form settings.
— Our solution would only provide Google Meet attendance report to meeting organizers, so let us collect requestor’s email here, which we can compare with organizer email that we’ll get from API response.
— This form would only be available to users in your Google Workspace tenant, so let us restrict to users in our company.
— Let us create a Google Sheet which would receive our Google Form responses.
— I would name it “Google Meet Attendance Reporter”, but feel free to name it anything you like.
— Your Google Sheet for form responses in now ready with its (1) title, (2) fields, and the (3) sheet tab name as shown in the screenshot below.
— Let us add three more fields to it:
(1) Email Address : This field would be added by your form automatically once your fill up your first form.
(2) Report Status : Our script would update this column with “Report Sent” after sending the report, so at next run we’ll skip processing the rows which are already processed.
(3) Report Link : We’ll put the Google Meet Attendance document link here for your / Google Workspace Admin’s reference.
Step 3 : Google Apps Script
Let us now add Google Apps script along with "on form submission" trigger to instantly send meet attendance reports to requestors.
— We would be leveraging Google Apps script to make our “Google Meet Attendance Reporter” work.
— Please hover on the script below, and it should show you a button to copy it with one click, I would show how where to paste this script in a minute.
-------------------------------------------------------------------------
// This script can help you setup a self service Google meeting attedance reporting for your users.
// This script is neither influenced nor supported officially by Google, I created it to solve my own use case, so please use it as a reference only.
// It would also check whether the requesting user is meeting organizer or not to ensure we only send attedance info to organizers.
// You should ideally be create a dedicated user for this script + give him reports api read permission.
function getMeetAttendance() {
// Get the form filled up values from our Google Form sheet
// Please make sure to change the sheet name here if it is different than Form Responses 1
var formSheet = SpreadsheetApp.getActive().getSheetByName("Form Responses 1")
var data = formSheet.getDataRange().getValues()
// Loop through the data to do our processing.
for (i = 1; i < data.length; i++) {
var userClaimedorganizer = data[i][3]
var meetingCode = data[i][1].replace(/-/g, "") // some users might copy and paste the meeting code which has "-", lets replace it here.
var meetingType = null
var status = data[i][4]
var userEmail = data[i][3]
// Regular meeting and Live Stream have separate endpoints and provide different outputs (e.g regular meeting gives us meeting attendee duration but live stream doesn't
// hence a different array / header row based on whether user has a regular meeting or live stream.
if (data[i][2] == "Regular Meeting") {
meetingType = "call_ended"
var fileArray = [
["User Email", "Duration (in mins)"]
]
} else {
meetingType = "livestream_watched"
var fileArray = [
["User Email", "Watch Status"]
]
}
// we do not to process the requests which are already fulfilled, let us only process new/fresh requests
if (status != "Report Sent" && status != "User Not Authorized") {
let activities = new Map();
var applicationName = "meet"
var pageToken;
var optionalArgs = {
event_name: meetingType,
filters: "meeting_code==" + meetingCode,
pageToken: pageToken
};
// for big meetings or livestreams where the attendees list would be large, let us keep calling the API till we parse the last result page via pageToken.
do {
apiCall = getMeetingDetails(applicationName, optionalArgs)
var apiResponse = apiCall.items
var pageToken = apiCall.nextPageToken
for (var key in apiResponse) {
var events = apiResponse[key]["events"]
var apiClaimedorganizer = null
// Parse the parameters from Events.
events.forEach(function(item) {
var parameters = item["parameters"]
var obj = {}
// Filter the array for the items we need (e.g identifier, duration seconds, organizer email)
parameters.forEach(function(filter) {
if (filter["name"] == "identifier") {
obj.email = filter["value"]
}
if (filter["name"] == "duration_seconds") {
obj.duration = Math.abs(filter["intValue"])
}
if (apiClaimedorganizer == null && filter["name"] == "organizer_email") {
apiClaimedorganizer = filter["value"]
}
})
// Push the values to our object based on our condition, e.g if the email isn't null.
if ((obj.email != "" || obj.email != null)) {
if (meetingType == "livestream_watched") {
activities.set(obj.email, "Livestream Watched")
} else if (meetingType == "call_ended" && obj.duration != null && obj.duration > 0) {
if (activities.has(obj.email)) {
activities.set(obj.email, (activities.get(obj.email) + Math.round(obj.duration / 60)))
} else {
activities.set(obj.email, Math.round(obj.duration / 60))
}
}
}
})
}
pageToken = apiCall.nextPageToken
} while (pageToken)
// Push the values to our fileArray
for (let [email, activity] of activities) {
fileArray.push([email, activity])
}
// create Google documenat by calling our createDocument function
var doc = createDocument(meetingCode, fileArray, userEmail)
if (userClaimedorganizer == apiClaimedorganizer) {
authorizedResponse(formSheet, doc.getUrl(), meetingCode, userEmail, i)
shareDoc(doc.getId(), userEmail)
} else {
unAuthorizedResponse(formSheet, doc.getUrl(), meetingCode, userEmail, i)
}
} else {
console.log("error")
}
}
}
// Various functions leveraged above.
// This functions takes the application name and optional arguments and call Reports API to get data
function getMeetingDetails(applicationName, optionalArgs) {
var apiCall = AdminReports.Activities.list("all", applicationName, optionalArgs)
return apiCall
}
// This fucntion email the Google document with meeting attedance details if the requester is meeting organizer.
function authorizedResponse(formSheet, docLink, meetingCode, userEmail, lineNumber) {
formSheet.getRange(lineNumber + 1, 5).setValue("Report Sent")
formSheet.getRange(lineNumber + 1, 6).setValue(docLink)
GmailApp.sendEmail(userEmail, "Your meeting report for " + meetingCode, `Here is your meeting report for ${meetingCode}
${docLink}
Regards,
Your Sweet Meeting Reporter:)
For any questions, please reach out to xyz@yourdomain.com`)
}
// This fucntion informs the requestor via email that he can't get meeting attendance as he isn't the organizer.
function unAuthorizedResponse(formSheet, docLink, meetingCode, userEmail, lineNumber) {
formSheet.getRange(lineNumber + 1, 5).setValue("User Not Authorized")
formSheet.getRange(lineNumber + 1, 6).setValue(docLink)
GmailApp.sendEmail(userEmail, "Your meeting report for " + meetingCode, `Sorry, you are not authorized to get meeting report for ${meetingCode} as you are either not the meeting organizer or you provided incorrect meeting code.
Regards,
Your Sweet Meeting Reporter:)
For any questions, please reach out to xyz@yourdomain.com`)
}
// This fucntion creates a Google document, populate it with attendance report
function createDocument(meetingCode, fileArray, userEmail) {
var style = {};
style[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] =
DocumentApp.HorizontalAlignment.RIGHT;
style[DocumentApp.Attribute.FONT_FAMILY] = 'Calibri';
style[DocumentApp.Attribute.FONT_SIZE] = 18;
style[DocumentApp.Attribute.BOLD] = true;
var doc = DocumentApp.create("Attendance Report for " + meetingCode)
doc.getBody().appendParagraph("Detailed Meeting Report for " + meetingCode).setHeading(DocumentApp.ParagraphHeading.HEADING1)
var docId = doc.getId()
var docLink = doc.getUrl()
var format = doc.getBody().appendTable(fileArray).getRow(0).setAttributes(style)
return doc
}
// This function shares the above created google doc with the requestor
function shareDoc(docId, userEmail) {
DocumentApp.openById(docId).addViewer(userEmail)
return userEmail
}
--------------------------------------------------------------------------
— Now go to “Tools” and click on “Script Editor” to open Google Apps script editor.
Here, you should do couple of things :
1. You should click on untitled project and give it a contextual name (you can name it anything).
2. You should also delete the default code/function, and paste the code that you copied above (here is the code link).
— Your apps script project would like this now with changed title, and code pasted.
— We need to enable couple of Advance Google Services to make our solution work, go to “Resources” menu and click on “Advance Google Services”.
— Each Google Apps Script program with advance services creates a Google Cloud Platform project, as this is our newly created user, let us click on “Cloud Console Terms of Service”, and accept the TOS.
— You may read the TOS and once/if satisfied, provide your agreement and click on “Agree and Continue” as shown in the screenshot below.
— Now go to “Resources” menu again and click on “Advance Google Services”.
— Enable “Admin Reports API” as our script will be calling this to get Google Meet activity report.
— Our script would be sending email with Google Meet attendance report to the requestors, so let us enable Gmail API too here.
— Click Ok to close this windows once you turned on Reporting and Gmail APIs.
— Now let us fill up our form to test our solution, fill up the form with your account, make you put the meeting code of the Google Meet which you organized.
— Note : Our solution script would send google meet attendance report only if the requestor is meeting organizer.
— Select the meeting type to regular meeting (if your meeting is regular meeting) or live stream (if your meeting was a live stream). Live streaming is only available in Google Workspace Enterprise plan.
— Click on submit button.
— Go back to your Google Apps Script editor by clicking on Tools –> Script Editor from your Google Sheet.
— Now click on “Select Function” dropdown, and select getMeetAttendance as shown in the screenshot below.
— After selecting getMeetAttendance function, click on the play button to run the script, as shown in the screenshot below.
— As this is the first time you are running this script, it will need you to review and provide permissions, click on “Review Permissions”.
— Select the account that you created above to use in this solution.
— Now Google would show you the permissions this script needs to run, you may review these and click on “Allow”.
— Now your script should run and update the status in your Google Sheet (e.g report status, and report link).
— If it does not work, go back to your script editor (Google Sheet –> Tools –> Script editor), select getMeetAttendance function and run it.
— You would see the email from our account (meetreports@ or whatever you named it) to the requestor account as shown in the screenshot below.
— Requestor can click on the Google document link to see Google Meet attendance report.
Note : Please feel free to change email content in the script.
— Requestor should see the attendance as shown below along with duration attendees attended meeting for.
Notes:
1. Duration is only available in regular meetings, Google does not provide it duration for live stream meetings.
2. This is just a demo hence you see just one entry here, but our script would populate this table in google doc with the details of all attendees.
— Now let us fill up our form again to test what happens when form is filled up by someone who is not the meeting organizer.
— You should now go back to your script editor (Google Sheet –> Tools –> Script editor), select getMeetAttendance function and run it.
— You would see that our script updated the status in our sheet saying “User Not Authorized” as this user isn’t the meeting organizer and we shouldn’t be sending him Google Meet attendance report.
— This requestor would get the email mentioning that only Google Meet meeting organizers can get the attendance as shown in the screenshot below.
— Please feel free to change email content in the script.
Our script has been working but it is time consuming to run it manually, so let us automate it with the triggers functionality in Google Apps Script.
— Go to your Google Apps Script editor project, and click on the “Clock” icon as shown in the screenshot below.
— Click on “Add Trigger” to add a new trigger to your script.
Our trigger should essentially listen to new form submissions, and run our getMeetAttendance function for each submission.
1. Your function should be “getMeetAttendance” (unless you changed its name in the script).
2. Select “Head” from which deployment should run.
3. Event source should be “From Spreadsheet“.
4. Event type should be “On Form Submit“
5. Let it notify you immediately in case of script failure.
6. Save changes.
Your trigger configuration should look like the screenshot below:
— Now you would see your Google Apps Script trigger created, and ready to run attendance function as soon as new form fill up takes place.
— Please feel free to test it out by filling up your Google Form, and it should automatically send your the Google Meet attendance report within just a minute.
Step 4 : Embed Form in Google Site
To provide rich experience to our Google Workspace users, let us add our Google Form in a Google Site (optional, but recommended).
— Go to sites.google.com (with the dedicated Google Workspace account you created for this solution), and create a new Google Site.
— Add a header to your header (1) Add site document name, (2) Add Site Title, (3) Optionally, add header banner.
— In the next section, double click any where, and you would see the circle like shown below.
— Click on Google Drive icon to open the drive search widget.
— On the right hand side, you would see a search option, search for your Google Form –> Select it –> and Click on Insert as shown in screenshot below.
You may also, add some description via text widget as you shown below, so your users understand what happens when they fill up the form.
— Finally, publish your Google site by clicking on the “Publish” button on top right.
— Give your site a name, and click on Publish.
Now, you can inform your Google Workspace users about this site to get meeting attendance report. You can also (optionally) web address map this site (e.g meetreporter.yourdomain.com) as per Google Workspace support documentation here.
— Congratulations, your users can now come to this site, fill up the form, and instantly get Google Meet meeting or live stream attendance report.
Google Meet Attendance Reporter - FAQs
WHAT IS THE FORMAT IN WHICH MY USERS SHOULD ENTER MEETING CODE?
Your users can enter 10 characters meeting code with or without the “-“.
For e.g either PEAMIRNSMQ or PEA-MIRN-SMQ both should work fine.
Note -: Our script removes “-” from the meeting code (if its there) as Google Reports API does not accept dashes in the meeting code when making API call.
HOW CAN I CHANGE THE EMAIL CONTENT?
Absolutely, look for “authorizedResponse” and “unauthorizedResponse” functions in above Google Apps script.
You can change the email content there including email subject, body and signature.
CAN OUR SCHOOL TEACHERS USE IT?
Absolutely, one you setup the tool as a Google Workspace Admin, you can make it available to your school teachers via Google Sites (or via any other way).
This solution can serve up to 1500 requests everyday, if that does not suffice you need, you can use any email API to overcome Gmail API email limitation.
HOW IS IT DIFFERENT THAN GOOGLE MEET ATTENDANCE ADD ON?
I have a few Google Workspace add-ons published in the Google marketplace, and based on my first hand experience I can tell, that add-ons would require access to your data (via OAuth) provide you result/output.
This solution however provides a script which runs within your own environment which means no need to provide access to your Google Workspace / Meet data with me / 3rd party developer.
CAN THIS PROVIDE GOOGLE MEET ATTENDANCE FOR MOBILE?
This Google Meet attendance reporting solution reads information from Google Sheet (via Google Form submit), so regardless of how you fill up the form (e.g on Web, Android, iOS), you should be able to get the email with attendance report.
CAN GOOGLE MEET ATTENDANCE BE DOWNLOADED?
Yes, by default Google Meet attendance reporter would send attendance information (including user name and attended duration) in a Google document which requester can download into required format (e.g pdf, word etc) right from Google Docs interface.
You can also enhance the above provided script if you prefer to straight away email attendance report as pdf attachment.
Related Posts
....