Problem-Solving featuring Excel and JavaScript

The issue:

A page was to be created that would feature a list of stores that were currently open (this was during COVID). Initially there'd be 80-ish stores, but it would be constantly changing and being updated, therefore it could reach over 300. Unfortunately, the data was all stored in a single Excel sheet managed by management so we couldn't pull the data automatically or do much with it. Each entry had the State, City, Mall Name, Hours, and URL (among other jargon we didn't need). We would have to take that information and create an accordion that would divide the stores by state and as you'd click on a state it would expand to show all the stores in that state.

The Bootstrap version we had was stripped down and didn't contain the carousel presets so we'd have to create it manually as well - also a whole different story on how I got that working.

Management's proposed solution:

We split up the stores and manually type up information and code it and then piece it all together. Not only would it take hours, but it would be a hassle each time it would need to be updated.

My solution:

I didn't think this was a very good use of our time so I started brainstorming ways to automate or at least make the whole process easier. I knew you could convert a spreadsheet to an HTML table so I figured I'd start there.

I copied all the data on the Excel sheet and pasted it into a new document and deleted everything we didn't need. I sorted it by State and created a new column with a function that would grab the URL and the Mall Name and format it as such: <a href="URL">MALL NAME</a>

The function ended up looking like this:

=CHAR(60)&"a"&" " &"href=" & CHAR(34)& B1 &CHAR(34) &CHAR(62) &A1 &CHAR(60)&CHAR(47)&"a"&CHAR(62)

In order to escape the characters USCII codes were to be used alongside the CHAR function and combined together using &.

I copied the column of newly created a href's and created a new column and pasted only the values and then went and deleted the Mall Name column and the column with the function. I then converted it all to HTML. It was then a matter of Finding and Replacing with the correct info. And although it did beat having to manually type out every entry as it significantly decreased the amount of time it'd take and it removed any human error, it was still a bit time consuming.

Here the top part is what the CSV-to-HTML data looked like and the bottom part was the end goal:

So I thought, well, what if I use JavaScript?

Initially, I thought about perhaps using Python but admin privileges were required to install an interpreter. Plus, I figured a HMTL with JavaScript saved in a network drive would be easiest of all options as it'd run in a browser.

I created an HTML file with two text areas and a Submit button. Pasting the converted HTML text on the left and clicking submit would run a JavaScript function that would output the result instantly in the second text area.

I defined variables for each State and what to replace each one with. Using the string.replace method I replaced all the first instances of each state.

For example, the first instance of Arkansas was replaced with the part that started from <div class=“accordionWrapper”> and ended with <table class=“data-table”> and it would do this for every State.

Then once that was finished, I used the .split(search).join(replace) to remove all the remaining instances of each state. I had to do this as every entry had the State name in it and only one was needed as all the entries would be nested under it. I also had to use a .split(search).join(replace) to replace the href tags and quotation marks that had been converted to HTML character entities.