It hurts to watch my wife do math for her job, so I made this algorithm to help us both

Letting a computer program do the math saves her four hours and one headache each month

The final webpage where my wife can upload a file and see a table of the calculated totals

My wife works in HR

At the end of each month, she must re-calculate the change in headcount for several lines of business within her company.

It involves analyzing each cell of various sheets within an Excel workbook.

There are many decimals involved, and plenty of room for error.

I asked her to describe what she does, so that I could imagine how I might build a script to do it for her.

• Each number in one column represents a change in head count for a particular division of the company
• For each of five subsets of each division, each change in head count must be summed up for the current month, and integrated into the previous month’s total (for a net positive, negative or zero change in value)
• The result is a table of values where each row corresponds to a division and each cell is the new total

My tools of choice

• JavaScript as the programming/scripting language
• Web browser and the FileReader API as the interface for uploading a file
• SheetJS as a library that can read from an Excel file uploaded to a webpage
• VueJS as a way to display the table of new calculations on a webpage

In my quest to find a ready-made solution, I found this very helpful article:

How to convert Excel data into JSON object using JavaScript

Highlights of building this algorithm

Turning a single sheet’s data into JSON

The important lines here are in the middle where I call an XLSX utility function to convert a spreadsheet’s data into a JSON array of objects

• When someone uploads a file
• I capture it via files[0]
• and declare the function that will run when I read this file using FileReader
• I read the file’s data using XLSX and store it to a variable workbook
• Using workbook, I store JSON-converted objects of two of the uploaded Excel file’s tabs: Details and Bridge
• From Bridge I only need the first 5 rows, so I slice it immediately after converting the data to JSON
• From Details I only need three of the nearly 10 columns, so I mutate the array using map and return a new object from each row

Two loops through nested objects to updated numbers

This code shows several loops, each detailed below

1. In the first block I iterate through one sheet’s rows in order to update a running total headcount for each of about a dozen codes within any one of five divisions
2. In the second block I iterate through a different sheet’s rows. For each of those rows, I iterate through each of the properties of the object that represents each row. Inside this inner loop, I ultimately add two numbers to acquire a new number (may be greater than, equal to, or less than it was)
3. In the third block I have another nested loop. The purpose of the inner-most code here is to do two things: first, to round all totals to two decimal places; second, to delete the property called ‘Total’ from one of the converted objects, as this algorithm doesn’t update row or column totals from the original Excel file — and not deleting it would risk displaying it in the table that appears on the page.

Here’s a demo of the working webpage

Upload file with correctly formatted worksheets. Get precisely calculated totals instantly!