Sharing Data is Easy with QUERY

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:

=QUERY(MASTER!A:F, "SELECT C,D,E,F WHERE A = 'Donna'", -1)

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.

Also on:

Scripting an Automated Solution

Spreadsheets

A plan for an automated monthly newsletter produced from Google Sheets. The intention is to develop data in a way that it can be used in a number of ways.


I recently wrote a post reflecting on the Digital Technologies curriculum. One of things that I realised through the process is that I often wait to the end to discuss my projects. Although this can be useful in providing an overview of learning and achievements, it does not necessarily allow others a means to provide feedback early on. I usually ask questions online, but this often lacks context. So this post is an attempt to plan out a new project, with the hope that others might be able to provide advice and guidance.

This year I started a monthly newsletter associated with Google. With GSuite the chosen learning and teaching platform in my organisation, I thought it would be useful to summarise the various resources for others. I started with a Google Doc, organising the various links under headings associated with the featured application, as well as a section documenting the overall updates.

This has ebbed and evolved as the year has gone on, with a clear order of applications to correspond with a range of modules. However, the question that has arisen is whether there is a better way of recording the various links and updates so that they are easily searchable.

Currently, you can go back through the various posts and look for resources, but this is both cumbersome and tedious. It therefore had me think about storing the links in a Google Sheet and possibly generating the monthly summary/newsletter from that.

I know that I could probably do this with a social bookmarking platform or even a blog, but I feel that putting the information into a spreadsheet provides more operability. It would mean that the data would be in a format with which I could present it a number of ways. It also means the links could be recorded using something as simple as Google Forms.

I am therefore thinking of creating a script in Sheets that collates all the links for the month in a Google Doc. To be honest, Google Apps Script is all still new to me, but I am wondering about the possibility of creating a template with merge fields. I remember Autocrat doing something similar. I could then use this to post in WordPress.

I am left with a number of questions, such as how should I action the script? Would it need some sort of selector or could it be done automatically? How customisable are templates? Could I generate a markdown version for the purpose of posting?

Maybe you have an idea or a post that you would recommend checking out before beginning or just a tip of where to start. 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.