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. Continue reading

The Ultimate Google Gradebook (with Individual Score Reports) – updated

(Updated 11/10/2016)

It seems Google has deprecated several functions in this script, meaning it may not work. Since I am neither a programmer nor the original author of this script, and since I no longer use it, I cannot offer any support. However, you are welcome to attempt to get it working (I recommend StackExchange for seeking help), and, if you do find a solution that works, I’d be more than happy to update my post again with the proper script. Having said all this, I may take a closer look this winter break and see if I can modify it to work.

(Updated 3/26/2014)

I have made an amazing little gradebook on Google Spreadsheets which allows students to view their individual grades in realtime. This means students click on my gradebook link and are taken directly to a page that shows only their grades. Any change I make in my gradebook will be reflected in their grade report automatically when they refresh or visit again.

Numerous people have already written and demonstrated how Google Sheets can be used as a gradebook for teachers, but most (if not all) of these gradebooks do not or cannot allow students to view only their grades. Instead they must view a whole roster’s worth of grades, which can be confusing, disorganized, and frightening if their names are there or other students know their student numbers. Using Google Spreadsheets, Google Apps Script and help from a super nice gentleman on StackOverflow, my students now have the ability to view their individual grades. They get a detailed report too, which includes their overall grade, attendance and participation breakdown, and notes. All this is easily customizable and I intend on explaining how you can make your own below.

Caveats

  1. This script only works when students are logged into Google. Therefore, you must use their Gmail address or an email address connected to Google.
  2. Your Google Spreadsheet must be made public (shared as “Anyone who has the link”) but is still secure because the link is hidden in the script and is in no way accessible to users.

Step 1: The Gradebook

There are hundreds of different gradebook options. Any of them would work with my gradebook app. I used a modified version of the weighted points gradebook here. This is a very powerful gradebook and easily converts to Google Sheets (simply upload it to your drive, right-click, and choose Open With -> Google Sheets). For some other examples, you can see this webinar or browse the countless templates from the Google Template Gallery. Any gradebook is fine, so long as it suits your needs.

Step 2: The “StudentGrades” Sheet

In my gradebook I have the following sheets “Gradebook” for overall grades, exams, and projects, “Attendance”, “Participation”, and “Names” for student names, numbers, and email address. I also have a sheet called “StudentGrades” which combines all these sheets into one meta-sheet.

Thescript pulls individual student data from a single meta-sheet. This would be a sheet that combines overall grades, assignments, attendance, participation, etc. The stuff you normally have separate sheets for. You would still have separate sheets for them, only they become combined in a super-sheet called “StudentGrades” (or whatever you choose to name it).

Normally, grade data is laid out in rows. This script looks at data in columns. This isn’t a problem. You can use the TRANSPOSE function to copy rows from other sheets and change them into columns in the “StudentGrades” sheet.

=TRANSPONSE(A1:H7)

The first column is for labels such as “Email”, “Name”, “Attendance”, etc. You can style this manually, or pull in data using TRANSPOSE. The remaining columns will be for students. One student per column. Student emails must be included in the first row of each column. The following video will explain how to create the meta-sheet. (Note: once you make your meta-sheet, you still need to follow the instructions in Step 3 below.)

Step 3: The Script

In Google Sheets, select Tools -> Script Editor and then copy and paste the code from here (updated 3/26/14). Be sure to change the spreadsheet key. In addition, read through the code comments (they begin as two slashes //) so you understand some things you can change.

Before the code can be used, it must be saved as a version. Go to File -> Manage Versions and enter in a number or name in the textbox. Then go to Publish -> Deploy as web app… Select the version, choose “User accessing the web app” under Execute, and “Anyone” under Access. Then hit Update. You will need to save a new version and re-deploy for any changes you make after a deployment. Test out your gradebook. Note: your email should be included in one of the columns for testing purposes.

Get the script here.

Step 4: Notes, Bugs and Annoyances

  1. Make sure your spreadsheet is shared as “Anyone with link”, but don’t worry – it is still secure.
    • If anyone has a fix for this, please let me know!
  2. In the “StudentGrades” meta-sheet, you may need to format numbers in different ways until you get it to look the way you want. Sometimes the student report shows numbers with an extra “/ 20” on them unless they are in Plain Text. Go to Format -> Number.
  3. When first using this web app, students will see a warning page that asks them to authorize the app. As far as I know, there is no way to avoid the warning. But, it should only appear once.
  4. If you find any other bugs or annoyances, please let me know in the comments!

 

Class Superlatives (with Google Forms)

For an end of semester activity, I decided to wrap up with something random and fun – a combination of closure to the classroom community we had built all semester and a throwback/head nod to the senior superlatives we do in US high schools.

The context for this activity can be anything: a one-off activity, an activity that tests specific vocabulary skills, an activity based around the grammar of superlative forms, after a series of presentations, etc. The activity is simply getting students to choose a few candidates for each question, voting on a Google Form, and then seeing the results. The form takes only a few minutes to setup and there are no formulas needed to calculate the results. Google does that for you.

I did my activity after showing a video I put together of students doing 2-minute summaries of their research papers. Watching a 30 minute video of other students simply talking could be a bit taxing on the attention system, even for the best learners, so I gave them something fun to do while watching:

Class Superlatives

While watching today’s video, choose several students who best fit the questions below. After the video, visit http://goo.gl/eqstb4 and vote for the best student for each question. Do not choose your own name.

 

1. Who had the best topic?
(1) __________, (2), __________ (3), __________

 

2. Who had the best pronunciation?
(1) __________, (2), __________ (3), __________

 

3. Who is most likely to be an English professor?
(1) __________, (2), __________ (3), __________

 

4. Who is most likely to be yelled at by an English professor?
(1) __________, (2), __________ (3), __________

 

5. Who is most likely to survive the zombie apocalypse?
(1) __________, (2), __________ (3), __________

 

6. Who is most likely to suggest 삼소 for a first date?
(1) __________, (2), __________ (3), __________

 

7. Who is most likely to win a fight against Batman?
(1) __________, (2), __________ (3), __________

 

8. Who is most likely to believe in Santa Claus?
(1) __________, (2), __________ (3), __________

For the Google Form, I simply created “Choose from a list” question types. To input the names of each student, I selected them all from my gradebook and simply pasted them into answer options on the Google Form, which automatically separates them.

Choose From a List

After watching the video, students then visited the Google Form by either entering in the URL or scanning the QR code printed on the worksheet. After I saw that all students had finished voting, I selected “Responses -> Summary of responses” from Google Form’s toolbar.

responsesmenu

This brings up the results of the forms, broken into a pie chart and a easy to read table. The pie chart is most useful here and clearly shows the most voted for for each question.

Pie Chart

You could end the activity there, congratulating the winners. As a follow-up, you could get voters to explain why they voted for a particular person. You could also get winners to guess why they were voted for.

I’m sure there are lots of different ways to structure and implement this activity. It’s simple for the teacher, and fun for the students.

My Upcoming Semester with Doctopus

Doctopus is a wonderful extension of Google Drive which is likely a dream come true for many teachers. Doctopus is an addon for Google Sheets which distributes files or folders to students. You can set the different permissions (view, comment, edit), change ownership, embargo documents for grading, and even leave feedback with a Chrome extension called Goobric. In addition, you can create dropboxes for individual students in order to better organize their work. In other words, Doctopus streamlines assignment distribution, submission, and in many cases feedback.

All it requires is a roster with names and email addresses. It is relatively easy to use, but does require some practice. Although one could use templates, that still requires students to find the template, rename the template to the correct title (not always easy), and then share the template. You would be surpised to find that many university students cannot manage a task like this.

So, how will I use Doctopus in my university writing classes next semester?

I will have many writing assignments. Several shorter ones (sentence- or paragraph-level writing assignments) will be completed using Google Forms. I will give feedback on individual sentences or paragraphs, use a mail merge script called Autocrat to send this feedback to students, then I will remove names and student numbers and make the whole spreadsheet available for student reference (if applicable).

Doctopus will be used with major and longer assignments. I have decided against creating dropboxes for my writing courses, as I want to organize submissions by assignment, not by student.

  1. After learning how to use Google Drive, I will assign use Doctopus to distribute an assignment to students, making sure to give them full editor rights so they can share it with a partner for peer editing. 
  2. I will then embargo the document after the due date so that I may grade them and give feedback. 
  3. I will use the comment tool of Google Drive to leave on-paper feedback. 
  4. I will also give them a rubric for their final grade. This will either be pasted in or given as a second document. If it is pasted in, I will remove the student’s peer editor from the file so that they do not see the final grade. If I give a separate document, I will share it with individual students via their email address. This takes no more than 5 minutes.
  5. I will also rename the document, appending ” – Feedback” to file names to indicate they have feedback.
  6. If it is an assignment that must be revised, I will make a copy of their document for my records and then change the editing rights one again. The drawback of this is that all my comments will be lost, however, I am working on a script that can copy document comments. In any event, it shouldn’t be a problem. I also have the ability to transfer ownership of the document to them, but right now I see no use for this. Maybe at the end of the semester I will use it, if I plan on deleting student work to save space.
That’s it! Pretty simple, eh? It beats killing trees and handling dozens or hundreds of papers. It beats getting individual emails and downloading hundreds of documents, all named differently. It also beats the hassles of Google templates and forgetting to share. Doctopus seems like a clear choice here.

Google Forms for In-Class Text Walls

Text walls are a relatively recent phenomenon in education. They are a way to engage students without interrupting the flow of the class. Text walls are interactive, real-time “walls” in which students can post questions, answers, feedback, and so forth, using their smartphones. Ask a question to check comprehension. Students can all text their answers and you can see who does or doesn’t understand. Students can send questions during lectures. Students can give examples via the text wall. Students can post highlights or thoughts from group discussions. All of these things can be fed directly back into class, making the learning and teaching experience much more interactive and dynamic.

There are many text wall services that can be found on the internet. A number of them are paid services, but there are some free ones as well.  Socrative is an excellent example of a free student response system that goes beyond the basic text wall. They offer exit tickets, quizzes, and even games. These can all be done in the classroom, via student smartphones.

For simple text wall uses, Google Forms is an excellent choice. All you have to do is set up a simple form, point students to it, and open up the Google Spreadsheet. The forms can be as simple or as complex as you want. For simple text walls, a single paragraph input box would suffice. You can require names, make it anonymous, or even semi-anonymous (meaning you can hide the “names” column on the spread sheet so only you can see them). You can set up voting and add simple functions in the spreadsheet to calculate percentages. You could even set up live quizzes that are self-grading, but that may be a bit more complicated.

To point students to your live text wall form, you could add a link on your website, or display a QR code that brings students directly to the Google Form. If you will use the text wall consistently, you could have them bookmark it as well.

Response will appear in real time in the Google Spreadsheet. You can choose to display it or keep it hidden. If you choose to display it, you can hide any unnecessary columns, such as the timestamp or names column. You can give feedback directly in the spreadsheet, and even share it back to the students for future reference.

In reality, there are many different possibilities for using Google Forms as a live text wall. I will give an example of how I plan to use it next semester in my composition classes.

I will have a single paragraph input form that will be linked to the course page. I often have students writing practice sentences using whatever technique or grammar point being discussed. Let’s say I am teaching subordinating conjunctions. In particular, I am teaching the nuances of concession connectors such as “nonetheless” or “nevertheless”. After explaining the different functions and showing examples, I can have students make their own examples using exercises in the textbook or their own ideas. Students draft their examples in their books and on their smartphones. On the screen is the Google Spreadsheet ready to receive their responses. After an appropriate amount of time, I direct students’ attention to the spreadsheet and begin discussing their sentences. I comment on good sentences, have the class fix mistakes, and do any editing or notes in a column next to the originals. After class, I copy the spreadsheet and share it back to the students so they can review how to use subordinating conjunctions of concession.

This is a single, simple idea, but one which is easily executed and would be very effective in enhancing the students’ learning experiences. Using Google Forms as a text wall or student response tool is limited only to our imaginations! Can you think of any other uses? Let me know in the comments.

Self-Grading Quizzes with Google Forms

Lately, I have been making one or two quizzes a week using Google Forms. I send these to my students to take after we wrap up a unit. They are short quizzes which assess whether they learned and can apply the grammar or vocabulary I was teaching. I then look for commonly missed questions to readdress back in the classroom. I usually ask several open-ended questions in which students must make a sentence. I also choose sentences that use the grammar/vocabulary point well, use it poorly, or have another common mistake. After grading the quizzes, I share them back to the students. Students can see their answers, their graded answers, the correct answers, notes about the correct answers, and the class average for each question. In addition, I leave comments on students’ open-ended answers to help them understand their major or minor mistakes.

Except for open ended responses, the quizzes are self-grading. This makes quizzes on Google Forms quick and convenient for teachers. I was inspired to try this after reading this post by Andy Wolber at TechRepublic. Making the quiz is super easy, but making it self-grading takes a small bit of work. After doing it a few times, it too becomes pretty easy. Google Forms saves its results in a spreadsheet, and then all that is required are some spreadsheet functions (the same that would be used in Excel) to grade and reorganize the data.

Below, I will take you step-by-step through how I do this, including how I share it with students without exposing their names or student numbers to each other. Before starting, see my example here.


Step 1. Make a Google Form
Making Google Forms is really easy, so I am not going to explain this part. If you need help, a quick search will reveal videos and guides to help you.

Step 2. Take the Quiz Yourself
This ensures it is working correctly and gets you the correct answers on the form so you can have an answer key.

Step 3. Make an Answer Key
Make a new worksheet called “Answers” or “Key” or any other name, but no spaces. Rename the “Form Responses” to “FormResponses” or “StudentAnswers” or any other name, but not spaces. In “Answers”, you can either display the correct answers horizontally in columns (minimal work, less organized, many questions) or vertically in rows (more work, more organized, less questions).

Display Answers Horizontally
In the first cell of “Answers”, type =StudentAnswers!A1 to copy the A1 cell. You can now copy this cell and drag it across the entire row. Hit paste, and all the questions from “StudentAnswers”, row 1 will be copied. You have now copied all the questions. Move down a line to “Answers”, row 2 and paste again. All your answers (the first form entry) will be copied as well. You can now select any columns you don’t want, such as the timestamp, and delete them. You should be left with only questions and answers.

Display Answers Vertically
In the first row of “Answers”, type =TRANSPOSE(StudentAnswers!A1:_2) to transform rows into columns. A1 represents the first cell of row one in “StudentAnswers”. The _ should represent the last column of data in row 2. For example, if my quiz requires a student’s name and then has 10 questions, it will be 11 columns long, plus 1 for the timestamp (total, 12 columns). My data would end in the L column. My questions would be on row 1, and my answers on row 2. So, I would type =TRANSPOSE(StudentAnswers!A1:L2) and that would put all the questions in the A column and all the answers in the B column. Therefore, questions and answers would be side by side.

Copy the newly created columns. Right click on the A1 cell and select Past Special > Paste values only. Delete any rows that are unnecessary.

Step 3. Set Up Answer Page
Delete any unnecessary rows so you only have the questions and answers. Here, if you want, you can offer explanations of answers in a new column. If any answers have an alternate, create a new column and add them where appropriate.

Step 4. Set Up Self-Grading Sheet
Create a new sheet, “Graded” (or another name). In the first cell of “Graded”, type =StudentAnswers!A1 to copy the A1 cell. You can now copy this cell and drag it across the entire row. Hit paste, and all the questions from “StudentAnswers”, row 1 will be copied. You have now copied all the questions. Delete any unnecessary columns. If you want any data brought over from the original form that will NOT be graded, such as a name, student number, etc., use the function =StudentAnswers!ColumnRow to copy over your cells in row 2.

Working in row 2, under your first question use the function =IF(StudentAnswers!ColumnRow=Answers!$Column$Row,”Correct”,”Incorrect”). StudentAnswers!ColumnRow would be the student’s first answer in row 2 (eg. D2). Answers!$Column$Row is the correct answer in the “Answers” sheet (eg $A$2). The $ symbol means that, as you copy and paste this formula to other cells, it will still point to the correct answer.  “Correct” means that if the answer is correct, the word “Correct” will display. “Incorrect” does the same. If you want to use points, replace “Correct” with 1 (no quotes) and “Incorrect” with 0 (no quotes). When you are finished, press enter and the desired result should display. If it is correct, you can paste this cell along horizontally and all of your answers will be scored. If this works out, paste vertically and your students’ answers will be graded as they are submitted!

Alternate Answers
Dealing with alternate answers is easy, so long as there are not many of them. In the following example, the student’s answer is in StudentAnswers!A1, the correct answer is in Answers!B1, and the alternate answer is in Answers!C1:

=IF(StudentAnswers!A1=Answers!$B$1,”Correct”,IF(StudentAnswers!A1=Answers!$C$1,”Correct”,”Incorrect”).

This formula checks to see if the student’s answer matches my answer in B1. If it doesn’t, it checks C1. If it still doesn’t match, it prints “Incorrect”.

Open Ended Answers
Use the simple copying formula, =StudentAnswers!B2, where B2 represents the open-ended answer. Then, working in the “Graded” sheet, you can simply type “Correct” or “Incorrect” (or 1 or 0) over their answer and it will be counted in their total and the averages. This does not affect their original answers in the “StudentAnswers” sheet.

Step 5. Totals
In your “Graded” sheet, you will want a new TOTAL column. If you used points (1 point for correct, o for incorrect), you can use a simple SUM function: =SUM(B1:K10). B1 represents the starting point, B10 represents the ending point. Change them as necessary.

If you used the Correct/Incorrect marking system, you can use the COUNTIF formula: =COUNTIF(B2:K2,”Correct”). This formula counts how many times “Correct” appears in the desired range.

You can make this a percentage of 100 by simply adding /10*100 after each formula. Replace 10 with the number of questions you had.

Step 6. Disguising Student Numbers and Names
This step depends on the information your required on the form. I usually delete the name column and keep only the last four of the student number. To disguise the student numbers, you can use the CONCATENATE function: =CONCATENATE(“*****”, RIGHT(C2,4)). ***** represents what you want to be displayed instead of the numbers. RIGHT represents where the function will begin counting. the 4 represents how many digits from the right will be kept. C2 points to the student number. After concatenating all the student numbers, I copy the newly disguised ones, right click, paste special > paste values only. Then, I delete the original column.

Step 7. Averages
Averages are useful for both students and teachers. You can either show total score averages, so students can see if they performed above, at, or below average, or per question averages so you can see which questions were the most difficult.

Total Score Average
If you want to display the average score in the class, decide where you will put this information (maybe next to every student’s total), then use the AVG function: =AVG(F2:F20). F represents the TOTAL column. 2 is the the first score, 20 is the last. The average score will be printed. If you are using “Correct” instead of points, you can use the following formula: =COUNTIF(Graded!C$2:C$49,”Correct”)/COUNTA(Graded!C$2:C$49)*100. COUNTIF counts how many times “Correct” appears, and divides it against COUNTA which counts how many cells are not empty in a range. *100 gives you a percentage.

Per Question Average
I usually use per question averages to determine which questions need to be readdressed. I put the averages next to the answers on the “Answers” sheet. You can follow the same formulas above, just remember to use a range of cells in a single column (a single question) on the “Graded” sheet.

Step 8. Share Back with Students
After grading the quiz, getting the averages, deleting names, and disguising student numbers, you can now share back your spreadsheet with students so they can check their scores. Leave comments on any open-ended answers that need them using Google’s comments tool.


That’s it! I know that it seems like a lot of work, but after the first or second quiz, it will be quite easy. Hopefully I have explained my steps well. If I haven’t and you are still interested in my method, leave me a comment and I may make a screencast of this process. Let me know how your quizzes go. Happy quizzing!

Google Forms for Collecting Student Data

Collecting student data is a commonplace activity during the start of any course. Basic student info like name, contact information, experience, demographics, and so forth can be vital for any teacher. Skill diagnostics or needs analysis is another kind of data that is important to collect, which can be used to shape the direction of a course. Traditionally this is done as a paper questionnaire handed out at the beginning of the semester. However, organizing data spread out over a dozen or a hundred pages is not easy. Comparing the information or getting “the big picture” is equally as challenging. Google Forms can easily take care of this for you.

Google Forms are easily constructed web-based forms which save their data in an Excel-like spreadsheet. Making a form is pretty self-explanatory. The form can be given out as a shortened link, or as a QR code, and students can take it right their on their smartphones if they have them or on any computer.
Responses are instantly recorded. Once you access the responses, you can do all sorts of data manipulation techniques. You can easily sort or filter the data in any way you’d like. Using the same functions as Excel, you can find averages, count occurrences, grade answers, or anything else you may want to do.

How I Use Them
At the start of the semester I create a form that asks for students’ basic information, their career goals, a self assessment of their skills, and how they study English. I embed this into a private post on my blog. It can only be accessed by registered users. I do this to make sure all students register for my site. I collect their emails and phone numbers and add then to my Excel grade book. The emails are essential for viewing their grades online through the KB Gradebook plugin I use. I then read all their responses in order to get a good picture of who my students are. If i notice students are interested in working on pronunciation, culture, expressions, etc., I make sure to add that to my teaching focus.

At the end of the semester, I make a similar form with similar questions and then compare their responses to see if they feel they have improved and if they generally learned what they wanted. The end of semester questionnaire also asks students to evaluate my course and my teaching. I will write more about these types of questionnaires in a separate post.

Tips
1. You should ask a question that will allow you to sort their responses in the same way your attendance sheet or gradebook is organized. For example, students are sorted by student number so I require that information and then sort my sheet in that way. I also do this so I can quickly see who did my survey and give them the proper participation points.

2. Make sure to do an exit survey!