Adrenalin’s Experience

Zend Framework Gdata API and Cakephp insert a new row to Google spreadsheet

Posted in cakephp by Adrenalin on November 7, 2009

To setup Zend Framework Gdata, see this post on Using the Zend Framework in CakePHP , this helped too.

As reference use: Google Spreadsheets APIs and Tools Developer’s Guide: PHP
In this doc you will see functions like printFeed, that can be found in Spreadsheet-ClientLogin.php it’s a SimpleCRUD implementation for google spreadsheet.

The document key is http://spreadsheets.google.com/ccc?key=HERE&hl=en, you will need it to point to the spreadsheet you want to edit.
Also spreadsheets can have multiple worksheets, you will need the key of the worksheet you want to edit, this key can be get by calling the promptForWorksheet() function.
Also, when adding a new row, you need to give a list of header-row=>value, so the tricky part here is to get the “header-row”. The documentation describe it as:
“the first row of the worksheet as a header row”
“column headers will be converted into valid XML namespaces”
I did just listGetAction() and look up the names of the header-row to use.

So here is the code, hopefully this will give you a fast start and save you time.

class SomeController extends AppController {
var $uses = null; // Don't use any model
function index() {
	App::import('Vendor', 'zend_include_path');
	App::import('Vendor', 'Zend_Gdata', true, false, 'Zend/Gdata.php');
	
	Zend_Loader::loadClass('Zend_Http_Client');
	Zend_Loader::loadClass('Zend_Gdata');
	Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
	Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
	
	$authService = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
	$user = 'username';
	$pass = 'password';
	$this->currKey = 'thesheetkey';
	
	try {
		$httpClient = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $authService);
		$this->gdClient = new Zend_Gdata_Spreadsheets($httpClient);
		
		$this->promptForWorksheet(0); // Put the 0th worksheet of our sheet to $this->currWkshtId
		$this->listGetAction(); // Will list all the rows inside the worksheet
		
		$row = array('column1'=>'value','column2'=>'value','columnN'=>'value');
		$this->listInsertAction($row);
		} catch ( Exception $e )  {
		echo $e->getMessage();
	}
}

public function promptForWorksheet($wordSheetI=0)
{
	$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
	$query->setSpreadsheetKey($this->currKey);
	$feed = $this->gdClient->getWorksheetFeed($query);
	print "== Available Worksheets ==\n";
	$this->printFeed($feed);
	$input = $wordSheetI;
	$currWkshtId = split('/', $feed->entries[$input]->id->text);
	$this->currWkshtId = $currWkshtId[8];
}
public function listGetAction()
{
	$query = new Zend_Gdata_Spreadsheets_ListQuery();
	$query->setSpreadsheetKey($this->currKey);
	$query->setWorksheetId($this->currWkshtId);
	$this->listFeed = $this->gdClient->getListFeed($query);
	print "entry id | row-content in column A | column-header: cell-content\n".
	"Please note: The 'dump' command on the list feed only dumps data until the first blank row is encountered.\n\n";
	
	$this->printFeed($this->listFeed);
	print "\n";
}
public function printFeed($feed)
{
	$i = 0;
	foreach($feed->entries as $entry) {
		if ($entry instanceof Zend_Gdata_Spreadsheets_CellEntry) {
			print $entry->title->text .' '. $entry->content->text . "\n";
			} else if ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry) {
			print $i .' '. $entry->title->text .' | '. $entry->content->text . "\n";
			} else {
			print $i .' '. $entry->title->text . "\n";
		}
		$i++;
	}
}
public function listInsertAction($rowArray)
{
	//$rowArray = $this->stringToArray($rowData);
	$entry = $this->gdClient->insertRow($rowArray, $this->currKey,
	$this->currWkshtId);
	
	if ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry) {
		foreach ($rowArray as $column_header => $value) {
			echo "Success! Inserted '$value' in column '$column_header' at row ".
			substr($entry->getTitle()->getText(), 5) ."\n";
		}
	}
}
}

The same code in the pastebin http://pastebin.com/f12021eb.

Tagged with: , ,

One Response

Subscribe to comments with RSS.

  1. Excellent, what a website it is! This webpage provides useful information to us, keep it up.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: