Using Google Forms with secure verification numbers


Google Forms is a great tool for using in the classroom and at schools. However, one of the first questions I usually get asked in teacher training seminars is about how secure it is. If you use a learning management system like Moodle every student has to log in with his or her credentials and it is almost impossible to rig a poll or survey.

With Google Forms there is no such security measure in place as a form is just an open webpage (unless you have GAFE sign-in available). Of course, you can embed a form in a password protected Google Site, but this doesn't really solve anything, as students still can submit several forms and distort the result of the poll.

One quick solution for making a Google Form more secure is to create a dropdown list with your students names, as you can spot duplicates immediately. This works fairly well if you use the form only with one class and if the poll doesn't have to be anonymous, like in the case of student - teacher feedback. Imagine, however, 500 students voting for their representative in school. In such a case a nice and easy solution would be to use verification numbers (or TAN/transaction numbers). Each students gets assigned (preferably emailed) a number which can be used to submit a form only once. You can’t prevent illegal submissions, but you can find them quickly in the response sheet created by the form.

Here is how it works: I have created a sample sheet, which you can use to create your own forms (just make a copy and use the sheet as a template). I am using three simple steps:

  1. create random verification numbers/TANs
  2. email the verification numbers and a link to the form to my students (their parents, other teachers, etc.) using mail merge (autocrat add-on, etc.)
  3. check the response form and if necessary delete illegitimate responses

I have used a different sheet within one spreadsheet for each of these steps.

For creating a six digit random number I chose the formula: =round(RAND()*10000000). The second column is for checking if there are no duplicates among the random numbers (there should be an equal number of unique numbers as random numbers). In column C I copy (values only) the unique number as the random numbers change every time you reload the spreadsheet and you need a set of numbers that don’t change anymore.  

The second sheet is for mail merge with the free autocrat add-on. All it does is email to each student a unique number and the link to the form. If you don’t want to use email in order to make your poll completely anonymous you can also print the numbers out and give one each to your students.


The third sheet is the response sheet created by the form. I have built in two mechanisms for checking if the response is valid:
Checking if the validation number is one of the numbers created in the first sheet by using data validation. You can spot invalid numbers because they are marked with a little red triangle. The second mechanism is using the unique formula again to check if there are any duplicate numbers (repeated submissions by one student).

NB: only delete the first FALSE row and not all of them simultaneously, as the mechanism automatically leads to all subsequent rows to show a “FALSE”. Deleting the first FALSE corrects all other responses again.

This setup takes only a little bit more work than creating the usual poll with Google Forms and can be easily reused. In case you want to do that all you have to do is create a new form within the same sheet, copy a new set of random number over to column C in the first sheet and apply the same data validation as well as the unique formula again.

Here is the link to the sample sheet again.

Beliebte Posts