Creating A RSS Feed From Google Spreadsheet

December 9, 2012 at 20:11 (Tech)

It is possible to use the Publish To Web option to create a RSS feed from a Google Spreadsheet. There are a few issues to note:

  • As with other Google products your Google ID (email address) may be made publicly available in the published results.
  • Because the entire feed is generated whenever a change is made to the source document, all items will always have the same published date.

There is very little documentation explaining how any of the RSS options operate or what structure the source document needs to take.

I’ve deduced the following:

  • Channel.Title is taken from the Sheet Name
  • Channel.Link is a link to the default (HTML) published view on the source document
  • Channel.Description is not set
  • Channel.LastBuildDate last saved timestamp of the source document
  • Channel.managingEditor Google username

The items are created differently depending if you select Cells or List

Cells traverses the sheet from left to right, top to bottom and creates an item for each cell

  • Item.title is the cell reference e.g. A1 or B2
  • Item.description contains the cell contents

The list option allows more data to be added to the feed. Apart from Row 1 each row will generate a new item.

  • Column A is used for each item.Title
  • Item.Description contains a string made up from the contents of the rest of the row
    • The Row1 value is used for a label is it is blank it will used an internal cell reference (e.g. _cokwr so it is best to use something)
    • The label is followed by a colon and space and then the contents of the next column i.e “: ”
    • The remaining columns will be added to the string separated by a comma e.g. “: , : ”
  • Each row generates a new item
  • The guid for each item is a link to the xml for the single cell

URL Format

The URL for the feed is made up in the following way:

Followed by the following options for Feeds:

  • /basic/ – Provides basic atom feed
  • /basic?alt=rss – RSS Version

Or other output options e.g.

  • &output=pdf – PDF Version of spreadsheet
  • &output =text – Text version
  • &output=html – Html version

It is also possible to only display a range by adding the following parameter:

  • e.g &range=A1%3AG5 for A1 to G5 (%3A is the url encoding for a colon)
Advertisements

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

%d bloggers like this: