In this tutorial we will create a WordPress Plugin which integrates with the Google Document Spreadsheet API to add content dynamically to your site straight from Google Docs (soon to be Google Drive).
Despite having a CMS in the form of WordPress there’s lots of times pulling info in from a Google Drive Spreadsheet can be really useful:
- We used it to pull in an event list which the golf club has to also maintain offline in an Excel spreadsheet. It’s simple to copy from Excel and paste into a G drive spreadsheet but not so much into a WordPress page.
- We used it to pull in information like price and availability for properties in a development. This let multiple members of staff update the details regularly without needing to login or understand the WordPress setup.
We’ve also used this on lots of HTML sites which don’t need CMS systems but need the ability to update one page on the site, like their prices or menu pages. If you want to do this yourself here’s a tutorial.
You will need:
- A Google Account with access to drive and Google Code
- PHP and WordPress API knowledge is helpful
- Our Helper Class which is written specifically for this plugin, download it here
Step 1 – The Documents
Create a couple of spreadsheets in Google Docs, these will be pulled into the application.
When you are signed in to Google Docs, on the left hand side select Create > Spreadsheet
Populate your spreadsheet with content, perhaps add a second sheet etc, and repeat until you have 2 or 3 documents for demonstration purposes.
Step 2 – The Application
Create a new Google API Project @ https://code.google.com/apis/console/. The application will require to have permission to access the Drive API.
To get started with the API playground, head to the API website and sign in with your Google account, one you have signed in you will need to create a new API project by selecting “create” from the menu below the Google logo.
Enter your project name in the popup box, once you have done so you will be taken though to select services, the only service you need to select is the Drive API not the SDK, which is below it.
The drive API is limited to 500,000 requests per day, however we will be adding options to cache any sheets, so the 500,000 should be sufficient.
The next part of the of the application set up is to set-up the API access, so on the left hand menu, select the “API Access” menu item.
By default your API project should have been generated with a simple API access to identify your project, however you need to set up an access which allows us access data, to do this, if you haven’t already, you need to set the project up with an OAuth 2 client id, which can be done by selecting the button at the top.
You will be prompted to enter an application name, which is the only required field for setting up the id and will be shown when you try to gain authorisation from the user, once you have chosen one, click next.
On the client ID settings, set the Application type to be a web application and enter your site url and click “Create Client ID”
Whilst there are more options to configure for the project, we cannot do that yet as we need to create the WordPress administration page.
Step 3 – The basic WordPress administration page
It helps at this step to have prior knowledge of PHP and WordPress plugins, that way you know what the code does and can customise it to your own likings, however the basic code will provide you with a functioning plugin
First of all we need to create a basic WordPress plugin:
<?php
/**
* Plugin Name: Google Docs Integration
* Description: Plugin to pull in and display spreadsheets from Google docs
* Author: Jamie Fraser
* Author URI: http://www.hitreach.co.uk/
* Version: 1.1
**/
//insert licencing agreement information (GPL)
//global variable for accessing class methods
global $GoogleDocsIntegration;
//global variable for accessing information from the extension class
global $GoogleDocsExtension;
//include the extension file or die, not exists
require( "gdoc-extension.php" ); //update to match the file name of the extension class
/*
* Make sure the class doesn't exist before declaring it, avoids naming conflicts
*/
if( !class_exists( "GoogleDocsIntegration" ) ){
class GoogleDocsIntegration {
/* class static variables */
static $cache_table_name = "google_docs_cache"; //DB cache table name
static $active_docs_table_name = "google_docs"; //DB holding information about active docs
static $doc_list_table_name = "google_docs_list"; //DB holding doc/sheet list cache
static $option_name = "google_docs"; //plugin option name
static $shortcode = "gdoc"; //plugin shortcode prefix
/* menu information*/
static $menu_page_title = "Google Docs Integration by Hit Reach";
static $menu_title = "Google Docs";
static $capability = "manage_options";
static $menu_slug = "googledoc-integration";
/* plugin db version, used for upgrades*/
static $db_version = 1;
/* cache time options */
static $cache_options = array( "never" => 0, "1 day" => 86400, "2 days" => 172800, "3 days" => 259200, "4 days" => 345600, "5 days" => 432000, "6 days" => 518400, "7 days" => 604800); //add more here as needed
/**
* Update plugin option with the supplied array
**/
function update_option($array){
return update_option(self::$option_name, $array);
}
/**
* Get the plugin options with the default values
**/
function get_option(){
$defaults = array(
"token_expiration" => 0, // internal counter for access token refresh
"database_version" => 0, // version of db, used for handling upgrades
"api_access_token" => "", // access token granted to the app
"api_authorization_code" => "", //authorization code granted to the app (used with refresh token to generate access token)
"api_refresh_token" => "", //refresh token (used with authorization code to re-new access
"doclistrebuild"=>0, //internal timer to refresh the doc list
//options relating to the app, filling in here is optional, it can be done from an options page if desired.
"client_id" => "",
"client_secret" => "",
"client_email" => "",
"client_google_account" => ""
);
return get_option(self::$option_name, $defaults);
}
/**
* Handle DB Upgrade when required after plugin activation
**/
function update_table(){
global $wpdb;
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
$table = "CREATE TABLE IF NOT EXISTS ".$wpdb->prefix.self::$active_docs_table_name." (
id INT(9) NOT NULL AUTO_INCREMENT,
content_url VARCHAR(512) NOT NULL,
doc_url VARCHAR(512) NOT NULL,
sheet_title VARCHAR(256) NOT NULL,
doc_title VARCHAR(256) NOT NULL,
cache_time VARCHAR(7) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
);";
dbdelta($table);
$table = "CREATE TABLE IF NOT EXISTS ".$wpdb->prefix.self::$doc_list_table_name." (
id INT(9) NOT NULL AUTO_INCREMENT,
doc_url VARCHAR(512) NOT NULL,
title VARCHAR(256) NOT NULL,
sheet_count INT(3) NOT NULL,
sheets LONGTEXT NOT NULL,
PRIMARY KEY (id)
);";
dbdelta($table);
$table = "CREATE TABLE IF NOT EXISTS ".$wpdb->prefix.self::$cache_table_name." (
id INT(9) NOT NULL AUTO_INCREMENT,
docid INT(9) NOT NULL,
contents LONGTEXT NOT NULL,
cached_time INT(16) NOT NULL,
PRIMARY KEY (id)
);";
dbdelta($table);
$option = self::get_option();
$option["database_version"] = self::$db_version;
self::update_option($option);
}
/**
* Plugin Constructor method
**/
function __construct(){
self::add_actions();
}
/**
* adds actions onto the administative hooks
**/
function add_actions(){
//register the administative menu hook
add_action("admin_menu", array(__CLASS__, "admin_menu_init"));
//register the shortcode + function call [gdoc]
add_shortcode( self::$shortcode, array(__CLASS__, "shortcode"));
}
/**
* Plugin activation, handles the upgrading of the plugin
**/
function activation(){
$option = self::get_option();
if($option["database_version"] != self::$db_version){self::update_table();}
}
/**
* Creates the menu item for managing the plugin
**/
function admin_menu_init(){
//register the menu page, for more information see: http://codex.wordpress.org/Function_Reference/add_menu_page
add_menu_page( self::$menu_page_title, self::$menu_title, self::$capability, self::$menu_slug, array(__CLASS__, "admin_menu") );
}
/**
* Populate the administation menu
**/
function admin_menu(){
print '<div class="wrap">';
print '<h1>'.self::$menu_page_title.'</h1>';
//options menu information goes here
print '</div>';
}
}
/*
* Function to initialise the class and extension class
*/
function GoogleDocsIntegration_init(){
global $GoogleDocsIntegration;
global $GoogleDocsExtension;
$GoogleDocsIntegration = new GoogleDocsIntegration();
$GoogleDocsExtension = new GoogleDocsExtension();
}
//action to setup plugin
add_action( "init", "GoogleDocsIntegration_init" );
//activation hook to handle the activation of the plugin file
register_activation_hook(__FILE__, array("GoogleDocsIntegration","activation"));
}This does look like a lot of code, but also it handles creating the necessary database tables when the plugin is activated, create an empty administration menu for the plugin, and registers the options default. Save this plugin in to a folder within a new directory inside your WordPress plugins folder e.g. wp-content/plugins/google-integration/, with a file name of something like google-integration.php, you will also need to download the extension class with interacts with Google API, which I have written for you. This extension will not need any editing during this tutorial and all additional code will be added to the google-integration file created above.
You can download the extension helper here
Download the extension class here and save it as gdoc-extension.php in the same directory as the main plugin file
Step 4 – Expanding on the code – Adding in options
Now we have the basic options page, however the plugin is not functional, the next step is to add in the options menu to add in the client API information which allows the plugin to access your list of spreadsheets within Google Docs. While WordPress does have a built in capability for handling options pages, sometimes it is easier to manually handle the form the form submissions for data.
First of all, update the GoogleDocsIntegration->admin_menu function to call self::main_options_form(); after the h1.
Next create a new class function within the class using the following code:
/**
* Options form for adding in settings
**/
function main_options_form(){
$options = self::get_option();
if( isset( $_POST["GDI"]["api_options"] ) && isset( $_POST["GDI"]["verification"] ) ){
$verification = $_POST["GDI"]["verification"];
//Verifiy the nonce field to ensure its coming from the correct place
if( wp_verify_nonce( $verification, __CLASS__."option_update" ) ){
$options["client_id"] = $_POST["GDI"]["api_options"]["client_id"];
$options["client_secret"] = base64_encode( $_POST["GDI"]["api_options"]["client_secret"] );
$options["client_email"] = $_POST["GDI"]["api_options"]["client_email"];
$options["client_google_account"] = $_POST["GDI"]["api_options"]["client_google_account"];
self::update_option($options);
echo '<div class="updated settings-error" id="setting-error-settings_updated"><p><strong>Plugin Options Updated</strong></p></div>'; }
}
?>
<?php if($options["client_id"] != ""){
$authurl = $GoogleDocsExtension->get_authorisation_url();?>
<p><a href="<?php echo $authurl?>">Gain Application Authorisation</a> (you need to add <code><?php echo $options["client_redirect_url"]?></code> as a registered redirect URI in the api console)</p>
<?php }?>
<h2>API Project Options</h2>
<form action="admin.php?page=<?php echo self::$menu_slug;?>" method="post">
<?php //Create the WP nonce field, for more information see http://codex.wordpress.org/Function_Reference/wp_nonce_field
wp_nonce_field( __CLASS__."option_update", "GDI[verification]");?>
<table class="form-table">
<tbody>
<tr valign="top">
<th scope="row"><label for="GDI[api_options][client_id]">API Project Client ID</label></th>
<td><input type="text" class="regular-text" value="<?php echo esc_attr($options['client_id'])?>" id="GDI[api_options][client_id]" name="GDI[api_options][client_id]"></td>
</tr>
<tr valign="top">
<th scope="row"><label for="GDI[api_options][client_secret]">API Project Client Secret</label></th>
<td><input type="password" class="regular-text" value="<?php echo esc_attr(base64_decode($options['client_id']))?>" id="GDI[api_options][client_secret]" name="GDI[api_options][client_secret]"></td>
</tr>
<tr valign="top">
<th scope="row"><label for="GDI[api_options][client_email]">API Project Client Email</label></th>
<td><input type="text" class="regular-text" value="<?php echo esc_attr($options['client_email'])?>" id="GDI[api_options][client_email]" name="GDI[api_options][client_email]"></td>
</tr>
<tr valign="top">
<th scope="row"><label for="GDI[api_options][client_google_account]"><abbr title="The Google Account email used to create the api project">API Project Google Account</abbr></label></th>
<td><input type="text" class="regular-text" value="<?php echo esc_attr($options['client_google_account'])?>" id="GDI[api_options][client_google_account]" name="GDI[api_options][client_google_account]"></td>
</tr>
</tbody>
</table>
<p class="submit"><input type="submit" value="Save Changes" class="button-primary" id="submit" name="submit"></p>
</form>
<?php
}This code handles the creation of the options form on the site and the addition of options to set up the OAuth2 integration which we will use to gain access to the doc list.
The fields in these options will be populated from the information from within the Google API Project console, once you have filled in the information, the plugin will return a message containing a redirect URI to insert into the API Project’s client ID redirect URIs, this is need to successfully authorise the plugin to access your spreadsheet data via the API Project. However, before authorising the API project to access your Google Docs data, we need to set up a function to capture the call back information, save it and generate the access token/refresh token, which we need for future access calls.
To do this, we need to create a new function with the following code:
/**
* Capture the OAuth response and store the information from it
**/
/**
* Capture the OAuth response and store the information from it
**/
function authorisation_capture(){
global $GoogleDocsExtension;
$option = self::get_option();
if(isset($_GET["auth_return"] ) && isset($_GET["code"]) ){
$code = $_GET["code"]; //the OAuth code
//use the auth code to get the access and refresh tokens
//set the auth code to the request header
$GoogleDocsExtension->setOAuthHeader_authCode($code);
//get refresh and acess tokens
$responses = $GoogleDocsExtension->changeCodeToTokens($code);
if( is_object($responses) ){
if(! isset($responses->error) ){
//need to store the 3 tokens
$option["api_access_token"] = $responses->access_token;// access token granted to the app
$option["api_refresh_token"] = $refresh_token = $responses->refresh_token; //refresh token
$option["token_expiration"] = date("U") + $responses->expires_in; //need to note the expiry time (if the token has expired it needs to be refreshed
echo '<div class="updated settings-error" id="setting-error-settings_updated"><p><strong>Application Authorised Sucessfully</strong></p></div>';
}
else{
echo '<div class="error settings-error" id="setting-error-settings_updated"><p><strong>An Error Occured, Check your settings and try again</strong></p></div>';
}
}
$option["api_authorization_code"] = $code;//authorization code granted to the app (used with refresh token to generate access token)
//save the options
self::update_option($option);
}
}In order for this code to execute properly you also need to add the following function call inside the admin menu function after the h1 and before the main_options_form():
if( isset( $_GET[ "auth_return" ] ) ){
self::authorisation_capture();
}
Once you have added this in, you can fill out the form with the API details, then follow the link to authorise the API project to have access to your details
At this point, the gain Application Authorisation link is no longer needed, so it is up to you if you want to hide it. If you do, simply update the line <?php if($options["client_id"] != ""){ $authurl = $GoogleDocsExtension->get_authorisation_url();?> from “main_options_form”, changing the if statement to: <?php if($options["client_id"] != "" && $options["api_access_token"] != ""){….
The next part is to ensure that the access token is always refreshed when needed; this is so the plugin can maintain constant access to the documents allowing them to be displayed on the front end of the website. We will set the access token to be refreshed the next time a page is loaded after the expiration time, so it is only refreshed when it has expired and is needed.
The call to this is made once the plugin’s 2 classes have been initialised, so inside the Google Docs Integration class add the following function:
/**
* Change the access code for the site and refresh as necessary
**/
function checkAccessCode(){
global $GoogleDocsExtension;
$option = $this->get_option();
$date = date("U");
if( $date > $option["token_expiration"] ){
$response = $GoogleDocsExtension->refresh_access_token();
if(!isset($nat->error)){
$option["api_access_token"] = $response->access_token;
$option["token_expiration"] = $date + $response->expires_in;
}
self::update_option($option);
}
}Also in the function GoogleDocsIntegration_init() outside of the class add a call to the function after both classes have been instantiated by calling the function $GoogleDocsIntegration->checkAccessCode();
Now the basic functionality should be working on the site, what we have so far is a plugin that stores the credentials for the API Project, requests authorisation and stores the token grant, and refreshes the access grant when required to provide constant access.
Step 5 – Get and cache the document list from Google docs
Pulling in the document list from Google is relatively straight forward, however it can be a fairly process intensive request as it has to get all the spreadsheets from the Google Docs, and then go though each sheet in the spreadsheet, so the time it takes execute depends on the number of spreadsheets and sheets you have.
In the GoogleDocsExtension class I have provided, there is a function automatically get all of the Spreadsheets from Google called get_docs_and_sheets, so all that is needed it to process the response and store it in the cache database.
The function in the class itself returns an array of docs, each one containing an array of sheets.
The code to use for re-caching the doc list is as follows:
/**
* check the doc list cache time to see if it needs updating
**/
function check_doclist_cache(){
$option = self::get_option();
$date = date("U");
$updatetime = self::$cache_options["7 days"]; //one week;
//refresh the doc list if the update time has passed or if the url variable is manually specifed
if( $date > ($option["doclistrebuild"] + $updatetime) || isset($_GET["refresh_doc_list"]) ){
self::update_doc_list();
}
}
/**
* Update Doc List
**/
function update_doc_list(){
global $wpdb;
global $GoogleDocsExtension;
$option = self::get_option();
//get the doc list
$documents = $GoogleDocsExtension->get_docs_and_sheets();
//empty the cached table
$wpdb->get_results("TRUNCATE TABLE ".$wpdb->prefix.self::$doc_list_table_name);
//cycle each row of the documents and insert into the database
foreach($documents as $row=>$doc){
//generate insert fields
$to_insert = array(
"doc_url" => $doc["id"],
"title" => $doc["doc_title"],
"sheet_count"=>$doc["sheet_count"],
"sheets" => serialize( $doc["sheets"] )
);
//field masks
$masks = array("%s","%s","%d","%s");
//insert into db
$wpdb->insert($wpdb->prefix.self::$doc_list_table_name, $to_insert, $masks );
}
//update doc list update option
$option["doclistrebuild"] = date("U");
if( self::update_option($option) ){
echo '<div class="updated settings-error" id="setting-error-settings_updated"><p><strong>Document List Updated</strong></p></div>'; }
}Add these functions into the GoogleDocsIntegration class and also add a call to the function (self::check_doclist_cache();) inside the admin_menu function before the main_options_form function call.
Now the document will update every 7 days (or the period of time specified), the time of the update is also stored as a timestamp in the plugin options and can be used to show a last updated message if desired.
Next we need to output the form that allows the user to create references to a sheet for use in the shortcode as an attribute. We will make it so that the user selects a document and sheet as well as a cache time and then the plugin creates an id for use in the shortcode.
We will start by making the form; inside the admin_menu function of the class, add a call to the function admin_main self::admin_main(); and then we will add the following function to the class:
/**
* Main Section of the admin menu
**/
function admin_main(){
global $GoogleDocsExtension;
$option = self::get_option();
//check that the app is authorised to access the data
if( $option[ "api_authorization_code"] != ""){
print("<h2>Display Code Options</h2>");
//display the form for adding a new display
self::display_new_form();
}
}This function will handle the display of further administration menu options, for the sake of keeping the class tidy.
Inside this function I have included a call to the function display_new_form it is this function that we will use to display the form.
/**
* Creates the form to add display codes to the site
**/
function display_new_form(){
//check for GDI POST (form submission)
if( isset( $_POST["GDI"] ) ){
$verification = $_POST["GDI"]["verification"];
//if verification matches display coede add
if( wp_verify_nonce($verification, __CLASS__."_code_add" ) && isset($_POST["GDI"]["code"]) ){
global $wpdb;
//get posted variables
$code = $_POST["GDI"]["code"];
$sheet = base64_decode( $code["doc_url"] );
$sheet = explode("||", $sheet);
$doc_url = $sheet[0];
$sheet_url = $sheet[1];
$doc_title = $sheet[2];
$sheet_title = $sheet[3];
$cache = $code["cache"];
//insert fields
$insert = array(
"content_url" => $sheet_url,
"doc_url"=>$doc_url,
"sheet_title" => $sheet_title,
"doc_title" => $doc_title,
"cache_time" => $cache
);
//field masks
$masks = array("%s","%s","%s","%s","%s");
//insert command
$insert = $wpdb->insert( $wpdb->prefix . self::$active_docs_table_name , $insert, $masks);
//sucess/failure note
if( $insert ){
echo '<div class="updated settings-error" id="setting-error-settings_updated"><p><strong>New doc code created, it can be called with the shortcode <code>['.self::$shortcode.' id='.$wpdb->insert_id.']</code> inside your post or page</strong></p></div>';
}
else{
echo '<div class="error settings-error" id="setting-error-settings_updated"><p><strong>Display Code Creation Failure</strong></p></div>';
}
}
}
$option = self::get_option();
/*optional but recommended display last update of the doc list + link to refresh
for more information on date see http://uk3.php.net/manual/en/function.date.php*/
echo "<p><strong>Document List Last Updated: ".date("l jS F Y - g:i:s a (O)", $option["doclistrebuild"])."</strong> <a href='admin.php?page=".self::$menu_slug."&refresh_doc_list'>refresh the list</a></p>";
/*end optional*/
?>
<form action="admin.php?page=<?php echo self::$menu_slug;?>" method="post">
<?php //Create the WP nonce field, for more information see http://codex.wordpress.org/Function_Reference/wp_nonce_field
wp_nonce_field( __CLASS__."_code_add", "GDI[verification]");?>
<p>Create a new display code for <select name="GDI[code][doc_url]" style="width:200px"><?php echo self::get_doc_option_list();?></select> and cache for <select name="GDI[code][cache]">
<?php
//output all the cache options from the class
foreach (self::$cache_options as $time => $ms){
echo "<option value='{$ms}'>$time</option>";
}
?>
</select> <input type="submit" class="button-primary" value="Save" /></p>
</form>
<?php
}
/**
* gets the document list and formats into options fields
**/
function get_doc_option_list(){
global $wpdb;
//get docs from DB
$sql = "SELECT * FROM `".$wpdb->prefix.self::$doc_list_table_name."`";
$results = $wpdb->get_results($sql);
$return_string = "";
//if there are docs
if( sizeof( $results ) > 0 ){
//cycles docs
foreach( $results as $doc ){
$return_string .= "<optgroup label='".$doc->title." (".$doc->sheet_count." sheets)'></optgroup>";
$sheets = unserialize($doc->sheets);
//cycle sheets in doc
foreach( $sheets as $id => $sheet ){
$return_string .= "<option value='".base64_encode( $doc->doc_url."||".$sheet["contenturl"]."||".$doc->title."||".$sheet["title"] )."'>#{$id} ".$sheet["title"]." (".$sheet["rowcount"]." Rows, ".$sheet["colcount"]." Columns)</option>";
}
}
}
//else no docs
else{
$return_string = "<optgroup label='No Docs Found'></optgroup>";
}
//return options
return $return_string;
}I have included 2 functions in the code, the first one displays the form for adding the new shortcode ids, and handles the adding of the id information to the database, the second function creates an option list, for sitting inside a select element, of all the documents and sheets found, and is separate for readability.
So now the plugin can create doc codes as well, e.g. [ gdoc id=1] or [ gdoc id=5], now we need to be able to edit and remove existing codes that are no longer required, or the document no longer exists for.
To add the display table function first we will to call the function self::display_existing_codes() inside the admin_main function, after the display_new_form function, then add the following function inside the GoogleDocsIntegration class:
/**
* Displays a list of display codes in the system
**/
function display_existing_codes(){
global $wpdb;
$option = self::get_option();
if( isset( $_REQUEST["GDI"] ) ){
if( isset( $_REQUEST["GDI"]["verification"] ) ){
$verification = $_REQUEST["GDI"]["verification"];
if( wp_verify_nonce( $verification, __CLASS__."_code_purge" ) ){
//code purge here
}
else if( wp_verify_nonce( $verification, __CLASS__."_code_delete" ) ){
//delete code
if( isset( $_REQUEST["GDI"]["id"] ) && isset( $_REQUEST["GDI"]["_id"] ) ){
if( wp_verify_nonce( $_REQUEST["GDI"]["_id"], __CLASS__."_".$_REQUEST["GDI"]["id"] ) ){
$wpdb->get_results( $wpdb->prepare( "DELETE FROM `".$wpdb->prefix.self::$active_docs_table_name."` WHERE `id` = %d", $_REQUEST["GDI"]["id"] ) );
echo '<div class="updated settings-error" id="setting-error-settings_updated"><p><strong>Display Code Deleted</strong></p></div>';
}
}
}
else if( wp_verify_nonce( $verification, __CLASS__."_code_update") ){
//Update cache time
if( isset( $_REQUEST["GDI"]["id"] ) && isset( $_REQUEST["GDI"]["_id"] ) ){
if( wp_verify_nonce( $_REQUEST["GDI"]["_id"], __CLASS__."_".$_REQUEST["GDI"]["id"] ) ){
$cache_time = $_REQUEST["GDI"]["cache"];
$wpdb->update( $wpdb->prefix.self::$active_docs_table_name, array("cache_time"=>$cache_time), array("id"=>$_REQUEST["GDI"]["id"]), array("%d"), array("%d") );
echo '<div class="updated settings-error" id="setting-error-settings_updated"><p><strong>Cache Time Updated</strong></p></div>';
}
}
}
}
}
?>
<table class='wp-list-table widefat' cellspacing='0'>
<thead>
<tr>
<th class="manage-column" width="20%" scope="col">Document Title</th>
<th class="manage-column" width="20%" scope="col">Sheet Title</th>
<th class="manage-column" width="20%" scope="col">Cache Time</th>
<th class="manage-column" width="20%" scope="col">Shortcode</th>
<th class="manage-column" width="10%" scope="col"> </th>
<th class="manage-column" width="10%" scope="col"> </th>
</tr>
</thead>
<tfoot>
<tr>
<th class="manage-column">Document Title</th>
<th class="manage-column">Sheet Title</th>
<th class="manage-column">Cache Time</th>
<th class="manage-column">Shortcode</th>
<th class="manage-column"> </th>
<th class="manage-column"> </th>
</tr>
</tfoot>
<tbody>
<?php
$codes = $wpdb->get_results( "SELECT * FROM `".$wpdb->prefix.self::$active_docs_table_name."`" );
if( sizeof( $codes ) === 0){
?>
<tr>
<td colspan="6"><p>No Codes Found</p></td>
</tr>
<?php
}
else{
foreach ($codes as $single_code){
?>
<tr>
<td><?php echo esc_html($single_code->doc_title)?></td>
<td><?php echo esc_html($single_code->sheet_title)?></td>
<td>
<form action="admin.php?page=<?php echo self::$menu_slug?>" method="post">
<?php //Create the WP nonce field, for more information see http://codex.wordpress.org/Function_Reference/wp_nonce_field
wp_nonce_field( __CLASS__."_code_update", "GDI[verification]");
wp_nonce_field( __CLASS__."_".$single_code->id, "GDI[_id]");?>
<input type="hidden" name="GDI[id]" value="<?php echo $single_code->id?>" />
<select name="GDI[cache]" style="width:120px;">
<?php
foreach(self::$cache_options as $name=>$time){
printf("<option value='%s'%s>%s</option>", $time, selected($single_code->cache_time, $time, true), $name);
}
?>
</select>
<input type="submit" class="button-secondary" value="update" />
</form>
</td>
<td>[<?php echo self::$shortcode?> id=<?php echo $single_code->id?>]</td>
<td><a href='admin.php?page=<?php echo self::$menu_slug?>&GDI[id]=<?php echo $single_code->id;?>&GDI[verification]=<?php echo wp_create_nonce(__CLASS__."_code_purge");?>&GDI[_id]=<?php echo wp_create_nonce(__CLASS__."_".$single_code->id);?>'>Purge</a></td>
<td><a href='admin.php?page=<?php echo self::$menu_slug?>&GDI[id]=<?php echo $single_code->id;?>&GDI[verification]=<?php echo wp_create_nonce(__CLASS__."_code_delete");?>&GDI[_id]=<?php echo wp_create_nonce(__CLASS__."_".$single_code->id);?>'>Delete</a></td>
</tr>
<?php
}
}
?>
</tbody>
</table>
<?php
}This function handles the display, cache time update, cache purge and deletion of display codes from within the system.
The actual purge function still has not been created yet, but it will be the same as the re-cache function that will be made as part of the shortcode parsing function.
At this point we have a plugin that can:
- Authorise a Google API project to access Google Docs spreadsheet data for a user
- Pull in a list of spreadsheets and the sheets contained within and store locally in the database
- Create shortcode displays for individual sheet displays
- Cache a sheet for a period of time so it is not needed to be loaded from Google on every page load
All that is left to do is create the shortcode and handle the display of a single sheet including caching and set up the purge function to manually re-cache a document, so, almost there then!
Step 6 – Displaying a sheet using a shortcode
The first step of displaying a sheet within a post or page is to register a shortcode; the function for doing this is quite straight forward. Inside the class constructor (__construct) add the following function:
add_shortcode( self::$shortcode, array( __CLASS__, "do_shortcode" ) );
This sets up the shortcode up within WordPress, and calls the function do_shortcode within the GoogleDocsIntegration class when the shortcode is used, now we just need to make the function.
The code within the function is one of the more customisable sections depending on how you want to display the sheet within the page. For this plugin I will make it simply display a data list, e.g. names and addresses or prices, etc.
The following code covers what I want to display it like:
/**
* Displays a sheet within a post/page/where ever else shortcodes work
**/
function do_shortcode( $args ){
//the function receives an array of arguments passed in from the shortcode
global $wpdb;
global $GoogleDocsExtension;
//only the attributes within the default args will be used, the rest will be ignored
$default_args = array(
"id" => 1,
"show_header_row" => 0, //show the first row as a header row
"repeat_header_row_at_foot" => 0,//show the header row at the footer too
"odd_even_highlight"=> 0,
"show_last_updated_time" =>0,
"show_doc_title"=>1,
"show_sheet_title"=>1,
);
//extract them too
extract( wp_parse_args( $args, $default_args ) );
//get the row from the db
$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `".$wpdb->prefix.self::$active_docs_table_name."` WHERE `id` = %d", $id ) );
if( empty( $row ) ){
//if no results return the message sheet not found
return "Sheet Not Found";
}
//get the sheet
$sheet = array();
$last_updated = 0;
if( $row->cache_time > 0 ){
//cacheing enabled
//check the cache for a version that is valid, e.g. was cached after time - cache_time
$max_cache_time = date("U") - $row->cache_time;
$cached_row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `".$wpdb->prefix.self::$cache_table_name."` WHERE `docid` = %d AND `cached_time` > %d", $id, $max_cache_time ) );
if( empty( $cached_row ) ){
//cache empty, re-cache
$sheet = self::recache_document( $id, true);
$last_updated = date("U");
}
else{
//use cache
$sheet = unserialize($cached_row->contents);
$last_updated = $cached_row->cached_time;
}
}
else{
//get from google, no cache
$sheet = $GoogleDocsExtension->readSheet( $row->content_url );
}
if( empty( $sheet ) ){
//sheet empty
return "Unable To Retreive Sheet";
}
//make sure to return rather than echo if you want to keep it in the correct place
//now to display table
//this is the output section, alter it as you wish
$out_string = "";
$out_string .= "<div class='spreadsheet_display'>";
if( $show_doc_title == 1 ){
$out_string .= sprintf("<h2 class='doc-title'>%s</h2>", $row->doc_title);
}
if( $show_sheet_title == 1 ){
$out_string .= sprintf("<h3 class='sheet-title'>%s</h3>", $row->sheet_title);
}
$out_string .= "<table class='spreadsheet spreadsheet-".$id."'>";
if( $show_last_updated_time == 1 && $row->cache_time > 0 ){
$out_string .= sprintf( "<caption>Sheet Last Updated: %s</caption>", date("l jS F Y - g:i:s a") , $last_updated );
}
if( $show_header_row == 1 ){
$row_s = "";
$row = array_shift($sheet);
foreach($row as $index=>$row_cell){
$row_s .= "<th class='header-cell header-cell-".$index."'>".$row_cell."</th>";
}
$out_string .= "<thead><tr>".$row_s."</tr></thead>";
if($repeat_header_row_at_foot == 1){
$out_string .= "<tfoot><tr>".$row_s."</tr></tfoot>";
}
}
$out_string .= "<tbody>";
foreach( $sheet as $row=>$columns){
$out_string .= "<tr class='row row-".$row."'>";
foreach( $columns as $column=>$content ){
$out_string .= "<td class='column column-".$column."'>".$content."</td>";
}
$out_string .= "</tr>";
}
$out_string .= "</table>";
//return the output so it sits in the correct place within a post
return $out_string;
}
/**
* recache the document id in the system
**/
function recache_document( $docid, $return_sheet = false){
global $wpdb, $GoogleDocsExtension;
$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `".$wpdb->prefix.self::$active_docs_table_name."` WHERE `id` = %d", $docid ) );
if( !empty( $row ) ){
$sheet = $GoogleDocsExtension->readSheet( $row->content_url );
$wpdb->get_results( $wpdb->prepare( "DELETE FROM `".$wpdb->prefix.self::$cache_table_name."` WHERE `docid` = %d", $docid) );
$wpdb->insert( $wpdb->prefix.self::$cache_table_name, array( "docid"=>$docid, "contents"=>serialize($sheet), "cached_time"=> date("U") ), array("%d","%s","%d") );
return $sheet;
}
}This function handles the entire output of the a sheet inside a table via a shortcode, it also defines the function recache_document which is the last bit needed for the plugin, so in the function display_existing_codes in place of the //code purge here, update it to be:
if( isset( $_REQUEST["GDI"]["id"] ) && isset( $_REQUEST["GDI"]["_id"] ) ){
self::recache_document( $_REQUEST["GDI"]["id"] );
echo '<div class="updated settings-error" id="setting-error-settings_updated"><p><strong>Cache Purged</strong></p></div>';
}This ended the coding, what we have now is a plugin that can pull in, cache and display Google Docs sheets.
The full shortcode for the plugin is [gdoc id=8 show_header_row=1 repeat_header_row_at_foot=1 show_last_updated_time=1 show_doc_title=1 show_sheet_title=1]
This plugin can be used for a lot of things, from listing shop prices to displaying tour dates or membership lists, all of which can be updated via Google Docs, no more messing about with tables!
The only issue with this is Google Docs doesn’t supply formatting information, so formatting in Google Docs (e.g. bold, 2 column span, red text) is reset in the display table.
James Agate
Roger Green
Want to chat about your project or requirements? If you call now you can talk to me directly:
This looks really interesting but the link to your helper class doesn’t work. Can you email this or fix the link please?
Hi Oliver,
Apologies, we recently had a server move and I think some files got lost, I have uploaded it now.
Thanks
Jamie