Google Apps Script
Time to setup
Save time with this Grade and Attendance Tracking System
After listening to our thousands of users from the education the education sector that use Sheetgo to automate and customize their student results tracking, we noticed that it is difficult for those that use Google Classroom to integrate data from Classroom with Google Sheets in an automated way. This grade and attendance tracking system was used to alleviate this problem and save teachers 2-3 hours per week.
This template will take about 30 min to set up per Teacher at the beginning of the school year. During the year, all reports are going to be created automatically, while the teacher will only focus on assigning grades in Google classroom and attendance in a single spreadsheet.
How this template works
There are 3 steps to set up your template for each Teacher:
- Grades: Will be exported automatically from Google Classroom to a spreadsheet, pulling the information of the students and test results from the teachers Classroom account
- Attendance: After following steps below, you will create a spreadsheet based on our template to input absences.
- Dashboard: Will compile the information of attendance and grades to generate a final report with students’ results.
For a preview of how the solution will look like when we’re done setting up, check this sample folder.
Let’s configure your system:
Step 1 – Grades Tracking Set-up
Review your Google Classroom account to create new classes for the year and archive the old ones. Now create the grade spreadsheet by following these steps.
- Click in this link then click “Make a Copy” to create a copy of the spreadsheet Grades Teacher. Don’t you worry it will start as a blank sheet!
- After opening your spreadsheet wait for the Classroom menu appears (about 30 seconds).
- Click on ‘Classroom -> Enable classroom api’. Accept the required authorizations and then you will go to Classroom API webpage and prompted to click to enable it.
- Go back to the Grade Teacher spreadsheet, click on ‘Classroom -> Start’.
The data from your active classes will be imported from Google Classroom to this Grade Teacher spreadsheet and it’ll be updated automatically everyday at 01:00 AM.
Step 2: Attendance Tracking Set-up
This is a tricky step, so be patient! It will be worthy. Completing this step will enable you to use a single spreadsheet on your mobile device to track student attendance for all of your classes, automatically, for the whole year and report it, automatically, at the end of the school calendar year.
Import student data from Grade Teacher spreadsheet
- Install the Sheetgo add-on to create the connections to automate data transfer. (Questions? Check out this article).
- Create a copy of the spreadsheet: Attendance teacher
- In the spreadsheets the spreadsheet click ‘Add-ons -> Sheetgo -> Start’
- In the Sheetgo sidebar, create a connection by:
- Clicking on the plus button to create your first connection
- Click on Select Files, find the Grade Teacher file, and select it
- Select one of your class tabs
- Leave the Connection Name as the default SG_class name
- Enable “Automatic Updates“
- Enable Filter, select Query and insert: Select A,C,B,E
- Save this connection and a tab named SG_class name will be created
5. Now, click on Make a Copy at this connection and change the source tab for the other class and change name to be SG_class name
6. Repeat the steps above until you finish creating connections for all of your classes
Create attendance Class sheets
- Open Attendance Teacher and substitute the tab name Attendance sheet sample for the name of one of the classes you transferred data from previously, i.e change to “Arts” to bring data for them tab SG_Arts you created previously
- In cell A1, change the Query formula range to bring all data from your SG_class name, ie. SG_Arts sheet
- Duplicate this tab, repeat steps 1 and 2 until you have one “Class tab” for each “SG_class name” tab.
You’ll use these attendance sheets to input the absence of your students every class day.
An automated script will erase absences that you had input everyday at 3am and the date of the sheet will be updated automatically to today. So, your spreadsheet will always be clean and ready for you to input new absences every day.
Tip: Access this Attendance Spreadsheet in you mobile device and easily input student absences and let Sheetgo do all of your reporting automatically.
Create a Master absence sheet
- Open Sheetgo and click on the plus button to create a connection
- Select your Attendance Teacher spreadsheet as Source
- Select one of the class tabs
- Click on Add more
- Repeat items 3 to 5 until you finish selecting all existing class tabs
- Go to settings
- Change the Connection Name to Master Absence
- Enable Automatic Updates to run Daily at “11 pm to midnight”
- Enable Filter > select Condition > select Column E and text contains now type Absence
- Save the connection to create the Master Absence sheet
This sheet will show in daily basis the absent students of all your classes.
Create historical data
- Open Sheetgo
- Click on the plus button
- Select as source your Attendance Teacher spreadsheet
- Select your Master Absence tab
- Go to Settings
- Change the Connection Name to Historical Absence
- Enable Automatic Updates to run Daily at “midnight to 1 am”
- Enable Append
- Click on the check mark and your Historical Absence sheet will be created
This sheet will transfer from the Master Absent sheet the Today Absence and append a new entries everyday to create an automated historical record.
We suggest you erase all sample sheets to have only the ones you are going to really use to process your data.
When finishing Step 2, you will see at the Attendance spreadsheet with 3 groups of tabs:
- SG_class name tabs: with the information from students names and class codes from the Grade spreadsheet that you created from Google Classroom
- Class name tabs: these are the ones for you to use to input the absence information when giving the classes
- Master and Historical Absence: these are the sheets to process your data automatically to support the dashboard and reports creation at Teacher Dashboard spreadsheet
Tip: If you want to hide all SG_class tabs, Master and Historical sheets, these will help make your spreadsheet more friendly for your daily usage.
Step 3: Teacher dashboard set up
- Create a copy of the spreadsheet: Teacher Dashboard
- Open Description tab to insert the annual calendar and parameters in the blank fields
- Click ‘Add-ons -> Sheetgo -> Start‘
- Click on the plus button
- Select the Attendance Teacher spreadsheet and then, Historical Absence sheet
- Go to settings
- Enable Automatic Update to run daily at “2 am to 3 am”
- Click on the check mark and your SG_Historical Absence sheet will be created
- Click on plus button to create a new connection
- Select the spreadsheet Grade Teacher and one Class tab
- At the left, click on the 3 dots and select Duplicate Source
- Select a new Class tab
- Repeat steps 11 and 12 until you have all classes selected
- Go to Settings
- Change connection name to Master Grade
- Enable Automatic Update to run at “1am to 2 am”
- Click on the check mark to create the connection
The Master Report sheet will show you the performance of all your students.
At the Class Report Generator, you can insert the class code and generate specific class reports to save as pdf document or print.
You’ve successfully created your Classes Grades and Attendance Online Worklow! Now you can control and monitor your students grades and attendance and also save a lot of time per week. If you are a programmer and would like to collaborate on this project, help us improve our code so that the solution keeps on evolving.