Using Excel to Create HTML

My good friend Cinda Baxter wrote a blog post that turned into a movement:  The 3 / 50 Project.

As interest grew, she created a simple website.  As it grew more, she added a supporters page, with a form to register that sent an email.

As she got buried in emails and having no automated way to add the new supporters to the supporters page, she became concerned.

The first step in a solution was convert the formmail form into a Google Apps form that left the results in a Google spreadsheet.

But moving the names to the supporter page was still a manual step of copying them into a GoLive page and

publishing.

Then the publicity hit…

18 hour days weren’t enough to catch up with the flood of sign ups.

So I offered to help.

I didn’t want to write any special software, or figure out how to integrate a MySQL database into her site at this point, so I picked a tool I had been using a lot—Microsoft Excel.  Plus Cinda had Excel so in theory she could do the maintenance herself…

I downloaded the HTML for the page, found a set of patterns for the State, City and Business name.

From those patterns it was relatively straightforward to develop a formula that would generate a line of HTML for each business name.

Its large, its ugly, but it creates the HTML

= IF(H2<>H1,"</p><p><span class=" & CHAR(34) & "headings_2" & CHAR(34) & ">"  & H2 & "</span><br />","") & IF(G2<>G1,"<STRONG>"  & G2 & "</STRONG><br />","") &"<span class=" & CHAR(34) & "body_text" & CHAR(34) & ">" & IF(B2="X","<a span class=" & CHAR(34) & "body_links" & CHAR(34) & " href=http://"& CHAR(34) & J2 & CHAR(34) & " target=" &  CHAR(34) & "_blank" & CHAR(34) & ">","") & F2 & "<br />" & "</span>" & IF(B2="X","</span> </a>","")

It took about an hour to generate the formula, a couple of hours to get the data cleaned up, and a couple of hours communicating with Cinda.

So in a little more than a half day she now has 1,600 supporters on the web page.

We’re working on further automation, along with a reorganization since 1,600 company names on the same page is way too many.  Stay tuned for more updates.