Use Google Forms and Google Gradebook to Collect Attendance and Homework

(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:

  1. Open up Google Forms.
  2. Name the document something unique, usually with the class and section name first (e.g. L 104 for my Listening 104 class).
  3. Your first question should be a grid option.
  4. To input your roster you can:
    1. type it in, one student per line
    2. paste it in by copying your roster from a spreadsheet and pasting it into the first box – the additional boxes should population.
  5. Add your column labels. My example contains Present, Absent, and Late.
  6. View your form and submit some test responses. This will be important for the next step.
    1. You can access the live form by click on the “eye” icon in the right-hand corner of the screen.atform15

 

atform1

Basic attendance form – editing view

atform2

Basic attendance form – student view

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.

  1. Click on “Responses” and click on the green spreadsheet icon.
  2. You can create a new spreadsheet and build your gradebook in there. Or, you can link the form to your already existing gradebook.
    atform3

    To access the response spreadhseet

    existingsheet

    Choose where the form data goes

    atform5

    Form responses – note, data populates vertically. Transposing the data in another sheet will make it populate horizontally

  3. Make a new sheet.

    atform4

    Make a new sheet

  4. In cell A1, input this formula
    1. =TRANSPOSE(‘Form Responses 1’!A1:n)
    2. 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.

      atform6

      Formula for transposing data

  5. 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.
  6. This will automatically update as responses are added (i.e. as you use the form).

    atform7

    Transposed data

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.

  1. 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).

    atform11

    Example columns to the left of the data

  2. In the first column (Column B, “Presents”), in cell B2, use the COUNTIF() forumula: =COUNTIF(first_column:last_column, “criteria”)
    1. last_column:first_column are the same as above
    2. criteria is the value you want to count, for example “Present” or “Absent” or “Late”
    3. My example

      atform12

      Formula for counting all “Present” responses

    4. This will count how many times “Present” appears.
  3. Repeat this for the other columns you have created.
  4. In the totals column, you will have to decide how you want to calculate attendance. I will explain two methods.
    1. 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

      atform13

      Calculating an average

    2. 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*1)+(D4*0.5))/COUNTA(first_column:last_column)*100

      1. B4 equals my count of presents times the point value I wish to assign
      2. D4 equals my count of lates times the point value I wish to assign
      3. first_column:last_column is the same as above

        atform14

        Calculating attendance based on different values

  5. 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.

hwform1

Example homework form – editing view

Your form responses will look a little different.

hwform2

Example responses for homework. Note column B contains the homework names.

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.

hwform3

Transposed homework responses. Note the formula range starts at “B” not “A”

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.

qzform1


Quiz form – editing view

qzform2

Quiz form – student view

qzfomr3

Quiz form – responses

qzform4

Quiz form – transposed

Notes

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.

9 thoughts on “Use Google Forms and Google Gradebook to Collect Attendance and Homework

  1. Tyson Seburn says:

    I am going to have to try the script you refer to in the linked post for my “Grades Master”. Showing students only their grades has been a complete nightmare where otherwise Google Sheets is so beautiful. Thanks!

    • Anthony Schmidt says:

      The script works but I no longer use it since it became tiresome to make 4 different gradebooks with the script 5 times per year!

  2. Jessica says:

    I am not able to add columns to the left of the first information recorded. The column appears and then disappears. Is there away to had the data transposed to an additional sheet page? Thank you.

Leave a Reply