Display google spreadsheet data in wordpress.

Hi my name is Tord Johansson Munk and this is my first internship at Örestad Linux. One of my first task to do here was to write a WordPress widget plugin to display data from a Google spreadsheet document and this is how i made it.

First things first: Research

As I have never worked with either Php or wordpress before i had to do some studying. The first thing i looked up was how you make WordPress plugins. This information was easy to find, a quick search on google and i found several tutorials on how it is done.

To make a wordpress plugin/widget is an easy task to do if you already have some php knowledge. You can find a lot of templates and examples to use on google. This is how my template looks like:

<?php
/*
Plugin Name: name of the plugin
Plugin URI: https://orestad-linux.se/
Description: Show trololololol
Author: Tord Johansson Munk
Version: 0.1
*/

function widget_function(){
#some function that the widget gonna use later
}

function control() {
#The options with the user input at the admin control page
}

function widget_mywidget() {
#the main widget function this is where you call on all the widget functions that the widget will show
}

function showgdoc_init(){
#This is where you initialize the widget and the control panel
}
#Add the action that the widget will preform
add_action(”plugins_loaded”, ”showgdoc_init”);
?>

as you can see the structure of the widget is pretty simple and straightforward. In order to install the plug-in you just put it in the wp-content/plugins folder and activate it in the admin dashboard.

The next step on the research is to find find out how to get the data from google spreadsheet. After some google searching i found the google zend api and some nice documentation on googles api page.

At first i wrote my own class to connect and get data from google spreadsheet. But the class proved to be very ugly and inefficient as i have never worked with Php before. Luckily i found a Google spreadsheet helper-class that suited my need perfectly.

Lets get to work

Now i have everything that i need to make my plugin i started writing the widget core function that gets the data from Google spreadsheet this is how it looks:

 
function ShowData($user,$pass,$spread,$colname,$collink, $colended){
include_once(”Google_Spreadsheet.php”);
$ss = new Google_Spreadsheet($user,$pass);
$ss -> useSpreadsheet($spread);
$text = $ss -> getrows();
foreach($text as $entry){
if($entry[$collink] == true && $entry[$colended] == false){
echo ”<a href=’http://$entry[$collink]’ target=’_blank’>”;
echo $entry[$colname];
echo ”</a>”;
echo ”<br>”;
}
elseif($entry[$collink] == false){
echo $entry[$colname];
echo ”<br>”;
}
}
 
}

it is not pretty nor perfect as this is my first go with Php and web programming and i don’t see myself as an competent programmer yet. But this is how it works.

The first part of the code gets the spreadsheet helper-class and sets ups the variables we pass into the function.

include_once(”Google_Spreadsheet.php”);
$ss = new Google_Spreadsheet($user,$pass);
$ss -> useSpreadsheet($spread);
$text = $ss -> getrows();

The next step is to print the data from the spreadsheet. This code prints out the columns as as a link if it have a matching link column, otherwise just print out the name column It also checks if the column has a matching ending column if so then we don’t print it out.

foreach($text as $entry){
if($entry[$collink] == true && $entry[$colended] == false){
echo ”<a href=’http://$entry[$collink]’ target=’_blank’>”;
echo $entry[$colname];
echo ”</a>”;
echo ”<br>”;
}
elseif($entry[$collink] == false){
echo $entry[$colname];
echo ”<br>”;
}
}

The functionality on this code is very specified to Örestads Linux spreadsheet, you have to modify the code if you want a more general way to show the spreadsheet data.

Now we write the function that deals with the user options this is how is looks:

function control($options) {
$options = get_option(‘widget_showgdoc’);
?>
<label>Widget title<input name=”widget_title” type=”text” /></label>
<label>Gmail<input name=”username” type=”text” /></label>
<label>Password<input name=”password” type=”password” /></label>
<label>Spreadsheet<input name=”spreadsheet” type=”text” /></label>
<label>Colum name<input name=”colname” type=”text” /></label>
<label>Colum link<input name=”collink” type=”text” /></label>
<label>Colum ended<input name=”colended” type=”text” /></label>
<?Php

 
$options[‘widget_title’] = attribute_escape($_POST[‘widget_title’]);
$options[‘user’] = attribute_escape($_POST[‘username’]);
$options[‘pass’] = attribute_escape($_POST[‘password’]);
$options[‘spread’] = attribute_escape($_POST[‘spreadsheet’]);
$options[‘colname’] = attribute_escape($_POST[‘colname’]);
$options[‘collink’] = attribute_escape($_POST[‘collink’]);
$options[‘colended’] = attribute_escape($_POST[‘colended’]);
update_option(‘widget_showgdoc’,$options);
}

The first part of the code gets the WordPress options array this is a global array that is provided with WordPress. You can name it as you want it. We also sets up the boxes in witch we get the user input.

$options = get_option(‘widget_showgdoc’);
?>
<label>Widget title<input name=”widget_title” type=”text” /></label>
<label>Gmail<input name=”username” type=”text” /></label>
<label>Password<input name=”password” type=”password” /></label>
<label>Spreadsheet<input name=”spreadsheet” type=”text” /></label>
<label>Colum name<input name=”colname” type=”text” /></label>
<label>Colum link<input name=”collink” type=”text” /></label>
<label>Colum ended<input name=”colended” type=”text” /></label>
<?php

And then we set the options array with the user input and update the options array with the update_option function.

$options[‘widget_title’] = attribute_escape($_POST[‘widget_title’]);
$options[‘user’] = attribute_escape($_POST[‘username’]);
$options[‘pass’] = attribute_escape($_POST[‘password’]);
$options[‘spread’] = attribute_escape($_POST[‘spreadsheet’]);
$options[‘colname’] = attribute_escape($_POST[‘colname’]);
$options[‘collink’] = attribute_escape($_POST[‘collink’]);
$options[‘colended’] = attribute_escape($_POST[‘colended’]);

update_option(‘widget_showgdoc’,$options);

Then we make the main widget function. This is how is looks:

function widget_showgdoc($args) {
extract($args);
$options = get_option(‘widget_showgdoc’);
echo $before_widget;
echo $before_title;
echo $options[‘widget_title’];
echo $after_title;
ShowData($options[‘user’],$options[‘pass’],$options[‘spread’],$options[‘colname’], $options[‘collink’],$options[‘colended’]);
echo $after_widget;
}

The first thing we do is to get the options array, after that we start to echo how the widget will print out the data. This is done with these variables: $before_widget, $before_title, $after_title, $after_widget what these do is pretty self explanatory. We choose to print the spreadsheet data between $after_title and $after_widget viagra where to buy.

We also put all the user input from the options array into the ShowData function so that the right spreadsheet data is shown.

What is left to do is to initialize the widget as a plugin and initialize the control panel and to add what type of action wordpress is using the widget/plugin for.

function showgdoc_init(){
register_widget_control(‘showgdoc’,’control’);
register_sidebar_widget(__(‘showgdoc’), ‘widget_showgdoc’);
}
add_action(”plugins_loaded”, ”showgdoc_init”);

And now we are done :D, it is pretty easy to do a WordPress plug-in/widget even if you don’t know any Php you just need some programming experience.

Please take note that this code is not very optimal i may have made a bunch of beginner mistakes and been sloppy with the code design. But it gets the job done and it gave me a soft start with Php programming.