One of the more common business problems that we come across is clients creating or validating a process using Excel and wanting to scale that into a web application. There are tools that will get you half way there but it's more important to understand the entire process and how the custom features are translated into the application.
This article will discuss the step by step process we use to transform from an offline Excel document to a full stack web application.
Because we are working from an existing application, there is already a schema that the Excel spreadsheet follows. This means there are entities and those entities have relationships. For example you might have a spreadsheet that keeps track of your inventory. Let's say lawnmowers have a one to one relationship with price. That means one lawnmower has one price. You already know all these rules and the spreadsheet has been created with these in mind. But reverse engineering it helps to create a more scalable solution and leverage existing technology (see step 3).
This might seem obsolete. If you're starting with an Excel application, isn't the scope of work just replicating that as a web application? The short answer is, it can be, but never is. Once you open up new possibilities, for example integrating with third party apps, the scope begins to expand. Because the same limitations no longer exist, the possibilities grow. That's why it's important to see what new advantages can be leveraged from the web application during a scoping process.
This is where we can start to get creative. The application is no longer limited by the native functionality of Excel. We'll discuss in more depth soon but one past example involved taking some pre-written python calculations and creating an API layer between the new web app and the python code. This cut an entire step out of the company's previous process and meant decisions could be made instantly. This part carries the most value, as well as the most risk. That's why step 2 is designed to settle on a defined scope and investigate the custom features/integrations that form part of the project.
Importantly, the migration to a new web application enables scalability. This means different things for different companies. In some cases it may mean being able to create a new user group to allow customers to interact with the application. In others, it might mean collaborating on an app that they previously couldn't. One of the more common reasons we've heard is that it centralises the knowledge and doesn't remain trapped in the mind of the creator.
This sounds great in theory, but how has it been done in practice? My first experience managing an Excel to web app project was a complex design system that took 3 minutes to open due to the size and number of functions used. Not only that, there were 3 of these spreadsheets that formed the overall design system.
It was a complex system in a complex industry. Safe to say, the challenge was sizeable.
After a few deep breaths and several coffees, we began a three week scope to settle on the future state of the application and reverse engineer the schema. The three seperate spreadsheets were consolidated and some steps were able to be removed by moving away from Excel. We discovered a huge opportunity to optimise the system by setting up the scaffold of a design quickly and getting users to make edits on a table. In addition, we could create an API layer and integrate with existing code which would save the company days in back and forth communication.
Once the scope was settled, we created the scaffold of the application in the first week, with the codebots using the schema our web developer built. From there on, it was custom feature development using the process outlined in the Way of Working. With the efficiencies gained from the process above and the Codebots technology, we were 2 weeks ahead of schedule nearing the initial end date. As a result, we were able to include some of the original 'nice to have' functionality as part of the first build.