As I scroll through my LinkedIn feed, I am frequently greeted with a quote that strikes a chord with me: “AI won’t replace you, but a person using AI will.” This sentiment is one that I have come to understand all too well in my recent work experience, where I was tasked with coordinating the allocations of graduate teaching assistants.
Upon my arrival in this new role late last year, I had a daunting responsibility of filling over 90 positions and obtaining approvals from more than 30 academics. It was clear to me that the traditional method of manual data entry and cross-checking of schedules was not only laborious, but also prone to error.
But, as with many challenges, a solution presented itself in the form of OpenAI’s ChatGPT and Google’s Spreadsheets. By harnessing the power of these tools, I was able to automate certain aspects of the allocation process and experience a significant improvement in efficiency.
In this article, I will share my experience using artificial intelligence (AI) in the form of ChatGPT and Google Spreadsheets to streamline the allocations process and demonstrate that, as the quote suggests, a person using AI can achieve greater success than one relying solely on manual means.
The Problem
The allocation of graduate teaching assistants is a task that is all too familiar, and it is one that is burdened with a great deal of manual work. Entering data, organising student applications, coordinating academic approvals, creating statements of engagement, and communicating with students and supervisors are all part of the process.
The process of collecting applications and approvals was a particularly tedious task. Despite utilising Google Forms and Google Spreadsheets to store data, the coordinator was still required to manually look at each application to identify who had said “yes” or “maybe” to the positions. These names were then copied and pasted manually into corresponding cells for academic review.
The academics would then proceed by selecting students’ names by manually copying and pasting them into another cell. The next step, verifying each student’s schedule availability, was the most time-consuming and prone to mistakes part of the entire process.
After filling as many positions as possible, the last step was to inform applicants of their allocations, sending a generic email to each student along with a statement of engagement letter outlining their allocations, hours, and academic supervisors. All of the information on these letters was already available in the spreadsheet data, but it had to be copied and pasted manually into each student’s letter and then emailed to them.
In a nutshell, it became clear that a more effective solution was required to smooth out the process and increase productivity and precision. I had a hunch that by automating the process, I could save a significant amount of time while also improving overall performance.
The Solution
I was no stranger to the platform when I set out to automate the allocation process, having used Google Drive for nearly a decade and Spreadsheets for a variety of purposes. However, my knowledge of Excel formulas was limited to the fundamentals, such as SUM, AVERAGE, and COUNT.
This meant that as I embarked on this automation journey, I found myself constantly scouring the internet for answers and reading through lengthy articles in order to progress. That’s when OpenAI’s ChatGPT came into the picture, and I decided to put it to use in helping me to create the necessary functions to automate the process.
Though ChatGPT may be considered a new technology, I had prior experience working with OpenAI’s other language models, such as GPT-2 and GPT-3, having used them to create a bot that generated tweets imitating myself based on data from my blog posts, news articles, and tweets.
Therefore, before delving straight into asking ChatGPT specific questions, I first made sure to give it a clear understanding of the task at hand by describing the situation in its entirety and outlining my desired outcome. This ensured that ChatGPT could provide me with accurate and helpful suggestions.
Processing Inputs
To get the ball rolling on automatic data processing, I needed to match up data from my Google Form and Spreadsheet.
The issue was that the data formats were different, for example, the lab names in my Google Form were appearing in the Spreadsheet with a “Year 1” prefix, like “Year 1 [Engineering Design]” while the Spreadsheet only listed the lab names like “Engineering Design“.
To fix this, I needed to extract the information within the brackets, and ChatGPT came to the rescue by providing me with the =REGEXEXTRACT(D10, “[(.*?)]”) function in no time. This allowed me to match up the data effectively.
Now, you may be thinking that the solution I found through ChatGPT could’ve been discovered through a simple Google search, but here’s the kicker – ChatGPT offers answers tailored specifically to my needs.
Take for instance, when I asked for help with compiling all the names in a single cell, separated by commas, of individuals who said “yes” to the corresponding labs. This was one of the most tedious tasks, but ChatGPT provided me with a solution that was customised to my spreadsheet and even included the specific column names I was working with. That’s the real value of using ChatGPT.
=JOIN(“, “, FILTER($D$3:$ZZ$3, INDIRECT(“D” & ROW() & “:ZZ” & ROW())=”Yes”))
Academics can now easily keep track of applicant names because they are displayed in the next column to their respective labs as soon as the applicant submits the Google form. There will be no more manual labour! Consider how many hours this automation will save the coordinator.
But there was still one task left, academics needed to manually copy and paste the names of applicants they wanted to accept into a corresponding cell. While it may not sound like a lot of work, it still required extra effort for data processing.
I decided to take it a step further by adding drop-down menus, but since I need drop-down menus for more than 90 rows, doing so manually would have taken too long. That’s when ChatGPT stepped in and suggested using Google Apps Script to create drop-down menus based on custom criteria. So I did, and the code generated over 500 dropdown menus in seconds.
Academics can now easily make their selections from the drop-down menus, eliminating the need for manual data entry and ensuring accuracy. All the data flows smoothly without any human intervention.
Another challenge was to check the availability of applicants and ensure there were no timetable clashes between allocations. To solve this, I developed a set of functions that took into account the students’ timetables and the maximum hours they were willing to work. When allocating a student to a position, the function checks if the student is available.
To make this happen, I needed to download the lab schedules and input the data into the spreadsheet, along with the lab names. This way, the spreadsheet can now check every applicant’s allocated labs and timetable to make sure there are no clashes. It took over 10 different functions to make this happen, but whenever I encountered a challenge, ChatGPT provided an alternative solution.
In conclusion, by utilising ChatGPT, I was able to receive instant and specific answers to modifying spreadsheet functions. The convenience of having tailored solutions to my specific case made it easier for me to achieve my goals.
Automating document generation
As I dove deeper into the process of streamlining the allocations, I realised that there was room for even more optimization. My goal was to automate not only the application process but also the generation of engagement letters and the sending of emails to teaching assistants and supervisors. With ChatGPT, I discovered that this could be achieved through the use of Google Scripts.
Though I had little experience with JavaScript programming, the principles were similar to those of other languages I had previously learned, such as Python and Pinescript. And with the guidance of ChatGPT, I was able to develop a Google Apps Script that automated the generation of engagement letters based on the confirmed allocation data.
This removed the need for manual data copying and pasting to generate separate Google Docs for each applicant and also spared me from sifting through endless articles in Google search results or trying different examples on my own.
It’s worth noting that while you don’t need to be a code expert, having some knowledge of it is beneficial to ensure proper implementation.
The Results
Streamlining the data entry process and automating the matching of applicants not only saved a substantial amount of time, but it also reduced the likelihood of human error.
The ability to quickly verify applicant availability and avoid scheduling conflicts made the entire process much more precise. The academic approval system further enhanced communication and ensured that the right applicants were paired with the right positions.
While this may not be the most sophisticated example of AI integration in the workplace, it is a testament to the versatility and effectiveness of OpenAI’s language model. By training the model to suit my specific needs, I was able to receive accurate answers in a matter of seconds. I estimate that this automation system will save the admin team over 100 hours every academic year, which is a significant improvement.
Had I relied solely on traditional methods such as Google search, I would have spent countless hours sifting through information to find the answers I needed. However, with the help of OpenAI, I was able to create this automation system in a matter of weeks, which goes to show the power of AI in streamlining work processes.
The Future
The success of this project has opened my eyes to the limitless potential of AI and automation. I believe that by harnessing the power of this technology, we can revolutionise processes and make them more efficient for all.
But I daresay, in the not-so-distant future, not just in a couple of years maybe, but within ten, we’ll see AI bots interacting with applicants and creating these processes on their own.
You may have heard that the ChatGPT API will soon be released, so we’ll likely see some drastic changes in management and administrative processes in the near future. In my case, the AI would be able to review Google Forms, Spreadsheets, and App Scripts and create its own processes with less human guidance.
In this rapidly evolving landscape, it’s important to stay ahead of the curve and find ways to optimise our processes. I encourage you to explore the potential of AI in your work and see how it can help streamline tasks and save valuable time.
To newcomers
A word of caution to those new to the world of AI: at this stage, it is best to exercise caution when sharing sensitive information with the technology. Given that AI is still in its infancy, human developers may eventually access and process any shared information.
In the next 2-3 years, I anticipate that we will see the emergence of GDPR-compliant AI solutions, at which point it will be safe to share such information. Until then, let us proceed with caution.