Skip to content

Instantly share code, notes, and snippets.

@slashinfty
Last active February 26, 2024 04:32
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save slashinfty/11d81eeec3174086d5a2eac3697d5ce1 to your computer and use it in GitHub Desktop.
Save slashinfty/11d81eeec3174086d5a2eac3697d5ce1 to your computer and use it in GitHub Desktop.
How to easily take attendance from Google Meets

Google Meet Attendance Made Easier

How are most things made easier? Usually by doing some work ahead of time.

Google Sheets

First thing to do is to have a spreadsheet for attendance. I suggest making a copy of this spreadsheet.

You need to get a list of your students. The easiest way to do this is to go into Canvas gradebook, and select Actions -> Export. It will download a .csv file. In your Google Sheet, select File -> Import, then Upload, and select the file from Canvas. In the resulting dialog, change the first option (Import Location) to Insert New Sheet(s).

In order to get names alphabetized within sections, we have to do a bit of work. Delete the first two rows (they are unnecessary headers) and create a column to the right of student names (names should be in the A column, and the B column should be blank). First, sort by the A column (names), then sort by the F column (section numbers) (note: if you have sections with B at the end, you might want to remove all of the B's so they're the same).

In B1, paste this formula:

=CONCATENATE(REGEXREPLACE(REGEXEXTRACT($A1,",\s.*$"),",\s",)," ",REGEXREPLACE(REGEXEXTRACT($A1,"^.*,"),",",))

This will give you student names as First Last, which you'll need to match Google Meet. Important: you will want to remove suffixes like "Jr." as they don't appear in Google Meet.

Then, you can copy and paste values only (ctrl+shift+v on Windows, or cmd+shift+v on Mac) the B column into the B column of the "Attendance" sheet (and manually fill in the period). Repeat for all classes, just adding them further down the B column in "Attendance."

Google Meet

You'll want to create a bookmark on your bookmarks bar of Chrome. The title can be something like "Copy Meet Names," but copy and paste the following into the address:

javascript:(function(){function copyToClipboard(e){var t=document.createElement("input");t.style="position: absolute; left: -1000px; top: -1000px",t.value=e,document.body.appendChild(t),t.select(),document.execCommand("copy"),document.body.removeChild(t)}var list=document.querySelectorAll('[aria-live="polite"]'),messages=list[0].querySelectorAll("div"),names=[];messages.forEach(e=>{let t=/(^.*?[a-zA-z])\d/.exec(e.textContent);null===t||null===t[1]||names.includes(t[1])||names.push(t[1])}),names.forEach((e,t)=>{/^(\b[A-Z][\w\']*\s*)+$/.test(e)&&"You"!==e||names.splice(t,1)});let namesAsString="";names.forEach((e,t)=>namesAsString=0===t?e:namesAsString+", "+e),copyToClipboard(namesAsString);})();

What does this do? It'll give you a list of all people who have sent a message in a Google Meet room. Important: you must have the chat window open when you press the bookmark. This means you'll want to have students say something (literally anything!) in the Google Meet in order to get mark present.

After you press the bookmark (again, making sure the chat window is open), go to your spreadsheet to the "Copy" sheet, and paste it in the appropriate B column. The A column, if it is unclear, should be mo/day-per in format, so if it's October 1st for 3rd period, it'd be 10/1-3.

How It Works

As long as you update the dates in the first row of the "Attendance" sheet, and keep the formatting consisting in the A column of the "Copy" sheet, this is the magic formula:

=IF(IFERROR(SEARCH($B2,INDEX(Copy!$A$1:$B,MATCH(CONCATENATE(C$1,"-",$A2),Copy!$A$1:$A,0),2)),-1)>-1,"X","")

note: this formula is already in C2 of the "Attendance" sheet

Aside from putting down the dates, you'll need to continue the formula for all students and all days. If you select cell C2 in the "Attendance" sheet, you'll want to click the box in the bottom right corner of the cell and drag it down the column until your last student - then all cells will be updated with the formula! Then, for any subsequent days, select the previous days cells (so something like C2 through C75, however far it goes down) and grab the box and drag it to the column to the right (for the next day).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment