The power of query in sorting out data in Sheets

I get a lot of requests for writing various queries, focusing on different information. Although I am always happy to help out, I am also interested in potential of creating spreadsheet templates that people can copy and use themselves. One template I developed recently was build around interpreting different data exports.

Often these export files have a lot of information, which people get lost in. Although they could just delete the columns that they do not want, the feedback is that that process is tedious. Therefore, I created a spreadsheet where users can import a CSV and then select the fields that they want to work with. I did this using a QUERY formula and a series of checkboxes associated with which columns to SELECT and which columns to ORDER BY.

="{QUERY(Sheet1!A1:CJ,"&char(34)&"SELECT "&JOIN(", ",QUERY(A3:D,"SELECT C WHERE D = TRUE"))&" ORDER BY "&JOIN(", ",IFNA(QUERY(A3:E,"SELECT C WHERE E = TRUE"),"Col1"))&char(34)&",1)}"

Users can then copy the query formula and run it in a separate tab.

The current workflow can be summarised as follows:

  1. Create a copy of the Google Sheet template.
  2. Import the data spreadsheet (File > Import > Upload > Browse > Open > Insert New Sheet(s) > Import data). This will bring the data into a new tab ‘Sheet1’
  3. In the SUMMARY tab, refresh the formula in cell A3, then use the checkboxes to select which fields to be displayed and the field(s) to order by.
  4. Copy the formula created in Cell B2 and paste it in Cell A1 in the data tab, adding ‘=’ at the start to activate it

Although this works fine, I was left wondering if it was possible to instead display the data dynamically, rather than copying and pasting the formula. I imagine that I would need some sort of ‘TO_FORMULA’ type of function, where the text is translated into a formula.

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.

Stories happen only to those who are able to tell them, someone once said. In the same way, perhaps, experiences present themselves only to those who are able to have them. Paul Auster ‘The New York Trilogy’

I remember going to Andy Warhol’s Time Capsule exhibition and seeing his film Kiss. It captured something in so much detail that I was left unsure exactly what to think about it. I had a similar experience reading Paul Auster’s New York Trilogy.

The ‘trilogy’ includes three separate novels – City of Glass (1985), Ghosts (1986) and The Locked Room (1986) – that serve as something of a whole. However, as Robert Briggs discusses, the trilogy is not straight-forward.

Nevertheless, a few points of correspondence can be found between the three stories, which could define Auster’s collection as not so much a nonidentical or uncertain trilogy as rather a trilogy about the nonidentical and the uncertain. So, although there is little continuity between genre and character, there is a certain persistence of duplicitous identities.

Source: Wrong Numbers: The Endless Fiction of Auster and Deleuze and Guattari and . . . by Robert Briggs

In a discussion on BBC World Service, Auster posits that ‘triptych’ might be a better word for the series, but does not have the same sound, therefore trilogy it is.

The novel has been described as ‘anti-detective’.

The re-working of the detective story as a search for the ultimate language shows that it is not the final and speculative textualization that is most appropriate for the postmodern world, but instead, the text that is written about the text. Stories about stories and books of questions, as opposed to books of answers, are the forms that best typify the difficult reality of our times. The New York Trilogy participates in the deconstruction of the legendary tower of the ancestral city and its language, as it describes the Babel-like shattering of the contemporary metropolis at the same time that it expresses the crisis surrounding linguistic representation. Its ideological structure of a wandering through and a detachment from pre-existing principles forces the postmodern subject to question the basis of all legendary archetypes.

Source: Paul Auster’s “The New York Trilogy”: The Linguistic Construction of an Imaginary Universe by Clara Sarmento

For me, this is where a gun is oddly both just a gun as well as something more, and you are never really sure which it is. Auster has said that the genre serves as a means to an end in the same way as Beckett uses vaudeville in Waiting for Godot. Whereas detection fiction is usually about answering question, Auster flips this expectation by using it to ask questions. This approach creates a feeling of excess, where there is always too much happening. Our desire for sense and control is always challenged. Take this quote from the end of City of Glass:

For the most part his entries from this period consisted of marginal questions concerning the Stillman case. Quinn wondered, for example, why he had not bothered to look up the newspaper reports of Stillman’s arrest in 1969. He examined the problem of whether the moon landing of that same year had been connected in any way with what had happened. He asked himself why he had taken Auster’s word for it that Stillman was dead. He tried to think about eggs and wrote out such phrases as “a good egg,” “egg on his face,” “to lay an egg,” “to be as like as two eggs.” He wondered what would have happened if he had followed the second Stillman instead of the first. He asked himself why Christopher, the patron saint of travel, had been decanonized by the Pope in 1969, just at the time of the trip to the moon. He thought through the question of why Don Quixote had not simply wanted to write books like the ones he loved— instead of living out their adventures. He wondered why he had the same initials as Don Quixote. He considered whether the girl who had moved into his apartment was the same girl he had seen in Grand Central Station reading his book. He wondered if Virginia Stillman had hired another detective after he failed to get in touch with her. He asked himself why he had taken Auster’s word for it that the check had bounced. He thought about Peter Stillman and wondered if he had ever slept in the room he was in now. He wondered if the case was really over or if he was not somehow still working on it. He wondered what the map would look like of all the steps he had taken in his life and what word it would spell.

All in all, it is a novel that attempts to find a means of coming to grips with the world.

One must understand how the universe functions before one confronts it with the force of creativity; this is the writer’s task. In citing Samuel Beckett, Auster defines his own ideological and literary bent, thereby depicting his profound critical acumen and his feeling for the mission of the artist: “To find a form that accommodates the mess, that is the task of the artist now.”

Source: Paul Auster’s “The New York Trilogy”: The Linguistic Construction of an Imaginary Universe by Clara Sarmento

Another interesting feature of the novel is the place of ‘New York’. This is as much as an internalised space. Clara Sarmento makes the comparison between New York and Walden’s forest.

The confining within the walls of New York is very similar to the solitude of the forest found in Henry Thoreau’s Walden, recollections of which dominate the Trilogy. In both these works, the authors achieve perfect isolation within the spaces delineated by the city or the forest that endows them with a transcending ability to observe and reflec

Source: Paul Auster’s “The New York Trilogy”: The Linguistic Construction of an Imaginary Universe by Clara Sarmento

In the end, the New York Trilogy feels like one of those conversations that you forget how or why it actually started once it has finished. Auster’s style has a music and rhythm to it in which you can easily become consumed. I like how Robert Briggs captures this in his reading of the novel:

Even though you might start off reading fiction, you can’t expect, in the end, not to find yourself writing the story of your life.

Source: Wrong Numbers: The Endless Fiction of Auster and Deleuze and Guattari and . . . by Robert Briggs

While Gary Matthew Varner argues that the book is ‘rhizomatic’ in that is ‘nullifies endings’:

What makes Auster’s Trilogy endless, and rhizomatic, is that it “nullif[ies] endings” (Deleuze and Guattari 25) … Readers may not want to begin reading Auster’s book at any point in any volume, but the Trilogy nevertheless nullifies its own “endings.”

Source: Paul Auster’s rhizomatic fictions by Gary Matthew Varner


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.

Highly productive individuals, teams, and organisations don’t get to that level merely by accident. It happens through hard work on process which, in turn, leads to consistently-great outcomes. Doug Belshaw ‘Trello Kanban’

This year I was seconded to help in the technical team. In some ways, I am doing much of the same work as before in responding to issues pushed to us, whether it be debugging problems and supporting schools. One change though has been a focus on the technical changes and enhancements. As a part of this, I was asked to complete Atlassian’s online courses associated with the use of Jira and the agile methodology.


In 2001, a group of developers published the Manifesto for Agile Software Development. The manifesto outlines four key values associated with the agile mindset:

  • Individuals and interactions over processes and tools
  • Working software over comprehensive documentation
  • Customer collaboration over contract negotiation
  • Responding to change over following a plan

Source: Manifesto for Agile Software Development

In unpacking these values, Atlassian provide six tips to build an agile mindset:

  1.  Show respect for all team members
  2.  Communicate openly and clearly
  3. Look for ways to innovate
  4. Actively improve your skills
  5. Your work doesn’t have to be perfect
  6. Have a plan, but always be ready to pivot

Although there are many agile frameworks, Atlassian focuses on two in particular: Kanban and Scrum.

The Kanban framework allows teams to visualise workflows associated with work and improvement. It focuses on columns of cards, which allows all involved to easily see the progression of various tasks. (For example, Doug Belshaw introduces Kanban with three columns: To do, Doing, and Done.) Associated with each card, there are details, checklists, attachments, labels, work in progress (WIP) and collaboration through comments. I have discussed the use of Kanban boards in the past in regards to Trello.

Scrum is about iterative delivery of batched improvements. Groups of issues are organised into sprints with a clear focus and a set number of WIP points. Unlike Kanban boards, sprints are time-based, with future work added to a backlog. In addition to the boards, there are certain structures and processes. This includes regular meetings focusing on what has been completed, what needs to be completed and any obstacles, a review of accomplishments, and a retrospective that focuses on what the learnings from the sprint. Associated with the boards and meetings, there are roles central to the cycle, including the product owner and the scrum master. The product owner is in charge of recording requirements and identifying priorities for the business, while the scrum master acts as a coach, responsible for the performance of the team.


Personally, I had dabbled with using Kanban in my work before. I had also explored the idea of agile management in education through Richard Olsen’s Modern Learning Canvas and Simon Breakspeare’s Teaching Sprints. However, I had never dived into the world of scrum and sprints as a part of my current work. One of the reasons has been that it has not been the methodology of the teams I have worked in.

One of the interesting challenges I find with any agile approach is how it works in practice. According to Atlassian, best practice is to make the habit front and centre. This involves using Confluence for documentation and communicating everything by using the @name from the various cards. This then allows the use of various reports to improve processes and outcomes. The problem with this is that it often places other things in the periphery. If all I did was project work, it would be perfect. However, I feel like I work in a dual operating system. I manage my time between the push for enhancements and improvements, and the pull associated with everyday support. The problem is that it feels that both sides are often oblivious of the other. In regards to agile, this often creates scope creep where things are often added that occur outside of project planning.

In addition to this, it would be nice if Jira (or Atlassian) was the only place where I worked. The project I am a part of has many different signals, including email, Google Drive, OneDrive, Microsoft Teams, Google Chat and Ivanti’s HEAT. There are often times when I am not sure where I am meant to look. For me, I think that the various cross-messaging is often akin to football and a team defense where everyone plays their role producing an outcome where the whole is greater the sum of the individuals. However, when there is not buy in across the board, then the system fails.

Another interesting consideration is the structures of something like scrum. In their introduction to the topic, Chris Sims and Hillary Louise Johnson talk about the optimal size of teams:

So, how many team members should a scrum team have? The common rule of thumb is seven, plus or minus two. That is, from five to nine. Fewer team members and the team may not have enough variety of skills to do all of the work needed to complete user stories. More team members and the communication overhead starts to get excessive.

Source: Scrum: a Breathtakingly Brief and Agile Introduction by Chris Sims and Hillary Louise Johnson

I am in a team of four and this creates some confusion at times about who exactly is doing what roll.


In the end, my biggest takeaway was that agile was a mindset, a way of thinking about continual improvement and development. I was subsequently left wondering if the best methodology is one devised based on the requirements of the particular context. With this in mind, I found Eric Ries’ The Lean Startup interesting in that it offers mindsets as much as approaches.


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.