Teleworking with my current client requires the submission of a form at the end of each day. This form includes hours worked as well as the particular tasks that were worked during that day. Each contractor needs to provide this data, typically organized by their direct manager and emailed to the Supervisor.
This form was only supposed to be used on the rare occasion you had to work from home. Most of the time you were in the office or at the client’s work location. With the onset and persistence of COVID-19 and working from home, this form became a daily headache.
Here is a diagram of the high level process to provide the telework form to the Supervisor.
This diagram shows my team’s early pandemic process with some automation already implemented. Other teams, and the standard process, require employees to open a Word document, fill out the telework form, and send it to their manager for submission to the Supervisor.
Since I’m a nice guy, I decided early in the pandemic to save my developers’ sanity and created a way to use Slack as the input mechanism for entering their daily status. Since they were already using Slack throughout the day, there was no need to have them go find and fill out a Word document separately. What developer wants to do that?
Slack makes this possible with Workflows. I’m not going to cover that in depth here but workflows allow you to add a quick process in Slack where you can present someone with a dialog box for them to fill out some information and then have the workflow route the output accordingly.
Here is the Slack form the developers fill out every day on my team.
This is accessible from our main team Slack channel via a shortcut. Once they fill it out and click Submit, it dumps the output, in a semi-standardized format into a private telework channel.
This was the first step in what would ultimately become a fully automated workflow.
One of the joys of automation is that once you start, you just keep going until everything is running gloriously on its own. As you work through the process, you say, “hmmm, maybe I can make this happen”. That evolves into, “well, since that happened, maybe I can make this happen too”. Before you know it you have a fully automated process and you get to go play Fortnite.
In the most simple terms, I needed to take input from all of the developers in Slack on one end, and spit out a fully filled in and consolidated Word document, based on the approved template, on the other end, oh, and then email it to the Supervisor.
The big problem - I was spending 30 minutes every night manually copy/pasting developer status from Slack to a Word document. This included typing in their work hours and each of their status items before writing and formatting an email to send to the Supervisor. Not fun.
I like to use tools that cost as little as possible, with free being even better. With that in mind, and given the use case and integration needs, some of the typical tools were sort of ruled out, such as UiPath, Microsoft Flow, etc.
I also wanted to keep it as simple as possible. I could’ve built a Slack bot with a Netlify backend service and engineer the hell out of it, but why? Automation is about making your life easier, not adding unnecessary complexity.
So, I decided to lean on the following tools for this project:
The following diagram provides an overview of how the process has been fully automated with the various tools.
Playing the role of Manager in this scenario, I now do nothing. The developers enter their status in Slack and out the other end comes an email with all of their status items and hours worked. #winning
Here are the steps of the process within this new automated workflow.
Slack, they launch the workflow from the team’s private channel. They enter the wonderful things they did today and click
Slack workflowdumps the output from each developer into a private
#telework-statuschannel as a collection point. The output is structured as much as possible given the built-in capabilities of
Slack workflows(which isn’t much). I do format it as much as possible for processing later by the
Google Apps Scriptthough.
Zapierhas been configured to monitor that private
#telework-statuschannel for any new messages. Once the developer’s message shows up in the
Zapierswoops in, grabs it, and saves it to a folder on my
Google Drive. You don’t have much control over the output of this process and unfortunately it saves it as a basic text (.txt) file.
Google Apps Scriptthat is scheduled to run automatically between 8pm and 9pm Mon-Fri. When that script runs it goes to the
Google Drivefolder and reads each text file that was created from the
Slack workflowand cleans it up. Cleaning it means creating a more formatted .csv file that can be imported into
Google Sheetslater. Developers can enter anything, in any way, during the input process so this step is needed to add some basic structure.
Google Apps Scriptloops through each newly cleaned .csv file and imports all of them into a
Google Sheet. This allows me to move from the unstructured developer data to structured data in
Google Sheets, which makes it a whole lot easier to manage the automated process of populating the
Google Doclater. After each .csv is processed it is renamed so that it is not processed again by the script.
Google Apps Scriptthen pulls the
Google Docstemplate from the
Google Drive. The script consumes the template, reading each section in order to write it out to a new document with all of the data populated. The script loops through each row of the
Sheet(which is each developer’s individual status), fills in the particular data elements on the form, and creates a new page in the
Google Docrepresenting each developer.
Google Docis stored on the
Google Driveand is ready to be sent to the Supervisor. In this particular case, it’s a 7 page document with headers, tables, and multiple paragraphs with data that was collected originally in
Slacknow seamlessly embedded in the natural flow of the status report text.
Google Apps Scriptthat is triggered to run in the 9pm hour that will take the consolidated
Google Docfile and email it to the Supervisor. I don’t want to send a link to the
Google Drive, or require the Supervisor to use
Google Docsto read the status, so the script converts the file prior to emailing it. Google doesn’t make it easy to convert to Microsoft Word (imagine that) so I went down the quicker path of converting it to a PDF. The script reads in the
Google Doc, converts it, then forms an email (e.g. header, subject, body), and fires it to the Supervisor. It also cc’s me so I can keep an eye on the process and conduct spot-checks.
Google Apps Scripttriggers in the 10pm hour to go out to the
Google Driveto perform clean up so this process can repeat the next business day.
The cool thing was it didn’t take long to implement this entire system. In fact, I researched, built, tested, and implemented the entire thing in one weekend. Many of these tools are self explantory and for the issues you might run into, there are many resources available online that you can lean on for help.
With anything, there are levels of “done”. I could still go in and make it more flexible, more dynamic, and more resilient (e.g. handling holidays) but for now it gets the job done with minimal oversight and I’m good with that.
As you know, the pandemic made us start working from home heavily in mid to late March 2020. I dealt with the manual process of consolidating developer status in a Word document for a couple of months before I hit my breaking point and had to find a better way.
I officially launched this automation process on May 19, 2020. Here are some fun facts:
It’s more than just pure time saved too. It’s also my sanity. Having to perform this nightly task was tedious, boring, and disheartening. I dreaded it. Now, I still get a feeling of satisfaction when I get cc’d on an email to the Supervisor around 9:30pm every night with the team’s status.
I’m always looking for new ways to automate tasks in my life, so this was a lot of fun. I continue to search for ways that automation can help me reclaim one of our most fleeting resources - time. To this end, I just recently used UiPath to build a couple of attended bots to process all of my team’s AWS expense reports. Fantastic.
If you are interested in learning more or have any questions, feel free to reach out via email or find me on Twitter @jeffdstephens.