Many of you have been highly interested in where Karen Vail has gone from The Large Morning Show in the Afternoon on KTRS 550 AM. You can read about that here.

It seems as if the period of try outs for Karen’s spot is over and that Tina Dalpiaz won the job. Though she seems to still have a probationary period to serve out, I think she’s here to stay.

Congrats Tina.

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:

Principal Due: $250,000.00
Interest Due: $318,861.22
Payment Due: $568,861.22

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.

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.

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. Read more…

I use a Microsoft Zune as my portable media player. Some people are all high on the iPod, but those are too rich for my blood.

Read more…

Today is election day in these Grand Old United States. As such, if you are of voting age and not a felon, illegal immigrant or otherwise disqualified from casting a ballot, get off your butt and get to the poll.

Sometimes some of the easiest tricks on a computer are the least often used. I would wager that over 3/4 of the population has never intentionally used a single function key on their keyboard. Sure, they might have mashed one or two in a fit of rage as their system locks up for the 17th time that day, but that doesn’t count. Read more…

These days, when you sign up for Internet service from any Internet Service Provider (ISP), you always get at least one e-mail account. Some providers will give you ten or more accounts with your service. In my opinion, they could give you eleventy billion accounts and you shouldn’t use any of them as your primary address.
Read more…

While putting this site together, I ran into an issue where some of the Google Adsense ad units were not displaying. They were all using the same code, so as such I was baffled. What I observed was that all ads would display on the individual post pages (single.php), but some were missing on the main page (index.php).

I found my answer and figured I’d post it here in case anyone else runs into the same issue. Read more…

While normally I find the Huffington Post to be Grade A Bull Plop, I’m a big fan of their Fundrace map. Very interesting.

http://fundrace.huffingtonpost.com/ (via Lifehacker)

If you use an RSS Reader (I’ll address RSS Feeds in a future post) to subscribe to this site, please note that there is a new feed address.

http://feedproxy.google.com/Marktastic