The power of query in sorting out data in Sheets

There are many challenges to sharing specific data in Google Sheets, some of these can be overcome using the QUERY formula.

I attended a day recently continuing the look into ongoing reporting. One of the focuses involved reflecting on various points pf data. Something that stood out was the willingness of teacher to share data. Whether it be with students, teachers or parents, for some providing access can still be a challenge.

I discussed this with a principal attending the day and one suggestion made is that it can be hard to share particular data, without sharing everyones data. This is especially the case when talking with parents or conferencing a student. An answer is using the QUERY formula.

With Google the chosen platform, a lot of teachers store their data in Google Sheets.

QUERY combines a whole lot of functionality into the one formula. As David Krevitt explains:

QUERY combines all of the capabilities of arithmetic functions (SUM, COUNT, AVERAGE) with the filtering abilities of a function like FILTER.

It is a language developed by Google, using the principles of Structured Query Language (SQL).

For example, you may wish to share the results of just one students:

To do this, you select the columns you want to copy from the MASTER tab and which value you wish to filter by:


If you wished to quickly create a tab for each student, Alice Keeler has created a script for generating tabs from a list. This personalised information can then be shared with students (see Jake Miller’s explanantion). Another thing to consider is to protect formulas by adjusting permissions at a celular level, as well as prevent others from copying the file, therefore getting the information that way.

The other option is to create a dynamic selector involving either a name:

Or even a whole form:

These options might be used when working with colleagues or talking with parents. You are able to bring up just the information required. Depending on the data, you can also create dynamic charts. One other benefit to using the QUERY formula is that it allows you to quickly and easily reorder the representation of data. So lets say ‘F’ is associated with literacy testing and you would like that at the start. Rather than writing SELECT C,D,E,F you would write SELECT F,C,D,E.

This is only the tip of the iceberg of what the QUERY formula can do. For more information, see posts from Ben Collins and David Krevitt. Collins also ran two webinars, which you can go back and watch. One on the basics, while the other getting a bit more complicated.

One thing to note when using Google Sheets to store data is what sort of information you are collecting. In some districts and regions there are issues raised about storing ‘sensitive data’ in platforms like Google.

Reflecting on Class Dojo, Ben Williamson explains that ‘sensitive’ can be the consequence of collecting data:

The ‘sensitive information’ contained in ClassDojo is the behavioural record built up from teachers tapping reward points into the app.

This same concern needs to be considered in regards to Sheets, especially with the changes being brought about by GDPR.

As always, comments welcome. Webmentions too.

If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.

flickr photo shared by mrkrndvs under a Creative Commons ( BY-SA ) license

Disclosure: This post is a part of my work with the Hapara Certified Educator course. The final task was to develop an implementation plan, which incorporated goals for supporting others and transforming education. Inspired by Ben Williamson’s work on Class Dojo, I have tried to provide a thorough introduction to what Hapara offers, as well as some ideas moving forward.

Hapara is an instructional management system designed to support educators who wish to organize, manage and monitor learning with the help of G Suite. Differing from Learning Management Systems, Hapara’s goal is to improve learning by improving instruction. The intent is to provide the tools to scale teacher-student relationships, not replace that role. Based on the work of John Hattie, Hapara was initially designed to support things such as:

  • Student agency
  • Constructivist dialogue
  • Differentiation
  • Formative assessment

The reality is that Hapara was developed to free up teachers to spend time on the more important elements associated with learning.

Hapara works through the use of application programming interfaces (APIs) provided by Google as a means of transforming the way in which G Suite works. Much of the web is built around APIs. They offer developers a way of incorporating different features, without recreating the wheel. For example, rather than create your own mapping program, Google provides the means of embedding Google Maps. It is also for this reason that there are similarities with other products like GoGuardian, because they are both built using the same building blocks provided by Google. For a richer discussion of APIs, I recommend reading Ben Werdmuller’s post or checking out Kin Lane’s work.


Classes and Subjects

The core product that makes up the Hapara suite is the Dashboard. This application provides something of a bird’s eye view over the G Suite ecosystem. Organised around classes and subject folders, it makes files visible and accessible by bringing them together on one screen.

Classes and subjects are managed at an administrative level. They are usually derived from the school timetable. However, it is easy enough for a school to make their own changes, such as creating an elective subject or adding multiple teachers to a subject. Within Manage Classes section there is the option of personalising the name and colour associated with classes you can personalise the names and colours attached to your classes. You just hover the cursor to see the original title. In regards to ownership, you can quickly add teachers in the Class Info section.

Student Panels

In regards to how the Student Panels are presented, there are a number of ways that information can be adjusted. Whether it be choosing how many items are shown in each panel or holding your cursor over a file to bring up metadata, such as author, updates, views and access rights. The panels can also be sorted both alphabetically and by group. Generic groups are generated by default, but just as with classes you can easily change the predefined names. You assign students to groups in options setting attached to each Student Panel. It is important to note that these groups are not visible to either students or parents. When you click on the file within Student Panel you are taken to the document as an editor. This allows you to quickly provide feedback by adding a comment. You can also email students via the options in the panel.

Sharing Content

For distributing content, Smart Share allows you to quickly send out files. Although sharing is relatively simple, there are some things to consider, such as:

  • What file you are sharing (it may simply be a blank document)
  • The title of the document
  • What access students will have
  • Which students and/or classes you are sharing with

Although you can reshare files and delete the previous version, you are unable to make changes to sharing options of files once they are sent. Once files have been allocated, students can organise their Google Drive into sub-folders. By opening up the options menu for each student, you are able to click a link to view their folder within Google Drive.

Searching for Information

In addition to the Student Panels, there are a number of ways to find information that may not be visible. You can search within the subject that you are currently in by simply clicking on the magnifying glass at the top of the screen. This allows you to filter by information within the title or content in the actual text of the document.

If the file you are looking for exists outside of the subject folder, you can use the Sharing Tab. These searches can be organised around various access levels. There are five categories which you can click on:

  • Unshared: Anything in the student drive that has not been shared with anyone else.
  • Public:  Files created in a student account that people outside of the domain can access.
  • External: Files created by a non-domain account and then shared with an account within the domain.
  • All Docs: list of every doc the student has in their Drive.
  • Docs Trash: see all documents in a student’s trash.

These various options provide the ability to search your whole domain and appreciate who might be shared with what, rather than having to use something like Who Has Access. It also provides the means for locating work that a student may have misplaced within their Drive.

Beyond the Dashboard and Sharing tabs, Hapara also provides the means of monitoring student Gmail accounts. This functionality allows you to search within the inbox, sent and trash folders. What is interesting is that when you open up a message, students are not notified and it will remain unread. There are a number of ways of adjusting the settings associated with the Gmail Tab within the Admin Console. This includes the ability to filter who is able to access the option, as well as sort the types of emails shown.

In addition to Gmail, you are also able to explore Blogger blogs and Sites. These show up for each class next to the various subject tabs.



Highlights is described as a ‘screen visibility and engagement tool’. Separate from the core dashboard functionalities, it provides insight not only into folders and files, but what students are doing on the web and how they are collaborating with others. It is similar in some ways to applications, such as Lanschool and GoGuardian. This added functionality is dependent on students signing into Google Chrome with their school’s G Suite account. It also requires the installation of an extension. There are four elements to Highlights:

  • Activity Viewer: provides an overview of what is currently happening in the class and how people are connecting. It provides analytics about different forms of browser usage. This includes, a list of sites currently open, collaboration that is occurring and any unique actions. Results are divided between current and previous usage.
  • Browser Tabs: Similar to the ability to share out files with SmartShare, Browser Tabs allows you to send out specific websites for focused browsing. There are quite a few options associated with this, including the ability to restrict students browsing to within a specific site, as well as set a time limit for working on a specific task. Some possibilities associated with this include: brainstorming around a particular provocation, guide groups or individual students to a specific video or task, provide a set amount of time for students to complete a test using their computer and avoid confusing URLs by directing students to a specific website. Another functionality associated with Browser Tabs is the ability to send messages to students. Although you could do this via email within Dashboard, sending a message within Highlights means that it comes up directly on the screen.
  • Current Screens: This provides a view into every browser screen in the classroom. Unlike applications such as Lanschool, the insight provided is not live, rather it is a screenshot refreshed every 5-10 seconds. It also only does this when you have the Current Screens function open. This process can create quite a demand on the bandwidth used. However, you can adjust the bandwidth settings within the Admin Console.
  • Snap: Whereas the screenshots shown via Current Screens are continually refreshed, Snap allows you to take a snapshot of student work when you see something interesting. Maybe it is something that you want to document or something to come back to at a later point. These are different from usual screenshots as they provide a range of additional information. This data includes student, subject, timeline of tab activity, who took the snap and when. These captured images can be saved by emailing to yourself or used as a means of sending feedback to students. They are automatically deleted from the system after seven days.

Each of the parts is designed to support further personalisation of the learning process and capture richer evidence associated with formative assessment to support future instruction. However, this is dependent on students logging into Chrome. To get around the various constraints, students can easily use incognito mode or a different browser. While there are also measures to prevent the invasion into personal space and time, such as time constraints and IP restrictions. For a full guide to Highlights, click here.


Workspace is a relatively new addition to Hapara. Separate to Dashboard, Workspace provides an efficient way of presenting a unit of work in one space. It involves the development of learning sequences using a series of cards. Similar to Google Classroom, each workspace creates a folder inside your drive. This though is separate to the Dashboard file structure, but viewable through the Sharing Tab. One of the biggest challenges is having a clear idea about the learning involved.

Planning For Learning

There are many different models that can be used to plan for learning:

Whatever the framework, they each provide a series of questions to consider. A popular model in many schools is Understanding by Design. One of the key features of UbD is that it is backwards planned. This involves identifying desired results, including various transfer goals. Understandings and essential questions are then listed. Once this is done, assessment evidence is determined. Associated with this, it is important to call out the various standards being addressed within the unit of work. This includes considering learning areas, interdisciplinary and social capabilities.

Organising your Workspace

Once you have your learning sequence planned out, you need to consider the structure of your workspace. There are a number of things to consider, including:

  • Number of Columns – There are four by default, but you only have to have Columns Two and Three.
  • Titles for Columns – You can easily change the default titles to make them what you like.
  • Layout – Information is presented in the form of cards
  • Styles – You can add HTML code to change the colour, size and format of the text.
  • Groups – This allows you to adjust who sees what, although it needs to be noted these groups are different from those in Dashboard.

Although you are able to adjust the order of cards within a column, it can be useful to use something like a Google Doc to develop a rough draft of your Workspace.

Supporting Learning

One of the best things about Workspace is the potential to incorporate a range of different activities and applications. Looking at the activities and information that you have provided and consider if there are anything that you could add to take learning further? Below are some options of things to consider:



It can be useful to review your initial plan and add in ideas for different activities and applications to further develop the learning opportunities.


One of the things that Hapara prides itself on is the ability to quickly and easily differentiate learning. This focus incorporates what is taught, including content, process, product and environment, changes to how something is taught, as well as why something is taught in the first place. See Carol Ann Tomlinson’s book The Differentiated Classroom: Responding to the Needs of All Learners for further explanations and elaborations associated with differentiation.

One of the powerful features of Workspace is the ability to differentiate learning in a number of ways. This includes:

  • Creation of groups
  • Gradual release of cards
  • Provision for individual tasks, as well as group work
  • Assigning different content to different students
  • Means for students to determine their own learning
  • Choice over what type of learning product is created

This is only a start and every context provides its own possibilities and potentials.

Sharing Workspaces

One benefits Workspace which makes it different to something like Google Classroom is the potential to easily share them others. Once made public, they are added to the global library. Searching the library can actually be differentiated between titles, groups and cards. You are also provided with a link to the actual Workspace, which can then be shared out on social media. (See for example my workspace on how to make a great workspace.) If the Workspace has been published to a class prior to sharing then it is taken back to its initial format, removing student content and groupings. This offers the possibility to easily remixing a unit from a prior year or sharing the one workspace with a number of teachers in a year level to make their own modifications.

Implementing Hapara

“Implementing Hapara” by mrkrndvs is licensed under CC BY-SA

Implementing Hapara

Moving forward, my goal in my current position is to support schools with the implementation of G Suite and the transformation of education. To me, Hapara with all of its different facets makes much of this possible. Working with so many different contexts, it is far too simplistic to use something like SAMR model in order to identify points of change. Although this may be useful in starting a conversation, I think that something like the Modern Learning Canvas provides a more nuanced approach:

This helps to frame the conversation about education technology within a richer discussion of learning and teaching. Technology has the potential to dramatically change what occurs in the classroom, but it also has the potential to maintain the status quo. It is only by developing a plan and purpose for each context that we will truly move forward.

If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.

This is based on a presentation at the Melbourne GAFESummit held at Xavier College on September 19th and 20th, 2016.

There have been many changes to learning brought about in the past decade, from MOOCs to social media, often though there are so many options that it can be hard to know where to start and more importantly, why. Technology enables us to easily develop digital communities and networks inside and outside of the classroom. The reality though is that connected learning is as much about creating spaces for learning and building on that, so let us start there.

Continue reading

If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.

flickr photo shared by mrkrndvs under a Creative Commons ( BY-SA ) license

Google Docs is the application that Google Apps seems most well known for. More than just a basic word processor, Docs provides a space to connect and collaborate in real-time (something Microsoft is finally doing with Office 365.) It includes such features as the ability to import and export a range of formats, extend communication through comments and chat, work across platforms, including the ability to work offline, add text via voice, edit Microsoft Word (originally via Quickoffice), as well as build documents and add to a range of templates found within a gallery. Going a step further, there are also a range of add-ons which allow you to do everything from create a bibliography to making a flowchart.

Some possible uses for Google Docs include:

  • Digital Workbook: One of the greatest benefits of Docs (and Google Apps) is the ability to move to a paperless classroom.
  • Collaborative Writing: Docs makes cooperative learning more doable, providing the means for interdependance.
  • Administration: Whether it be taking minutes or sharing curriculum documents, Docs provides the means to organise such work.


An interesting example of the use of Google Docs is in the creation of HyperDocs. A cross between Thinglink and a webquest, a HyperDoc is a document which incorporates different interactive features, such as graphic organisers and linked content. In its basic form, it can be conceived as a digital worksheet. However, as with all technology, it has the possibility of amplifying preexisiting practices, providing a means to structure self-directed learning. As Highfill, Hilton and Landis explain,

A true HyperDoc is much more than some links on a document. Digital collaboration is choreographed through the inclusion of web tools that give every student a voice and a chance to be heard by their classmates. Critical thinking and problem solving skills can be developed through linked tasks that ask for authentic products to be created and shared digitally.

Some of the benefits for using HyperDocs include:

  • Deeper Engagement: With the interactive nature of such documents, students are unable to move on without actually clicking through. This is often provided through scavenger hunts.
  • Additional Resources: Through the use of links, HyperDocs provides a means of providing additional stimulus and resources,
  • More Cooperative Learning: Provides the means of working collaborative, as well as independently, whether this be completing a personal copy through Google Classrooms or adding to shared content.

Here then is four steps to creating a HyperDoc:

  1. Identify area of learning: This might be a skill or a point of understanding.
  2. Choose a structure of learning: This involves two steps, firstly choreographing the learning task (see templates) and secondly how this might look as a series of documents.
  3. Incorporate different content: Hyperdocs involve links to a range of different content, from videos to Forms
  4. Publish document: This might involve simply sharing a link or could be done through Google Classroom. As people will be adding content, it is important to think about how this will be done.

Here are some additional resources for Google Docs:

Google Docs Cheat Sheet – Anintroduction by Kasey Bell covering all the key features

The Best 10 Google Docs Tips For Teachers As They Go Back To School – Joshua Lockhart provides a good list of suggestions as to how to use Google Docs to support you in the classroom, including providing creative feedback and giving access to resources.

Google Docs began as a hacked together experiment, says creator – Ellis Hamburger interviews Sam Schillace, the man behind Writerly, the text-editor that became Google Docs, and discusses some of the challenges faced in the process.

12 Free Add-Ons That Take Docs and Sheets to the Next Level – A collection of useful add-ons, including the ability to add a signature and translate text.

6 Powerful Google Docs Features to Support the Collaborative Writing Process – Susan Oxnevad unpacks the writing process highlighting the many benefits of collaboration.

Google Docs Templates – A collection of templates that users can access, as well as add to.

Voice Typing – A discussion of voice typing and how it can be used to support learning.

Hyperdocs – A resource created by Lisa Highfill, Kelly Hilton and Sarah Landis unpacking everything associated with Hyperdocs.

Hyperdoc Tour – An example of a hyperdoc whcih provides a tour of some features associated

HyperDocs – Changing Digital Pedagogy – A collection of hyperdoc lessons from a range of subjects and year levels.

If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.

flickr photo shared by mrkrndvs under a Creative Commons ( BY-SA ) license

So often we perceive innovation as being about the big things. However, sometimes it can be something small which can have the biggest impact. Last year, Google released voice typing for Google Docs in Chrome. This had been available both via the iPad app and using Google Keep. However, it was not something built into Docs on the web.

While conducting a writing conference in intervention the other day, I decided to open up a Doc and give it a go. As we read through the handwritten document, it recorded the text. This meant that together we could then add comments in the Google Doc and begin the editing process there. Several other students showed quickly hovered around with interest.

To record, you simply go to Tools and click on Voice Typing. Once expanded, you simply click on the microphone to turn it on and off. There are also a range of cues you can use to add punctuation, such as period, comma, exclamation point, question mark, new line and new paragraph.

For some, such as Clive Thompson, voice recognition technology has the potential to dramatically change the ways in which we consider handwriting and writing. I guess only time will tell.


I was recently asked about the ability to record and transcribe interviews on the computer. Naomi Barnes said that Docs was a possibility if the recording was clear enough. This reminded me of something that Alan Levine did a few years ago, where he recorded both the audio and a transcript at the same time. Along with YouTube’s automatic transcription service, both these solutions still require some sort of human intervention in reviewing the finished product. It is for this reason that many who I asked recommended doing your own transcriptions. Ian Guest mentioned that you can get foot pedals that plug into the computer to start and stop recordings, while Clive Thompson said that if you don’t transcribe yourself there are services like Rev which you can outsource to. Testing out another option, John Johnston demonstrates what Happy Scribe can do.

If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.

Image via JustLego101
Image via JustLego101

Google Classroom is a platform for communicating and collaborating using Google Apps for Education. Unlike other platforms, Classroom focuses on three key areas: pose questions, make announcements and set assignments. Although this may seem somewhat limited, as Alice Keeler demonstrates, it provides a foundations for so many possibilities.

Some of the benefits include:

  • Sharing Resources: Whether it be as an assignment or through the announcements, you can easily share resources with students. In addition to this, Classroom creates a structured filing system in Google Drive.
  • Structured Organisation: Unlike spaces like Edmodo which can end up with a random student who mysteriously has three accounts, Classroom provides a central management system through Google Apps Admin meaning that you can in fact add students yourself. In addition to this, you are now able to have multiple teachers, something that was not possible at the beginning.
  • Extending Google Apps: Not to be confused with Learning Management Systems, Classroom works best when it is integrated with Google Apps. For example, through the creation of an assignment you can generate an individual copy of a Google Doc.
  • Multiple Device: Like Edmodo, Classroom is available in the browser, as well as on the iPad and iPhone.
  • Assessment and Rubrics: Although there are answers, such as Alice Keeler’s Sheets Add-on RubricTab or Andrew Stillman’s use of Goobric and Doctopus, the easiest way to create a rubric with Classroom is by creating a copy for each student via assignments. Associated with this, Classroom provides the ability to turn work in. This means that students can signal to you that although they may have shared a document with you, they have actually finished with it
  • One Less Logon: A part of Google Apps, using Classroom as opposed to other spaces means one less log on for students to remember.

So what about you? Have you used Google Classroom in your classroom? What have you seen as some of the benefits and challenges? As always, comments welcome.


If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.

flickr photo shared by mrkrndvs under a Creative Commons ( BY-SA ) license

I recently wrote about powering up blogs by adding video, audio and GIFs. Another form of content which you can add is an interactive map by embedding a Google My Map.

Google Maps has been a staple of Google’s applications for a long time. However, something that is often overlooked is the potential of creating your own maps. In the past, the place people often went was Google Earth, using features such as Tour Builder, while more recently Maps Engine Lite offered a way of customising traditional Google Maps.

One significant changes in rebadging Maps Engine Lite as My Maps has been to house the files within Google Drive. This has made it easier to create, collaborate and share various creations of space. You are able to easily make layers, add place marks, draw shapes and create directions. To take this to the next step, you can also import information via a spreadsheet. Uploading can be good if you have a long list of coordinates. While you can also export data from My Maps as a KML file. This can be useful if you want to add information into Google Earth.

In addition to this, you can add content to the place marks or shapes, whether this be a description, image and video. This then comes up when you click on the marker. In addition to this, you can also change the place marker icon and colour of the shape. Therefore giving you with another layer of meaning.

Some ideas for using Google My Maps include:

What is good about My Maps is that it provides a different way for communicating information and telling a story. So what about you, how could you use My Maps? As always, comments welcome.

Additional Resources

If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.