How To: Use Google Spreadsheets as a Data Source in WordPress (Part 2)

This post is the second installment of How to user Google Spreadsheets as a Data Source in WordPress. Click Here for Part 1.

Step 5 – Build the SimplePie function call and process the result

There are tons of ways to customize and modify what I’m about to demonstrate; for much more info, visit the SimplePie Documentation Page. For PHP help, visit this site.

The first thing we need to do is make a call to the SimplePie function using the feed for our spreadsheet that we determined in step #2 and store the result in an Array.

$gsRSSArray = array(SimplePieWP('http://spreadsheets.google.com/feeds/cells/pYpgiAA4C958k2u5UzDJYJw/od6/public/basic?min-row=1&min-col=8&max-row=4&max-col=8', array('enable_order_by_date' => false)));

Now we have our data in an array to be used. But I found SimplePie inserts some quirky html into the array. This includes wrapping the array in a div and putting line breaks between each array element. To eliminate this extra markup, we are going to dump the array out to a variable (one character at a time), strip some of the markup and reform an array.

First we clear the variable to hold all the characters

unset ($RSSParseString);

Then we dump the array into the variable one character at a time

foreach ($gsRSSArray as $key=>$value) { $RSSParseString = $value; }

And then we create our new Array based on the <br /> markup

$RSSParseArray = explode('<br />', $RSSParseString);

At this point, you now have an array of your data without any <br /> characters in it. Though a lot of different options are available for processing and use of the data, I needed to save it and display it at any given time. To do so, I stored each value in a Session Level variable and did so using a Switch Case.

foreach ($RSSParseArray as $key=>$value)
switch($key) {
case 1:
$_SESSION['gsPrincipalDue'] = $value;
break;
case 2:
$_SESSION['gsInterestDue'] = $value;
break;
case 3:
$_SESSION['gsPaymentDue'] = $value;
break;
}

Your first array element might have a <div id="simplepie"> in it. To eliminate that, Add this line to your first case:

$tmp = trim(substr($value,strlen("<div class='simplepie'>$"), strlen($value)));

My example data is numeric and I want to format it in a standard currency format ($X,XXX.XX). To do so in each case, store each array value in a tmp variable and add the following lines.

Extract the only the digits from the Array value and trim any excess characters from the string.

$tmp = trim(substr($value,strlen("$"),strlen($value)));

Convert the string to a number and format it to two decimal places.

$tmp = number_format((float)$tmp,2);

Store the result in a session variable and put a dollar sign back in front of it.

$_SESSION['sessionVariableName'] = '$' . $tmp;

Putting it all together and we get:

<?php
$gsRSSArray = array(SimplePieWP('http://spreadsheets.google.com/feeds/cells/pYpgiAA4C958k2u5UzDJYJw/od6/public/basic?min-row=1&min-col=8&max-row=4&max-col=8', array('enable_order_by_date' => false)));

unset ($RSSParseString);

foreach ($gsRSSArray as $key=>$value) { $RSSParseString = $value; }

$RSSParseArray = explode('<br />', $RSSParseString);

foreach ($RSSParseArray as $key=>$value)
{
switch($key) {
case 1:

$tmp = trim(substr($value,strlen("$"),strlen($value)));
$tmp = number_format((float)$tmp,2);
$_SESSION['gsPrincipalDue'] = '$' . $tmp;
unset($tmp);
break;
case 2:
$tmp = trim(substr($value,strlen("$"),strlen($value)));
$tmp = number_format((float)$tmp,2);
$_SESSION['gsInterestDue'] = '$' . $tmp;
unset($tmp);
break;
case 3:
$tmp = trim(substr($value,strlen("$"),strlen($value)));
$tmp = number_format((float)$tmp,2);
$_SESSION['gsPaymentDue'] = '$' . $tmp;
unset($tmp);
break;
}
}
?>

You could create as many cases as cells that you are receiving through the SimplePie array and could further customize the processing steps to your liking.

So now that we have our Session Variables populated, what do we do with it?

Step 6 – Install and Activate the PHP Execution Plugin of Your Choice

Since we have our data as we want it stored in Session variables, the only thing left to do is to display the contents of each variable where needed. This will be done using a php echo command.

There are many methods to execute inline PHP code. I chose the PHPExec plugin. The ExecPHP plugin does the same thing. And Sniplets are intriguing (in fact in this demo, that’s what I’m using).

The PHPExec code is:

<phpexec><?php echo $_SESSION['YOUR VARIABLE NAME'];?></phpexec>

The Final Result:

[AmParse]

So Where Do I Put this Code?

What I did was create a section in the header.php in my theme and inserted the function calls there. That way each page load on my site would read and store the Spreadsheet data in the Session variables. This is overkill, but I had 7 different cells to display without wanting to generate unique code for each cell.

I also found that trying to build the entire thing using PHPExec was hairy at best and using the Visual Editor to compose pages would delete my code. It got to be very frustrating. ExecPHP does the same thing. There are ways around this, but I haven’t explored them fully. Storing your code in the header.php (or another template file) bypasses this code deletion. You still have to echo the Session variables and those can still disappear due to the visual editor issues, but its much simpler to recreate an echo statement than it is to recreate all of the above code.

While creating this demo, I discovered the Sniplets plugin. It allows you to compartmentalize your code in Sniplets. You could create a sniplet for each cell you want to work with and use their markup to return the result where you need it. For this example, I used the above code, but echoed the results instead of storing them in Session variables. The Switch Case in my Sniplet looks like this:

switch($key) {
case 1:
$tmp = trim(substr($value,strlen("$"),strlen($value)));
$tmp = number_format((float)$tmp,2);
echo 'Principal Due: $' . $tmp . '<br />';
unset($tmp);
break;
case 2:
$tmp = trim(substr($value,strlen("$"),strlen($value)));
$tmp = number_format((float)$tmp,2);
echo 'Interest Due: $' . $tmp . '<br />';
unset($tmp);
break;
case 3:
$tmp = trim(substr($value,strlen("$"),strlen($value)));
$tmp = number_format((float)$tmp,2);
echo 'Payment Due: $' . $tmp;
unset($tmp);
break;
}

Sniplets thus does a couple of things for us. First, Sniplets solves the code retention issue that PHPExec and ExecPHP have when you view a page in the visual editor as there is no php code to insert into a page or a post. This plugin also bypasses the need to store the results of each case in Session variables.

There might be some more upfront work to create all the Sniplets you’d need, so I’m not sure it saves any development time. Regardless of any time savings, it makes more sense to use this compartmentalized approach versus trying to force your php inline into your pages and posts and having to modify template files. I’ll be modifying my technique to use Sniplets.

Update – October 2012: 

So Sniplets is no longer being developed. It is also actively conflicting with the updated WordPress Core. When activating the plugin, it gives an error “The plugin generated 907 characters of unexpected output during activation. If you notice “headers already sent” messages, problems with syndication feeds or other issues, try deactivating or removing this plugin.”

Sniplets is also preventing various Java elements on a page in the UI from rendering properly. It still works, but it has become a pain in the rump to work with.

So I’ve updated the site where I use this Google Spreadsheets data injection to use Shortcodes UI. Seems to fit the bill nicely. One quirk of this tool is that you can’t echo php. You have to set a variable and return it. Not the end of the world, but something to take into consideration.

Summary

All in all, this is a solution that works to get data from Google Spreadsheets and insert it inline into your pages and posts. It needs some refinement. I don’t claim to be a programmer, so some of the code is probably not as efficient as it could be, but it works and that’s what matters most.

If you have questions or would like to share ways to improve this method, please leave a comment.