Looking Up Alternative Copies of a Book on Amazon, via ThingISBN

As Amazon improves access to the long tail of books through Amazon’s marketplace sellers and maybe even their ownership of Abebooks, it’s increasingly easy to find multiple editions of the same book. So when I followed a link to a book that Mike Ellis recommended last week (to The Victorian Internet in fact) and found that none of the editions of the book were in stock, as new, on Amazon, I had the tangential thought that it’d be quite handy to have a service that would take an ISBN and then look up the prices for all the various editions of that book on Amazon.

Given an ISBN for a book, there are at least a couple of ways of finding the ISBNs for other editions of the book – the Worldcat xISBN service, and ThingISBN from LibraryThing (now part owned by Amazon through Amazon’s ownership of Abebooks; for who else Amazon owns, see Amazon “Edge Services” – Digital Manufacturing).

So here’s a couple of Yahoo pipes for looking up the alternative editions of a book on the Amazon website, after discovering those editions from ThingISBN.

First of all a pipe that takes an ISBN and looks up alternative editions using ThingISBN:

What this pipe does is construct a URL that calls for the list of alternative ISBNs for a given ISBN. That is, it constructs a URL of the form http://www.librarything.com/api/thingISBN/ISBNHERE, which returns an XML file containing the alternative ISBNs (example), grabs the XML file back using the Fetch Data block, renames the internal representation of the grabbed XML so that the pipe will generate a valid RSS feed, and output the result.

So now we have an RSS feed that contains a list of alternative ISBNs, via ThingISBN, for a given ISBN.

Now to find out how much these books cost on Amazon. For that, we shall find it convenient to construct a pipe that will look up the details of a book on Amazon using the Amazon Associates web service, given an ISBN. (For a brief intro to Amazon Associates web services, see Calling Amazon Associates/Ecommerce Web Services from a Google Spreadsheet.)

Here’s a pipe to do that:

(If you use the AWSzone scratchpad to construct a URL that calls the Amazon web service with a look up for book by ISBN, you can just paste it into the “Base” entry form in the Pipe’s URL Builder block and hit return, and it will explode the arguments into the appropriate slots for you.)

So now we have a pipe that will look up the details of a book on Amazon given its ISBN.

We can now put the ThingISBN pipe and the Amazon ISBN lookup pipe together, to create a compound pipe that will lookup details for all the alternative versions of a particular book, given that particular book’s ISBN:

Okay – so now we have a pipe that takes an ISBN, looks up the alternative ISBNs using ThingISBN, then grabs details for each of those alternatives from Amazon…

Now what? Well, if you use this pipe in your own mashup, you may find that if you construct a URL that calls a pipe with a given ISBN, if you don’t handle the ISBN properly in your own code, you can pass a badly formed ISBN to the pipe. The most common example of this is dropping a leading 0 on the ISBN – so e.g. you pass 441172717 rather than 0441172717.

Now it just so happens that LibraryThing offers another webservice that can correct this sort of error – ISBN check API – and it’s easy enough to create a pipe to call it:

Good – so now we can defensively programme the front end of our pipe to handle badly formed ISBNs by sticking this pipe at the front of the compound pipe that calls ThingISBN and then loops through Amazon calls.

But there’s something we can do at the other end of the pipe too, and that is make use of a ‘slideshow’ feature that Yahoo pipes offers as an interface to the pipe. If the elements of a feed contain image items that are packaged in an appropriate way, the Yahoo pipes interface will automatically create a slidesho of those images.

What this means is that if we package URLs that point to the book cover image of each alternative version of a book, we can get a slideshow of the bookcovers of all the alternative editions of that book.

Here’s just such a pipe:

And here’s the example output:

If you click on the “Get as Badge” option, you can then embed this slideshow on your own website or start page:

For example, here I’ve added the slideshow to my iGoogle page:

Now to my mind, that’s quite a fun (and practical) way of introducing quite a few ideas about webservice orchestration that can be unpacked at a later date. But of course, it’s not very academic, so it’s unlikely to appear in a course near you anytime soon… ;-) But I’d argue that it does stand up as a demo that could be given to show people how much fun this stuff can be to play with, before we inflict SOAP and WS-* on them…

Rock the Academy

Now there’s nothing wrong with conference posters, but this is more LIKE IT

[via the CogDog himself: Rock the Academy Video]

And can you imagine showing this to your head of research, I mean, your HEAD OF RESEARCH, and saying “I wanna go to this… I REALLY wanna go to this…”. They’ll probably look at you and say:

“Kid, we don’t like your kind, and we’re gonna send your fingerprints off to Washington.”

And friends, somewhere in Washington enshrined in some little folder, is a study in black and white of my fingerprints. And the only reason I’m singing you this song now is cause you may know somebody in a similar situation, or you may be in a similar situation, and if your in a situation like that there’s only one thing you can do and that’s walk into the shrink wherever you are, just walk in say “Shrink, You can get anything you want, at Alice’s restaurant.”. And walk out. You know, if one person, just one person does it they may think he’s really sick and they won’t take him. And if two people, two people do it, in harmony, they may think they’re both faggots and they won’t take either of them.

And three people do it, three, can you imagine, three people walking in singin a bar of Alice’s Restaurant and walking out. They may think it’s an organization. And can you, can you imagine fifty people a day, I said fifty people a day walking in singin a bar of Alice’s Restaurant and walking out. And friends they may thinks it’s a movement.

And I think that’s what we’ve got here – a movement… You can get anything you want, at Alice’s restaurant..

PS so OU folks, when we gonna put together movies like this to advertise each and every course on the courses and quals site?! DMPB, would that fall under your remit? Or would Ian be fighting you for it?;-) heh heh

Printing Out Online Course Materials With Embedded Movie Links

Although an increasing number of OU courses include the delivery of online course materials, written for online delivery as linked HTML pages, rather than just as print documents viewable online, we know (anecdotally at least, from requests that printing options be made available to print off whole sections of a course with a single click) that many students want to be able to print off the materials… (I’m not sure we know why they want to print off the materials, though?)

Reading through a couple of posts that linked to my post on Video Print (Finding problems for QR tags to solve and Quite Resourceful?) I started to ponder a little bit more about a demonstrable use case that we could try out in a real OU course context over a short period of time, prompted by the following couple of comments. Firstly:

So, QR codes – what are they good for? There’s clearly some interest – I mentioned what I was doing on Twitter and got quite a bit of interest. But it’s still rare to come across QR codes in the wild. I see them occasionally on blogs/web-pages but I just don’t much see the point of that (except to allow people like me to experiment). I see QR codes as an interim technology, but a potentially useful one, which bridges the gap between paper-based and digital information. So long as paper documents are an important aspect of our lives (no sign of that paper-less office yet) then this would seem to be potentially useful.
[Paul Walk: Quite Resourceful?]

And secondly:

There’s a great idea in this blog post, Video Print:

By placing something like a QR code in the margin text at the point you want the reader to watch the video, you can provide an easy way of grabbing the video URL, and let the reader use a device that’s likely to be at hand to view the video with…

I would use this a lot myself – my laptop usually lives on my desk, but that’s not where I tend to read print media, so in the past I’ve ripped URLs out of articles or taken a photo on my phone to remind myself to look at them later, but I never get around to it. But since I always have my phone with me I’d happily snap a QR code (the Nokia barcode software is usually hidden a few menus down, but it’s worth digging out because it works incredibly well and makes a cool noise when it snaps onto a tag) and use the home wifi connection to view a video or an extended text online.

As a ‘call to action’ a QR tag may work better than a printed URL because it saves typing in a URL on a mobile keyboard.
[Mia Rdige: Finding problems for QR tags to solve]

And the hopefully practical idea I came up with was this: in the print option of our online courses that embed audio and/or video, design a stylesheet for the print version of the page that will add a QR code that encodes a link to the audio or video asset in the margin of the print out or alongside a holding image for each media asset. In the resources area of the course, provide an explanation of QR codes, maybe with a short video showing how they are used, and links (where possible) to QR reader tools for the most popular mobile devices.

So for example, here is a partial screenshot of material taken from T184 Robotics and the Meaning of Life (the printout looks similar):

And here’s what a trivial change to the stylesheet might produce:

The QR code was generated using the Kaywa QR-code generator – just add a URL as a variable to the generator service URL, and a QR code image appears :-)

Here’s what the image embed code looks like (the link is to the T184 page on the courses and qualifications website – in practice, it would be to the video itself):

<img src=”http://qrcode.kaywa.com/img.php?s=6&d=http%3A%2F%2Fwww3.open.ac.uk%2Fcourses%2Fbin%2Fp12.dll%3FC01t184&#8243; alt=”qrcode” />

Now anyone familiar with OU production processes will know that many of our courses still takes years – that’s right, years – to put together, which makes ‘rapid testing’ rather difficult at times ;-)

But just making a tiny tweak to the stylesheet of the print option in an online course is low risk, and not going to jeopardise quality of course (or a student’s experience of it). But it might add utility to the print out for some students, and it’s a trivial way of starting to explore how we might “mobilise” our materials for mixed online and offline use. And any feedback we get is surely useful for going forwards?

Bung the Common Craft folk a few hundred quid for a “QR codes in Plain English” video and we’re done?

Just to pre-empt the most obvious OU internal “can’t do that because” comment – I know that not everyone prints out the course materials, and I know that not everyone has a mobile phone, and I know that of those that do, not everyone will have a phone that can cope with reading QR codes or playing back movies, and that’s exactly the point

I’m not trying to be equitable in the sense of giving everyone exactly the same experience of exactly the same stuff. Because I’m trying to find ways of providing access to the course materials in a way that’s appropriate to the different ways that students might want to consume them.

As to how we’d know whether anyone was actually using the QR codes – one way might be to add a campaign tracking code onto each QR coded URL, so that at least we’d be able to tell which of the assets were were hosting were being hit from the QR code.

So now here’s a question for OU internal readers. Which “innovation pipeline” should I use to turn the QR code for video assets idea from just an idea into an OU innovation? The CETLs? KMi? IET (maybe their CALRG?) The new Innovation office? LTS Strategic? The Mobile Learning interest group thingy? The Moodle/VLE team? Or shall I just take the normal route of an individual course team member persuading a developer to do it as a favour on a course I’m currently involved with (a non-scalable result in terms of taking the innovation OU -wide, because the unofficial route is an NIH route…!)

And as a supplementary question, how much time should I spend writing up the formal project proposal (CETLs) or business case (LTS Strategic, Innovation Office(?)) etc, and then arguing it through various committees, bearing in mind I’ve spent maybe an hour writing this blog post and the previous one (and also that there’s no more to write – the proof now is in the testing ;-), and it’d take a developer maybe 2 hours to make the stylesheet change and test it?

I just wonder what would happen if any likely candidates for the currently advertised post of e-Learning Developer, in LTS (Learning and Teaching Solutions) were to mention QR codes and how they might be used in answer to a question about how they might “demonstrate a creative but pragmatic approach to delivering the ‘right’ solution within defined project parameters”?! Crash and burn, I suspect!;-)

(NB on the jobs front, the Centre for Professional Learning and Development is also advertising at the moment, in particular for a Interactive Media Developer and a Senior Learning Developer.)

Okay, ranty ramble over, back to the weekend…

PS to link to a sequence that starts so many minutes and seconds in, use the form: http://uk.youtube.com/watch?v=mfv_hOFT1S4#t=9m49s.

PPS for a good overview of QR codes and mobile phones, see Mobile codes – an easy way to get to the web on your mobile phone.

PPPS [5/2010] Still absolutely no interest in the OU for this sort of thing, but this approach does now appear to be in the wild… Books Come Alive with QR Codes & Data in the Cloud

Calling Amazon Associates/Ecommerce Web Services from a Google Spreadsheet

[UPDATE: Note that since Amazon Product Advertising API, or whatever it’s called now – the thing that was the Amazon E-commerce API – started requiring signed calls (August 2009), this trick has stopped working…]

I’ve never really been one for using spreadsheets – I’d rather write code in a text environment than macros and formulae in a Microsoft environment (because Excel is the spreadsheet you’re likely to have to hand in most cases, right?), but over the last week or so, I’ve really been switched on to how we might be able to use them as a scribble pad for playing with web services…

So for example, in Viewing Campaign Finance Data In a Google Spreadsheet via the New York Times Campaign Data API I showed how to do what it says on the tin…

… and today I had a look at Amazon Associates Web Service (formerly known Amazon ECS (eCommerce webservices)).

Until now, the best way of getting your head round what these services can do has been to use the tools on AWSzone, a playground (or scratchpad) for previewing calls to Amazon web services.

In the case of the REST flavoured web service, the form simply provides a quick’n’easy way of creating the RESTful URL that calls the webservice.

The SubscriptionId is required and can be obtained by registering for access with the Amazon Associates web service.

So just pick the web service/function you want to call (ItemSearch in this case), fill in some necessary details (and some optional ones, if you like…) and view the results:

(You might also notice the scratchpad contains a tab for creating a minimal SOAP request to the web service (and viewing the associated SOAP response) and a tab for creating Java (or C#) code that will call the service). Amusingly, you view the SOAP request and response via a URL ;-)

Whilst the scratchpad makes it easy to construct web service calling URLs, the XML response is still likely to be unmanageable at best (and meaningless at worst) for most people. Which is where using a Google spreadsheet as a display surface comes in.

How so? Like this: take a URL for a (working) webservice call constructed in the AWZ Zone REST scratchpad and paste it into cell B3 in a new Google spreadsheet (enter “URL” as a label in cell A3).

In cell D3 enter the following formula:

This calls the Amazon Associates web service via the RESTful URL in cell B3, and then attempts to display the XML for each results “Item”.

Compare this with the actual XML results file:

The spreadsheet has loaded in the ASIN (the ISBN for each book result) and the DetailPageURL, but the ItemAttributes are not loaded in (or if they are, they aren’t displayed because a single cell can’t display more than a single XML attribute, and it would have to display the Author(s), Manufacturer, ProductGroup and so on).

(Hmm, I wonder what a Treemap spreadsheet would look like? How would it handle the display of XML subtrees?!)

Tweak the formula in D3 so that it says:
in cell A4 enter Path and in B4 enter //Item.

Hopefully results table will remain the same, only now you can experiment with the path setting easily.

Inspect the ItemAttributes by setting the path (cell B4) to //ItemAttributes

A single result can be obtained by specifying which result to display. For example, set the path to //Item[1]/ItemAttributes to display the ItemAttributes for just the first ([1]) results Item.

By importing several XML results files, you could list just the results for the first, second and third results, for example. By loading formulas with different paths into different cells, you can force different results attributes into particular cells.

For example, set the path to //Item[2]/ItemAttributes to display the ItemAttributes for just the second ([2]) results Item.

It’s also possible to craft changes that will apply to the web service URL. In cell A2 enter the label Search and in cell B2 a single word search term, such as google.

Cut the URL from cell B3 and replace it with the formula =CONCATENATE(“”), then paste the URL back in between the double quotes of the CONCATENATE formula.

Now go into cell B3, and find the part of the URL that encodes the search keywords:

In the example above, I was searching for the keyword mashup – replace the keyword with “,B2,”.

What this will do is add the search term in cell B2 into the URL that calls the Amazon web service. So now you can use cell B2 as a search box for a search on the Amazon Associates web service.

Note that you can only use single word search terms at the moment – if you want to use multiple search words, you must use + instead of a space between each word.

So – that’s how to build a search (of sorts) using Amazon Associates web services in a Google spreadsheet. :-)

PS Now I know that that for webservices to count in an academic environment, you’ve got to use SOAP (this counts for teaching computing just as much as it counts in JISC funded projects!), so I don’t expect any of this to count in that environment. But for “mortals”, this way of accessing webservices and then doing something useful with the results may actually be a way forward? ;-)

Mashup Mayhem BCS (Glasgow Branch) Young Professionals Talk

On Monday I gave a presentation for the BCS Glasgow branch at the invite of Daniel Livingstone, who I met in the mashup mart session at the CETIS bash last year.

I’d prepared some slides – even rehearsed a couple of the mashups I was going to do – and then fell apart somewhat when the IE6 browser I was using on the lectern PC failed to play nicely with either Pageflakes or Yahoo Pipes. (I had intended to use my own laptop, but the end of the projector cable was locked away…)

“Why not use Firefox Portable?” came a cry from the floor (and I did, in the end, thanks to Daniel…). And indeed, why not? When I was in the swing of doing regular social bookmarking sessions, often in IT training suites, I always used the local machines, and I always used Portable Firefox.

But whilst I’ve started “playing safe” by uploading at least a basic version of the slides I intend to use to Slideshare before I leave home on the way to a presentation, I’ve stopped using Portable Firefox on a USB key even if I am taking the presentation off one… (There is always a risk that “proxy settings” are required when you use your own browser, of course, but a quick check beforehand usually sorts that…)

So note to self – get back in the habit of taking everything on a USB key, as well as doing the Slideshare backup, and ideally prepping links in a feed somewhere (I half did that on Monday) so they can be referred to via a live bookmark or feedshow.

Anyway, some of the feedback from the session suggested handouts would have been handy, so here are handouts of a sort – a set of repurposed slides in which I’ve taken some of the bits that hopefully worked on Monday, along with a little bit of extra visual explanation added in. The slides probably still don’t work as a standalone resource, but that’s what the talking’s for, right?!;-)

There are also some relevant URLs collected together under the glasgowbcs tag on my delicious account: http://delicious.com/psychemedia/glasgowbcs.

Referrer Traffic from Amazon – WTF?!

As it happens, I have been know to look at my blog stats from time (!), and today I noticed something odd in the referrer stats:

A referral from Amazon. WTF?

The link goes to a book detail page for a book about Wikipedia:

Scrolling down a bit, I found this:

A blog post syndicated in the product page from one of the book’s authors that linked to my post on Data Scraping Wikipedia with Google Spreadsheets.

My immediate thought – is there any way we can blog info about courses that use set textbooks back into the related Amazon product page?

Amazon “Edge Services” – Digital Manufacturing

When is a web service not a web service? When it’s an edge service, maybe?

Last night I was pondering the Amazon proposition, which at first glance broadly seems to break down into:

The retail bit splits down further: physical goods and digital downloads, shipped by Amazon; and marketplace goods, where products from other retailers are listed (using Amazon ecommerce webservices, I guess) and Amazon takes a cut from each sale.

It was while I was looking at the digital downloads that the idea of “edge services” came to mind – web services that result in physical world actions (if you’re familiar with the Terminator movies, think: “Skynet manufacturing”;-) [It seems an appropriate phrase has already been coined: direct digital manufacturing, (DDM) – “the process of going directly from an electronic digital representation of a part to the final product [for example] via additive manufacturing”. See also “Digital Manufacturing — Bridging Imagination and Manufacturing“.]

But first let’s set the scene: just what is Amazon up to in the digital download space?

Quite a lot, as it happens – here’s what they offer directly under the Amazon brand, for example (on the Amazon.com domain):
Amazon MP3 Downloads store, a DRM free music downloads site;
Amazon Video on Demand Store – for movie and TV downloads;
Amazon e-books and docs – download e-books and electronic documents (“eDocs”);
– the Kindle store. If you haven’t heard about it already, Kindle is Amazon’s consumer electronics play, an e-book reader with wi-fi connectivity and a direct line back to the Amazon store;
– and just this week (and what prompted this post initially), Amazon bought up Reflexive, a company that among other things is into the online game distribution business.

And although it doesn’t quite fit into the “digital download” space, don’t forget the person-in-the-machine product Amazon Mechanical Turk, a web service for farming out piece work to real people.

But that’s not all – here, for example, are the companies that I know about that are in the Amazon Group of Companies:
IMDb – The Internet Movie Database (which apparently is now streaming movies and TV programmes for free);
Audible – audio book downloads;
Booksurge – book printing on-demand (just by-the-by, in the UK, Amazon’s Milton Keynes fulfilment centre is about to go into the PoD business (press release);
CreateSpace: PoD plus, I guess? Create print on demand books DVDs and CDs, backed up by online audio and video distribution services.

(Amazon also own Shelfari, a site for users to organise and manage their own online bookshelves, and have a stake in LibraryThing, another service in the same vein, through the acquisition of second-hand, rare and out-of-print book retailer Abebooks.)

UPDATE: And they’ve just bought the Stanza e-book reader.

So here’s where it struck me: Amazon is increasingly capable of turning digital bits into physical stuff. This is good for warehousing, of course – the inventory in a PoD driven distribution service is blanks, not one or two copies of as many long tail books you can fit in the warehouse – though of course the actual process of PoD is possibly a huge bottleneck. And it takes Amazon from retailer, to manufacturer? Or to a retailer with an infinite inventory?

If this is part of the game plan, then maybe we can expect Amazon to buy up the following companies (or companies like them) over the next few months:
MOO.com – personalised business card printing, that’s also moving into more general card printing. Upload your photos (or import them from services like flickr) and then print ’em out… photobox does something similar, though it maybe prints onto a wider range of products than MOO currently does?
Spreadshirt – design (and sell) your own printed T-shirts;
Ponoko, or Shapeways – upload your CAD plans and let their 3D printers go to work fabricating your design;
Partybeans – personalised “candy boxes”. Put your own image on a tin containing your favourite sweets:-)

(For a few more ideas, see Money on Demand: New Revenue Streams for Online Content Publishers.)

That said, Amazon built up it’s retail operation based on reviews and recommendations (“people who bought this, also bought that”). The recommendation engine was (is) one way of surfacing long tail products to potential purchasers. And I’m not convinced that the long tail rec engine will necessarily work on ‘user-generated’ content (although maybe it will scale across to that?!). But if you run an inventoryless operation, then does it matter?! Because maybe you can resell uploaded, user-contributed content to friends and family anyway, and several sales for the price of one upload that way?

Or maybe they’ll move into franchising POD and fab machines, and scale-up manufacturing that way? One thing I keep noticing at conferences and events is that coffee increasingly comes in Starbucks labeled dispensers (Starbucks – For Business). So maybe we’ll start seeing Amazon branded POD and fab machines in our libraries, bookstores and catalogue shops? (Espresso, anyone? Blackwell brews up Espresso: “Blackwell is introducing an on-demand printer the Espresso Book Machine to its 60-store chain after signing an agreement with US owner On Demand Books.“) Also on the coffee front – brand your latte

A few further thoughts:
– if Amazon is deliberately developing a digital manufacturing capacity to supplement it’s retail operation (and find ways of reducing stock levels of “instanced products” (i.e. particular books, or particular DVDs) then is the next step moving into the design and user-contributed content business? Like photo-sharing, or video editing..? How’s the Yahoo share price today, I wonder?! ;-)
– Amazon starts (privacy restrictions allowing, and if it doesn’t already do so) to use services like Shelfari and IMDb (though its playlists) to feed its recommendation engine, and encourages the growth of consumer curated playlists; will it have another go at pushing the Your Media Library service, or will it happily exploit verticals like IMDb and Shelfari?
– will companies running on Amazon webservices that are offering “edge services” start to become acquisition targets? After all, if they’re already running on Amazon infrastructure, that makes integration easier, right? Because the Amazon website itself is built on top of those services (and is itself actually a presentation layer for lots of loosely coupled web services already?) (And if you go into conspiracy mode, was the long term plan always to use Amazon webservices as a way of fostering external business innovation that might then be bought up and rolled up into Amazon itself?!)

There’s a book in there somewhere, I think?!

PS another riff on services at the edge, AWS satellite ground stations: Instead of building your own ground station or entering in to a long-term contract, you can make use of AWS Ground Station on an as-needed, pay-as-you-go basis. You can get access to a ground station on short notice in order to handle a special event: severe weather, a natural disaster, or something more positive such as a sporting event. If you need access to a ground station on a regular basis to capture Earth observations or distribute content world-wide, you can reserve capacity ahead of time and pay even less. AWS Ground Station is a fully managed service. You don’t need to build or maintain antennas, and can focus on your work or research.

[Dec 2020] Or how about this? Amazon Monitron, "an end-to-end system that uses machine learning (ML) to detect abnormal behavior in industrial machinery, enabling you to implement predictive maintenance and reduce unplanned downtime [using] Monitron Sensors to capture vibration and temperature data". So: Amazon start to automate manufacturing analytics, use that to in part bootstrap the development of their own (additive) manufacturing processes, initially for producing own-brand lines (whose adoption is identified from analysis of their marketplace sales data) but then rolled as a service and then sold as industrial equipment (built in part, rep-rap style, from their own manufacturing lines).

I wonder if Jeff Bezos (or Elon Musk..] has ever read this 1981 NASA funded research report: A SELF-REPLICATING, GROWING LUNAR FACTORY.

Mashup Reuse – Are You Lazy Enough?

Late on Friday night, I picked up a challenge (again on Twitter) from Scott Leslie:

After a little more probing, the problem turned out to be to do with pulling a list of URLs from a page on the Guardian blog together into a playlist: The 50 greatest arts videos on YouTube.

As Scott suggested, it would have been far more useful to provide the list as a Youtube playlist. But they didn’t… So was there an easy way of creating one?

Now it’s quite possible that there is a way to programmatically create a playlist via the Youtube gdata API, but here’s a workaround that uses a Grazr widget as a player for a list of Youtube URLs:

So let’s work back from this widget to see how it was put together.

The Grazr widget works by loading in the URL of an OPML or RSS feed, or the URL of an HTML page that contains an autodiscoverable feed:

The URL loaded into the widget is this:


If we load this URL into our normal browser, (and then maybe also “View Source” from the browser Edit menu, or “Page Source” from the browser View menu) this is what the Grazr widget is consuming:

If you know anything about Grazr widgets, then you’ll maybe know that if the feed contains a media enclosure, Grazr will try to embed it in an appropriate player…

So where is the feed the Grazr widget is feeding on actually come from? The answer, a Yahoo pipe. This pipe in fact:

Let’s have a look at how it works – click on the “Edit Pipe” button (or maybe Clone the pipe first to get your own copy of it – though you’ll have to log in and/or create a Pipes user account using your Yahoo account first…):

And here’s what we find:

What this pipe does is Fetch a CSV file from another URL and rename the pipe’s internal representation of the data that was loaded in from the CSV file in such a way that the pipe now represents a valid, if not too informative, RSS feed:

The Loop element is used to run another pipe function (TinyURL to full (preview) URL), whose name suggests that it returns the target (original) URL from a TinyURL:

This pipe block is actually one I created before (and remembered creating) – if you inspect the debug output of the block, you’ll see the TinyURLs have been unpacked to Youtube video page URLs.

(If you want to explore how it works, you can find it here: TInyurl to full (preview) URL.)

The final bit of the pipe renames the attribute that was added to the pipe’s internal feed representation as an enclosure.url, and then rewrites the links from URLs that point to a Youtube video (splash) page to the URL of the video asset itself (a Flash swf file).

So that’s how the pipe works – it takes a CSV file input from somewhere on the web, and generates an RSS feed with a video file enclosure that can be played in a Grazr widget.

So where does the CSV file come from? If we look at the URL that the CSV block is loading in in the pipe we can find out:


Here’s the spreadsheet itself: http://spreadsheets.google.com/ccc?key=p1rHUqg4g421ms8AMsu2-Tw

And the cells we want are cells A30:A82:

TinyURLs :-)

But where are they coming from?

Hmm, they are CONTINUEing in from cell A1:

The little orange square in the top right of cell A1 in the spreadsheet shows a formula is being used in that cell…

So let’s see what the formula is:

Here it is:

I recognise that URL! ;-) So what this formula does is is load in the The 50 greatest arts videos on YouTube page from the Guardian website, and then pull out all the anchor tags – all the <a> tags… which happen to include the links to the movies which we found at cells A30:A82.

Just looking back at the original page, here’s what it looked like:

And here’s what it really looks like if we view the page source:

So to recap, what appears to be going on is this:

A Google spreadsheet loads in the Guardian web page as an XML document, and strips out the URLs. The top 50 video URLs appear in contiguous cells as TinyURLs. These are published as a CSV file and consumed by a Yahoo pipe. The pipe takes the TinyURLs in the CSV feed, creates an RSS feed from them, unpacks them to their full Youtube URL form, and adds a link to the actual Youtube video asset as a feed enclosure. The feed is then rendered in a Grazr widget that automatically loads an embedded video player when it sees the Youtube video enclosure.

So how did I put this “mashup” together?

Firstly, I looked at the original page that contained the links that Scott (remember Scott?… strains of Alice’s Restaurant etc etc ;-), and knowing that I could use a Grazr widget as a player for a feed that contained Youtube movie enclosures all I had to do was get the URLs into a feed… and so I looked at the 50 top videos for cluses as to whether the links were in a form I could do something with, maybe using the Yahoo pipes ‘impoort HTML’ page scraping block; but the page didn’t look that friendly, so then I View source‘d. And the page structure didn’t look overly helpful either; but the links were there so rather than look t the page too closely, I though (on the off chance) I’d see what they looked like if I just link scraped the page. And knowing that the Google importXML function (with the //a XPATH) is a link scraper, I gave it a try; and the TinyURLs were all blocked together, so I knew I could use ’em by publishing that block of cells via a CSV file. And I remembered I’d created a TinyURL decoder block in Yahoo pipes some time ago, and I remembered creating a Youtube enclosure pipe before too, so I could crib that as well. And so it fell together…

And it fell together because I’d built reuable bits before, and I remembered where I could find them, so l took the lazy solution and wired them up together.

And I think that’s maybe what we need more of it mashups are going to become more widely used – more laziness… Many mashups are bespoke one-off’s because it’s getting easier to build “vertical” disposable mashup solutions. But sometimes there’s even easier to put together if they’re made out of Lego… ;-)

See also: Mashup Recycling: Now this is Green IT! and The Revolution Starts (near) Here

Getting an RSS Feed Out of a Google Custom Search Engine (CSE)

Alan posted me a tweet earlier today asking me to prove my “genius” credentials (heh, heh;-):

As far as I know, Google CSEs don’t offer an RSS output (yet: Google websearch doesn’t either, though rumour has it that it will, soon… so maybe CSEs will open up with opensearch too?)

So here’s a workaround…

If you make a query in a Google CSE – such as the rather wonderful How Do I? instructional video CSE ;-) – you’ll notice in the URL an argument that says &cx=somEGobbleDYGookNumber234sTUfF&cof….

google cse

The characters between cx= and either the end of the URL or an ampersand (&) are the ID of the CSE. In the case of How Do I?, the ID is 009190243792682903990%3Aqppoopa3lxa – almost; the “%3A” is a safe encoding for the web of the character “:”, so the actual CSE ID is 009190243792682903990:qppoopa3lxa. But we can work round that, and work with the encoded CSE ID cut straight from the URL.

Using the Google AJAX search API, you can create a query on any CSE that will return a result using the JSON format (a javascript object that can be loaded into a web page). The Google AJAX search API documentation tells you how: construct a Google AJAX web search query using the root http://ajax.googleapis.com/ajax/services/search/web?v=1.0 and add a few extra arguments to pull in results from a particular CSE: Web Search Specific Arguments.

JSON isn’t RSS, but we can get it into RSS quite easily, using a Yahoo pipe…

Just paste in the ID of a CSE (or the whole results URL), add your query, and subscribe to the results as an RSS feed from the More Options menu:

The pipe works as follows…

First up, create a text box to let a user enter a CSE ID cut and pasted from a CSE results page URL (this should work if you paste in the whole of the URL of the results page from a query made on your CSE):

Then create the search query input box, and along with the CSE ID use it to create a URL that calls the Google AJAX API:

Grab the JSON data feed from the Google AJAX Search API and translate the results so that the pipe will output a valid RSS feed:

And there you have it – an RSS feed for a particular query made on a particular Google CSE can be obtained from the Get as RSS output on the pipe’s More Options menu.