Display Google Docs spreadsheets in WordPress Pt2
Following on from my post on how to use Google Docs as a lightweight CMS, this is how to include editable tabular data with custom fields in your WordPress theme.
1. Edit core WordPress theme files
You will only need to edit two files in your theme:
header.phpsingle.php
Copy the contents from the source code in my previous post:
<style>
body {font-family: arial; background: lightgrey}
table {margin: 30px auto; background: white; border-collapse: collapse}
th {text-align: left; padding: 20px 20px; cursor: pointer}
th:hover {background: white}
td {padding: 5px 20px; border: 1px solid lightgrey}
</style>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type="text/javascript" src="http://tablesorter.com/jquery.tablesorter.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
//for each table row, set it's background to light grey
$('tr:even').css('background','#eee');
//make it sortable
$(".csvTable").tablesorter();
});
</script>
And paste it into header.php
2. Add the .csv spreadsheet reader php to single.php
After the line
<?php the_content('<p class="serif">Read the rest of this entry »</p>'); ?>
paste in the following code
<?php
# if there is a spreadsheet...
if($csv !== '') {
# write an html table
echo "<table class='csvTable'>";
# open the csv
$handle = fopen($csv, "r");
$data = fgetcsv($handle, 1000, ",");
echo "<thead><tr>";
# column headers from 1st row of csv:
foreach($data as $value) {
echo "<th>$value</th>";
}
echo "</tr></thead>\n<tbody>\n";
# data rows:
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
echo "<tr>";
foreach($data as $value) {
# write table cell data
echo "<td>$value</td>";
}
echo "</tr>\n";
}
echo "</tbody>\n</table>\n";
}
else { echo "Can't connect to google, sorry!"; }
?>
This is the same as from the previous post, but this time we will remove the following line, as we will pass the .csv url dynamically
# set your csv spreadsheet url here $csv = "http://spreadsheets.google.com/pub?key=t6wIcgSnjd6eJX1NbaOqWtg&output=csv";
3. Look for the .csv Custom Field
In single.php, change the following line from
<?php if (have_posts()) : while (have_posts()) : the_post(); ?>
to
<?php if (have_posts()) : while (have_posts()) : the_post(); // check csv $csv = get_post_meta($post->ID, 'csv', $single = true); ?>
This will check for Custom Fields with the name of ‘csv’
4. Add a Custom Field to your post
With both files saved, log in to your WordPress admin, and create a new post. Below the post and excerpt textareas, under Add custom field, set the Name to ‘csv’ and the value to the url of your Google Docs spreadsheet (or any .csv on the web) – in this case http://spreadsheets.google.com/pub?key=t6wIcgSnjd6eJX1NbaOqWtg&output=csv.
Press Publish and admire your spreadsheet! Obviously, your theme will need to be big enough to accomodate your table! Any amendments to your original document will be updated on your post – great for collaborative data or dynamically generated spreadsheets.
Ahhh this is exactly what I’m looking for but can’t get it to work. =( I’m trying to implement it in a Page as opposed to a Post so I’m editing page.php instead, but that shouldn’t matter, right? When I make all these changes, everything just breaks and I get the white page of death. =/
Aaron
11 Aug 10 08:19
That seems weird… I’ll email you in a minute so you can send me your theme and take a look
felix
11 Aug 10 08:41
I got it working. Great stuff! Now if I could make that data searchable via the WordPress search function, that’d raaawk!
Thanks for sharing this. =)
Aaron
11 Aug 10 09:36
Sorry, forgot to post my fix for anyone else that comes across this. I just had to increase my PHP Memory Limit.
Aaron
11 Aug 10 09:44
Great post!
Im interesting to this with page instead of post, how to do that?
tnx
Ljuboja
25 Dec 10 12:53