news music links contact

21 February 2011

JavaScript, Google Spreadsheets, and Ultimate Victory

I like a project now and then. So the other day, I decided I was going to try to save myself some time on this new blog I've been working on. Three days of Googling, reverse-engineering code, and muttering cusswords in my sleep later, I will now be able to save a few seconds every time I make a post there.

The problem:
I wanted certain words to link directly to a dictionary every time they appear with a special link color, and I didn't want to take the time to go to the dictionary myself every time, copy a link, paste it into Blogger, and set the special link class myself.

The solution:
This is so stupid, but since it's a Blogger-hosted blog with no hosting of its own, I couldn't do any backend work. So I decided instead that I wanted to make a list of the target words in Google Spreadsheets (easy to access, easy to maintain), and create a JavaScript that would access that list every time the page loads, find the target words, and replace them with links to searches for those words in Google's own dictionary. That is to say, I wanted the script to change "pusillanimous" to "pusillanimous" when it found it on the page.

The process:
This turned out to be a huge pain in the ass, mostly because I had no idea at all what I was doing. Determination is a hell of a drug, though.

  1. Find some code that does a neat JavaScript find and replace. A quick Google Search led me to Allen Liu's tutorial here which got me started. This code takes a search term, and either replaces that term or highlights it, depending on user input. Nice.
  2. Find some code that reads from Google Spreadsheets. This was much more difficult. Lots of people are messing with this for different reasons, most involving JSON, but I couldn't find a single person doing what I wanted to do (which is why I'm posting my shitty code here even though it's embarrassing -- I figure it might help someone out).

    Eventually I found Mike McKay's demo here which was incredibly helpful. An actual programmer might have been able to just look at his code and figure out what the hell to do, but a demo made it possible for me to play with things in Chrome's debugger until I could kinda see what was going on.
  3. Figure out how to iterate a whole list of variables through the replace code. This took me forever, and was the source of my second strongest feeling of badassery during this project when I finally got it to work.
  4. Figure out how to run the replace code on page load instead of on user action. This would be totally rudimentary for anyone with a modicum of JavaScript experience, but it took me a while anyway.
  5. Create an arrayed variable out of the data from my Google Spreadsheet. This was brutal, and when I finally got it right I got so pumped that I would have kicked anyone right in the face without even thinking about it. Unfortunately I was alone in my apartment at 3 AM, so I had to settle for a quiet fistpump. Mike McKay's code just displays the data on the page, so first I had to figure out how to save it as a variable, then I had to figure out how to disregard all the metadata at the beginning of the string about the spreadsheet, then I had to convert it into an array. This took me hours
  6. Make the Google Spreadsheet idiot-proof. I am, after all, going to be the one using it. Don't need to do anything fancy with ordering or making sure there are no blank cells, since the JavaScript seems to handle those situations just fine. However, it does not handle duplicates well; it replaces them twice. A quick search through the Google Spreadsheets script library revealed some simple code to eliminate duplicate entries, which I'm able to schedule to run on the sheet every 5 minutes (way more often than is necessary). So even if I boner it and enter the same word more than once, it won't create a problem on the live site for more than a few minutes.
  7. Install it on the blog. All the while I had been testing the code here (where you can still see it in action), but of course it wasn't going to play nicely right away on the blog. 
    1. The find and replace code requires an element ID to know what to search and replace, so I had to figure out which of those to use. I settled on "Blog1".
    2. In actual deployment, I realized the original Regular Expression search I was doing (lifted straight from the tutorial) was far too permissive, and it was creating havoc on the page. So I had to figure out how to search only for exact, whole-word matches. I also had to decide to only replace the first instance of a word, because posts were starting to look like link soup.
    3. PAGE CRASHES EVERYWHERE. The list of words is quite long (more than 700 entries), as is the page. My code at the time had the replace command inside the for statement, so I was replacing the whole page about 700 times on every load. Awesome.
    4. Annoyingly, there are "hl"'s in the spreadsheet URL's, and those don't play nicely with Blogger's XML backbone. Don't ask me why, dude. So I'm hosting the .js here for now, until I figure out something better.
Remaining issues:
If any real coders are reading this and feeling generous, I'd really love some help with the following (Updated - seems to work fine in IE, and I've recoded it in another file that has far fewer variables, but is also more difficult for a human to follow):
  1. I'm told this still doesn't work in some versions of IE, although I haven't verified that myself yet (works on my PC). Obviously that sucks if it's true. I have no idea how to fix that.
  2. Capitalized words don't stay capitalized when replaced. 
  3. This only works with one-column spreadsheets that have been published; it's no good for sensitive data or anything that comes in more than one column.
  4. Basic inelegance: I'm sure this could have been done more efficiently (I know for sure I used more variables than I needed to, for example).
The code:
Here it is, heavily commented. If it's helpful to you, I'm glad. If it's not, I'm sorry, but I probably can't help you modify it much. See it working here.

1 comment: