Using Google Spreadsheets to Create Book Widgets

Thanks to the Code Words blog written by Jason Clark I found out a method you can use to create a book widget using published Google Spreadsheets.  We’ve been looking for a book cover widget solution for our library website and most of them either involved paying a fee or uploading all the cover images to our server.  Neither of which was a good option.

The instructions were fairly straightforward so I won’t repeat them here – please see the original posting for how to start.  WARNING – If you follow the directions as is – your widget will not work.  

Here is the trick that I found to make the magic work:

In order to make this work you will need to go to File –> Publish to Web  and then click the “Start Publishing” button.

If you don’t do that – your book covers will never appear!  Sounds simple, but I was completely flabbergasted trying to get this to work copying all docs and code directly from the original source.

Another great trick I found was that you can publish multiple sheets from the same spreadsheet, each in their own widget.  After you’ve added additional worksheets you will need to find the “worksheet ID” of each sheet which Google does not make easy to find AT ALL.  I scoured the web for answers and there was none that was ‘easy’, I was trying to avoid additional script creation or mucking around with my server.  I finally stumbled upon a solution that involves neither.  Here it is

EASY – How To Find a Google Spreadsheet Worksheet ID

Step 1:  Go to File → Publish to the Web

Step 2:  In the first part called “Sheets to publish” choose the worksheet for which you want to discover its ID number.

Step 3:  ‘Republish‘ that sheet.  You temporary disable your book widget by doing this – but this would normally only be done once and most likely while you are building your gadget.

Step 4:  In the second part called “Get a link to the published data” you will see two boxes of options to choose from.  Click on Web page  

Step 5:  Choose ATOM or RSS

Step 6: Look at the resulting URL displayed in the box.  The worksheet ID is located at the arrow tip.  This will be different for each sheet in the spread.

That’s it – ta da!  To see a basic widget in action (I switched it to a horizontal view and modified the script to link directly to the library catalog) – take a peek at the second tab titled “Reference Books”  in my Philosophy Guide.  

To see multiple widgets all generated from one spreadsheet check out the Sustainability Guide.  It pulls in titles from the other sheets as well, but they all the same theme so it will work in this instance.  If I could find a way to stop the bleedthrough I would be able to control all you book widgets from one spreadsheet – but that will have to wait.

I still need to fancy up the widgets (maybe with some jquery?) and learn how to create anchor links inside a tab – but for now we have basic workable book widgets that have cover images and link to the catalog automatically.  All this with just an ISBN.

Contact me if you would like to replicate this in your library website and need a little help.

Leave a Reply

Your email address will not be published. Required fields are marked *