The power of query in sorting out data in Sheets

My first iteration using Query and Sheets to automate a solution for turning a collection of data into a regular newsletter.

This year I decided to create a monthly newsletter collecting together the updates and resources that I came upon during the previous month. As I have reflected elsewhere, I realised that there was an opportunity to automate some of this process. Although some spoke about Pinboard or generating a post via email, I was interested in sticking to GSuite. I therefore began with the aim of generating a solution within Sheets. So here is my first iteration of an automated solution for turning a collection of links into a summary.

Organising the Data

I remember being in a session with Jay Atwood a few years ago talk about the importance of considering the way you collect your data before anything else. As I looked at my databases, one for updates and the other for resources, I realised that the first thing that I needed to do was reorganise the way that I was storing information. This included restricting the options associated with type and application, as well as separating the link and title and then smashing them together using the HYPERLINK formula. Inspired by Ben Collins’ post on working with text, I also created a column summarising the information in each row into Markdown summary.


Reorganised Data w/ Formulas by mrkrndvs is licensed under CC BY-SA

From Lookups to Queries

Once I had my data organised, I then started explored sorting and shaping the data. I began with a VLOOKUP with a dynamic selector. This allowed me to filter it in different ways. However, I quickly realised that this was limited. I turned to QUERY.

I remember David Krevitt talking about QUERY, describing it as the, ” big kahuna of Sheets functions.” I think this initially put me off. This time I opened up a number of guides from Krevitt, Collins and Anand Varma and dived in. This lead me to rewrite my VLOOKUP as a QUERY.

Bit by bit I stretched the solution. I began with a dynamic selector to represent variables and explored the ability to define queries by date. I then created a prototype with a query for each application across the sheet.


1st Iteration by mrkrndvs is licensed under CC BY-SA

Once I had that working, I create a vertical set of queries. To allow for the variable of the unknown number of posts each month, I left 30 blank rows between each formula.

Filtering Results

To get rid of the spaces and the data headings, I used a FILTER formula and removed the spaces and column headings produced by the QUERY formulas. This left me with a choice, copy the MarkDown data and paste it without formatting, therefore removing the table/sheet that it was in, or using the add-on Sheets to Docs to copy the text to a Google Doc.


So that is the first step in my solution using Sheets to generate the text for a newsletter. My next challenge is transferring this to a Google Script. If you have any thoughts and advice about this, I would greatly appreciate it. Otherwise, as always feel free to leave a comment.


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.

Tom Woodward on APIs and automation

Building on the APIs provided by Google Sheets and Google Calendar, I demonstrate how to automate the addition and change of multiple events.


I have been spending some time developing resources to support schools with timetables and reports. One of the things that occurred to me is the challenge of remembering to complete a number of key steps throughout the year. I therefore thought of creating a digital calendar that could be shared with schools that would help remind them.

In my search for a tool that would help with this process, I discovered From Sheets to Calendar, a Google Sheets add-on, that would allow me to create a series of events from a spreadsheet. As I explored this, it occurred to me that this might have ramifications for other groups in my organisation, especially those coordinating professional development. I have seen many plan things out visually in a spreadsheet. However, this means then creating these events again in a calendar. Here then is a guide to managing events with GSuite and sharing them with others.


Setup Calendars

Before setting up the various events, you need to make sure that you have created the various calendars. For example, you might have one for ‘meetings’, ‘professional development’ and ‘events’.

First Start

With everything set up in Google Calendar, install the add-on in Google Sheets. Once this is done, go to Add-ons menu and run ‘FirstStart’ to populate the template to work with.

Add in Events

With all the headings provided, enter the various information, such as title, time, location and description. Also, make sure ‘Add’ is listed against each of the events in the Action/Status column.

Import to Calendar

Once the events have been added, go to the Add-ons menu and run the ‘Import to Calendar’ to create events. Once created, there is an option to update and delete by changing the request in the Action/Status column.

Share the Calendars with Others

Although it is possible to send invites via the sheet, the other option is to share the particular calendars. For schools using Microsoft Outlook, Google Calendars can be shared as an ‘internet calendar’. The other option for users without a Google Account is to download and share a copy of the iCal file. The problem with this is that recipients will not be able to receive updates if there are any updates.


One of the challenges with a solution like this is that it is dependent on someone else’s scripts and support. In addition to this, to create more than 20 events, you need to pay a subscription of a $1 US a year. For those wanting an enterprise option, Zapier provides the connections, but it comes at a cost of $25 per month. There are also a range of scripts to build upon in Github. Another option is to manually import a CSV spreadsheet. So what about you? Are there any processes that you use when automating the creation of calendar events? 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.

I recently came across a post from Jennifer Kloczko discussing the notion of original ideas. In it she documented some of the ideas that she has implemented this year and where she ‘stole’ them from. I too have written about this before, discussing the benefits of sharing and working collaboratively. However, one aspect that Kloczko does not necessarily address is the way in which ideas ‘stolen’ can morph and evolve as they become ingrained in other contexts. This process often starts with a problem. For me, this problem is Synergetic.

This year I have spent a lot of time learning about different facets of Synergetic. A ‘total solution’, Synergetic is a management system with a focus on administration. My work has included developing a reporting solution, setting up the attendance process and configuring online spaces. One particular area that has absorbed quite a bit of my time though has been timetabling.

For secondary schools this is more obvious. You link in a third party applications, such as Timetabler or Edval, to manage things. This however is not the case for primary schools. They have no need for the intricacies of a robust timetabling package, the issue though is that they still need a timetable.

The solution Synergetic offer is a lightweight application called Primary Time. It allows users to create timetable blocks and place them within a visual grid. I have two concerns with Primary Time. The first is that you need to populate a lot of information associated with rooms, teachers, groups and subjects that does not flow through to the timetable file produced. Although there is the means of uploading this information in Primary Time via CSV files (click here for a copy of the different files), these files still need to be made beforehand and maintained moving forward. The second issue is that there is not a direct connection between the two applications. Unlike other software packages that develop a constant connection that allows for a flow of information back and forth, users are required to manually download a file from Primary Time and upload this into Synergetic.

The problem with all of this is that the timetable applications ideally act as the source of truth when it comes to timetable related information. That means if Primary Time were to be used in this way, users would need to follow the tedious cycle of updating Primary Time and then reloading it, every time a change needed to be made, no matter how large or small this change may be. (This issue is compounded by the fact that you cannot download timetable related information from Synergetic and upload it into Primary Time.)

Another factor at play is the reality that most primary schools do not require an explicit period-by-period timetable. Instead, timetables are usually developed around exceptions, with the rest of the time being allocated to a classroom teacher, allowing the to balance and bend their teaching time. Locking in a highly descriptive timetable therefore serves little purpose and is often a hindrance, rather than a help. For most primary schools in Victoria the timetable is required for roll marking purposes, with primary schools mandated to enter results for AM and PM.

An alternative to using Primary Time is manually entering the timetable within Synergetic. Although this is an option, especially when generating roll marking periods, it to is still a very tedious process and not an ideal solution.

After these initial experiences, I was left with the question

How might we use another application to make the development of a timetable for schools easier and more efficient?


At the end of the day, all that Primary Time does is produce a CSV file with six columns: day, period, form, class, room and teacher. This seemed quite simple. I therefore started by trying to reproduce a school timetable by cutting and pasting cells in Google Sheets. This worked, however it was still fiddly, therefore not a feasible solution.

I wondered if there was some way of automating this process, at least generating a basic timetable that could be manipulated. I remembered reading a post a while back from Martin Hawksey documenting a formula for repeating data. (A win for serendipity.) I started with this and then progressively unpacked each column further, addressing the particular requirements. I must admit, I did get some additional help from Hawksey on the formulas that I had conjured together. I eventually managed to put something together.

It involved entering the days in the timetable, the sessions running each day and a list of classes with their rooms and teachers. It would then generate a list with a copy of each class for each session. There were two problems with this. Firstly, it was not easy to add in the specialist classes. Secondly, it was still unwieldy and confusing.

Fine I could make a copy of the timetable adjust it in order to add specialists, one of the challenges with this is working with the timetable in a list format. The ability to visualise the timetable is one of the benefits of using an application like Primary Time. My solution was to recreate the lists as a dynamic table, with a dropdown button to choose the teacher.

To make this, I built on the work of Ben Collins and David at CIFL around the use of the VLOOKUP formula. It also meant I had to INDEX the table to start with the form column. In addition to this, I made a dynamic selector created using data validation to choose the class. Although this answered the visual problem, as soon as different variables (10 day timetable or 8 period day) were added then the table would break. Maybe there is another way I could do this, but I felt like I hit a wall, so I decided to focus on making a simpler solution.

I had started out with the intent of making it easier to create a timetable for Synergetic, so rather than worry about creating a full timetable, I instead turned my attention to creating a timetable solely for timetabling purposes. Rather than create a line for each period, I focused on creating a line for each roll marking period. This way I did not have to worry about anyone making sense of the lists of periods and classes, instead the end user would enter their values and download the corresponding CSV file. The problem that remained was how to make this fail proof.

Spreadsheets can become busy places very quickly and the sight of lengthy formulas puts a lot of people off.

=TRANSPOSE(SPLIT(REPT(JOIN(“,”,ARRAYFORMULA(REPT(SPLIT($H$4,”,”)&”,”,$I$3))),$I$2),”,”))

The challenge then was to focus on inputting the values. I moved the inputs from the page with the formulas and made a separate sheet for that information. After some feedback I then split this information again, with one sheet providing a space to list the teachers, classes and rooms, while the other sheet providing a summary of the days in the timetable and the roll marking periods. Although this hopefully made it easier, there was still the challenge of downloading the CSV file.

To me the process was clear. Enter the classes and definitions, then download the timetable file. The process though of clicking on the right sheet, going to File and then downloading a CSV provided too many concerns. I asked around if it would be possible to turn it into a script. Some colleagues said yes, but suggested just focusing on the downloading of the CSV, with that being the particular point of contention. So I did what I often so, returned to Google.

I have wanted to explored Google Apps Script for a while, but always found other things to distract me. Finally I had a clear purpose. To start off I worked through Ben Collins’ introduction to Apps Script. It provided a useful starting point and a button for my script, but it did not address my particular challenge. After reading numerous forum posts and scrolling through the Developer Page and Drive APIs, I stumbled upon a code that Michael DeRazon had shared on GitHub for downloading a Spreadsheet to Google Drive. As with all things borrowed, I took to bending the code to fit my needs, but none of the changes that I tried worked. I eventually had a colleague look over it and provide some guidance. He pointed out that there was a loop that was messing things up and came up with the solution of downloading the CSV file to Drive and then download to the computer.

Although this was not necessarily the solution I had hoped for, in that it downloaded a copy to Drive before downloading a CSV to the computer, it at least addressed the problem, making the creation of a timetable easier and more efficient. You can get a copy of the sheet here.


For those who got this far, well done and thank you. It would have been easy to have just shared a copy of my resource and be done with it, but I think that the real value is found in the thinking behind it all. To me, this captures the power and potential of digital technologies as summarised by Richard Olsen. He breaks it down into the following:

  • Feedback-Rich Learning
  • Reuse-Rich Learning
  • Continuously Evolving Learning

To build on Jennifer’s point at the start, there are no original ideas and the remix thereof is an ongoing process.

So what about you? What ideas have you borrowed and bent? How have you changed and extended them? 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.

Switching to Google Sheets

In my work supporting online learning, I use a lot of Google Forms to collect and curate information. One of the problems that can occur is that there are many sheets with data spread across the all. Here then are some of the steps that I have taken to streamline some of the steps and processes.

Multiple Sheets Imported Together

Summary sheets linked to forms can add up quickly. One way around this is to use the Import Range formula to collect a number of responses in one place. Along with formulas to translate text, generate sparklines and fetch financial data, IMPORTRANGE is a part of the Google collection:

=IMPORTRANGE(spreadsheetkey, rangestring)

The formula allows users to bring in a range of data from one spreadsheet to another. It needs to be noted that the first time it is used, the user will be prompted to grant permissions.

Conditioning Completed

Another issue with forms is that long lists of data can become unwieldy. One particular use is submitting responses and feedback. Often these tasks involve an action, however it can be difficult to manage these. One answer was to add an additional column and use this information as a trigger for conditional formatting to colour a whole row. The following custom formula that allows this is:

=$A1=”TEXT”

‘A’ is the column that includes the trigger, while ‘TEXT’ being the actual trigger.

Developing a Dashboard

Having all the data imported into separate tabs within the one spreadsheet is one step in organising information. The next step is representing this content in the form of a dashboard. My first iteration was to provide a summary of the responses across all the sheets. To capture this I counted the responses by focussing on emails, using the UNIQUE formula (thanks Martin Hawksey:

=COUNTA(UNIQUE(A:A))

The reason that I included ‘UNIQUE’ is because some people submitted multiple responses for various reasons. Although there are other means of avoiding this (submit once or adjust responses), these solutions sometimes create their own issues and confusions.
Once this summary table was complete, I used it to create a chart to visualise it. To share this particular information, I made it a separate tab and published it. This way I do not need to give access to the sheet and instead can give access to the summary. Although this is not technically ‘a dashboard’, I will most likely share the whole dashboard as I develop it further. For more information on designing a dashboard, I recommend this post from Ben Collins.


So these are some of the ways in which I have streamlined data and the way in which people are able to engage with it. The add to my previous tips and tricks associated with Google Sheets. What are some of the ways in which you use Forms and Sheets? 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.

The #edublogsclub challenge this week relates to books. I am always reading. However, instead of writing another review, I thought that it might be more pertinent to review a review of my reviews I made with Awesome Tables.

Awesome Table allows you to take data recorded in a Sheet and use App Scripts to create dynamic visualisations. As the website explains:

Awesome Table lets you display the content of a Google Sheet into various types of views: From a simple table to people directories, Gantt chart views, Google Maps, card views… There are many possibilities to suit your personal and professional needs. With it, data in Sheets are shown in a more functional way and can be shared with viewers.

I had started exploring options for dynamic displays with the help and guidance of Tom Woodward, who shared a template for my blog roll with me. However, he also directed me to Awesome Tables as another possibility.

Even with tags and categories, one of the challenges of having a blog with hundreds of posts is how to make it easier to find what you might be looking for. One example is finding my reviews of books and podcasts. I am often asked for suggestions and short of giving people a long list, wondered if there was a way of refining this process. This led me to Awesome Tables.

 


For those interested in making your own Awesome Table, I have created a basic guide:

1. Go to Awesome-tables.com and sign in with your Google account. This will then link with Google Sheets.

2. Choose a template from those provided. Preview it and once happy, click Use Template to create a copy.

3. Open the Spreadsheet attached to the template. There are two tabs: data and template. To use the basic template as is, delete the dummy data and add your own.

4. If there are fields that you do not want built into the template, then you can change the headings in the Data sheet. However, make sure that you do the same within the Template sheet.

It is possible really customise an Awesome Table or even start from scratch. However, it can get rather fiddly and involve a lot of abstraction. For those wanting to go down this path, there is a support site with a range of documentation.


So what about you? What strategies do you use to track the books you read? Have you used Awesome Tables in any other ways? 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.

Three Things

“Three Things” by mrkrndvs is licensed under CC BY-SA

I have been doing a bit of work with Google Sheets lately, here . To be honest, Sheets has been one of those applications which I have wanted to go further with for a while, but never really found the time or purpose. With the help of Ben Collins, Alice Keeler, Chris Betcher, Jay Atwood, Chris Harte and Eric Curts I have explored everything from formatting to formulas. Here then are some of my lessons learned through it all:

Smashing Cells Together

I have lost the amount of times that I have had to create a spreadsheet with a range of different data, but each somewhat related. For example, one column has a list of usernames, which then needs to be turned into an email address. Obviously the simple answer is to write two lists. However, the shortcut is to use ‘&’ to smash the two cells together. If you want two names combined then you use ‘&” “&’. To remove the formula from the cell, download as a CSV. This turns everything into text. Then you can either continue using the spreadsheet in another program or re-upload the new sheet.

Validating More than Just Data

I had always been aware of data validation. However, I had never quite seen the potential. Jay Atwood talks about keeping a menu of items in a separate tab in your sheet which you can easily make into a validated columns when collecting raw data. Another interesting use that I came upon is using a data validation cell is a button to select a particular focus. I found this via a video from Ben Collins who documents how to use the VLOOKUP formula to make a dynamic table. (You can actually find one built into the gradebook template in Sheets.) I took this further and made a dynamic table based on a CSV download of a simple timetable. Collins has also written a guide to creating wildcard to search through a data set. While the team at CIFL have developed a thorough introduction to the possibilities of the VLOOKUP formula.

Formatting

One of the big differences between Sheets and Microsoft Excel that I discovered early on with my use of GAFE/GSuite was the absence of formatting. It did not necessarily concern me. However, I worked with some teachers who were frustrated by this. Over time, Google has improved the formatting within Sheets. Now you can merge cells, add alternate colours and change direction of text. Although Sheets does not have the variety of preset formatting options, the Explore Tool now makes its best attempt to provide useful recommendations.

Colourful Conditions

Although it is easy enough to apply a conditional formatting to a set of numbers, it is not as obvious about how to deal with categorical data. The answer is to combine multiple single colour rules. Another useful trick is selecting the ‘awesome’ box with the conditional format menu open. This will then show all the rules applied throughout the sheet. Also, as you select each one, the range being affected is highlighted in the sheet.

Life Made Easier with Formulas

Last year, I decided to analyse my blogroll in an attempt to appreciate the diversity or lack thereof. I started by downloading my OPML file from Feedly and opening this up in a sheet. I then progressively went through my 200+ rss feeds and replaced them with the website, as well as the various categories. I recently discovered that I could have imported some of this data using the IMPORTFEED formula. In part it was Tom Woodward who uncovered this possibility for me through his post on exploring WordPress. The further that I go, the more I realise that formulas afford so many more possibilities than what is offered in the menu. For example, sorting using either filters or dropdowns can be limiting and restrictive. The SORT formula does the same thing, but with more of the nuances.

In Scripts with Trust

Moving on from formulas, I have also being toying around with a few scripts and addons lately. This has included:

  • TAGS Explorer: Martin Hawksey’s Twitter visualisation tool.
  • TimelineJS: the tool from KnightLab to build a visual timeline from a spreadsheet for representing timelines.  
  • Epic Rubric: Alice Keeler’s script for creating, collating and sending out rubrics to students.

More than building formulas, I feel that scripts involve a bit more effort and patience. Sometimes things do not work, but that is part of the learning to work backwards working out where things may have gone wrong. Usually it involves me breaking code that I was not meant to touch. I must be honest, I am still yet to properly dive into scripts and APIs, but know that is probably one of my next moves.

Creative Sheets

One of the areas that has surprised me about exploring Sheets is the various creative activities that seem to rise. Whether it be Tom Woodward’s play on magnetic poetry, Alice Keeler’s idea for pixel art, Eric Curts random emoji writing prompt generator and Jay Atwood’s use of text rotations to create a shape poem. Activities like this always leave me rethinking the limits as to what an application like Sheets may have to offer.


So that is me, what about you? Have you had any experiences with Sheets? 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.