Trying to create a student understanding tracking system using Google Forms and Sites #gafe

I used (and sorely miss) Geddit. It was very useful in gauging student understanding during a lesson and was a low-cost, high-gain tracking tool that I could refer to after lessons, before end of topic tests and during parents evenings (if needed).

I decided to try and create something along a similar vein, but using Google Forms and Sites. The advantage of this is that I can restrict access to those within the school, automatically use GAFE login details, and – in the future – customise it with more complex Google Apps Scripts so that students can be emailed and Google Charts automatically generated into a dashboard (I’m thinking by student or class at the moment).

My late-night sketch was simple enough – the teacher could choose a class and enter a question into a teacher-only Google Form. This would then be parsed by a Google Script to somehow display the most recently entered class and question in or above the student Google Form. When I’m generating reports into the dashboard I can use the timestamps from each Google Form response spreadsheet to correlate which question the student response relates to.

Anyway this was easy enough to prototype:

Teacher question control form
Screen Shot 2016-01-04 at 01.48.33

Student response form
Screen Shot 2016-01-04 at 01.49.05

The problem however was getting the most recent teacher question to appear in the Google Form. I decided to create a new Google Site and see if I could publish a range of the teacher Google Sheet as a webpage. I thought that this would be the easiest way to display the current question.

First I used a Google Sheet query in a new tab (called Question Feed) to reverse the order of the Google Form submissions:

=query('Form responses 1'!A1:Z, "select * order by A desc", 1)

I then created another Google Sheet tab (called Web Page) to create the view to be embedded in the Google Site:

Screen Shot 2016-01-04 at 01.56.53

I managed to publish this tab as a web page and then went about embedding it into my Google Site. All worked great! However Google Sheets only seem to refresh every 5 minutes so I investigated a way of doing something similar using a Google Script.

Using code similar to the following I was able to change the page title of the Google Site’s current (only) page to the most recent question submitted:

function displayQuestion() {
var ss = SpreadsheetApp.openById("put your Google Sheet ID here");
var sheet = ss.getSheetByName('Web Page'); // or whatever is the name of the sheet
var range = sheet.getRange(2,1); // Get the question
var question = range.getValue();
var range = sheet.getRange(1,1); // Get the class
var data = range.getValue() + ": " + question; // concatenate
var site = SitesApp.getSiteByUrl("put your Google Site URL here");
var page = site.getChildren()[0];
page.setTitle(data); // Puts current question from SS into page title section


I added the script to the Google Site and set a trigger to run the function every minute.

The student view of the system currently looks like this:

Screen Shot 2016-01-04 at 02.03.25

Yes there are UX issues – such as the page refresh to get a new question, or the need to click on “Submit another response” to change the teacher question or add another student response, however I think I’m happy with it as a starting point.

Please feel free to use the above code if it is useful to you. I’m going to try it out with a few classes in the next few weeks and see if I can use some real data to create reports from. Any comments on how I can improve the system would also be greatly appreciated.

#edchat Summary: How does giving students more control of their education affect the quality of the education?

I’ve hovered around #edchat fringes for a couple of years now. Adding the occasional link but never really sure when to take part as people post using that hashtag all the time. I was chatting with @drdouggreen and he was kind enough to give me some more details about when the scheduled chats take place (5pm / midnight GMT on Tues in UK).

So on Tuesday I took part in my first #edchat. I hadn’t realised that they had two different topics for the weekly #edchats and had been expecting discussion on the flipped classroom, but I was more than happy with the alternative: how does giving students more control of their education affect the quality of their education?

I really enjoyed the hour and noticed a few familiar faces from the UK join in as well. I think it worked well for me as I’m on October break and this makes a 5-6pm chat feasible. Usually I’d just be getting in the door and I don’t think the family would appreciate me disappearing with the laptop instead of finding out about their day. I don’t think I’d like that either, so perhaps a midnight #edchat is the way forward – for me at least!

I’m a big believer in promoting learner choice in my classroom. I teach to the planned outcomes (sometimes in a round-about way!) but in a way that suits the class. That one class. It means a lot of work for me at times, but I get a huge kick out of the enthusiasm it generates amongst my learners. What works for one class or child may not work for another and I would not be doing my job properly if I ignored this fact. Yes, you have to retain your role as troubleshooter (I typed behaviour manager first but that sounds far too controlling!) and facilitator but sometimes – and as much as possible – you have to let your class take charge of the learning. I didn’t do this based on any educational research or current policy, I remembered the best and worst lessons of my own school years and remembered that when we were given the freedom to play within the boundaries of the topic we enjoyed it and saw relevance. With that in mind I want to share with you a TED talk by Alison Gopnik. She explains a little about what babies are thinking.

You may think this is a strange choice of supporting video. Please watch it, I hope all becomes clear.

Here are some of the main themes from the discussion:
  • Personal learning
  • Project-based learning
  • How to learn
  • Motivation
  • Passion
  • Learning through making mistakes
  • Open and honest discussion with learners
Here is a selection of some of the comments:
  • “Hopefully we do not give students “much” control over content…but give them some control over how they demonstrate mastery.” – berryed1
  • “It will improve quality from the child’s point of view. What else matters?” – drdouggreen
  • “If kids rely on the teacher for the answers, then we have failed them as teachers. ” – stumpteacher
  • “Ownership ups the ante of the student’s interest, involvement and motivation.” – drmcgettigan
  • ” ‘Children should be given a voice not only about the means of learning but also the ends, the why as well as the what.’ Alfie Kohn” – cybraryman1
  • “I would love the concept of Personal Learning Networks to be introduced to, and used by students early on.” – tomwhitby
  • “We have to stop thinking we are the only experts/teachers/leaders in our classroom” – pernilleripp
  • “Listen outside clssrm door: Who’s voice do you hear? If solely tcher’s voice: Tcher-Directed. If studs’ voices: Student-Centered.” – prlowe91
  • “Teaching kids how to learn is more important than what content to learn. If they commit to the content, the how to becomes easier.” – tomwhitby
  • “good for teachers to say “i don’t know”, brings in whole class and dilutes the idea that the teacher is the source of all answers.” – familysimpson
  • “Stdnts need 2 feel “allowed” 2 make a mistake or get something wrong & comfortable enough 2 try again. That’s when learning happens.” – KristinHenry1
  • ” the goal matters & it’s got to be their goal” – inquirebook
  • “Yes, I hope to make poor choices! Makes me human & helps me learn! I’ve learned more from poor choices!” – davidwees
To follow the complete discussion see here
As ever, there were some great links shared:

New to Edchat?

If you have never participated in an #Edchat discussion, these take place twice a day every Tuesday on Twitter. Over 1,000 educators participate in this discussion by just adding #edchat to their tweets. For tips on participating in the discussion, please check out these posts!

More Edchat


If you’re new to hashtag discussions, then just show up on Twitter on any Tuesday and add just a few tweets on the topic with the hashtag #edchat.

What do you think? Leave a comment!

Ian Simpson is a Scottish secondary teacher, specialising in Computing. His particular passions include: mobile learning, web 2.0 & games based learning.

The coffee shop as a classroom: mobile learning environments

Image shared under Creative Commons licence - Kate Williams

I like coffee. No – let me rephrase that – I LOVE coffee. It plays a big part in my teaching – at times providing a versatile prop for explaining the difference between an object and operation, at others simply providing the nervous energy to keep the learners learning. (I’ve been re-reading #MoveMeOn, curated by Doug Belshaw @dajbelshaw. thanks to @frankcrawford for that particular gem!)

I managed to clear my desk on Thursday earlier than planned so took the opportunity to walk around the three shopping malls near my new workplace. Not just to kill time (heaven forbid Mr McCormac!), I wanted to expand an ongoing series of lessons on data protection and loyalty schemes I had delivered to my S4 ICT class earlier in the term and me having an up-to-date knowledge of the businesses in the local area was a pre-requisite. It didn’t take long to complete what I needed to do (UK shopping malls or shopping centres are much smaller than in the US with perhaps 20-40 stores, some smaller) so I had a quick stop at the Apple store in Aberdeen to eavesdrop of a group of six pensioners who were being shown how to use their brand new iPads then started my lunch break at the nearby Starbucks with a crème brulee macchiatto and a quick refresh of my social media sites on their free wi-fi.

It was a lot busier than usual – I had, in the past, only used this particular Starbucks as a go-to when late afternoon trains were cancelled and I had an hour or so to kill before the next one – but I found a small table with plenty of scope for people-watching. The coffee-house furniture is a mixture of hard back chairs and small “regular” tables, sofa chairs and low tables, benches and long tables, stools and narrow bars. Students, parents, office workers and transients like myself sat and chatted, read quietly, enjoyed their purchases and from time-to-time accessed their devices if they had them and if they needed to. A few of my Advanced Higher Computing students had had a similar idea to my own, joined me at my table (they asked first!) and then… we had a fantastic unplanned seminar on what had been taught over the past few weeks, about mobile apps, about Steve Jobs, about programming, about social media,  and then finally about learning spaces! During this enthralling conversation (I think we all learned a lot in this half-hour) one asked why schools didn’t create spaces like this and I saw immediately what he meant because it had been percolating in my head at that moment too. Why can’t we all have learning rooms where the furniture offers visitors choice of working areas, where the wireless Internet access is a background consideration that “just works with a quick log-in”, where there isn’t a designated space for the teacher to lecture from, where learning becomes personalised? All three of us had mobile devices on the table between the cups and plates and augmented our conversation with these when we needed to: I showed them Twitter for example and explained why it was such a great resource for me to make contact with others who share similar interests. I posted this tweet:

Coffee chains have undoubtedly studied the effect of their environment on their customers from a financial point of view and have generally come to the conclusion that a varied, customisable, slightly eclectic environment is the worm that keeps us on the hook. So who is doing this for education? There are educators in each authority, in roles from classroom to management, who are striving to find the best furniture or layout for existing classrooms. Most of us tend to tinker with our rooms if we can. But if you’ll indulge the point of view of a Computing teacher for a moment (well you’ve got this far!) this may be the problem – moving the furniture in any space which has been built to deliver the Victorian model of education has inherent restrictions but when you also have electrical equipment, cables and power sockets to worry about you really can’t change very much without the firm belief and financial support of your school management team. And then you move on and the next teacher has a different idea… it’s really not that feasible an option for any subject with fixed resources.

But why do they have to be fixed? Hasn’t the last decade of technical innovation shown that mobile devices are here to stay? That an increasing number of learners arrive in classrooms with devices that can easily be personalised to suit their learning, rather than forcing the learner to make use of a machine set up to a generic specification? Are educators really, as a group, nervous about losing control?

And who is studying the effect of the coffee shop environment on learners? Well, a few have come to my attention. I stumbled on a paper whilst writing this blog post called “The Classroom Coffeehouse” which focusses on reworking the layout of an English classroom to promote sharing of written work between New Jersey 8th graders – well worth a read! Also highly recommended is the well-considered post “The Coffee Shop: A Classroom for Creativity, Reflections from a Coffee Shop in Harbin, China“. The Edinburgh Coffee Morning model is something which I envied a few years ago while at Inverurie and provides a nice text break below!

Edinburgh Coffee Morn Stop Frame from brandfeed on Vimeo.

Edinburgh Coffee Morning: from Mike Coulter, on Vimeo.

Last night I read Angela Maiers’ post “What If You Knew You Mattered?” where she describes an increasingly common experience of customer non-service where recognition of failure and empathy with the customer would do much more than a discount voucher. It’s at the core of GIRFEC policy for teachers to make their learners feel included and respected while at the same time encouraging their development as respectful and inclusive citizens. The two words “you matter” apply to each young person who steps into your classroom, but if you are delivering your education as if you were working a conveyor belt at a factory when do you have the time to make sure your well planned generic summaries (and even the differentiated materials) are actually arriving at their destination? Through spending some time listening to the learners and learning something new yourself. Where better than the relaxed environment of a coffee shop?

I’m not advocating that we all abandon our classrooms for the nearest coffee chain but that school leaders and decision makers take a look around the wider world and really see what engages people. I highly recommend you view the articles, blog posts and videos I’ve linked to if you are in any way interested in developing mobile learning, and please suggest more using the comments option below! I am already planning to make this chance meeting a more formal part of my teaching at upper secondary level in the next term and although, yes, it will be more work initially filling in risk assessments and carefully planning my mobile lessons and – depending on the numbers – speaking nicely to the manager of the coffee shop! Escaping the classroom might be just what our learners need, and all it took was a coffee.