During my first engineering co-op I was tasked with the automation of various tasks, using the power of scripts. My first major project was for the training coordinator for the company, to try to take some of the tedious manual work off of her plate. The current method for on-boarding new hires was that a spreadsheet was maintained with the hire’s start date, title, name, among other useful information. Every week, the training coordinator had to interpret this information, and create the appropriate Google Calendar training events so that each hire knew where and when each of their introduction / training classes were for the first week or two. Obviously, this required a huge amount of tedious double-checking and manual intervention. It was my task to improve this process, if possible, while minimizing the maintenance required on the script, since I was going to be leaving the company after my co-op ended.

I quickly realized that there were a lot of pieces ready to fall into place, they just needed some simple scripts to do the heavy lifting. After much iteration and meetings with the training coordinator, we came to a solution that made her job a lot easier. The following image shows the overview of the program.

Since she was already maintaining the basic information on each new hire in a Google Sheet for her own records, my script simply pulls that information and extrapolates the useful data: name, title, username, number of days in training. To reduce the amount of maintenance required on the technical end, I added two more spreadsheets for her use that determined how the code would interpret the data from the first spreadsheet.

The second spreadsheet was for basic rules to categorize new hires, with each row of the spreadsheet being a different rule. The code saw the spreadsheet as a lookup table of “IF <title> AND <# days in training> AND <etc…> THEN [assign them to a category]”.

The third spreadsheet was for all the possible calendar events. Each row represented an event which had a title, a start/end date/time, information to go in the body of the event, and which groups should be assigned to it. For example, “monkeys” and “chimps” would be assigned to “Bananas 101”, but “alligators” would not. The second spreadsheet helpfully categorized John Smith as a “monkey”, so the program knows that John should be added to the Bananas 101 calendar event.

Once the script had collected the information from all the spreadsheets, it then created Google Calendar events and assigned the new hires to their respective classes. Obviously, there are a lot of edge cases and other issues that cropped up during iteration, but I can assure you that most if not all have been accounted for, even though they were not mentioned in this write up, for the sake of brevity. One of the most “fun” bugs to find was that when the training coordinator used emoji or accents, the whole script broke. I had to then implement unicode handling for all inputs!

As a final backup, the script sends out a summary email to the training coordinator with any errors that were found when running the script. If an event was not created, it gives the name of it, and a message of why it failed (invalid dates, etc). Among other items, it lists metrics such as “X new hires added successfully” and “X events created”, to further find errors. If the run is incredibly bad, there is another script that can be run to revert that week’s created events.

Over the course of this project, I learned a lot about how the average person expects programs to work, and how you should never underestimate the non-developer’s ability to break the program immediately! 🙂 This project was very rewarding because I was able to determine the problem and work toward the possible solutions with the “client” directly, which allowed me to be creative in my approach. The training coordinator was very pleased with the outcome, and when I checked in 6 months later, it was still working week-to-week as designed.