Background
In 2018, I started working at Convoy (a Seattle-based digital freight company) as a data entry specialist. Our team frequently used a tool built in Google Sheets, internally nicknamed...
Our team used this tool to process and retrieve info from e-mails, perform calculations, and log data. It also communicated in real-time with Convoy's internal API, allowing our team to make decisions on shipments with the most up-to-date info available. While the tool suited our needs reasonably well, there were still many parts of our workflow that were tedious or repetitive, so I started working on updates for it during downtime! This process started with defining some concrete steps.
|
The Problems
|
The Solutions
|
User Research
Due to the sensitive nature of some of the info these tools handled, I'm unable to go into great detail regarding specific features, but I can discuss my methodology.
User Interviews
Interviews were held informally as I spoke to each member of our team individually, finding which parts of their workflow were the biggest pain points. After compiling this qualitative info, I parsed their frustrations into actionable design decisions. This largely included determining which processes could be automated for my team, and in response, leading me to create new tools to complete these functions in the background as they worked.
As time went on, I became the Team Lead, and one of my duties included training new team members. This included teaching them how to use tools such as the Tender Sender, and these training sessions became excellent learning opportunities for me as well. I was able to see a brand-new user's reactions to using the tools, and I discovered what may be intuitive to a team veteran could seem confusing to someone else.
When I joined the team, there were about 7 members, and it grew to 14 at its largest. Working with this number of teammates meant that I had the chance to work with people who had a range of experience with technology. Working with team members who weren't as familiar with productivity features (i.e. keyboard shortcuts) also trained my ability to create tools that were self-explanatory and easy to use.
As time went on, I became the Team Lead, and one of my duties included training new team members. This included teaching them how to use tools such as the Tender Sender, and these training sessions became excellent learning opportunities for me as well. I was able to see a brand-new user's reactions to using the tools, and I discovered what may be intuitive to a team veteran could seem confusing to someone else.
When I joined the team, there were about 7 members, and it grew to 14 at its largest. Working with this number of teammates meant that I had the chance to work with people who had a range of experience with technology. Working with team members who weren't as familiar with productivity features (i.e. keyboard shortcuts) also trained my ability to create tools that were self-explanatory and easy to use.
Quantitative Data
My research methods also included quantitative measurements. This included recording the amount of time it would take a user to complete a task with a new tool versus the manual way, how frequently new tools were used, and the number of milliseconds it took scripts to execute before and after re-writing code.
In our workflow, different shipping companies had different needs; for example, some additional calculations/reference info was needed for certain shipments. As our tools evolved, some types of shipments that were once notoriously time-consuming had their difficulty greatly reduced, and as a result, the team members that handled them had more free time. As I was able to take advantage of the data analyzing tools within Sheets and Google Apps Script, I estimated how much time different shipments took. With this, I was able to take a data-driven approach to delegating duties as the Team Lead, ensuring we had a fair workload.
As Convoy's partnerships with shippers grew, so did our team's duties. Our data logs proved that updates to the Tender Sender significantly increased productivity, and the number of shipments we were responsible for grew dramatically; our team handled shipments for approx. 15 companies in late 2018, but by early 2020, this number was in the hundreds. The tool was effectively able to scale to this increase in workload, though, and constant updates ensured that we were able to respond to frequent changes accordingly.
In our workflow, different shipping companies had different needs; for example, some additional calculations/reference info was needed for certain shipments. As our tools evolved, some types of shipments that were once notoriously time-consuming had their difficulty greatly reduced, and as a result, the team members that handled them had more free time. As I was able to take advantage of the data analyzing tools within Sheets and Google Apps Script, I estimated how much time different shipments took. With this, I was able to take a data-driven approach to delegating duties as the Team Lead, ensuring we had a fair workload.
As Convoy's partnerships with shippers grew, so did our team's duties. Our data logs proved that updates to the Tender Sender significantly increased productivity, and the number of shipments we were responsible for grew dramatically; our team handled shipments for approx. 15 companies in late 2018, but by early 2020, this number was in the hundreds. The tool was effectively able to scale to this increase in workload, though, and constant updates ensured that we were able to respond to frequent changes accordingly.
The Tools in Action
Below are examples of some of the tools that came to be used on a daily basis.
Locking for Logging
Part of our team's duties included logging all shipments we managed. This was done by pressing a button in a user's individual working spreadsheet, connected to a script to copy the shipment's data to another spreadsheet. We discovered, though, that if two users pressed the button on their sheet at the same time, one would overwrite the other. After some research, I found a solution: Apps Script's Lock Service. This function could be used to prevent more than one user at a time from using a specific service. After implementing this in our code, our problem with overwriting data was solved.
Reminders
A tricky part of our job was the number of exceptions to our default rules that a shipment could fall under. Over 25 variables had to be considered, including a shipment's pick-up time, its location, our existing number of shipments, and more. Early on, our team needed to frequently reference documentation to check our rules; not only was this error-prone, it was also time-consuming. This was solved with a major update to the Tender Sender, where a custom message could be written for any combination of variables. For example, if a shipment was being picked up in California, if we had 5 or more such shipments already, and the shipper was Starbucks, a manager could write a message saying, "Check with supply team for this shipment!"
Parent Spreadsheet Editor
The most versatile update to the Tender Sender was the Parent Spreadsheet Editor. This was my solution to a need to update a large number of spreadsheets at once; for performance reasons, each team member had their own Spreadsheet file they worked from. When I updated the Tender Sender, I needed to manually apply these changes to each sheet, sometimes interrupting team member's work. With the Parent Spreadsheet Editor, though, I was able to make all changes from one Spreadsheet, and after running a script, that change would be applied to each member's Spreadsheet automatically. In 2020, I was given permission from my supervisors to make this tool open-source.
The following were valuable sources of info in my research to find the most efficient ways to solve my team's challenges.
On GitHub, I reviewed projects built with Google Apps Script to find standardized methods
of writing and designing productivity Add-Ons.
|
The documentation Google provides for Apps Script was critical in understanding how Apps Engine worked.
|
Stack Overflow was a great resource for finding answers to very specific questions, as well as creative solutions that may not be found in official documentation.
|
Google Marketplace Publishing
|
After receiving permission from my supervisor to make the Parent Spreadsheet Editor open source, I used Google's JavaScript Style Guide to document my code ahead of uploading it to GitHub. Additionally, I explored options to put the tool on Google's Marketplace as a free add-on.
In addition to providing materials such as descriptions and Terms of Use, I also needed to create visual assets for the app. Two key components were needed: a banner (minimum size of 220x140 pixels) and an icon (minimum size of 48x48 pixels). |
Google Marketplace is a site where users can download free and paid add-ons for Google Workspace apps.
|
Banner Art
This artwork would be used as the main icon in the Marketplace. In the visual design, I aimed to represent the app's purpose: applying changes from one parent spreadsheet to a number of child sheets. I visually represented this flow from one location to another with arrows, though I also wanted to make the artwork stand out among other products on the Marketplace. Where most have a flat background, I opted for one with designs that mimic ripples in water - this reflected and enhanced the meaning already portrayed by the icons that served as the focal point. Additionally, these extra details would make the art unique in the Marketplace.
Icon Art
This icon would serve as secondary art where needed. It would primarily be used in the OAuth screen when the app is installed, which asks a user if they accept the Terms and Conditions of the program, as well as the functions it would perform in their Spreadsheets. Its visual design echoes that of the banner - as a user would always see the banner before this icon, I felt that the storytelling component had already been accomplished, and this would serve as a subtle reminder of the app they're viewing.
While I was ultimately unable to have the tool published on the Marketplace, I was able to re-use the visual assets for the open-source release on GitHub.
Results and Closing
Many of the tools I created for the Tender Sender were in constant use by all members of my team while I was at Convoy. I'm proud of the work we were able to accomplish, and I'm glad to have had the opportunity to make some of those tools open source, allowing other Google Add-On designers to benefit from them.
The code is available on my GitHub at this link. Thanks for reading!
The code is available on my GitHub at this link. Thanks for reading!