Creating the UCD School of Business Event Register

How the project came about:

What was the need

In short, the need was for a shared, specifically Google, calendar. The problem, if you like, was three fold. It had been raised, almost in passing, about events being planned on top of one another. Obviously this leads to a conclusion that having a list of events would be of use for reference. Thirdly, as our College is spread out across multiple sites and buildings, we needed an all College approach – the physical distance between locations also affects the ability of people to attend events of interest.

Who was involved

I manage a cross-functional IT/AV team in the College of Business, located in the Smurfit School. I led the technical work with support from various colleagues. We have engaged with the respective School Managers and colleagues whose work revolved around managing, promoting and reporting on events.

What was the scope

There was a huge opportunity here to go beyond simply creating a shared Google Calendar. I strongly advocated for what we have come to call the “Event Register” and one of the products of the register and its processes is a shared calendar.

So what did we try to solve in one go? Obviously, we need to populate a calendar.

We wanted to avoid having a shared calendar where people could enter and edit the events themselves. Our “staff” community comprises roughly 285 people, so that’s a lot of potential errors – where events go into the wrong calendar or accidentally get removed or edited etc without a good way to manage oversight. Not a good place to start as the integrity of the information is key.

Perhaps someone curates the calendar? We don’t have someone on our staff who could spend the time required to trawl through all of the sources and curate an accurate list.

So what if we had people enter the information through a Google Form which lands in a spreadsheet (like we’ve all done) but then we add some script to the sheet to process that information, adding it to a Google Calendar everyone has access to. The College of Business, and the Smurfit School in particular participates in several ranking and accreditation processes. There are many metrics used and several relate to events and certain criteria for those events. When events are being submitted to the register, we ask people to select relevant criteria etc, and now that we are collecting the information uniformly through, we can report on it accurately. This will remove the need for manual harvesting of data from silos around the college.

What the initial phase was

The form design

The form began as a fairly simple Google form with all of the information you’d expect to add for an event, like the title, times, location, etc. As I worked with different colleagues, particularly across our different schools and buildings, patterns emerged where the information we would like to collect diverged. In an effort to maintain the principles of having one central event register I opted to expand on the form using the available “conditional” options within Google Forms. This allows us to have one form, but offer different questions depending on what building your event is in, or what room within a building – essentially layering the conditions – but without writing any code or adding particular complexity. I cannot stress how crucial it is that you consider the front end of the process as crucially as you consider the back-end or the coding. Collecting all of the required info, in a useful format means you can do many things with it. I think of this as collecting the data once, uniformly, directly from the source(s) – those completing the form – and then “putting it to work” for us, on more than one occasion and for multiple purposes.

The iterations

Beyond the form iterations above, there were iterations for how the spreadsheet data was presented and how the script functioned.

When you link a google form to a spreadsheet to collect responses, by default they go into a sheet called “Form Responses 1” (unless you decide otherwise!). Deliberately, we do not alter this data or the sheet at any point, so we preserve the data as submitted to us. As the entries arrive, we process them and append them to an Event Register sheet, along with some of the information generated during the processing, like a link to the created event on our shared Google calendar.

Subsequent significant iterations saw the addition of some basic “event management” options, where we can update a row in the Event Register sheet and then write the changes to the event in Google calendar. This allows us to minimise divergence in the data between the sheet and the calendar. We can delete events similarly.

For a concise business case document

We are launching the Event Register for staff in the College of Business over the summer.

Event register  Walk through – Functionality & Script

Skills & Challenges

What is your skill set here

I have 20+ year of experience in a variety of technical areas but significantly I have some experience and understanding of coding. I had not used Forms, Sheets and App Script for these purposes before but I was able to begin prototyping quickly. I can safely say that it is doable, but it’s not an afternoon’s work for a beginner. Don’t be afraid to ask ChatGPT and friends but don’t expect it to spit out a full solution either. Side note: along the way, I asked ChatGPT for an example of how to do one thing and it provided me with an example, which didn’t work. 5 minutes later when I said it didn’t work, it said “you’re right, it doesn’t”.

What other contributions were needed

Along with lots of conversations with many colleagues and nuggets of wisdom here and there a particular thanks must go to Beth Gormley, Communications and PR Manager in the College of Business. Since we first discussed a concept, a couple of years ago, some way to have a view of everything event related happening in the College, we have always wondered how best we could go about it. Beth has the onerous task of contributing information to many ranking and accreditation reports amongst other things, so the potential to combine “just a calendar” with a way to report on all of that data was too great an opportunity to pass up. I am indebted to Beth for her time to consider and contribute to not just how we begin with this but what it can be doing for us in six or 24 months time.

The hurdles

This began as “the shared calendar” with some bells and whistles tagged on and I quickly realised it just sounded a bit like complicated overkill. That’s where the reframing of the concept as the Event Register helped to move it forward and colleagues were able to more readily see the value in the whole process.

What is the potential

How could other people use it

I am confident that there is a cohort of people who are in a similar situation; they have events and they need a good way to collate that information and it would be great to have it pushed into a Google Calendar, so you can leverage all of the features inherent in there.

 

If you would like to learn how to make your own Event Register consider signing up for the Event Register session in WST Summer School

You may also like...