Plumbing Project: Yahoo! Pipes for Aggregating Comments across a Syndicated Course Blog

So last week I mentioned that after figuring out how to display comment counts on syndicated posts, the next hurdle was to figure out how to display the most recent comments across a whole collection of syndicated blogs/posts.

The dilemma lies in the fact that while there is an RSS feed for each post’s comments, that feed needs to be essentially “exploded” and then all of the exploded feeds need to be aggregated together in order to get a view of comments across all of the syndicated blogs. I wasn’t sure how we were going to do that within WordPress/FeedWordPress. As you can imagine, as the content for ds106 grows, there are more and more posts being aggregated. EACH of those posts has an associated comment feed, and each of those feeds could have any number of comments. Potentially, we could be talking about thousands of comments, in the end. That’s a big feed to both construct and parse.

On Tuesday it occurred to me that maybe I could tackle this in Yahoo! Pipes, so I took a stab at it and I’ve been sort of successful.

Here’s the pipe’s process:

Step One: Fetch the feed out of ds106.us. We’re already off to a tough start. Right now Jim is syndicating the last 600 posts on the blog. That’s so that as students joined the class this spring and subscribed to the feed, they could get a backlog of everything that had come before. Keep in mind that there are almost 900 syndicated posts on the site, and, um, we’re in the third week of class. So, I’m already not starting with everything on my plate. Theoretically, if you go leave a comment on the very first post that was syndicated into the site, I can’t capture any information about it.

But, we’ll forge ahead.

Step Two: I’m going to pull out one element of the feed: the wfw:commentRSS property which I’ve already determined is pretty universal (esp. on WordPress blogs) and contains the URL of the post’s comment feed.

Step Three: Now I’m going to run through a loop that basically fetches the data contained in those comment feeds that I just pulled out of the site feed. For some reason, I had to use this function on Yahoo! Pipes as opposed to the Fetch Feed function again. I don’t know why. I’m not that smart. Whatever it finds in those comment feeds is then emitted into a new feed.

Steps Four & Five: Now I have to do some kind of klugey stuff. I know I need to filter out items that are empty — I have empty items because not every comment feed has contents (because no one has commented on those posts yet). But for some reason, the Filter function in Yahoo! Pipes doesn’t work when I put in a regular expression to try and match, say, the item title to a null value. However, I can use the Regex function to replace a null item title with a string — in this case “XXX.” And then, I can filter out all of the titles that are equal to “XXX.” It’s stupid, but it works.

Step Six: Now I truncate the feed to the last 10 items, because that’s all I want to display on the course site in the sidebar (and I’m trying to limit the amount of parsing WordPress needs to do).

Step Seven: I output the pipe.

If I’m lucky, all the pieces fit together and I get a widget on the ds106 sidebar of the last 10 or so comments.

But sometimes, I just get a feed error. Probably because the whole thing is pretty intensive. So, now I ned to figure out if there is a better way. I’m thinking I need some way to cache this data as it’s compiled. I mean, it’s not going to be changing a lot and there’s no reason to repeat the parsing of every feed every time. But, I admit, I’m not sure what steps to try next.

Ultimately, I’d like to get this to a Yahoo! Pipe that we could clone anytime we’re running a course blog so that we can include recent comments all the time.

As always, ideas, thoughts, and reactions are welcome!!

Displaying Comment Counts on Syndicated WordPress Posts (using FeedWordPress)

So here’s something neat that I figured out how to do on Jim’s DS106 class (soon to be available on my own course site). As you may know, both sites are syndicating in feeds from students’ blogs using the amazing FeedWordPress plugin. We use this plugin all over UMW Blogs to allow faculty to manage course “mother” blogs into which students’ blogs are fed.

When we feed blogs using FWP, we almost always have the post on the mother blog link to the original posts on each individual blog. That’s a decision that reflects our belief that we need to encourage students to develop a sense of agency about their sites — each blog is not merely a cog in the greater course engine. Rather, it is a node that represents the real presence of a person in the class. We want readers of the course blog to make their way to the students’ blog and leave comments in that space — where each individual student can monitor and reply on his/her commenters.

However, one of the drawbacks has always been that on the course blog there is no way to witness the conversation that is developing around the posts that are syndicated into the site. Since the post on the course blog is never actually viewed or commented upon, the site always suggests that there are “No Comments” for the post — when, in actuality, if you click the link and go to the individual student’s blog, there may indeed be rich commentary there.

When DS106 was starting up, Jim mused that it would be very cool to finally crack this nut — how can we have information about the comments on each post trickle up into the course blog?

I decided to try and tackle this challenge this week, and, at first, I thought it was going to be fairly complicated. I know that in WordPress, at least, every post has an individual comment feed associated with it. But I wasn’t sure if the URL structure for those feeds would be easy to program. I’ve got the permalink for the post coming in when the post is syndicated, but depending on the permalink structure on the site, the actual feed URL may vary. In addition, not everyone in the class is using WordPress — how to handle folks in other systems?

I started by simply trying to solve the WordPress post comment challenge and leave the other systems for later. But, sure enough, when I simply tried to append what I thought the rest of the feed URL would be to the permalink URL, the code broke.

At that point, I noticed something interesting. Whenever FeedWordPress syndicates a post, it pushes some data from the RSS feed into custom fields. It seems like with each new version of FWP, more and more data has been getting pushed into these fields. While I was trying to figure out what to do with the post comment feed URL problem, I noticed that many (if not most) of the posts in the system had a custom field associated with them that was labeled “wfw:commentRSS”. Sure enough, the value of that field was the URL of the post comment feed!

(A bit of research turned up that “wfw:commentRSS” comes from the Well Formed Web project, and WP seems to use it in the creation of feeds by default. I admit I’m clueless when it comes to RSS format and namespaces, so if anyone wants to educate me on all of this, please do so.)

The bottom line is that I was able to fairly easily hack some code using a built-in WP function called “fetch_feed.” All this function does is parse a feed and then allow you to spit it back out in parts. I don’t actually have to do any spitting out — I’m just counting the number of items in the feed and building the little notation at the bottom of the post that indicates how many comments there are on the post. But, in this case, the link on that notation goes to the comment space of the original post (this URL, as it turns out, is also passed in a custom field when you pull a WP feed in using FeedWordpress).

Here’s the code I hacked into the Twenty-Ten child theme we’re using for the DS106 blog (FYI, I’m using a heavily modified loop for this site). Basically, you want to replace everything that is typically in the <span class=”comments-link”></span> with the following:

<span class=”comments-link”>
<?php if (is_syndicated()) {  // this just tests to see if the post is a syndicated post ?>
<?php if (get_post_meta($post->ID, 'wfw:commentRSS')) { // this tests to see if the custom field we're looking for actually exists
$comments_url = get_post_meta($post->ID, 'wfw:commentRSS', true); // this passes the posts comments feed URL into a variable
$comments_link = get_post_meta($post->ID, 'rss:comments', true); // this passes the link to the comments section of the post into a variable
$comments_rss = fetch_feed($comments_url); //this parses the feed variable into its parts
if (!is_wp_error( $comments_rss ) ) { // this tests to see if the parsed feed is viable
$maxitems = $comments_rss->get_item_quantity(); //counts the number of items in the feed
if ($maxitems !== 0) { // tests to see if there are more than 0 items
echo '<a href="'; // the next couple of lines spit out the HTML to display the comment count.
echo $comments_link;
echo '">';
echo $maxitems;
echo ' comments. </a>';
} else { // if there are no comments the next few lines are displayed (with a link to leave a comment).
echo '<a href="';
echo $comments_link;
echo '"> Leave a comment</a>';
};
};
}
} else {  // if it's not a syndicated post, just do the regular bit for comments?>
<?php comments_popup_link( __( 'Leave a comment', 'twentyten' ), __( '1 Comment', 'twentyten' ), __( '% Comments', 'twentyten' ) ); ?>
<?php } ?>
</span>

Honestly, we’ve been talking about wanting this bit when we use FeedWordPress for ages in DTLT, and it’s almost embarrassing how easy this was in the end.

There are still some bugs to figure out — the comment count is getting stuck on some posts, and I think that’s a caching issue with the parse function that I need to solve. Also, I’m not sure yet what’s happening with all the non-WordPress sites. Some aren’t working (resulting in nothing being displayed), but I need to track this down.

UPDATE: The incorrect comment count does, indeed, seem to be the cache on fetch_feed. The fix (which I found here) is to add the following to your functions.php file (The “600” represents the number of seconds to cache the feed for. So, this means I’ve set it for 10 minutes. I’m not sure if that’s going to be too much of a load on the server. We’ll see):

add_filter( 'wp_feed_cache_transient_lifetime', create_function('$a', 'return 600;') );

The next nut? How can we show the latest comments across all of the posts. I think this is much tougher proposition.

(Neatness has no place in education.)

My re-reading/re-viewing of Gardner Campbell‘s “Personal Cyberinfrastructure” and “No More Digital Facelifts” is juxtaposed against another activity on my calendar this week: attending a series of vendor demonstrations to determine the University’s new course management sytem.

Yesterday, I attended a 90 minute presentation by one of the most well-known CMS vendors. Here are some random thoughts I had during the presentation and afterwards (while reflecting on the experience):

  • When up against a wall, commercial vendors will co-opt the language of the open Web and citizen-created media with absolutely no concern about whether they actually understand the language. Witness, the “mashup tool.” Quote from the video behind that link: “Mashups provide a simple way to add multimedia to course without having to create it yourself.” Here’s another definition.
  • When a product has been around long enough and patterns of use have been (corporately) defined enough times, a huge chasm can grow between the perhaps initial, intended (advertised) purpose of the product and what the product actually does.
  • The “partnering” of a course management system vendor with “the global leader in interactive markting services” in order to deliver “AFFORDABLE STUDENT IDENTITY VERIFICATION” may signify the greatest, most-frightening ethical leap that higher education can take. To suggest that “the global leader in interactive marketing services” is providing this service because they simply want to do something for higher education (out of the goodness of their corporate heart) is naive, at best, and equally, frighteningly unethical, at worst. Every school that is capitalizing on this partnership should take a long, hard look at what exactly they are paying for — and what they, ultimately, are perhaps selling.
  • Every time you use the phrase “delivering content” to describe the art of teaching, a small, exceedingly cute marsupial dies.
  • Faculty who attend these presentations can’t be blamed when their questions reflect a greater concern with the “efficiencies” provided (or promised but not delivered) rather than the way in which technology can/should/will alter the practices of teaching, learning, knowing, and thinking.They have been sold a bill of goods when it comes to how we consider technology within the ecosystem of higher education. For a decade and a half, we have let companies tell us what technology can do for us rather than demanding that our larger communities engage the deeper, messier, far more profound questions about how technology can/should/will alter the practices of teaching, learning, knowing, and thinking.
  • Once you have begun to grapple with these messy and essential questions, sitting through presentations aimed at describing content delivery and administrative efficiences sucks a little more of your soul out of your being.
  • Once again, louder please: Our job is not to manage students. It is to teach them. Our job is not to manage learning. It is to build communities and spaces in which learning happens.
  • I am not an idiot. I GET that to run a University you have to figure out some way to manage the administration of a University’s practices. And I GET that technology is going to play a role in this. But can we PLEASE not pretend/suggest/pronounce that the these needs come before an investigation and engagement with the deeper, messier, far more profound questions about how technology can/should/will alter the practices of teaching, learning, knowing, and thinking.
  • The technologies you choose to frame the experience of teaching, learning, knowing, and thinking MATTER. The actual code behind these vendor solutions is forged in a corporate mindset founded on finding efficiences, delivering content, and defining patterns of practice that are, fundamentally, limiting. That code breathes itself into every corner of the system. We have to stop fooling ourselves into believing that within these coded spaces we can build something that is other than the fundamental nature of the code.
  • I am drawn to open-source technologies for my practice because I believe that the code inside these systems is forged in finding alternatives, creating experiences, and defining new patterns of practice that, recursively, turn the code into something new. I believe these values are breathed into the corners of the systems I use, too. My spaces are coded as well, but my spaces are fundamentally flexible, communal, and, yes, sometimes they are messy.
  • Ultimately, then, our conversations about technologies must grapple with our larger community’s values — and what code we think enacts these values.
  • (Neatness has no place in education.)

Meanwhile, I’m reading/viewing Gardner, and thinking about the notion of a cyberinfrastructure — and I’m asking my own students in Digital Storytelling to attempt to build this for themselves. And I realize that my desire for them to grapple with these spaces is directly related to my reflections to CMS vendors. I want them to understand the meaning of these coded spaces. I want them to realize that there are corporate, political, and social forces that inhabit this code. I want them to understand that they are not Google’s customers — they are its products. I want them to recognize that all digital systems are composed of patterns that someone has identified and codified. I want them to question those patterns and consider their own patterns — and then I want them to attempt to codify those, too. Perhaps not forever, but at least for a semester. I would like to think that they will walk away from this course with a deeper understanding of the digital spaces they inhabit, but, more importantly, I would like them to walk away with a messy set of questions that will plague them as they continue to grapple with these spaces.

The View

Took another stab at an animated gif tonight. This one is from my favorite movie, and it’s of the moment before, what I think, is one of the most romantic few seconds in a movie. Just watching it reminds me of the first time I saw this movie and how powerful I found this scene. (The music in this scene is, I think, critical. It’s kind of weird to watch it in silence.) This movie introduced me to E.M. Forester when I was a teenager. I plowed through a collection of his novels right after I watched it.

In fact, I haven’t watched it in years, and now I think I’ll schedule a viewing for myself tomorrow night.

1 Second of "Room with a View"

This time I actually used Handbrake to capture the clip from my DVD and then used Photoshop to import the video into frames. Again, I couldn’t believe how easy that was. I didn’t drop frames on the import, so it’s pretty smooth. And I’m pretty happy with it.

The Plan for Day One

Tomorrow evening is my first class meeting for my own section of Digital Storytelling. I’ve been anticipating this for months, and now that it’s here, I’m really excited. I thought I’d take a moment to map out my plan for the first day — as much for me to think through the agenda as anything else.

My class is only meeting once a week, from 6-9 on Thursday evenings. It was the best choice for my schedule, but I’m realizing that planning a once-a-week class presents some interesting challenges. And, of course, I’m a bit worried about being able to sustain the energy for three hours. My general approach, I think, will be to try and structure the class into clear segments so that students feel like there is some change to the pace of each class — a rhythm, so to speak, that will help us maintain some momentum for the entire duration.

As the semester progress, the natural rhythm will probably be to spend an hour or so first on readings & reflecting on the previous week’s work and then the second half of the class workshopping the next assignment. But for this first week, there’s quite a bit of housekeeping to do, and I’m not sure we’ll be ready to jump into to much hands-on work.

Here’s the basic plan

  • Intros/30 second stories. I sat in on Jim’s class on Tuesday and this was his kickoff activity. It was a great ice-breaker. Jim’s a natural at keeping the tone light and making the students feel comfortable. I’m hoping I can achieve the same atmosphere.
  • Syllabus Review. This is my opportunity to convey the way in which this class may challenge my students’ expectations a bit. I’ll be introducing the notion of a domain of their own as a way to frame a digital identity in which they’ll be investigating the ideas of the class and sharing their work. I’ll be going over the course objectives, but I’ll also be talking about my expectations for their own activity/participation in the class: public sharing of their work and ideas; meaningful online engagement with each others’ works; tackling assignments with as much creativity as they can muster; and sharing the challenges they face when assignments don’t go as they had planned. I want them to understand that I don’t expect perfection but I do expect to see commitment.
  • Introduction to the DigSto Collective. A portion of my students’ grade will be determined by their contributions to a collection of resources about digital storytelling. They’ll be asked to use Delicious to tag examples, tools, techniques, tutorials, readings, etc. I’ll be using the WP Post Ratings plugin to let them vote on each other’s contributions, and I’m hoping to use stuff that emerges in the collective each week to start off class. This is a pretty big experiment. In my experiences, group/organized bookmarking never really works. Of course, I have the advantage of being able to require them to do it, but it’ll be interesting to see what emerges.
  • Introduction to the Digital Storytelling Toolkit. This is a resource that I’ve just started to build and that I’m going to be working on throughout the semester (I’ll invite students to contribute to it, as well). It’s basically just a laundry list of some sites/software that students will likely need to investigate and/or use as part of the course. It’ll be a few weeks before we jump into most of these, but I want them to understand a bit about what’s coming.
  • Assignments for the Next Week. I’ll take about 10 minutes or so to go over next week’s assignments which include Gardner Campbell’s “A Personal Cyber Infrastructure” and “No Digital Facelifts.” (Gardner’s going to be joining a combined section of Jim’s and my classes next Thursday night for a discussion of these — how awesome is that?) In addition, they’re going to have to purchase their domain name and Web hosting account so that next week we can start setting up their sites.
  • What Exactly is Digital Storytelling? I’m planning for the last portion of the class to be this conversation. I’m probably just going to start with the Wikipedia definition and ask them to consider if they think it does a good job of defining what they think digital storytelling is.

    I guess I’m hoping to do two things. I’d like to get a better sense of their expectations for the class. After all, they signed up for a class called digital storytelling. What exactly did they think that was going to be about?  What kinds of stories did they think they’d be creating/examining? Have they ever created a digital story? What made it a digital story?

    But I’d also like to get them to push a bit against the Wikipedia definition. It’s pretty narrowly focused on the idea of auto-biographic narratives told through the use of personal writing, photographs, and video. I’ve seen some excellent examples of these kinds of stories (and we’ll be looking at some of them over the semester), but I want to push them to consider other examples. I’m assembling a few examples that I’ll be using for this part of the discussion.

    I think I’ll probably end by talking about the VW Gingerbread Bus project my husband and I did — I do want to delve further into this idea of how the mere presence of an audience can provoke story.

I was hoping there might be sometime to actually do some hands-on work, but I think that’s too much to expect for the first class. And I think I already have a pretty full plate.

Feedback is always welcome!

(In other news, I think teaching this course is finally making a real blogger out of me!)

The Imperative of Audience

People who follow me elsewhere on the Web know that a few months ago, our family acquired a new member. We call her Moby (Which doesn’t make any sense because, obviously, she’s a girl. But it fits.)

Erik grew up rebuilding VW buses, and he’s longed for one since he had to sell his last one before moving across the country for graduate school. This summer we decided to take the plunge, and we ended up with Moby.

When Erik’s birthday came around, it was inevitable that everyone would buy him VW (bus) related stuff. As I was searching for presents, I came across this book on Amazon.

When I saw one recipe in particular, I knew I had to buy it:

That would be a recipe to build a Volkswaben Gingerbread Bus. The book basically contains a recipe for gingerbread. But on the cookbook site, you can download gingerbread bus plans.

When the holidays came around, Erik and I had to build it. We ended up making two buses, because we knew Madigan would want to decorate one, and we wanted one to decorate ourselves. For Madigan’s we followed the plans and made an early-model “Splitty” VW camper bus. For our bus, we modified the plans to make a “Westy” bus like Moby.

It took about two nights to cut out and bake our buses. Putting them together took another two nights. Instead of using traditional icing to assemble them, we used the cookbook’s advice and melted sugar. It’s a much nicer “glue” — if you keep the temperature consistent, the color matches the gingerbread almost exactly. It cools very quickly though, so you have to work quickly. In the end, both of the buses went together pretty well considering we had no idea what we were really doing.

But the night that we finished the second one, Erik was handling it and broke the entire front end. Rather than cursing the gingerbread gods, we realized we now had to stage a gingerbread bus crash.

When we finally found the time to tackle the project, we spent about two hours putting it all together. With the help of our Playmobil collection, I think it came out pretty well. (The first few shots are of the final, fixed, decorated buses — note the apricot fruit-roll-up for the canvas top. Brilliant.)

(The best part of this, IMHO, is the inane Playmobil characters who can’t stop smiling at the carnage. The cow, BTW, caused the entire accident. Damn livestock.)

My husband shot the photos, and ended up sharing them on Facebook. As we were finishing up the photo shoot, I asked him if he would have bothered doing any of this if he didn’t have a place like Facebook to share it in. His reply was, “Of course not.”

I had been thinking the same thing, and it struck me how deeply this idea of “an imperative of audience” influences the kinds of activities I’m hoping to foster in Digital Storytelling. One of the cultural movements I want us to consider is the way in which digital tools and the open Web are not only making it possible to create and share our stories but how they are creating a virtual stage upon which we are compelled to perform.

Creating a (Rube Goldberg) Digital Storytelling Assignment Repository

CC: Some rights reserved by medea_material on Flickr

Jim’s already done a pretty good job of summarizing the approach I took to building a tool for his online DS106 class which allows visitors to submit assignments. But I figured I’d go ahead and detail the process a bit more fully. To be honest, I’m doing this as much for me as for anyone else. I seriously doubt that it’s going to be that useful to many people, but there’s a really good chance that I’ll forget the circuitous steps that I had to take to make this work.

To summarize, the goal was to create a form on the course site that would allow people to submit assignments for the course (this need developed, in part, as the blog- and Twitter-based conversation before the course even started revealed all the great assignment ideas that people were coming up with). Jim had six general categories that he wanted people to submit digital story assignment ideas for: visual, design, audio, video, mashup, fanfic. Once the ideas were submitted, we wanted a way to then display them on the course site so that anyone could choose one to work on.

It’s a pretty simple idea, and there a probably a lot of ways to achieve it. One way I didn’t explore was to create a custom post type that would allow registered users of the blogs to create a post. I’m sure it’s possible, but I wasn’t really up for figuring out the solution to opening up a post editor on the front-end for non-standard roles.

Instead, I decided to hack a solution using a Google Spreadsheet. Here’s the basic process:

  1. Create a Google Spreadsheet/Form based on the info we wanted to collect.
  2. Hack the form to embed it directly into a custom WP page template.
  3. On the spreadsheet, do some additional manipulations to the incoming data.
  4. Utilize the Spreadsheet feed (available once it’s made public) to suck the data back into posts on the course blog using FeedWordPress.
  5. Develop another custom WP template to display the syndicated posts.

I have to admit that it’s a pretty Rube Goldberg solution. I also have to admit that my favorite solutions these days are Goldberg-esque. I’m not sure why. I realize that taking this approach introduces more points of failure. I recognize that it’s all held together with the digital equivalent of shoelaces, paperclips, and duct tape. But, bottom line, I find incredible satisfaction in finding a way to make these different pieces work together like some kind of machine I’d build in the garage. To me, this approach is incredibly elegant. That said, I recognize that it’s actually the exact opposite of elegant. If someone can help me understand why my definition of elegance seems to be in direct opposition to the real definition of elegance, that’d be awesome.

So, I’m going to give a more detailed breakdown of exactly how I did this. But, please be forewarned, I’m not going to document every tiny step. It’s just too complex, and, frankly, too specific to my particular project. If you care to attempt something similar, you should be able to come away from this with an 80-85% understanding of what you’ll need to do. The rest is up to you. 🙂

1. Create a Google Spreadsheet/Form based on the info we wanted to collect.

This is the easiest step, but it’s still really important. All you need to do is set up a basic Google Form. But be aware that the rest of your project is going to depend on this form. It’s in your best interest to get this form exactly how you want it before proceeding. Otherwise, you’ll find the following steps harder.

2. Hack the form to embed it directly into a custom WP page template.

I can’t take any credit for figuring this next bit out. I learned all of it at Morning Copy’s Web site. <– That link will take you to a series of tutorials written by MC that explain how to truly embed a Google form on your site. Please note, you don’t have to do this. Google Forms come with standard embed code that you can use, but this code uses an iFrame, so there’s no way for you to control the styling of the form on your site. It’s going to look the way Google makes it look. I’m a control freak, so that’s just not cool for me.

The great thing about those Morning Copy tutorials is that they keep updating them to keep up with new features. So you’ll find code to redirect to your own confirmation page as well as to use jQuery to do some basic form validation.

I used the tutorials to embed the code in WordPress template that I then applied to this page.

3. On the spreadsheet, do some additional manipulations to the incoming data.

The form we embeded in step two will dump data into a Google spreadsheet just like a regular Google form. If I was happy with using just the data I’d collected on the course site, I could skip this next step.

However, for our purposes, I wanted to create a custom category associated with the assignments. I wanted this for two reasons: First I wanted to be able to sort/order the incoming posts based on the assignment category people chose when they filled out the form. Second, I wanted to be able to provide a tag that people could use when completing the assignment. This would make it possible to easily aggregate the submissions and display them with the assignment. I thought that would be cool.

In order to do any manipulations on Google Form data, you have to copy it. While you can put formulas directly into the worksheet where the data is dumped, each time the form is submitted, those forumlas get overwritten.

The trick is to create a second worksheet and then use cell references to copy the data into the new sheet. Then, you can add formulas into the columns on the right to manipulate the data. In our case, I created three formulas:

  • The first one just assigned a unique ID number to the assignment. I did this by putting the following formula in the first cell of the
    column:

    =ArrayFormula( IF( ROW(‘From Form’!A:A)=1; “ID” ;
    IF(LEN(‘From Form’!A:A);ROW(‘From Form’!A:A)-1;IFERROR(1/0))))

  • The second formula takes the category that was assigned to the entry and concatenates it with the word “Assignment.” The result is something like “DesignAssignment.” I then wanted to wrap that in some special HTML (more on this later) Here’s that formula:=ArrayFormula( IF( ROW(‘From Form’!A:A)=1 ; “Category Two”;
    IF(LEN(‘From Form’!A:A),”<a rel=’tag’>”&’From Form’!D:D&
    “Assignments”&”</a>”; IFERROR(1/0))))
  • The third formula concatenate the results of the first formula with those of the second formula, so I get something like “DesignAssignment1.” Again, I wanted this wrapped in some special HTML, too. Here’s the third formula:=ArrayFormula( IF( ROW(‘From Form’!A:A)=1 ; “Category Two”;
    IF(LEN(‘From Form’!A:A),”<a rel=’tag’>”&’From Form’!D:D&”
    Assignments”&”</a>”; IFERROR(1/0))))

Okay, now a word about these formulas. I didn’t write them. I did write some formulas (that were a lot simpler) and they basically worked except for a weird bug that I encountered. When I generated the copied data on the second sheet through cell references, I found that new entries weren’t iterating properly. Basically, cell references were incrementing upwards so that they are out of sync with the original spreadsheet.

My next step was to search the Google Spreadsheet help forum, and sure enough I found out a bunch of people were having similar problems (although the problem seems to be inconsistent). So, I went ahead and asked for some help. When I put up the post, I followed the example of others on the forum and made my spreadsheet public and editable. (Not really an issue since there was no sensitive data and I could always revert.)

Within about 20 minutes, a user named ahab had edited my spreadsheet with the proper formulas. That was basically amazing. I’m pretty sure I could have NEVER solved this problem, but I had an answer in under 30 minutes. How’s that for the power of openly sharing (and asking for help)?

So at this point, I had my original data as well as my newly generated fields.

4. Utilize the Spreadsheet feed to suck the data back into the course blog using FeedWordPress.

You may not know this, but once you make a Google Spreadsheet public, you can get an RSS or Atom feed for updates. In previous projects, I had played with this so I knew that it was possible to syndicate this feed into a WordPress blog using FeedWordpress. Each item can then become a new post on your blog.

If you click on the Share button on a Spreadsheet and choose “Publish as a Web Page” you’ll find the interface to retrieve all kind of versions of your data, including an RSS or Atom feed. However, there’s a trick. (Surprise, surprise!)

The default RSS feed doesn’t give you the data in the format you need. But, the Google Docs API has tons of information about working with feeds and Google spreadsheets. There are actually lots of variations on the standard feed that you can use to get your data in different formats.

The final feed URL I needed to use was this:

https://spreadsheets.google.com/feeds/list/0Apx3w9ad7t61dE5mNnZ2bjdySzJHLTRQMUpFQV9vbHc/od7/public/values/

I also figured out something new that was incredibly cool. The latest version of FeedWordpress has the capability to recognize a microformat called “inline tags.” (scroll down to the “tag” section on that last link for a description of what these are.) Basically, this means that if a word/phrase is wrapped in the following HTML, it can be automatically converted to a tag or category for the new post:

<a rel=”tag”>tag</a>

THIS is the “special HTML” that I mentioned adding to the formulas above in step 3. By simply adding text to the formula that wrapped the new categories I had created in these tags, I could have FeedWordpress turn them in to actual categories on the blog — which means we can use them to filter posts, create category clouds, etc.

But wait, there’s more! 😀

So, at this point, if I stopped, I could create posts that included the title of the assignment (this would go into the post title) and the categories (which would be added as WP categories.) Obviously, this isn’t enough. I need to import the description, author, and example URL. Each of these exists in the spreadsheet, but how was I going to associate them with the incoming post?

As it turns out, FeedWordpress has another feature that allows you to assign custom fields to a post as it is syndicated. FWP can actually parse a feed and pull out additional data that lives in it. See, the feed that I’m grabbing out of Google Spreadsheets contains all of that data, I just have to tell FeedWordPress how to find it and what to do with it.

A picture will probably be easier than describing how you do this in FWP:

Click for Larger Version

On the left-hand side, I give the custom fields a “key” (or name), in the middle field, I put in some code that tells FWP where to find the data. In my case, my data isn’t part of the standard RSS namespace, instead it lives in some custom attributes defined by Google. I honestly don’t remember how I figured out how to generate that code that tells FWP how to parse the data in the Google portion of the feed. I know I found some resources, but I can’t find them now. If I do find them, I’ll update with a link.

In the meantime, basically this is the code I used:

$({http://schemas.google.com/spreadsheets/2006/extended}_cokwr)

The URL points to google’s definition of it’s RSS namespace/attributes (forgive me if I’m using the wrong terms). The “cokwr” is just the coded “name”  in the feed that corresponds to that data field.

Note that in the image above you see me setting up a few custom fields to grab data out of the feed (title, type) which I didn’t ultimately use or need.

5. Develop another custom WP template to display the syndicated data.

The final step was to create a custom template for the different assignment categories.

I’m able to have a single template work for each category. Here’s how:

  1. I create a page for each category (“Visual”,”Design”, “Audio”, etc.).
  2. When I create that page, I assign my template to it.
  3. I also create a custom field for each page called “Assignment Category.” In the value for this custom field, I enter the name of the category that is going to show up on that page (“Visual”,”Design”, “Audio”, etc.)
  4. In the template (the one I assigned in step 2) , I run a custom query based on the value of that custom field.

This is a pretty simply way to code a single template to filter posts based on the value of a custom field. Using it, I’m able to easily create six pages, one for each assignment category.

In terms of the layout, Jim and I settled on a design shows each assignment in a kind of “card” format. I’m using the double loop technique described here to separate the posts into two columns.

Within each card, I’m showing (and styling) the title, author, an image/screenshot of the example that was submitted, the description, the tags that someone should use if they complete the assignment, and a rating system (using the plugin WP Post Ratings).

Most of this is pretty straightfoward — I use WP’s built-in template tag to display the title. Then I use the get_post_meta function to spit out the author, description, and an image/screenshot associated with the example URL (more below). Finally, I use get_the_category to run a loop through the categories associated with the assignment (and these are the tags we recommend people use when submitting an assignment).

For the example URL, I’m doing some sniffing to see if the URL is an image. If it is, I display it. If it isn’t, I’m using the free version of ThumbnailsPro.com to generate a screenshot of the Web site (this is limited to 1000 shots a month).

Throw in some styling, and what we end up with is something like this. (Just realized when I went to that link that I’m not doing a check to see if there actually is a URL in the example field, that’s why some screenshots are broken. Guess I have more work to do).

Summary

Okay, that’s basically it. Here’s where I ask you to tell me if this was worth it (if you managed to get this far). Also, tell me all the ways I could have done this that would have been easier.

I know this is convoluted, but as I’ve said before part of why I love these projects is because I learn so much when I’m doing them. As a result of doing this project, I learned more about WordPress, Google Spreadsheets, RSS, FeedWordPress, php, and css.

And I’m really glad I wrote this all down. I can’t believe how much I had forgotten since starting this in mid-December!