Working from home isn’t easy for anyone, but for teachers, it poses a major organizational challenge. As one of the most interactive jobs in the world, teaching involves personal communication with up to 30 students at any one time. Multiply that for all your classes, and it’s not uncommon to be working with several hundred students every week.
Although technology can never replace the physical experience of standing at the front of a classroom or lecture hall, online teaching, collaboration, and video software are improving all the time.
One of the most popular online educational tools is Google Classroom. Dubbed “Mission Control for Teachers”, Classroom enables educators to share materials, set assignments, grade work, and give feedback to students all from one central platform.
It has loads of great features but one downside is that there’s no inbuilt Google Classroom attendance tracking function.
Monitor Google Classroom attendance
As well as checking whether students are up-to-date with the course material, attendance monitoring can help teachers keep an eye on students’ development, health and wellbeing. When a school goes remote, teachers often become the only point of contact for students. As a result, tutors may need to take on a safeguarding and support role, checking on vulnerable students and communicating any concerns to managers.
A system for teachers and admin staff
While teachers need to track their own students, administrators have the complex task of collating information from across the entire school. Again, Google Classroom doesn’t contain an inbuilt function to help with this. But if your school uses Google Classroom, you’re probably already using other G Suite tools such as Google Docs, Slides, and Sheets.
It’s possible to build a custom attendance management system yourself using everything that’s included in Google Workspace for Education. In this article, I’ll show you how to set up an automated attendance tracking system using Google Classroom, Google Sheets and Google Forms.
Why use Google Sheets to track Google Classroom attendance?
- Google Sheets is flexible, allowing you to create a custom system for your school. When needs change, you can adapt and change the system yourself, without IT support.
- If you’re using G Suite for Education, Sheets is free so you won’t need to invest in any new software.
- Google Sheets is easy for teachers to use and accessible from anywhere, at any time — as long as they have an internet connection.
What’s a spreadsheet-based workflow?
- A spreadsheet-based workflow consists of multiple connected Google Sheets. These files exchange data between them via Sheetgo connections.
- A workflow enables teachers and administrators to share data automatically without sharing the same spreadsheet. Separate, but connected files, ensure data privacy. Each teacher handles information on their own students, but managers can access data from across the whole department, school or district.
- A Google Sheets workflow is scalable: when you need to add a new class or extra teacher, you simply add an extra sheet to the workflow. It’s also easy to integrate the workflow with other software or to connect to other departments, such as accounting, to generate reports for managers.
Track Google Classroom attendance with a template
Sheetgo’s attendance management template is a pre-built system for monitoring students. Although it’s based in Google Sheets, it’s a little different from a standard spreadsheet template.
With this system
- Teachers get a simple way to track Google Classroom attendance.
- School administrators can gather attendance records from every teacher automatically and generate automated attendance reports for each student and class.
- Managers can see the “big picture” in the automated dashboard, with charts displaying remote attendance levels.
If you’re a teacher working remotely and you need help from your manager to implement this system, share this article with them.
How to get the Google Sheets attendance template
Click on the blue button below to sign up for Sheetgo and install Sheetgo’s attendance workflow template.
Attendance template
Spreadsheets based workflow template to record and analyze student attendance.
How the template works
The template is designed for face-to-face lessons, but it’s easy to adapt to online teaching and Google Classroom attendance monitoring.
Normally, each teacher opens their spreadsheet at the start of the lesson on their laptop or tablet. They manually enter whether each student is P: present, T: tardy (late), E: excused or U: unexcused (absent) using the dropdown menu. If you want to use the template for face-to-face classes, get full instructions here.
To adapt the template for remote classes, you use Google Forms instead. At the start of each online class, the teacher shares a special link with students via Google Classroom. All students participating must “check in” to class by submitting the form. Instead of the teacher having to manually register which student showed up to class, the data flows into the teacher’s spreadsheet automatically from the forms.
Step 2: Adapt the template for remote classes
Sheetgo’s attendance management template contains one master spreadsheet which consolidates attendance data from every class. It summarizes the data in charts to give managers an overview of attendance across the school.
This central spreadsheet is connected to three Class spreadsheets. The files included in the template are named History, Math and Science. They contain sample data, just to show you how the system works.
Teachers use these Class files to record student attendance. The data is transferred to the master sheet via Sheetgo connections. This creates a “workflow” that can be automated by scheduling regular updates.
2.2 Copy the new Class spreadsheet
To adapt the workflow for remote classes, you simply need to replace each pre-connected Class spreadsheet with a different file. Click this link to copy the new Remote Class spreadsheet.
Make as many copies as you need for different classes and rename the files accordingly.
2.3 Check the Google Forms
The Remote Class spreadsheet comes with two Google Forms attached. First of all, let’s check if these forms are configured correctly.
Open the spreadsheet and go to the Attendance form tab. Go to Tools in the menu bar at the top of the screen.
Click Manage form > Edit form. Within the form itself, click on the link “Change settings”.
Within Settings, click on Responses and make sure that the Collect email addresses and Restrict to users in [name of your organization] and its trusted organizations boxes are both checked.
By ticking these two options, the form will automatically obtain the email address from the user’s Google account (in this case, that’s the student who is logged in).
Your students will use this form to confirm that they are present in an online class. Note that this form doesn’t contain any fields to fill out. Your students simply have to click the Submit button to register their presence in the class.
At the beginning of each online session, you need to share the form link with your students via Google Classroom. I’ll show you how to do that later, but first, let’s set up the template.
How to register absences
In addition to the Attendance form, you will see that the spreadsheet also contains an Absence excuse form. You can use this form to register an approved absence. This form can also be used by parents to communicate when and why their child will be absent from class. It’s much easier than emails and everything is logged in the spreadsheet allowing administrators to check the information.
Unlike the Attendance form, the Absence excuse form contains a couple of fields to be filled out. These include the student’s email address, date of absence, justification, and a file upload field to attach files (such as medical appointment receipts, photos, videos, etc). This form is ready to use; you don’t need to check or adjust the settings.
2.4 Replace the Class spreadsheets with the Remote Class spreadsheets
As outlined before, Sheetgo’s attendance management template contains three pre-connected Class spreadsheets (History, Math and Science) for face-to-face classes. You now need to swap these for Remote Class spreadsheets.
Go to Google Drive, open the folder that Sheetgo created with the old Class files. You will find this folder under Google Drive > Sheetgo Templates > Attendance management.
Delete the 3 Class spreadsheets.
2.5 Replace the connections in the workflow
Open Sheetgo, locate and click on the Attendance management workflow in the bar on the left of the screen.
Click on the Connections tab in the right top corner. You will see there are three connections: Overview per student, Overview per month and Attendance complete check.
First of all, delete the connection Attendance complete check.
This connection is not required if you’re adapting the template for remote classes.
To delete the connection, click on the menu (⋮) next to the connection and click Delete.
Overview per student and Overview per month are Consolidate connections. This means they pull data from multiple spreadsheets (the Class files) into one file (the Attendance Management master sheet).
Sheetgo works by moving data from a source tab (sheet) to a destination tab (sheet) so when you connect the Remote Class files, you need to pay attention to the tab name. The tab name is the same as the connection name.
To start with, let’s connect the first new Remote Class file. In this example, the spreadsheet I want to connect to the template is called Remote French Class.
Next to the first connection, Overview per student, click on the menu (⋮) then Edit Connection.
The connection editor opens. At the top of the screen, Source Data: Multiple Files indicates that this is a Consolidate connection.
You will also see that Sheetgo displays the old files (History, Math and Science), although you have deleted them from your Google Drive. This is just so you can see the connections and replace them with new files.
Click on the first file in the list: History.
Now click on the Google Drive icon Change file.
Select the first of your new remote Class spreadsheets and click Done.
Here you can see I have selected my file Remote French Class and it appears in Sheetgo.
Select the file tab
Now for the important step: select the correct file tab. This is the source tab in my Remote French Class spreadsheet that contains the data I want to pull into the master sheet.
In this case, that’s Overview per student.
Here, I will replace the Science and Maths files with two further remote spreadsheets for my German and Spanish classes.
Click Save changes.
Repeat for the other connection
You have now replaced the files for one of the connections: Overview per student. Now it’s time to connect the files for the other connection: Overview per month.
From the workflow connections list, go to Overview per month, click on the menu icon (⋮) then Edit.
As before, replace each file in the list with your new remote files and select the correct source tab. This time, the correct source tab is Overview per month.
Click Save changes.
Step 3: Publish the forms
Inside each of your new Remote Class spreadsheets, go to the Attendance form tab. In the top menu bar, click on Form > Go to live form.
A new tab opens in your browser with the live form. Copy the URL and publish it in the Google Classroom page for that specific subject.
When you want to share the Absence excuse form, do it in exactly the same way.
Open the Absence excuse tab, then click on Form >Go to live form. Copy the URL and share it with parents or administrators so they can register and provide a reason for a student’s absence.
These forms are independent of Google Classroom, so you can publish them on your school intranet or send them by email or group chats.
Step 4: Complete the set-up of your remote attendance system
This remote attendance tracking template works in a similar way to Sheetgo’s original template. As you read before, normally teachers manually select P (present) T (tardy) E (excused) and U (unexcused) for every student.
In this adapted workflow, these inputs are automatically filled out by in-cell functions. These functions retrieve data from the Attendance form and the Absence excuse form.
It’s important to note that this adapted template requires both the student’s name and their email address (unlike the original template).
The student’s email address is used by the functions to cross reference information and verify which student has logged in and attended the class.
For this reason, you must fill out all student names and email addresses in the Instructions tab of each Remote Class spreadsheet.
Here’s how it works:
The function searches the Attendance form to see if the student checked in that day. If so, the cell will display a P (present) for that date.
When the student is not found in the Attendance form responses, the function will search the Absence excuse form. If the function finds an excuse for the student on that specific date, the cell will display an E (Excused) otherwise it will display a U (Unexcused).
Unlike the original template, this template does not include the T (Tardy – or late) status.
Step 5: Start and automate the Google Classroom workflow
If you’re an administrator and you haven’t already done so, share the new Remote Class spreadsheets with each teacher responsible, using Google Sheets’ sharing function.
Once everything is set up and you and your colleagues start using the template, it’s time to update the workflow. Each time you update the workflow, fresh data is pulled from the source spreadsheets (in this case, the teachers’ Remote Class files) into the destination spreadsheet (your attendance management master sheet).
You can do this manually by opening Sheetgo, clicking on the Attendance Management workflow and clicking Run in the left bottom corner .
Want to expand the template?
To add more source files to the workflow, copy one of your Remote Class spreadsheets and connect it using Sheetgo.
Open Sheetgo, click on your Attendance Management workflow then go to the Connections list. Click the menu icon (⋮) next to each connection then Edit connection.
Under Source Data> Multiple Files, you will see your current list of connected files.
Scroll down and click +Add another source file.
Locate the new file in Google Drive and click Done
Select the correct File tab, then click Done.
Repeat this for further files, then click Save changes.
Need help?
Having difficulties setting up the template? If you would like our team to build the system for you, or you’re looking for a more complex workflow, book a short call with our automation experts.
Did you like this post?
Share this article with your network by clicking on the social media buttons on the left.
