Replacing Excel with a Next.js Web App: A Dev's Playbook

Every developer has encountered the situation where a company relies on a 40-tab Excel file for essential operations. The sole "Excel expert" manages all the information, with formulas occasionally breaking and month-end reporting requiring a tediously long process of copying and pasting data. To address this common issue, the team at aimEncoders has developed a comprehensive playbook to replace these spreadsheet systems with modern web applications.

The process begins with discovering the real workflow behind the sheets. A 40-tab spreadsheet is rarely a single, cohesive system, but rather a combination of multiple interconnected systems dealing with orders, inventory, staff, and reporting. To begin building the new application, the team maps out each sheet and consults the people who actually use them. Often, many of these sheets are redundant, leaving only the essential entities like customers, orders, products, users, and approvals.

The key takeaway here is that data modeling is the foundation of the project. If the entities and relationships are correctly established, the rest of the development process becomes much simpler. Conversely, if these foundations are weak, the project may need to be rebuilt multiple times.

The recommended technology stack for these replacements includes Next.js (using the App Router), server components for dashboards, server actions for mutations, and PostgreSQL as the primary database. PostgreSQL offers features such as concurrent writes, audit trails, and real constraints, which are essential for reliable and efficient operation.

Authentication is managed through row-level permissions, where managers can view margins while staff can see their individual tasks. Background jobs, such as reminders, report generation, and integrations, are handled through a queue rather than direct browser interactions. This approach greatly reduces the potential for errors and enhances the overall performance of the system.

A significant aspect of the migration process is data cleanup, which often takes up about 40% of the work. The team builds a dedicated import pipeline using SheetJS for parsing, fuzzy matching for deduplication, and provides a clear visual representation of any differences before any changes are made to the production database. It is crucial to remember that never trust a direct import, as it can lead to data inconsistencies and inaccuracies.

To minimize risk during the transition, the hard parts that were not discussed in advance should be taken into account. The new application should be run in parallel with the existing spreadsheets for a few weeks. The team uses both systems simultaneously, and any discrepancies are investigated and resolved rather than argued about. Once the new system is confirmed to be accurate, the old spreadsheets can be retired.

Another crucial element is the implementation of audit trails. The audit log records the actor, timestamp, and the old and new values for every meaningful mutation. This feature is vital for maintaining transparency and accountability, making it much easier to answer questions like "who changed this number?" The audit trails also serve as strong evidence for the benefits of the migration.

Testing the workflows is equally important to simply testing the endpoints. Playwright end-to-end tests are run over the critical paths, such as submitting an order, getting it approved, and dispatching it. This ensures that the business processes are accurately represented in the new application.

Ultimately, the decision to replace an Excel-based system with a web application should be based on a thorough analysis of the specific requirements and circumstances. The rule of thumb is to keep using Excel when the workflow is simple, the user base is small, and errors would not have significant consequences. When an existing off-the-shelf product can effectively cover most of the workflow, it should be considered.

However, building a custom solution is justified when the process is critical to the business, requires integration with other systems, and would involve costly compromises if using existing tools. The migration should be approached in phases, starting with the most painful workflow, delivering value early on, and using the savings to fund subsequent phases.

By following this structured approach, organizations can effectively transition from spreadsheets to web applications, improving efficiency and reliability in the process.