Text

|
I’m not a programmer so I have to find ways to get things done until I learn more. Tom Woodward ‘Text Acrobatics in Google Spreadsheets’

I have been spending quite a bit of time lately thinking about how to improve my processes. Here is another example.

I was recently asked to review the setup of a number of schools that I am a part of supporting. At the moment there are nearly three hundred schools. Once upon a time when there were only fifty or so schools. Back then, such a task was tedious, but not that time consuming. However, as the numbers increase, such reviews start to become a cumbersome affair.

The focus of my analysis was understanding the rollover rules for each semester. This started with the creation of a unique list of all the schools in the data set using the UNIQUE formula. With this list, I also created an additional number column in which I numbered each school. I then used a VLOOKUP to add this information to the data so that I could visually break-up the data using conditional formatting with the custom formula.

= ISEVEN($B2)

For the next step, I created a data validation to help categorise what sort of setup a school had. In addition to this, I created a data validation for the issues column produced from the column itself. To do this I created a separate UNIQUE column, summarising all the issues. I then referenced the UNIQUE column and simply chose ‘show warning’ for an invalid responses. This way, if there was an issue I had previously entered, I simply selected them from the dropdown list.

Once complete, I created a series of queries MATCHED with the various issues and then used a JOIN to summarise all the schools associated with each issue in one cell:

=ARRAYFORMULA(JOIN(", ",TRANSPOSE(QUERY(A3:J202,"SELECT A WHERE J matches '.*Description of the issue.*'"))))

On reflection, I wondered how I could cut down on this manual process. My intent was to create a template in which I could load the raw data and then have it broken down for me using formulas.

After spending a lot of time trying to reverse engineer the output I wanted using various IF, FILTER and UNIQUE formulas, I realised that rather than trying to embed everything in one formula, I maybe better suited in creating an additional table to help further organise the data.

The first step was to pull the data into this helper table using a QUERY. This means that once loading external data into sheet1 (the default first sheet), I just needed to refresh the query for everything to work.

In addition to this, I created a unique reference for each line by joining together the different pieces of information associated with each line to make a unique reference.

=TEXT(JOIN("",C2:F2),"")

In a new tab, I put together an analysis of the schools. I started this by generating a UNIQUE list of all the schools.

=UNIQUE(DATA!A2:A)

Using this data as a reference, I then created a query for each school, joining the different rollover rules together.

=(JOIN("|",QUERY(DATA!A$2:I,"SELECT I WHERE A = '"&A2&"'")))

I then used this data to produce a summary of the data. This began with a UNIQUE list of combined rollover rules.

=UNIQUE(QUERY(SCHOOLS!A2:C,"SELECT B,C WHERE A IS NOT NULL"))

Using this as a reference, I queried all the schools with the same rule and joined this data together.

="E"&JOIN(",E",QUERY(SCHOOLS!A2:B,"SELECT A WHERE B ='"&C2&"'"))

I also created another columns in which I used a SWITCH formula to describe each of the sets of rules in a more meaningful manner beginning with a category: “standard”, “two semesters” and “needs to be fixed”. Associated with this, I created a column which pulled this category and then used this information to create a series of conditional formulas in order to visually differentiate the different setups.

For anyone interested, a copy of the template can be found here.

After completing this exercise, I have found that I can easily adjust this template for any other data I wish to analysis quickly. It is also testament to the power of making your own tools. I am left wondering if I could use macros or scripts to improve the process, but for now my formulas all duct taped together is working for me. As Tom Woodward sums up:

I’m not a programmer so I have to find ways to get things done until I learn more.

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

|
If you’re trying to understand complex formulas, peel the layers back until you reach the core (which is hopefully a function you understand!). Then, build it back up in steps to get back to the full formula. Ben Collins ‘Use The Onion Framework To Approach Complex Formulas’

I was recently asked to have a look at spreadsheet that listed all the guides and videos located in a support folder as a reference. This folder also included a number of folders within folders, which created a level of complexity.

The current workflow involved using an Add-on File Cabinet from the developers behind Awesome Table to generate a list. The problem with this list is that the exported data was not in the desired format. Whereas the output focused on file, folder and link, the list produced using File Cabinet included other information such as owner, creation date and file size. In addition to that, there were some folders and files listed that needed to be removed. Therefore, it had become manual heavy exercise to refresh the data and update the directory meaning it did not happen that regularly.

After looking at the list and the current finished product and the list produced using File Cabinet, I realised that rather than displaying the sub-folder, that the directory was showing a top level folder. Therefore to manage this, I used a SWITCH formula to create a new column updating the folder name to the overall area it belonged to.

To create the SWITCH, I created a UNIQUE list of all the folders and then pasted them as values so that I could delete those not required to be displayed in the directory. After culling the list, I then added the area in the cell next to each folder. Once I had these two columns complete, I used this formula to add quotation marks to them all:

=ArrayFormula(""""&Sheet1!A:D&"""")

I then copied this list into the SWITCH formula and added in the commas:

=IFNA(SWITCH(B1:B,
" EOY 2020","EOY",
"Absences and Attendances","eSIS",
"Class Maintenance","eSIS",
"Crystal Reports","eSIS",
"Debtors","eSIS",
"eSIS - Information Sheets","eSIS",
"eSIS Navigation-Overviews","eSIS",
"General _ Misc","eSIS",
"Managing Community","eSIS",
"Managing Student Lifecycle","eSIS",
"Reporting","eSIS",
"Staff Maintenance","eSIS",
"Student Medical Maintenance","eSIS",
"SynWeb","eSIS",
"Timetabling","eSIS",
"eLearn General ","eLearn",
"eLearn QRGs","eLearn",
"Accounts Payable","eFIN",
"General","eFIN",
"Purchasing","eFIN",
"ePortal","ePortal",
"Create New Staff","eHR",
"eHR - General","eHR",
"eHR Materials","eHR",
"Employee Self Service","eHR",
"Employee Self Service (ESS)","eHR",
"End of Year 2020 - eHR & Payroll Guide","eHR",
"Leave","eHR",
"ICON General Information","General Information",
""," "
))

With this addition column, I then used a QUERY to capture the title, area and link to present as a directory in a separate spreadsheet to share with a wider audience:

=SORT(QUERY(IMPORTRANGE("docs.google.com/spreadsheets/d/.../edit","Sheet2!A:D"),"SELECT Col1,Col4,Col3 WHERE Col4 is NOT NULL"),2,FALSE)

Ideally, if creating this solution from scratch, I would probably have started each folder with a prefix indicating which area it belonged to, therefore avoiding the need for the SWITCH formula. However, that horse had long bolted. I also like the idea of using the SWITCH formula to manage which folders are displayed. It is definitely easier to pass on to somebody else.

I feel that it would be plausible to find a different script (see for example these examples from Alice Keeler, mesgarpour and Spreedsheet Dev) that might do some other things, such as run the process as a cron job or pull only the desired data. However, that is beyond my current skillset and patience level to dig any further at this point.. Therefore, I will stick to using various formulas to filter out the data for me.

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

|
I am blood in the streets, the catastrophe you can never forget. I am the tide running under the world that no one sees or feels. I happen in the present but am told only in the future, and then they think they speak of the past, but really they are always speaking about the present. I do not exist and yet I am everything. You know what I am. I am History. Now make me good.

There are some novels where you get to the end and are left thinking more about yourself and the world you live in than the actual plot of the story or the characters. The Ministry for the Future is one of those novels. Kim Stanley Robinson imagines how we might save ourselves and the planet from the perils of global warming. This is not a blueprint with all the answers laid out neatly, but rather a provocation that asks many questions that need to be considered.

The story itself is loosely tied together by Mary Murphy, head of the UN Ministry for the Future, a group set up in accordance with Article 14 of the Paris Agreement.

The Conference of the Parties serving as the meeting of the Parties to this Agreement shall keep under regular review the implementation of this Agreement and shall make, within its mandate, the decisions necessary to promote its effective implementation. It shall perform the functions assigned to it by this Agreement and shall: (a) Establish such subsidiary bodies as deemed necessary for the implementation of this Agreement

It follows her journey in putting in place a number of steps and strategies to turn the tide on climate change. Associated with this is Murphy’s relationship with Frank May, a survivor from the extreme heatwave in Uttar Pradesh, the event which dictates the necessity for change. 

However, The Ministry for the Future is really a bricolage of many different voices captured through various texts, whether it be riddles, meeting notes, interviews and accounts. A kind of docudrama. These all combine to provide different perspectives for how things could get done. The reality confronted throughout is that global warming is bigger than a single person or a particular place. It is everywhere and a problem that never really settles. Something Timothy Morton has described elsewhere as a hyperobject.

Global warming is perhaps the most dramatic example of what Timothy Morton calls “hyperobjects”—entities of such vast temporal and spatial dimensions that they defeat traditional ideas about what a thing is in the first place.

In an interview on the Marooned! on Mars podcast, Robinson describes The Ministry for the Future as a novella between Frank and Mary with everything else hanging off it. Therefore, as a structure, it challenges the conventions of the realist novel as it can never really be folded into a particular character. Instead it provides a perspective about how things could get done collectively from a number of perspectives and voices.

In the end, The Ministry for the Future is an example of near-future anti-dystopian science fiction, what Robinson has described as a shot ahead of a moving object that is the ever present. It is therefore an urgent book for now. A piece of modal schizophrenia that challenges the reader to consider what is and what ought to be. It raises many questions about topics such as violence, sacrifice, wealth, responsibility and what is actually possible. This challenge not only comes through the content grappled, which even at the end of the novel nearing the year 2050, still seems present, but also through the structure of the writing, whether it be the use of riddles to aid in thinking and reflecting or the fractured nature of the narrative that requires the reader to fill in the gaps as they go. I guess this is why Barack Obama included it in his books for 2020?

For a different and shorter perspective on the novel and the challenge of global warming, Kim Stanley Robinson presented a TED video recounting the story from the year 2071.

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

|
If I can't quite be my own good mother, Ill find one in music. Her name is Enya. Chilly Gonzales ‘Enya: A Treatise on Unguilty Pleasures’

I have long been encapsulated by Chilly Gonzales and his ‘musical genius’. Whether it be his work with various artists, pop music masterclasses and minor christmas album, I have been enamoured with the way in which he manages to break music down to capture what is essential. I was therefore intrigued by a book on Enya.

I purchased the Enya: A Treatise on Unguilty Pleasures in good faith with little idea what to expect. I thought it might be some sort of technical breakdown of Enya’s work. Although I am always interested in what Chilly Gonzales has to say about any sort of music, I was not sure how interesting an extended breakdown of Enya’s music would actually be. What I had not expected was the way in which Gozanales used Enya and her music as a frame for his own memoir on music.

It was almost a joke that rose up from my unconscious. But it was my way in. With Enya as a constraint, I could finally write a musical memoir, the very book the publisher had asked for years ago.

Fine Gonzalas spoke about the Enya’s lullaby quality, the guilt often associated with liking such music, her use of the pizzicato strings on the Roland D-50 synthesiser in lieu of a rhythm track and the way in which she has managed her career by continually say no. However, often these references are merely jumping off points for Gonzales to reflect upon his own memories and experiences with music. Whether it be the relationship between harmony and melody:

Harmony is melody’s bitch, with no life of it’s own.

His desire for music that can be both serious and drop into the background:

This is what my Enya book is about. This idea of music that sounds good while you eat or party or take a bath, versus music that you give your full attention to. And you guys are having the wrong argument. It’s not that all music falls into these two categories. The goal of music should be to function on both levels. It’s like with people.

Disdain for loud voices:

Vibrato is a bit like my formerly beloved jazz fusion: technically very difficult to learn but even more difficult to listen to. But to sign with no vibrato at ll, to let the music itself do the emotional work is the purist’s choice.

And his preference for the music over lyrics:

Wordlessness works for me. I was never a lyrics junkie outside of my affection for listening to rap. Rap lyrics are direct, playful and journalistic, standing in contrast to the impressionistic, poetic style of singer songwriters. With some exceptions I listen to music where the lyrics are in the passenger seat. No one really hears or cares what the Bee Gees are singing about, and I doubt that a single Bee Gee would even dispute that.

In some ways Gonzales’ reflection on Enya reminds me of Damian Cowell’s Only the Shit You Love podcast. Like Gonzalas’ constraint as a guide, Cowell uses his video series as a starting point from which to reflect upon music past and present. They are both musical memoirs of artists engaging in artifice. Maybe the real purpose of such texts is not to uncover the author but to provoke the reader (or listener) into considering their own thoughts and finding their own good mother in music.

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 challenge to me is to go beyond the question of instruction and understanding of different languages. Beyond debates about fitting it within an already crowded curriculum. Instead the focus should be on creating the conditions in which students are able to take action and create new possibilities. Maybe this involves Minecraft, Ozobot or Spheros, maybe it doesn’t. Most importantly it involves going beyond worrying about training or competency, as Ian Chunn would have it, and instead embracing the world of making by leading the learning. - Aaron Davis ‘Did Someone Say Coding?’

In my work, I recently had an issue with absence data where the system had generated some duplicate data. Short of creating an actual report within the system (which I neither have the time or access to do), I decided to have a go at creating a reusable template that I could easily share with anyone. The idea was to take the data and produce a number of specific outputs, including the duplicates and a specific set of ‘All Day’ duplicates.

I started the template by creating a spreadsheet with three tabs: Sheet2, DUPLICATES and ALLDAYDUPLICATES. Sheet2 contained a summary of the ID, event data, name, absence type and event type.

=QUERY(Sheet1!A1:R,"SELECT A,R,E,F,G WHERE A IS NOT NULL")

I then made a column of unique data by combining the ID and event date. To do this, I used Ben Collins’ trick using formulas in a named range as a means of capturing the variability of the data. 

=ARRAYFORMULA(CONCAT(INDIRECT(IDS),INDIRECT(DATES)))

With IDS named range being:

="'Sheet2'!A2:A"&COUNT(Sheet2!A2:A)+1

and DATES named range being:

="'Sheet2'!B2:B"&COUNT(Sheet2!B2:B)+1

I then used the column of unique data to create a TRUE / FALSE column as to whether the absence might be a duplicate.

=ARRAYFORMULA(countif($F:$F,(INDIRECT(UNICON)))>1)

With the UNICON named range being:

="'Sheet2'!F2:F"&COUNTA(Sheet2!F2:F)+1

I borrowed this formula in part from Collins who used something similar to use Conditional Formatting to highlight duplicates:

=countif(A:A,A1)>1

In the DUPLICATES tab, I created a QUERY that brought in the duplicates ordered by date

=QUERY(Sheet2!A:G, "SELECT A,B,C,D,E WHERE G = TRUE and A IS NOT NULL ORDER BY B")

While in the ALLDAYDUPLICATES I created another QUERY which focused on ‘All Day Absences’

=QUERY(DUPLICATES!A:E, "SELECT A,B,C,D,E WHERE E = 'AllDayAbsence'")

With all this in place, to query any data, I make a copy of the template. I then import the absence data exported from the external system. Although I could possibly copy and paste the data, I have found that this is fraught with dangers. In regards to the ‘Import Location’ I select Insert New Sheet(s). This then loads the new data into Sheet1. I then click on the formula in Sheet2!A1 and press enter to refresh the QUERY formula and populate the rest of the spreadsheet.

For those interested, a copy of the spreadsheet can be found here.

—

Growing up, I never really went deep into the world Microsoft Excel. It was not until exploring Google Sheets that I started appreciating the power and potentials. One of the consequences of this is that a lot of the habits that I have learnt are not applicable in reverse. For example, Ben Collins has a site discussing the power of moving from Excel to Google Sheets. This includes a discussion of IMPORT features and the QUERY function. However, there is nothing that I am aware of that goes the other way. I guess this is the nature of coding in general. Every language has its own set of affordances and we work within these constraints. What the situation has taught me though is that sometimes solving problems is about working with what is at hand. I guess the challenge is developing our box of tricks so that we do have something at hand. In some ways, this reminds me of something Amy Burvall once said:

“in order to connect dots, one must first have the dots”

So what about you? What small tools have you created to solve a particular problem? What are the strategies that you use to continually add to your toolbox so that you are always ready when a problem may arise? As always, comments and webmentions 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.