(UPDATED: These steps have been simplified!)
The most popular post on my site is about how to use Google Sheets as a gradebook for students, including allowing them to access their individual grades via a web-app embedded in this gradebook. I don’t use the web-app much anymore, but I still use the gradebook for keeping track of assignments, attendance, homework, quizzes, and, of course, grades.
For things like attendance and homework, I used to open up the spreadsheet and manually input them. However, now, I simply use my smartphone to do it. I open up my “class” folder on my phone, click attendance, and then fill out a Google Form. It saves a lot of time and is quite convenient. The biggest benefit, however, is that the data feeds directly into my main gradebook! This post will explain how to use Google Forms on your phone to feed into your Google gradebook. It takes a little time to set-up for the first time, but once you know how to do it, it’s a breeze.
Step 1: The Form
The form can be customized to meet whatever class needs you have. You can keep attendance, input homework, and even input participation and quiz grades. You will have to set up one form for each class. You should also be sure you have your full roster and will not be making any changes to it because, once set up, you shouldn’t change your Google Form. It’s also important to make sure the order of names in your roster is the same on your gradebook and form. Here’s what you should do. I’ll use attendance as an example:
- Open up Google Forms.
- Name the document something unique, usually with the class and section name first (e.g. L 104 for my Listening 104 class).
- Your first question should be a grid option.
- To input your roster you can:
- type it in, one student per line
- paste it in by copying your roster from a spreadsheet and pasting it into the first box – the additional boxes should population.
- Add your column labels. My example contains Present, Absent, and Late.
- View your form and submit some test responses. This will be important for the next step.
Step 2: Form Data
After making your form, you need to work with the data to make it so that it is in the right format to feed into your spreadsheet. This is actually quite simple.
- Click on “Responses” and click on the green spreadsheet icon.
- You can create a new spreadsheet and build your gradebook in there. Or, you can link the form to your already existing gradebook.
- Make a new sheet.
- In cell A1, input this formula
- =TRANSPOSE(‘Form Responses 1’!A1:n)
- n represents the column letter of the student at the bottom of your roster. In other words, look at the “Form Responses 1” sheet and look at the last column populated by a name. That column number will be n. In my example, that column is D.
- Your data should now be arranged so that student names are on the left column, dates are running across the top row, and responses are in the body.
- This will automatically update as responses are added (i.e. as you use the form).
Step 3: Calculating Data
Now that you have the data in your gradebook, you are almost finished. However, we probably want to calculate this data. There are many ways to do so. Because my data is text, I will want to calculate how many “Present”, “Absence”, and “Late” values I have. This can be done with the COUNTIF() function.
- Working in your gradebook, click on your first column of data (Column B) and insert as many columns as you need to the left. I have inserted four, Present, Absences, Lates and Total (for an average score).
- In the first column (Column B, “Presents”), in cell B2, use the COUNTIF() forumula: =COUNTIF(first_column:last_column, “criteria”)
- last_column:first_column are the same as above
- criteria is the value you want to count, for example “Present” or “Absent” or “Late”
- My example
- This will count how many times “Present” appears.
- Repeat this for the other columns you have created.
- In the totals column, you will have to decide how you want to calculate attendance. I will explain two methods.
- The first method is to calculate the average, which is the number of times present (inlcuding lates) divided by the number of classes. The formula is simple. Add the lates + the presents (it’s better to start right to left for some reason) and divide by the number of classes. Here’s the formula, based on the columns in the picture above: =C2+B2/COUNTA(first_column:last_column)*100
- The second method assigns points to each value. Presents are worth 1 point, lates are 0.50 points and absences are 0 points. A class of 10 days with 6 presents (6 points), 2 lates (1pt), and 2 absences (0 points) equals 7 points out of 10 days, which equals 70%. Here’s the formula:
- B4 equals my count of presents times the point value I wish to assign
- D4 equals my count of lates times the point value I wish to assign
- first_column:last_column is the same as above
- However you want to calculate the value of attendance, all the data is there for you to manipulate.
This pretty much finishes attendance. However, you need to make a convenient way to access the form quickly. Hence, the next section.
Step 4: Smartphone Shortcuts
The best way to access your attendance or other forms is to have a shortcut on your phone. I recommend emailing yourself links to the live forms and then following one of the guide below.
I set all my shortcuts in their own folder for ease of access.
Modifying Forms for Other Types of Input
Google Forms is useful for more than attendance. I use them for homework and have used them for quizzes too.
For homework, you would follow the directions above. However, you would add a short answer question where you could input the homework name.
Your form responses will look a little different.
The only difference between the Attendance and Homework forms would be in transposing. Before, when we transposed the attendance, we chose to begin with column A (the date). For homework, we simply need to begin with column B, the homework name.
The rest of the directions above would be the same.
For quizzes, the form itself takes a bit more to create, as you have to make a short answer question for each student, but the same directions for homework (the transposing) remains.
The only note that I can think of is that if you need to change a value, grade, response, etc, you must do it in the Form Response sheet, not the sheet with transposed values, nor the gradebook. This is why you should include a URL to your form response sheet in your gradebook.