Fratopolis

Hey I saw your post about attendance. I've created something for our district and made it public. https://www.youtube.com/playlist?list=PLk8RygAHqdHfjgZ_UovEv3sNfNaYkbJIh https://docs.google.com/spreadsheets/d/1W1W_Y8N-C6dpfJ7pzIK2rSYNtzGsgS8CFCm5zRuXViE/edit#gid=1521585388 https://docs.google.com/spreadsheets/d/1ZOE3wNXJb8Ddwrl-oFvcRMDLkTEUsnoojImTcHjnjfI/edit#gid=1560385

I created some videos and added them to a playlist on youtube on exactly how to convert my sheets to use in your org. If you have GAMADV-XTD3 already setup then you should be able to have it up and running in 30-60 minutes. https://www.youtube.com/playlist?list=PLk8RygAHqdHfjgZ_UovEv3sNfNaYkbJIh

The morning and afternoon tabs are just for our summer school sessions. During the next school year we will add blocks 1-8 instead. It's important to note that once you setup gam for auto import this is hands off for IT dept. Unless google changes something. For this public version you can play with these cells (or make a copy to see all) ATTENDACE TAB: B1, C4, F4 MORNING $ AFTERNOON TABS: H1, K2 When teachers make a copy the protect ranges do copy with it if you set them to warning protect instead of user protect

EDIT ADDED A Complete howto on setup for your org https://www.youtube.com/playlist?list=PLk8RygAHqdHfjgZ_UovEv3sNfNaYkbJIh EDIT END So I have decided to make my sheets public but before I go full blown with it I'd like to know how much interest there is in this. Each teacher makes a copy of the attendance sheet for themselves and if the main attendance sheet is updated they are notified within their copied sheet to make a new copy. This is a static version so it only updates if I manually add meet data. Our actual one updates itself every hour with the latest information. MAIN DATA SHEET https://docs.google.com/spreadsheets/d/1ZOE3wNXJb8Ddwrl-oFvcRMDLkTEUsnoojImTcHjnjfI/ ATTENANCE SHEET https://docs.google.com/spreadsheets/d/1W1W_Y8N-C6dpfJ7pzIK2rSYNtzGsgS8CFCm5zRuXViE/

I tested the extensions out there and found them to be lacking and very buggy For attendance gam is best if using google meet, I have built an entire auto updated attendance platform using gam and google sheets. It auto adds users reconnect times for totals and give one output for the user. Teachers just need to select from a drop down menu the meet id to look at attendance after they enter their email.

Also remember to change the -5/24 section to match your timezone. Since it pulls universal time I needed to take 5 hours off of it to match our time zone.

Now I can show real interactions/engagement with the classes. The first with green color coding shows engagement in a joint of both/either Google classroom and/or Google Meet. The data is there. But manipulating it to matter and make sense and be usable and automating it is craaaaaaaaaaazy. Three different google sheets and a total of 37 sheet tabs combined to make this work lol. Still finding more data to pull as well. Wish we could pull clever and seesaw data per student.

gam print users query "orgUnitPath='/OUPATHIS YOU WISH'" firstname lastname lastlogintime todrive tdtitle "GOOGLESHEETNAMEGOESHERE" tduser GOOGLEEMAILACCOUNTTOSENDTOGOESHERE tdupdatesheet tdfileid GOOGLESHEETIDGOESHERE tdsheet NAMEOFTABINGOOGLESHEETGOESHERE - gam report users parameters gmail:last_interaction_time todrive tdtitle "GOOGLESHEETNAMEGOESHERE" tduser GOOGLEEMAILACCOUNTTOSENDTOGOESHERE tdupdatesheet tdfileid GOOGLESHEETIDGOESHERE tdsheet NAMEOFTABINGOOGLESHEETGOESHERE - gam report users fields 'classroom:last_interaction_time' todrive tdtitle "GOOGLESHEETNAMEGOESHERE" tduser GOOGLEEMAILACCOUNTTOSENDTOGOESHERE tdupdatesheet tdfileid GOOGLESHEETIDGOESHERE tdsheet NAMEOFTABINGOOGLESHEETGOESHERE I then use this to convert the timestamp to readable format. Will need to be adjusted for your sheets and cannot be put in the tab where your data is written as the whole tab gets overwritten on next eport. So if this makes sense you create anoter tab next to each one and query the tab you want to adjust and add a column with this formula =--TEXT(SUBSTITUTE(SUBSTITUTE(GAM!D2,"T"," "),".000Z","")-5/24,"mm/dd/yy")

I pull classroom info using gam. here are useful commands for getting times gam print users firstname lastname lastlogintime gam report users parameters gmail:last_interaction_time gam report users fields 'classroom:last_interaction_time'

I Created this last month. an example of what gam can help you create and updates itself automatically (there is a tab for each building showing which students are not engaging) obviously names and other data blocked out for privacy reasons The main tab has all the information and continues down for thousands of rows

This is driving me nuts as well. We have about 700 iPads and everyday on average 1-2 drop out of management. But it's not even from a OS upgrade. Just happens. Are you still having this issue.