Php: interacting with Google spreadsheet using Zend Gdata

Here’s a quick post describing how to interact with a Google spreadsheet using Zend Gdata libraries. Download the libraries from here, grab the Zend directory from the library folder and put it in the root of the script described below.

Create a new Google spreadsheet, and grab the “key” part from the browser address bar query string. This is the unique key used by the api to reference your spreadsheet. You also have to set your google account username and password.

The script isn’t fancy at all, but it does the basics:

• Get the spreadsheet data content into an array
• Get the column names
• Dynamically adds some new rows

It’s a good start, I guess. The Zend Gdata Spreadsheet api seems to be well developed. I’m looking for solutions for doing the same thing with text documents, but I haven’t come through yet.

Well, here’s the script. Be my guest!

<?php
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Gdata_AuthSub');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
Zend_Loader::loadClass('Zend_Gdata_Docs');

echo "<pre>";

//-------------------------------------------------------------------------------
// Google user account

$username = 'yourusername'; // Your google account username
$password = 'yourpassword'; // Your google account password

//-------------------------------------------------------------------------------
// Document key - get it from browser addres bar query key for your open spreadsheet

$key = 'tx1LYk4BpIQaglM38cJbTNA';

//---------------------------------------------------------------------------------
// Init Zend Gdata service

$service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
$client = Zend_Gdata_ClientLogin::getHttpClient($username, $password, $service);
$spreadSheetService = new Zend_Gdata_Spreadsheets($client);

//--------------------------------------------------------------------------------
// Example 1: Get cell data

$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
$query->setSpreadsheetKey($key);
$feed = $spreadSheetService->getWorksheetFeed($query);
$entries = $feed->entries[0]->getContentsAsRows();
echo "<hr><h3>Example 1: Get cell data</h3>";
echo var_export($entries, true);

//----------------------------------------------------------------------------------
// Example 2: Get column information

$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($key);
$feed = $spreadSheetService->getCellFeed($query);
$columnCount = $feed->getColumnCount()->getText();
$columns = array();
for ($i = 0; $i < $columnCount; $i++) {
	$columnName = $feed->entries[$i]->getCell()->getText();
	$columns[$i] = strtolower(str_replace(' ', '', $columnName));
}
echo "<hr><h3>Example 2: Get column information</h3>";
echo "Nr of columns: $columnCount";
echo "<br>Columns: ";
echo var_export($columns, true);

//-------------------------------------------------------------------------------------------------
// Example 3: Add cell data

$testData = array();
foreach ($columns as $col) {
	$testData[$col] = "Dynamically added " . date("Y-m-d H:i:s") . " in column " . $col;
}
$ret = $spreadSheetService->insertRow($testData, $key);
//echo var_export($ret, true);

Advertisement

Posted in: PHP, RESTful, Zend Framework