A Google Forms-based prototype

When starting out on this project, one of my first thoughts was that I might harness Google Forms as a delivery mechanism. This would bring several advantages:

  • Uses existing infrastructure: Google gives all schools the ‘education’ version of apps for free
  • Pupil data sits within the school’s Google account: Much less security concern

However, my early attempts failed and I went down a different route. In the intervening period Google have improved both Forms and Sheets such that it is now much more feasible to use them for delivery. Therefore, this evening I have put together a first stab at a certainty-scored quiz and it works pretty well!

How to use:

Make a copy of the Class Assessments spreadsheet. (Use your school Google account as there will be pupil data going into this spreadsheet. The copy will be private to your account).

  • Rename your copy to something more useful (i.e. class name for secondary, subject for primary)
  • Add the pupil names in the first row of the ‘Summary’ sheet

Now make a copy of the Template Test form

  • Rename this with the test subject
  • Write some questions
  • Go to responses and click on the little green icon (Create spreadsheet)
  • In the popup (Select response destination) choose ‘Select existing spreadsheet’ and then choose your Class Assessments spreadsheet

Now pop back to the Class Assessments spreadsheet where a new sheet will have been created (Form responses 1).

  • Rename this (i.e. [Test name] responses)
  • Duplicate the ‘Analysis template’ sheet and rename it (i.e. [Test name])
  • Add the name of the responses sheet (i.e. [Test name] responses) to the top-left cell of the of the analysis sheet. You will know if this has worked because the questions will appear along the top row.
  • In the cell beneath each question enter the correct answer

At this point, you are good to go. Share the test (form) with your class by whatever means you have.

The summary sheet

This is optional but, if you should do a few of these tests, you may well want a reference to show trends. To get it working, simply fill out the top row with the name of the analysis sheet (i.e. [Test name] if you have followed my recommendations).


  • Pupil names: I’ve built this on the premise that trying to get pupils to authenticate is an unnecessary complication. Instead, I have simply provided a ‘Enter your name’ question at the beginning. In the analysis sheet, pupils will appear in the order that they submit their answers. For the summary sheet, it will look up pupils by name from the Analysis sheet. To get this working slickly you should ensure pupils enter their names to match those you have in the summary sheet.
  • New tests: You can create your tests well in advance but you can only link one form at a time to a spreadsheet. When you need to switch, first unlink the old test (open the form, select responses, in the menu you’ll find ‘unlink form’), then follow the instructions above to link the next test to the spreadsheet.
  • Repeating tests: Forms retain their data so to repeat a test you need to either make a fresh copy or clear the data (riskier).

Leave a Reply

Your email address will not be published. Required fields are marked *