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.
Sharing Data is Easy with QUERY by Aaron Davis is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
Prashanth KV provides some options for complex string comparison operators in regards to the QUERY formula using the WHERE clause:
To NOT select a particular condition, you can use
or!=. This would look something like this:=QUERY(ICON,"Select B,C,A,AF,AG,AH,Z WHERE AF!='NO'")Alice, this is a great demonstration of the collaborative power and potential of Google Sheets. In particular, I like the possibilities to query data for different information. For example, I made a copy and had a tinker with your template, creating a query of the particular assignments for a given week generated from a wildcard.
=IF($Q2="Week 1",{QUERY(A4:M58,"SELECT A WHERE C = TRUE")},IF($Q2="Week 2",{QUERY(A4:M58,"SELECT A WHERE D = TRUE")},
IF($Q2="Week 3",{QUERY(A4:M58,"SELECT A WHERE E = TRUE")},
IF($Q2="Week 4",{QUERY(A4:M58,"SELECT A WHERE F = TRUE")},
IF($Q2="Week 5",{QUERY(A4:M58,"SELECT A WHERE G = TRUE")},
IF($Q2="Week 6",{QUERY(A4:M58,"SELECT A WHERE H = TRUE")},
IF($Q2="Week 7",{QUERY(A4:M58,"SELECT A WHERE I = TRUE")},
IF($Q2="Week 8",{QUERY(A4:M58,"SELECT A WHERE J = TRUE")},
IF($Q2="Week 9",{QUERY(A4:M58,"SELECT A WHERE K = TRUE")},
IF($Q2="Week 10",{QUERY(A4:M58,"SELECT A WHERE L = TRUE")},
IF($Q2="Week 11",{QUERY(A4:M58,"SELECT A WHERE M = TRUE")}
)))))))))))
I then built upon this query to generate a unique list of subjects with an assignment:
=UNIQUE(IF($Q2="Week 1",{QUERY(A4:M58,"SELECT B WHERE C = TRUE")},IF($Q2="Week 2",{QUERY(A4:M58,"SELECT B WHERE D = TRUE")},
IF($Q2="Week 3",{QUERY(A4:M58,"SELECT B WHERE E = TRUE")},
IF($Q2="Week 4",{QUERY(A4:M58,"SELECT B WHERE F = TRUE")},
IF($Q2="Week 5",{QUERY(A4:M58,"SELECT B WHERE G = TRUE")},
IF($Q2="Week 6",{QUERY(A4:M58,"SELECT B WHERE H = TRUE")},
IF($Q2="Week 7",{QUERY(A4:M58,"SELECT B WHERE I = TRUE")},
IF($Q2="Week 8",{QUERY(A4:M58,"SELECT B WHERE J = TRUE")},
IF($Q2="Week 9",{QUERY(A4:M58,"SELECT B WHERE K = TRUE")},
IF($Q2="Week 10",{QUERY(A4:M58,"SELECT B WHERE L = TRUE")},
IF($Q2="Week 11",{QUERY(A4:M58,"SELECT B WHERE M = TRUE")}
))))))))))))
To do this, I added an additional column associated with the assignments associated with the subjects.
One other thing I wondered is whether this could be achieved with each subject recording their own information and then feeding it all into a master sheet using the IMPORTRANGE formula. I have discussed my use of this elsewhere. Separating the data can be a way of managing the data without stepping on each others toes (or cells).
Also on:
Dan Manley, although I agree with the suggestions of top tips, such as checkboxes, cleanup suggestions and explore tool. I think I agree with David Krevitt that the most important tool worth grappling with is the QUERY formula. It is the answer to so many of my problems.
Also on:
Ben, I really need to do some work on picking the right tool for the task, so often I just use the QUERY formula to adjust my criterion out of habit. Using a COUNTIF seems a lot cleaner.
I never cease to be surprised with what I can do with the QUERY formula in Google Sheets. I had a problem today where I wanted to match two datasets using the date column. The problem was that they were in two different formats. So I started searching. Low and behond, I discovered that you can in fact format using the QUERY format. The formula looks something like this:
=QUERY(A1:C7,"select * format B 'd-mmm-yy (ddd)'")These are the available values:
d | Day without a leading zero for 1-9 (i.e. 7)
dd | Day with a leading zero for 1-9 (i.e. 07
ddd | Day as an abbreviation (i.e. Wed)
dddd | Day as a full name (i.e. Wednesday)
m (if not preceded or followed by hours or seconds) | Month without a leading zero (i.e. 8)
mm (if not preceded or followed by hours or seconds) | Month with a leading zero (i.e. 08)
mmm | Month as an abbreviation (i.e. Aug)
mmmm | Month as a full name (i.e. August)
mmmmm | First letter of the month (i.e. A)
y or yy | Two digit year (i.e. 19)
yyy or yyyy | Full numeric year (i.e. 2019)
I know Ben Collins has spoken about formatting dates before, but I never knew there were all these options.