How To: Use Google Spreadsheets as a Data Source in WordPress (Part 1)
This is the first in a two post series that will walk you through how to insert content from Google Spreadsheets Inline into WordPress pages.
Background
Recently, I was converting a site from ASP to WordPress. This site details a mortgage loan and its amortization schedule. The amortization schedule is built with Google Spreadsheets. I found it much easier to work with the amortization data in Google Spreadsheets as opposed to storing it in a MySQL database, as I have no experience working with Databases and can do a lot more with Spreadsheets.
As such, I needed a way to pull data from the Google Spreadsheet and display it as needed in a given web page. In the ASP version of the site, I used a script from Bytescout, which pulled data through the RSS feed of spreadsheet. This worked really well and I hoped to replicate it in WordPress.
I looked for a similar solution in the form of a wordpress plugin. I tried:
- inlineRSS – I ran into issues getting feed data without a barrage of errors.
- Inline Google Docs – I got this to work successfully, but it did more than I was looking for. It showed the entire sheet without any options to only pull in specific cells.
- inlineFeed – I know I tried this, but it didn’t work, though I can’t remember more than that.
Ultimately, none of those worked, and I was thinking I would have to start learning MySQL and become a database expert. Then I found SimplePie. Keep reading for how to integrate your Google Spreadsheet data into WordPress pages.
Step 1 – Create your spreadsheet in Google Spreadsheets
For this example, I’ll be using this spreadsheet. You can use any Google Spreadsheet you’d like. You will need to isolate the data you are looking to return in it’s own cell. By this I mean that if you want to do some data manipulation of any of your data, store the result in its own cell. Your spreadsheet will handle the calculation, RSS will deliver the result.
Step 2 – Determine the RSS Feed for your spreadsheet.
After you have your sheet created to your liking, we have to figure out the feed for that spreadsheet.
Google uses the following format for the spreadsheet feeds:
http://spreadsheets.google.com/feeds/list/<DOCUMENT ID>/od6/public/basic
In our example, the document ID is: pYpgiAA4C958k2u5UzDJYJw. We obtain that by looking at the address bar of our spreadsheet:
To obtain specific cell data from your spreadsheet, use this format for the feed:
http://spreadsheets.google.com/feeds/CELLS/<DOCUMENT ID>/od6/public/basic
To further restrict what cells get pulled over, you can add the following variables to the feed address:
- min-row
- min-col
- max-row
- max-col
Using our example spreadsheet to obtain the values for Total Principal Due, Total Interest Due and Total Payment Due (Cells H1 to H3) through the RSS feed would be:
- min-row=1
- min-col=8
- max-row=4
- max-col=8
Putting this all together, the feed for the cells in our example is:
UPDATE (4/22/09) – In the URL mentioned above, you see an od6. I recently added a new spreadsheet I wished to publish and had to change that to od7. Just an FYI in case you are running into problems.
Step 3 – Install and Activate the Simple Pie Core Plugin
About this Plugin
This plugin does absolutely nothing except load the core SimplePie library so that all other plugins that utilize SimplePie can all share the same up-to-date version. It also helps minimize potential conflicts between SimplePie-powered plugins.
Step 4 – Install and Activate the Simple Pie Plugin for WordPress
About this Plugin
This is the official plugin from the SimplePie team. It relies on the SimplePie Core plugin, and includes several features:
- A configuration pane under the Options tab in the WordPress software.
- “Multifeeds” support (merging and sorting multiple feeds together).
- MUCH better control over the plugin’s output.
- Simple, easy-to-use tags for nearly every piece of data that SimplePie can output
- Support for multiple templates
- Global configuration of default values for several configuration options
- Ability to override the defaults for any given feed — including giving a feed it’s own output template.
- Ability to post-process feed data (e.g. stripping out all content except for images).
- Support for internationalized domain names.
- Support for short descriptions is configurable.
- Support for PHP 4.x and 5.x.
- And much more!
Both of the plugins are required to build any code using SimplePie.
