Okay, maybe that isn't the basis for the story I'm about to tell, but I was nearly driven to drink by these three, um, tools.
As part of my day job, I work with a small database of resources. Until recently, the database has lived on a Moodle site---a long and painful sidebar to this story, which I will spare you. As our story really begins, this database is downloaded into a csv file and begins its new life as a spreadsheet. The goal is to webify this content and make it interactive---all without the expertise (and expense) of a developer. We do have the ability at our workplace to have a table on the web with alphanumeric sorting---but no filtering. This is great for things that you only need to sort on one attribute (and for a small dataset). My pseudo-database didn't fit these criteria.
Part One: The Easy Way Out...Or Not
I tried using the Excel Web App first. I really wanted this to work for two reasons. First, it would be awesome to have the first idea work and be able to end the story at this point. The other reason was simply an IT one: in my workplace, it will be a lot simpler to argue to embed a Microsoft product vs. a third-party app. So, I converted my spreadsheet to a table, uploaded it to SkyDrive, and if you know what you're doing, you can filter the results using the app. But this just wasn't going to work. By the time you built in enough directions to navigate the filters, a novice user would probably just give up on the damned thing. Plus, you could only filter one column, so I was no better off than just the simple sorting we already had.
Part Two: Back to the Drawing Board
I went back to Excel to see if I could get a handle on the some of the mechanics (formulas) I would need to build in the filtering. It's simple enough to build some data validation (dropdown) menus that allow a user to choose. But I had to learn how to generate a dynamic list from these choices. It's one thing to use INDEX and MATCH to find one item on a spreadsheet...but how do you use it to find all of the items for a set of criteria? A list that will change with every possible combination of input from the user? Yikes. I did find the answer to this and built a little interactive form. It worked great offline, but I couldn't move it onto the web, because the Excel web app doesn't support data validation. But hey, Google does!
Part Three: A Move to the Dark Side
Let's give Google Spreadsheets a crack at things, eh? I uploaded the workbook..even figured out how to make the array formulas work. The dropdown menus were functional. But then, Defeat was snatched from the jaws of Victory. You see, the dropdown feature only works in the spreadsheet---it doesn't work if you embed it in another webpage. Argh. So, I decided to build a Google Form for the input and connect that with the spreadsheet to generate the dynamic lists of resources. Um, no. Again, it worked great in the live worksheet, but didn't make a bit of difference for the embedded one. Le sigh.
Part Four: Gee, It's Good to Be Back Home Again
I'm starting to get pissed. Twice now, I've gotten down to the very last piece of the puzzle, only to find that it doesn't exist. But I'm also a bit more focused on what needs to happen. Whatever gets developed needs to (a) live on SkyDrive, (b) be intuitive for the user (c) not need data validation or VBA. So, I'm back to square one with Excel, begging my old lover to take me back and teach me some new tricks. I ended up building some IF statements based on whether the user placed an "X" beside a choice and used that for the input. The only final piece was to use the HYPERLINK formula to piece together the title and location of a resource from the spreadsheet with the data into the list for the user, so they could just click-n-go.
Part Five: It's Alive!
After some final formatting, I uploaded my Frankenspreadsheet to SkyDrive, and it worked. I suppressed my urge to run around the office like Colin Clive in Frankenstein and scream "It's Alive! It's Ah-liiii-ive!" I admit that it's a little clunky, but it meets the criteria it has to satisfy.
I've mocked up a sample using different a different dataset for you to see (without hyperlinks, since these refer to documents instead of webpages). If you're viewing this post via RSS, you'll have to click through to the original post to see the embedded spreadsheet.I'll give you the background on building this in the next post. For now, any suggestions for improvement?
To play with the spreadsheet, place an "X" beside one item in the list of Units and an "X" beside one item underneath Category.
No comments:
Post a Comment