Category
Technology
Submitted By
Andrew Bruce, abruce@uccs.edu, Student Assistant
Project Team
Andrew Bruce, abruce@uccs.edu, Student Assistant
Project Description
JavaScript programs automate repetitive Excel tasks using student and donor information. For our large, detailed, Karen Possehl Women’s Endowment (KPWE) scholar sheet (picture: KPWE 1), the scripts create a new sheet of only scholar and mentor information (picture: Scholar Mentor), create a new sheet containing only KPWE scholars’ graduation details (picture: graduation tracking), sort the scholar information by their graduation times (picture: KPWE 2), and rewrite names in different formats as needed (picture: KPWE 3). For our Donor details sheet (picture: Donors 1), the script sorts all donors by their last names and handles multiple name formats (picture: Donors 2).A script was also made for the Donors Bios Master List which sorts by last names in various formats, including multiple individual names, couple’s names, and certain other common symbols in the file.
Project Efficiency
The automated processes include adjusting scholar information formats, sorting scholars by graduation dates, creating a sheet of scholar graduation information, creating a sheet of only scholars and mentors, and organizing donor profiles by last names (which appear in many formats). The scripts allow users to edit information once in the main KPWE sheet and easily update the whole workbook, instead of making multiple edits. In summary, tedious editing and manual sorting of unconventional metrics within the KPWE and Donor workbooks is easy and fast. To my knowledge, I’m the first and only one in my office writing Excel scripts.
Project Inspiration
Bridget Roberts asked me to create for her a very detailed spreadsheet of our KPWE Scholars, along with a way to quickly and easily update several related spreadsheets. To do this, I learned on-the-job how to create TypeScript programs for Excel and began figuring out how to automate several tasks with the Office Scripts API. After creating several scripts for Bridget, I spoke with Saad Bajwa about scripting and ended up making a script for the Donor sheet.
What Makes You Happiest about this Project?
I appreciate the opportunity to exercise my programming skills as a student employee. I enjoy learning new tools, helping my coworkers, and solving challenges. I’ve noticed many of my coworkers dislike Excel, so I’m hopeful I can write some scripts to help them as well. With this experience, I also feel reassured that my choice to study computer science is the right fit for me. Lastly, I feel fortunate to have had the independence to guide the project as I saw fit while still reaching the desired outcome.
