Text

|
The power of query in sorting out data in Sheets

One of the challenges we have within the project I am currently a part of is that we have never had a contextual reference point for school information. Although different teams have had various lists floating around, we needed one that we owned as a whole. Added to this, the lists were often kept in an Excel spreadsheet meaning nobody ever really knew if they had the definitive version. Being an organisation that uses Google, I suggested to a colleague that we work together to develop a spreadsheet in Google Sheets and share a link with colleagues. Here then are some of the steps we have taken in developing this:

Data in, Data Out

If there is one thing that I have learnt from Ben Collins’ work, it is the importance of cleaning up your data before you do anything else. This includes avoiding merged cells, an issue we had with older versions of the data. The focus was creating a dataset with each cell telling a particular story. Other than using CTRL+ENTER to separate the different parts of addresses and locking the header, there was no formatting applied to the core data.

We were also deliberate with how we collated the information. With 40+ columns, we spent some time splitting the data into four groups – details, contacts, administration and learning – with empty columns at the end of each in case additional fields needed to be added at a latter date. Although I wanted to use Google Forms to structure all this, we decided not to because of the fluid nature of the dataset. There are also times when we wanted to be able to update the data in bulk for various reasons.

Ranges, Imported and Named

So that people  were able to access the information, a second document was set up, with the data brought in via the IMPORTRANGE formula.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcdefghijklmnopqrstuvwxyz/edit", "CONTACTS!A1:CA500")

This meant that the wider team could have access to the content, without messing up the original data. Although we could have restricted access to the tab or range in the source document, I was concerned that this would be fiddly to maintain, therefore chose a separate document altogether.

With the IMPORTRANGE, I imported this into a sheet and created a named range to reference this.

MAIN!A1:BE500

I tried to embed the IMPORTRANGE within my formulas, but for some reason it would not work, even after I gave permission. Therefore, I resorted to simply bringing in a copy working with this. I also thought then there is only one external call running.

Different Data for Different Purposes

Once I had my data in place, I used a QUERY to reorganise it. In addition to having a single point of reference, my argument to my colleague was that we can represent this data in different ways depending on the purpose. 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.

Using the QUERY formula I created a copy of the core data removing any blank columns.

=QUERY(MAIN,"SELECT B,C,E,D,F,G,J,K,L,M,N,O,P,Q,R,S,T,U,V,Y,Z,AA,AB,AC,AD,AE,AI,AJ,AK,AL,AM,AN,AO,AP,AQ,AR,AS,AT,AZ WHERE C IS NOT NULL ORDER BY B")

As I set the data up with space to grow, I used

WHERE C IS NOT NULL

This removed any blank rows. I also used

ORDER BY B

As this column contained the school numbers (i.e. E1234). In order to get this to work, we removed the E from the source document and used the custom formatting to add the E back to the number, while also being able to sort numerically.

In addition to the main list, I created a number of specific queries, focusing on things such as pay periods, business managers and applications being used.

=QUERY(ICON, "SELECT B,C WHERE X = 'Yes' ", -1)

I tried to make a query where the user would tick a checkbox associated with the information required, but could not figure how to create a variable associated with the SELECT function.

Custom Lookups

Another way of engaging with the data was to focus on a particular school. To do this I created a series of VLOOKUP formulas revolving around the E number which was strategically placed in the first column of the dataset.

=VLOOKUP(B$2,vLookup,5,FALSE)

In the cell above, B2 is the cell where the school number is entered and 5 is the column for the information to be displayed. I then repeated this formula for all the other information to be displayed.

Another use was to create a lookup for the business managers associated with the different schools.

=VLOOKUP(A2,BM!Z1:AH500,2,FALSE)

This involved making a new named range with the name of the business manager in the first column. I also used data validation restricted to the names in the dataset.

I also then combined this search key with the QUERY formula to display the list of schools and their core information:

=QUERY(MAIN,"Select B,C,E,J,AZ WHERE Z = '"&$A$2&"' ORDER BY B")

Mix and Match Formatting

In order to make the data a bit more friendly, I added some conditional formatting. The challenge was that I wanted different colours for different groups of schools. I did this using the school number as the reference:

=left($A2,2)=”E1″

I then used an even formula to break up the lists:

=ISEVEN(ROW())

By placing the even conditional formula at the top of the list, this means that it is prioritised.

I have included this all here as a reference. I am not sure this is much use to anyone. As always, thoughts and questions 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.

Text

|
The more leaders focus their relationships, their work, and their learning on the core business of teaching and learning, the greater will be their influence on student outcomes.

Sometimes it feels like some work is more important than others, but at the end of the day it is all real work.

An old colleague and I were recently discussing work and he shared the joy of doing what he termed ‘real work’ with schools. I stopped him in his tracks and explained that I understood where he was coming from. Working with teachers has always felt more meaningful, that it has more of an impact. However, I pointed out to him that the work that I do is no less real than his work.

Supporting student systems and those in administration, I have come to realise how much we often take for granted in regards to how schools run today. I was speaking to a coordinator at a school the other day about the various services and subscriptions they use. He explained that when there is a new student they need to be loaded into six different services. Technology is more than just a tool it is a complex set of connections that builds up over time.

One of the arguments for ‘real’ work is the ability to impact student outcomes. As Vivianne Robinson argues in her book, Student Centred Leadership,

The more leaders focus their relationships, their work, and their learning on the core business of teaching and learning, the greater will be their influence on student outcomes.

Although it may sometimes seem like a challenge to link some of my work back to the core business of learning and teaching students, I still think it is possible.

For example, one of the elements of the project I am working on is to provide schools a data analytics tool that supports teachers and leaders in making informed decisions. The challenge with this is that there are a lot of dependencies associated with it. For example, there is a dependency on schools having a recorded timetable. Although this is common for secondary schools, it is not so common for primary schools. I have therefore done a significant amount of work to limit how long this exercise takes. In regards to learning and teaching, this has the indirect impact of then allowing schools to spend more time focusing on learning and teaching.

The work that I do has many focuses. Sometimes it is about supporting simple transactions, other times it is about everyday efficiencies. Sometimes it is about helping schools reflect upon particular workflows to ease their workload, other times it is about improving a process, such as the creation of timetables. All of this though is real work that ends up having some sort of impact on student learning in the end.

What do you think? Is there some work that is more real than others? As always, thoughts and 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.