How to collaborate (or crowdsource) by combining Delicious and Google Docs

RSS girl by Heather Weaver

RSS girl by HeatherWeaver on Flickr

During some training in open data I was doing recently, I ended up explaining (it’s a long story) how to pull a feed from Delicious into a Google Docs spreadsheet. I promised I would put it down online, so: here it is.

In a Google Docs spreadsheet the formula =importfeed will pull information from an RSS feed and put it into that spreadsheet. Titles, links, datestamps and other parts of the feed will each be separated into their own columns.

When combined with Delicious, this can be a useful way to collect together pages that have been bookmarked by a group of people, or any other feed that you want to analyse.

Here’s how you do it:

1. Decide on your tag, network or user

The spreadsheet will pull data from an RSS feed. Delicious provides so many of these that you are spoilt for choice. Here are the main three:

A tag

Used by various people.

Advantages: quick startup – all you need to do is tell people the tag (make sure this is unique, such as ‘unguessable2012’).

Disadvantages: others can hijack the tag – although this can be cleaned from the resulting data.

A network

Consisting of the group of people who are bookmarking:

Advantages: group cannot be infiltrated.

Disadvantages: setup time – may need to create a new account to build the network around.

A user

Created for this purpose:

Advantages: if users are not confident in using Delicious, this can be a useful workaround.

Disadvantages: longer set up time – you’ll need to create a new account, and work out an easy way for it to automatically capture bookmarks from the group. One way is to pull an RSS feed of any mentions on Twitter and use Twitterfeed to auto-tweet them with a hashtag, and then Packrati.us to auto-bookmark all tweeted links (a similar process is detailed here).

The RSS feed for each will be found at the bottom of pages, and is consistently formatted like so:

Delicious.com/tag/unguessable2012

Delicious.com/network/unguessable2012

Delicious.com/unguessable2012

2. Create your spreadsheet

In Google Docs, create a new spreadsheet and in the first cell type the following formula:

=importfeed(“

…adding your RSS feed after the quotation mark, and then this at the end:

“)

So it looks something like this:

=importfeed(“http://feeds.delicious.com/v2/rss/tag/unguessable2012?count=15”)

Now press enter and after a moment the spreadsheet should populate with data from that feed.

You’ll note, however, that at most you will have only 15 rows of data here. That’s because the RSS feed you’ve copied includes that limitation.

If you look at the RSS feed you’ll see an easy clue on how to change this…

So, try editing it so that the count=15 part of that URL reads count=20 instead. You can put a higher number – but Google Docs will limit results to 20 at a time.

3. Collecting contributions

Technically, you’re now all set up. The bigger challenge is, of course, in getting people to contribute. It helps if they can see the results – so think about publishing your spreadsheet.

You’ll also need to make sure that you check it regularly and copy into a backup spreadsheet so you don’t miss results after that top 20.

But if you find it doesn’t work it may be worth thinking of other ways of doing this – for example, with a Google Form, or using =importfeed with the RSS feed for a search on results for a Twitter hashtag containing links (Twitter’s advanced search allows you to limit results accordingly – and all search results come with an RSS feed link like this one)

Of course there are far more powerful ways of doing this which are worth exploring once you’ve understood the basic possibilities.

Doing more with =importfeed

The =importfeed formula has some other elements that we haven’t used.

Another way to do this, for example, is to paste your RSS feed URL into cell A1 and type the following anywhere else:

=importfeed(A1, “Items Title”, FALSE, 20)

This has 4 parts in the parentheses:

  1. A1 – this points at the URL you just pasted in cell A1, and means that you only have to change what’s in A1 to change the feed being grabbed, rather than having to edit the formula itself
  2. “Items Title” – this is the part of the feed that is being grabbed. If you look in the feed you will see a part that says <item> and within that, an element called <title> – that’s it. You could change this to “Items URL” to get the <URL> part of <title> instead, for example. Or you could just put “Items” and get all 5 parts of each item (title, author, URL, date created, and summary). You can also use “feed” to get information about the feed itself, or “feed URL” or “feed title” or “feed description” to get that single piece of information.
  3. FALSE – this just says whether you want a header row or not. Setting to TRUE will add an extra row saying ‘Title’, for example.
  4. 20 – the number of results you want.

You can see an example spreadsheet with 3 sheets demonstrating different uses of this formula here.

Advertisements

7 thoughts on “How to collaborate (or crowdsource) by combining Delicious and Google Docs

  1. Thomas Gam Nielsen

    Hi there

    Thanks for the tip. It would be great, if Google Docs wouldn’t limit results to 20 at a time.

    Another way of using delicious to collaborate is to combine it with Yahoo Pipes. E.g. Fetch feed from del.icio.us/USERNAME1/TAG and del.icio.us/USERNAME2/TAG and unit it into one rss feed.

    Let’s say that all the staff on a local newspaper were reading different stories on the same subject. They could use the same tag through delicious and the collaborated feed could be shown along with their own stories on the subject.

    Maybe there is a smarter way to do it?

    // Thomas

    Reply
    1. Paul Bradshaw Post author

      I’m pretty sure you should be able to use =importXML to get around the limit but need to make sure – will add an update once I have.

      Reply
  2. Pingback: links for 2011-07-20 | Joanna Geary

  3. Pingback: Staffroom Secrets | Blog | #Tip of the day from Journalism.co.uk – How to add a Delicious feed to a spreadsheet

  4. Pingback: SFTW: How to scrape webpages and ask questions with Google Docs and =importXML | Online Journalism Blog

  5. Pingback: SFTW: How to scrape webpages and ask questions with Google Docs and =importXML | Online Journalism Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s