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:

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!