Kind of poetic, isn’t it? The act of speaking to an art-AI feels like a communication word-game — like playing Charades or Taboo, where you have to trigger your collaborator to produce the right result by talking around a subject. Except in this case, the goal is to find the correct incantation that awakens the spirits residing within yonder eldritch cauldron of vectors, and summons them to do your bidding. Clive Thompson “The Psychological Weirdness of Prompt Engineering”

I must admit, I have been somewhat circumspect to what impact applications like ChatGPT would have on my work and life. I am not going to deny the potential, but there are too many stories that make me wonder, such as John Johnston’s chat with Bing. Really, I have had enough trouble handing over some of my work to colleagues, I therefore cannot see my work disappearing anytime soon, even if I were to somehow hand it over to a chatbox.

However, one area that I have found it interesting to explore is the ability to learn with the support of a chatbot. This is a use that I have seen come up in my feed. For example, Ben Collins talks about using AI tools to create formulas:

The AI tools can create formulas for you too.

ChatGPT and Bard generally give correct answers for simple formulas but it’s hit-and-miss with more complex formula asks.

Again, it pays to be as specific as possible with your prompt.

Source: What AI Can Do For You As A Google Sheets User. Is The Hype Justified? by Ben Collins

While Doug Belshaw has reflected on using AI tools to improve code:

I’ll not go into too much detail, but I wanted to replicate the style of my main archives page which is generated using the Simple Yearly Archive plugin. I duplicated archive.php in my themes folder, renamed it category.php and then tinkered around with it. ChatGPT was excellent at giving me the code I needed to do the things I wanted, including for the category RSS feed.

Source: Tinkering with WordPress category archive pages by Doug Belshaw

Through my work Microsoft account, I discovered I have access to Bing’s Co-Pilot chatbot based on OpenAI’s GPT-4. I have therefore been tinkering with this a bit. In particular, I have been using this when I have a question about creating a formula or script. For example, today I asked for a PowerShell script to change the naming format of a group of files. I began by asking:

How do I use PowerShell to swap around parts of multiple file names each split with an “”? For example, the current format is 12343_FirstName_Surname.pdf but I want it to be 12343_Surname_FirstName.pdf

Bing came back with a formula that spoke about moving around the different parts split into an array and move the elements around, however it did not account for the ‘.pdf’ ending. So I asked the following:

Using PowerShell, How do I split a file name into an array using _, but exclude the ending .pdf

It then added the missing lines associated with removing the .pdf ending at the start of the process and then adding it back at the end.

See the final script below.

# Set the current directory
$directory = "xxx"

# Get all files in the directory
$files = Get-ChildItem -Path $directory

# Loop through each file
foreach ($file in $files) {

    # Remove the .pdf extension from the file name
    $fileNameWithoutExtension = $file.Name.Substring(0, $file.Name.Length - 4)

    # Split the file name into an array using the underscore as a delimiter
    $fileParts = $fileNameWithoutExtension.Split("_")

    # Swap the first and last elements of the array
    $fileParts[0], $fileParts[1], $fileParts[2] = $fileParts[2], $fileParts[0], $fileParts[1]

    # Join the array back into a string using the underscore as a delimiter
    $newFileName = $fileParts -join "_"

    # Add the file extension to the new file name
    $newFileName = "$newFileName.pdf"

    # Rename the file with the new file name
    Rename-Item -Path $file.FullName -NewName $newFileName
}

What I have found is that although I often get an answer, it is not always the final answer or correct for that matter. This was the case when after I recently exported all my links from Diigo and asked Bing for a Google Sheets formula to check which URLs sent back a 404 and which didn’t. Co-Pilot gave me back the following formula:

=IF(HTTPResponse(A1)=404,"404 Error","No Error")

I tried this in Google Sheets only to get the error:

Unknown function: ‘HTTPResponse’.

I then asked Bing, “What is the HTTPResponse function in Google Sheets?” To which Bing responded that there was no function ‘HTTPResponse’ built-in, but that I could use UrlFetchApp.fetch in Google Apps Script to create a custom function. It then also provided links to a number of sources which I followed, finding Adham El Banhawy’s guide the most helpful. Ironically, I then got an error in trying to run the custom script, which I raised in Co-Pilot, and was given a fix.

function getStatusCode(url) {
  if (url === undefined || url === null) {
    return null;
  }

  var url_trimmed = url.trim();
  // Check if script cache has a cached status code for the given url
  var cache = CacheService.getScriptCache();
  var result = cache.get(url_trimmed);

  // If value is not in cache/or cache is expired fetch a new request to the url
  if (!result) {

    var options = {
      'muteHttpExceptions': true,
      'followRedirects': false
    };
    var response = UrlFetchApp.fetch(url_trimmed, options);
    var responseCode = response.getResponseCode();

    // Store the response code for the url in script cache for subsequent retrievals
    cache.put(url_trimmed, responseCode, 21600); // cache maximum storage duration is 6 hours
    result = responseCode;
  }

  return result;
}

In each of my experiences of CoPilot, I have had to make adjustments to the code provided. A part of this is actually learning what is happening. However, this may well be the questions that I asked:

  • talk to it as if it were a person
  • set the stage and provide context
  • have the AI assume an identity or profession
  • iterate with multiple attempts
  • keep it on track
  • specify output format
  • explicit constraints on responses
Source: How to write better ChatGPT prompts for the best generative AI results – David Gewirtz, ZDNet, Oct 12, 2023 Commentary by Stephen Downes

Or it may well be the trial and error nature of coding. The thought that I am left with is a comment a few months back that questioned if we have to learn what prompts to use with AI tools, how intelligent are they? I With this in mind, for me I feel that AI tools are useful as an aide, but I am circumspect about using them as the answer. I guess time will tell.

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.

I snickered at QR codes because I couldn’t see any real use for them. It took a pandemic to reveal their serious utility. And while I generally avoid predicting the future, I suspect QR codes might be with us for a while, because they’re turning out to be extremely useful even outside of the realm of pandemic-adaptation. Restaurants are enjoying not having to print and reprint menus; contactless payments are super convenient even in situations where you’re not worrying about fomites. Clive Thompson ‘4 Lessons From the Improbable Rise of QR Codes’

One of the things that I often struggle with is with the purpose my online meanderings. Although I agree about ‘collecting the dots‘, it can sometimes be hard to justify in amongst the everyday hustle and bustle. However, again and again I find myself diving into something I read long after the fact.

Today I was testing barcodes with an electronic sign-in system. I had a Crystal Report which produces student cards that included a barcode associated with the ID. However, I found that although I could get them to work with the scanner, it was very fiddly and was far from optimal.

Knowing that any adjustments to the Crystal Report would take some time as it involved a number of teams and processes, I wondered if I could produce my own cards using a spreadsheet. I noticed that the third-party application used a QR Code when enrolling a new device which has really quick, so I wondered if I could also use a QR Code for the student cards. After doing an initial test with one student ID to confirm that QR Codes would work, I searched up a post from Ben Collins regarding the generation of QR Codes using Google Sheets. After exporting a test copy of the student data and importing this into the Google Sheet in Sheet1, I created a template where a QR Code was generated for the each student ID:

=ARRAYFORMULA(IMAGE("https://chart.googleapis.com/chart?chs=250x250&cht=qr&chl="&ENCODEURL(QUERY(Sheet1!A2:A,"SELECT A WHERE A IS NOT NULL")) ))

Then in the next column I entered the student details, including first name (Column L), last name (Column K), class/form (Column I), year (Column F) and ID (Column A). I also used Char(10) to separate this information onto different lines.

=ARRAYFORMULA(QUERY(Sheet1!L2:L,"SELECT L WHERE L IS NOT NULL")&" "&QUERY(Sheet1!K2:K,"SELECT K WHERE K IS NOT NULL")&""&Char(10)&"Class "&QUERY(Sheet1!I2:I,"SELECT I WHERE I IS NOT NULL")&" / Year "&QUERY(Sheet1!F2:F,"SELECT F WHERE F IS NOT NULL")&""&Char(10)&"ID "&QUERY(Sheet1!A2:A,"SELECT A WHERE A IS NOT NULL"))

I was then left with a list of cards, the problem is that I wanted to print two columns to a page. To achieve this I created two dynamic named ranges, one for column one:

"Sheet2!A1:C"&(ROUNDUP(COUNTUNIQUE(Sheet2!B1:B)/2))

And column two:

="Sheet2!A"&(ROUNDUP(COUNTUNIQUE(Sheet2!B1:B)/2))+1&":C"&(ROUNDUP(COUNTUNIQUE(Sheet2!B1:B)))

Then in a new tab I used the INDIRECT formula to bring in the two columns.

The last step was to adjust the printer settings. This included extending the margins and changing the scale to 55%.

Knowing that this would be used by different people, I made a copy of the template and deleted Sheet1 that could be used as a master. I then put together the following instructions for how it could be used from scratch.

  1. 1. Create a copy of the QR Code Google Sheet (https://docs.google.com/spreadsheets/d/1lzfHDR9229g5xWTb16JPRjKDTBHlix1NmOTJE9fp-ck/copy)
  2. Import the student spreadsheet previously saved and Insert a New Sheet
  3. Click on Sheet2
  4. Click on Cell A1 and press Enter
  5. Click on Cell B1 and press Enter
  6. In Cell E2 add ‘=’ at the start of the formula
  7. In Cell F2 add ‘=’ at the start of the formula
  8. Click on StudentCards tab
  9. Click on FIle > Print

Not sure if anyone else has used Google Sheets for similar purposes. Comments appreciated.


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 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.

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.