Linux and PHP web application support and development (Bromsgrove, UK)
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 –
composer.json :
{
"require": {
"asimlqt/php-google-spreadsheet-client": "2.2.*",
"google/apiclient": "1.0.*@beta"
}
}
(i.e. getting data back from Google, so as to prove it’s a viable solution).
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());
}
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).
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
‹ Filesystem Magic on AWS with EBS volumes and BTRFS 9 years old (last month). ›
Thanks for sharing. It did helped a lot.
Hi David,
I notice the fixtures list is no longer available on the WordPress site you referenced. Does that mean Google have changed so much of the API that your PHP script no longer works?
Costas
No. It will still work – the problem is just that no one is maintaining the spreadsheet, so it’s not showing anything useful any longer.