office-1548307_1280

Automate all the things!

I am often asked by our students how I got into programming. I have several instances that I can think of where I saw the lightbulb turn on, but I think my favorite moment came when I saw the power of what writing an automated task can have on productivity.

Working in a school district, you quickly realize how careful technology departments are when it comes to syncing student data between local student information systems and 3rd parties. And rightfully so! In a district position I once had, I was given the weekly task of updating the format of a couple hundred thousand row spreadsheet for a third party vendor. Our SIS export could not exclude special characters, and records with special characters could not get imported into the third party vendor’s system after it was sent via FTP server. In addition to the characters, there was a 2 page list of step by step instructions on what items needed to be updated.  These directions read like “Line 3452 – Remove ` “, or “Change Peterson Elementary to Robert Peterson Elementary”, etc.

The first time I completed the task, it took me about 2 hours.  And every second of the task, I was certain there was a better, more efficient way of completing it.  So that weekend, I began doing research how to automate things in Excel.  A quick google search later and I found something called VBScript. So I started small, like searching for “VBScript go to row”.  I replicated what I found and saw that it worked. Score! Then found a specific character, then updated that character, and so on and so forth. And before I knew it, I had basically reproduced the entire 2 page instruction sheet, only telling excel to do the work this time. I ran the script for the first time, and of course it blew up, but after some debugging, I got the script to run successfully. And it turned a 2 hour task into about 20 seconds. I had a moment that I would only describe as pure excitement.  And the best part, other than the amount of time it saved, it also proved to be much more accurate! Shocker.

Recently I have been playing around a ton with Zapier to build automated workflows. I just love digging in and finding ways to cut out repetitious tasks.  Need to scrape data from pdfs and populate google spreadsheets?  No prob, use DocParser.  Need to tap into web hooks from payment sites and send out custom alerts?  Super easy in Zapier. Things get super powerful when you can inject your own Python or Javascript and coax the tool into doing exactly what you want it to do.

All in all, I’m a huge advocate of automating all the things. It is what excited me to get into programming and continue to improve my skillset. And as my buddy Jwo says, “You know what does repetitive really well?  Computers.”  It’s true!


Also published on Medium.