Apologies for missing several weeks. It’s easy to get sucked into work and forget to take a step back every once in a while and self-evaluate. I have trouble remembering to update my personal journal every month, so a weekly schedule is especially tough. Most Fridays I’m too mentally exhausted to write a reflection, regardless of the fact that it would probably help me clear my mind.

Since my last post, we’ve gotten a lot of stuff done on the Hendrix Today app: it conforms to the Hendrix College Style Guide (although that was mainly Dr. Goadrich - I’m not too fond of UI work); all the unit tests are written and passing; most classes, methods, and fields have documentation comments describing their purpose; and we had a client meeting to show off our work so far.

The meeting went very well! The communications director at the meeting was very impressed and had little feedback on the design of the app, which is always a good feeling. One of the main things we decided to remove was the tag system. The only feature it added was the ability to easily sort items by preset categories like “Sports” or “Lecture,” but the search page already uses the title and body text of every item to generate search results, so if the phrase “visiting lecturer” appears in the description of an event then it will already be searchable by the keyword “lecture” (or “LECTURE,” or “ecture,” or “ting le;” it’s not a sophisticated search algorithm).

We also removed the list of offices and their associated maps, since those details change often and would be troublesome to update in the app. We replaced it with a link to the online campus map, which I think is a great resource. They technically give every new student a paper copy of the map, but it’s not nearly as convenient as the online version that you can view on your phone/laptop/device. When I was an incoming first-year student, I pored over that digital map before I even got here as a method to alleviate my anxiety, so I’m sure it will help a lot more students to have it easily available.

Our coworker Olivia is back in town and has begun working on the water quality testing app; Teddy and I will join her on that project once we wrap up work on our current ones, each of which we have brought to an acceptable beta-testable state. The only necessary functionality that’s currently missing from Hendrix Today is the backend pipeline that connects the Hendrix Today submission form to the Firestore database, and it has honestly been the most complex piece of the puzzle so far.

The submission form is a Microsoft Form - this cannot change, since making it a Google Form would not allow for a clean integration into the school’s Microsoft work environment. The Communications department already has a master Excel spreadsheet that contains all of the submissions which they manually transcribe for the daily email, and they would like to continue sending the email for accessibility purposes. That means the workflow used by the app has to be backwards-compatible, and the Excel sheet will stay. We would like to automate the Firestore upload process so that the people working for Communications don’t have to learn how to use the Firebase web console (it would be tedious work anyway), so one of the Firestore APIs will need to be used. The solution that was settled on before I joined the project was a Python script that would take a CSV version of the excel sheet, read it via a pandas DataFrame, and upload each row to Firestore via the firebase_admin API.

It technically “worked” at some point, but the process was by no means automated, so I’ve been updating the Python script and writing a VBA macro in Excel to automatically save a table as a CSV and send it to Python on the click of a button.

Can I take a quick aside to talk about how funny VBA is? It’s literally Microsoft’s version of Basic handed down from generation to generation, still wedged into Office applications. I had to open the Excel options menu and enable the “Developer” tab just to get to the VBA editor, which is a completely separate window with another embedded window inside of it that still has the Windows 7 style and is only slightly better than programming in Notepad! I’m not mad; I use the IDLE Python IDE all the time. It’s just jarringly different from the rest of the Office toolbox.

I’ve taken the liberty of splitting this process into three functions: Upsert, Query, and Remove.

  • Upsert adds new items and updates existing ones in Firestore. This is done by giving each item a unique key, which can be any string. Simple enough for non-programmers to understand.
  • Query gets the current contents of the database and puts it in an Excel table. This allows the user to check which items are currently in the databse, what their values are, and what their IDs are.
  • Remove is a simple list of the IDs of items that the user would like to remove from the database. This table looks very different from the others and has lots of warning texts on it!

The Excel workbook has a table for each action, and each one sends a different command line flag to the Python script to interact with Firestore. The VBA scripts were surprisingly simple - at least, compared to the Python script. Python is the one doing the Firestore API calls and behaves like a traditional script: it gets input from VBA via command line flags and its only avenue of communication is via exit code (although I guess I could redirect Python’s sys.stdout to a log file to be read by VBA, but I’m not sure how much communication Python will even need to do).

I’m not sure how I feel about the whole VBA/Python interaction. For one, it makes for quite an alarming spectacle when you run the macro: another Excel book briefly pops up then vanishes before the user can see anything, then an empty black terminal window appears for a few seconds, then an info box appears that says “Success!” just to let the user know that they didn’t just download a virus or something. The black window is because the only way to run the Python script on a computer that doesn’t have Python installed is to compile it into an executable via pyinstaller, py2exe, or a similar tool. This means that another folder full of DLLs and other dependencies needs to be dragged along with the spreadsheet, and that the Python script has to be compiled for every platform it could be running on (even if that’s just Windows and MacOS, that’s still two compilation passes that each require the compilation to be done on their respective platforms and I don’t own a Mac).

I did, however, learn that Firestore has a REST API which VBA may be able to use. This has the huge benefit of cutting out the middleman and keeping the spreadsheet’s functionality entirely self-contained into a single .xlsm file and its scripts. There are already libraries to work with JSON objects and do HTTP requests in VBA. The only downside to this is…

sigh… I have to keep using VBA. Oh well; it’s not the worst. I’m sure there are VS Code extensions out there to make the VBA-writing experience a bit more pleasant.

So that’s what I’ll be doing for the next week. I’ll try my best to remember to write again next week when we transfer projects, and I’m very ready to have the Hendrix Today upload process finished.