Each year in my work, we ask the question as to how might we improve efficiencies associated with the end of year process. In the past I have created a database of school contacts, a process for generating timetables and a template for reviewing data. Two particular requests that were raised this year were the desire to reduce emails associated with updates and personalise correspondences sent to schools.

A Dashboard of One’s Own

In the past we used to have a dashboard with a dropdown as to who completed each task for each school. The problem with this was two fold. One, schools had no visibility to where the end of year process is at as this was only visible to support staff. Two, there was a lot of dependency on emails to know where things are at.

Associated with this, we shared a template with each school which included form allocations. My solution was to create a tab in the school template with a checklist that provided schools with clarity where the process was at and then pull this information into a shared spreadsheet so that the support team had an understanding where things were at. The challenge with this was to create and collect the templates in an efficient manner.

The process for generating the templates in the past was to manually create a copy using the /copy method and then sharing this with schools. This always felt cumbersome. I wondered if there was a more efficient method for creating the templates. I also wanted a more efficient method for collecting all the links. I remember Alice Keeler created a Google Sheets Add-on for making a copy of rubrics, so I knew that it was technically possible. Googling the problem, I found a video (and template) from Carl Arrowsmith that walked through using Google Apps Script to make multiple copies of same file.

What I like about finding such resources is getting into the code and unpacking how it all works, and then making my own changes, especially when the person sharing leaves various notes within the code to guide you. I have always wanted to dig further into Apps Script, but never really found the time to start from scratch. A couple of changes I made was including the school name and school number in both the template and title of the document. You can find a copy of my updates here.

Once I had made the changes, I generated a template for each school. This not only created all the copies, but also provided a copy of each URL in the spreadsheet. I then brought this list back into a dashboard and imported the checklist using the following formula:

=TRANSPOSE(IMPORTRANGE("school spreadsheet","Sheet1!B3:B")

In addition to adding conditional formatting to highlight whether the task was complete or waiting, I added a status cell to provide an update at a glance:

=IF($S2=FALSE,"Waiting on Support",IF($T2=FALSE,"Waiting on School",IF($U2=FALSE,"Waiting on Support",IF($V2=FALSE,"Waiting on Support",IF($W2=FALSE,"Waiting on School",IF($X2=FALSE,"Waiting on Support",IF($Y2=FALSE,"Waiting on Support",IF($Z2=FALSE,"Waiting on Support",IF($AA2=FALSE,"Waiting on School",IF($AB2=FALSE,"Waiting on Support",IF($AC2=FALSE,"Waiting on Support",IF($AD2=FALSE,"Waiting on Support","All Complete"))))))))))))

Although this summary did not provide details about who completed each task, it did however provide more visibility. I guess you can’t have everything?

Personalising Emails

The other improvement related to sending out an email to 300+ schools. In the past we would just use an Outlook template. However, as each of the emails contained a unique link, this no longer worked. As I had a list of these links in a spreadsheet, I worked out that I could just create a unique email for each school, with the link being a variable:

="Dear to whom it may concern,</code></p>
Below you will find a link to the Google Sheet.</p>
"&Sheet1!R4&"
Instructions for filling in this information can be found in the End of Year guide.</p>
School Support"

It then occurred to me that as I had a list of the staff associated with each school that maybe I could replace the cold ‘Dear to whom it may concern’ with ‘Dear NAME’. The problem I was faced with though is that I had all the names in one cell, with each on a new line:

Mahatma Gandhi

Virginia Woolf

Fiona Hardy

My first step then was to SUBSTITUTE the new line (CHAR(10)) with a comma:

=SUBSTITUTE(C4,CHAR(10),", ")

However, I was then left with the following:

Mahatma Gandhi, Virginia Woolf, Fiona Hardy

My next task was to somehow replace the last comma in the string with an ‘and’. Unsure how to go about it, I went online and found a REGEXREPLACE on the Infoinspired website that achieved the desired outcome:

=regexreplace(A1, "(.*),", "$1 and")

This then provided me with the following:

Mahatma Gandhi, Virginia Woolf and Fiona Hardy

I then wondered if I could somehow remove the surname and leave only the first name in the list. After scratching my head for a while, I wondered if I could QUERY the list and select only the names that were not followed by a comma. I found the following REGEX formula:

"^[^,]+$"

I subsequently, SPLIT and TRANSPOSED my list of names and used a QUERY with a match containing the REGEX formula.

=regexreplace(SUBSTITUTE(JOIN(", ",QUERY(TRANSPOSE(SPLIT(SUBSTITUTE(C4,CHAR(10),", ")&","," ")),"SELECT * WHERE Col1 MATCHES '^[^,]+$'")),CHAR(10),", "), "(.*),", "$1 and")

Returning to my email template, I then replaced the ‘to whom it may concern’ with the formula. This meant I could quickly and easily create an email that addressed the actual users and included their spreadsheet.

="Dear "&regexreplace(SUBSTITUTE(JOIN(", ",QUERY(TRANSPOSE(SPLIT(SUBSTITUTE(C2,CHAR(10),", ")&","," ")),"SELECT * WHERE Col1 MATCHES '^[^,]+$'")),CHAR(10),", "), "(.*),", "$1 and")&",
Below you will find a link to the Google Sheet.
"&Sheet1!R2&"
Instructions for filling in this information can be found in the End of Year guide.
School Support 

Providing the following outcome:

Dear Mahatma, Virginia and Thomas,

Below you will find a link to the Google Sheet.

UNIQUE LINK

Instructions for filling in this information can be found in the End of Year guide.

School Support

—

I am sure there are more efficient ways to achieve the same outcome using different applications that I do not necessarily have at my disposal. For example, it would be even better to automatically send the the email from the Google Sheet, rather than copy and pasting the text into Outlook. I also must be honest, even though I completed Ben Collins’ REGEX course, I still have not got my head around it all yet, but I feel that the first point of learning is not always knowing how to do something, but actually knowing that it is possible.

As always, comments welcome.