Keene is crazy about baseball and softball. In the men’s softball league alone there are 56 teams, they have played 73 games so far, and will play another 431 through August. I know this because the schedule was made in Excel, and published as a web page that Excel’s Data->From Web feature can easily read back.

That Excel spreadsheet isn’t at all useful, however, if you want to combine the schedule with other public calendars, or with your own personal calendar. For that you need an ICS feed. And almost nobody — from the major league websites to local leagues like mine — bothers to provide those.

So I made an ICS feed for Keene men’s softball, and I did it in an unusual way. My first thought was to point FuseCal at the schedule page, which is just an HTML table that looks like this:

DATE TIME FIELD AWAY HOME Lg
Fri. Apr 17 6:00 PM D Computer Solutions of
Keene		 J.A. Jubb C1
Fri. Apr 17 6:00 PM O Peerless Insurance C&S 1 D2

But FuseCal wouldn’t read that page. It’s a service that specializes in digging structure out of unstructured text, and I guess it got freaked out when it saw too much structure in this page!

Normally in cases like this I’d write a script to read the HTML table, parse out the dates and times, and write an ICS feed. But that isn’t a skill most people have, and I’m looking for ways to help calendar curators do this kind of thing for themselves.

Then it occurred to me: What would FuseCal read? How about this:

Fri. Apr 17 06:00 PM,
Computer Solutions of Keene vs. J.A. Jubb, Field D
Fri. Apr 17 06:00 PM,
Peerless Insurance vs. C&S 1, Field O

In other words, the same stuff lightly reformatted, and coalesced into a single cell per row. And yes, FuseCal will read that.

So I added a column to the Excel sheet with this formula:

=CONCATENATE(A4, " ", TEXT(B4,"hh:mm AM/PM"), ", " D4, " vs. ", F4,
 ", ", "Field", C4)

Then I exported that column back out as this HTML page, used FuseCal to create this ICS feed, and bookmarked it for inclusion in the aggregator.

This has to be the weirdest maneuver I’ve ever thought of. Taking away structure in order to be able to add structure? Crazy! And yet it makes perfect sense. FuseCal is a component that specializes in turning weakly-structured calendar-like data into better-structured calendar data. It also knows how to do other useful things, like monitor the source of that data for changes, and convert the data into ICS format. If it’s easy enough to provide the sort of weak structure that FuseCal expects, why not just do that and leverage its strengths?

So I did, and here are the key outcomes:

  1. The softball events now show up on the aggregated calendar.
  2. They’re also available directly from the ICS feed, so that players and their families can add these events to personal calendars.

Nice!

It would be even nicer if, as a member of, say, the Blazers, I could scoop up just my own team’s events. And in fact FuseCal does support filtering. As the creator of the feed, I can go into the application, type Blazers, and restrict the feed to just those events. But I’d have to create 56 separate filtered calendars to provide feeds for all the teams. Feature request for FuseCal: Support filtering on the feed URL, so I can form URLs like:

http://fusecal.com/calendar/view/ 741833?h=5f7c2ac6-13cc-11de-a48e-00163e284ee0&filter=Blazers

http://fusecal.com/calendar/view/ 741833?h=5f7c2ac6-13cc-11de-a48e-00163e284ee0&filter=Greenwald+Realty

While we’re wishing, here’s a feature request for Yahoo Pipes: Add a module for ICS feeds! Pipes is a fabulous tool for transforming, filtering, and merging RSS feeds. It would be great to be able to do the same kinds of magic with ICS feeds.

Advertisement