Pale Purple

Integrating a Google drive spreadsheet with WordPress

palepurple

Recently, the Bromsgrove Hockey club asked if they could show upcoming matches / fixtures / results on their website.

Here’s a short summary of how we integrated Google Drive/Docs Spreadsheets with WordPress.

Knowing we needed something ‘simple’, an obvious back end data store was to use a Google Docs Spreadsheet – which can be shared between a number of end users – and then integrated into the website. Most people are familiar with a spreadsheet like interface, so training was minimal, and Google Drive itself provides history tracking/permissions etc.

From our point of view, the more interesting bit was integrating the spreadsheet onto their website – (spoiler: see here for the end result).

There were a few steps to this –

  1. Registering a new project under the Google Developers Console
  2. Adding an OAuth Service account to this – Google generate a long @developer.gserviceaccount.com address for this – which you then use in the Spreadsheet’s sharing widget to grant access.
  3. Getting the PHP Google API client and PHP Google Spreadsheet Clients working (some code below).
  4. Integrating the above into a WordPress plugin, so an appropriate shortcode can be embedded into a page on the Hockey club’s website.
  5. Adding appropriate caching of the data, so page load time doesn’t suffer too much

Some PHP Code

Libraries / dependencies

composer.json :

{
    "require": {
       "asimlqt/php-google-spreadsheet-client": "2.2.*",
       "google/apiclient": "1.0.*@beta"
     }
}

The “Hello World” part

(i.e. getting data back from Google, so as to prove it’s a viable solution).

See also : http://stackoverflow.com/questions/21470405/google-api-how-to-connect-to-receive-values-from-spreadsheet/22130673

require_once('vendor/autoload.php');
// these are from Google dev console
$G_CLIENT_ID = 'longstring.apps.googleusercontent.com'; 
$G_CLIENT_EMAIL = 'longstring@developer.gserviceaccount.com'; 
$G_CLIENT_KEY_PATH = dirname(__FILE__) . '/xxxxxxxxxx-privatekey.p12'; 
$G_CLIENT_KEY_PW = 'noasecret';    

// connect to Google, boilerplate code...
$obj_client_auth = new Google_Client ();
$obj_client_auth->setApplicationName ('BromsgroveHockeyWebsiteIntegration');
$obj_client_auth->setClientId ($G_CLIENT_ID);
$obj_client_auth->setAssertionCredentials (new Google_Auth_AssertionCredentials(
    $G_CLIENT_EMAIL, 
    array('https://spreadsheets.google.com/feeds','https://docs.google.com/feeds'), 
    file_get_contents ($G_CLIENT_KEY_PATH), 
    $G_CLIENT_KEY_PW
));

$obj_client_auth->getAuth()->refreshTokenWithAssertion();
$obj_token  = json_decode($obj_client_auth->getAccessToken());
$accessToken = $obj_token->access_token;

$serviceRequest = new Google\Spreadsheet\DefaultServiceRequest($accessToken);
Google\Spreadsheet\ServiceRequestFactory::setInstance($serviceRequest);

$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();

$spreadsheet = $spreadsheetFeed->getByTitle('Bromsgrove Hockey Club Fixtures 2014-2015');

$worksheetFeed = $spreadsheet->getWorksheets();
// we want Fixtures
$worksheet = $worksheetFeed->getByTitle('Fixtures');
$listFeed = $worksheet->getListFeed();

foreach($listFeed->getEntries() as $entry) {
     var_dump($entry->getValues());
}

Integrating into WordPress

  1. Create e.g. wp-content/plugins/hockeyclub/plugin.php
  2. Add functions to plugin.php which when called will output (echo) the desired HTML
  3. Register those functions with WordPress as shortcodes (see below).
  4. Enable plugin within WordPress admin panel
  5. Embed shortcode calls within the appropriate page through the WordPress CMS
function display_team_fixtures($params) {
    $options = shortcode_atts(array('after' => "-7 days', 'before' => '+9 days'), $params);
    return _render_fixtures($spreadsheetData, $options['after'], $options['before']);
}

add_shortcode('team_fixtures', 'display_team_fixtures');

Where the shortcode would be embedded into wordpress like :

Blah blah blah
[team_fixtures after="-14 days" before="+5 days"]
Blah blah blah

(The after/before options are to make it relatively easy to change the date ranges for upcoming fixtures).

Caching

I found the Google service relatively slow – there would often be a 1-2 second delay while fetching the data. Therefore, I cached in within WordPress using something like :

$data = wp_cache_get('fixtures-data');
if(!$data) {
    // Get from Google
    wp_cache_set('fixtures-data', $data, null, 60); // cache for 60 secs.
}

In this case, I’m using a WordPress caching plugin which stores the data in a local memcache daemon – found here

Exit mobile version