Joe Gornick

Recently, I was given a simple task to take a set of data and add a row to a Google Spreadsheet daily.

A great thing about the Zend Framework, they have a nice component called Zend_Gdata. Zend_Gdata provides you with classes that interact with Google services. Zend_Gdata_Spreadsheets specifically allows us to interact with Google Spreadsheets easily.

The documentation provided by Zend covers in great detail the basics of authenticating the Google user, loading a spreadsheet/workbook and manipulating data. There are only a few things I found that were not explained in detail. Please note that Google has detailed information about the Spreadsheet service they offer.

I’ve provided a sample file (not a lot of error catching) to demonstrate how we can insert a row into an existing spreadsheet/workbook.

First, we need to authenticate our user/password to Google:

$authService = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
$httpClient = Zend_Gdata_ClientLogin::getHttpClient($this->username, $this->password, $authService);
$this->gClient = new Zend_Gdata_Spreadsheets($httpClient);

The code above simply creates a new Zend_Http_Client which is used to communicate with the spreadsheet service.

Next, we will need to get our spreadsheet ID and worksheet ID. Each account can have multiple spreadsheets and have multiple worksheets within each spreadsheet. For the class I’ve created, I am interested in specifying the spreadsheet and worksheet by name:

// Get spreadsheets and pick the specified spreadsheet name
$feed = $this->gClient->getSpreadsheetFeed();
foreach($feed->entries as $entry)
{
  if ($entry->title->text == $this->spreadsheetName)
  {
    $this->_spreadsheetId = split(”/”, $entry->id->text);
    $this->_spreadsheetId = $this->_spreadsheetId[count($this->_spreadsheetId) - 1];
  }
}
 
// Get worksheets in spreadsheet and select the specified worksheet name
$docQuery = new Zend_Gdata_Spreadsheets_DocumentQuery();
$docQuery->setSpreadsheetKey($this->_spreadsheetId);
$feed = $this->gClient->getWorksheetFeed($docQuery);
foreach($feed->entries as $entry)
{
  if ($entry->title->text == $this->worksheetName)
  {
    $this->_worksheetId = split(”/”, $entry->id->text);
    $this->_worksheetId = $this->_worksheetId[count($this->_worksheetId) - 1];
  }
}

In the code above, we want to get a list of all spreadsheets and select the one that matched the specified spreadsheet name. Once we have the spreadsheet ID, we can use it to get the worksheet ID by the specified worksheet name.

A couple of notes when a spreadsheet/worksheet match is found:

The $entry->id->text value is a URL to the spreadsheet/worksheet. The last value in the path of the URL is the ID associated to the spreadsheet or worksheet. This is why after we split the $entry->id->text, we end up setting the spreadsheet/worksheet ID’s to the last element in the array.

An example of the spreadsheet and worksheet URL’s are listed below:

Spreadsheet URL: http://spreadsheets.google.com/feeds/spreadsheets/[spreadsheet-id]
Worksheet URL: http://spreadsheets.google.com/feeds/worksheets/[spreadsheet-id]/private/full/[worksheet-id]

If everything is successful, we will have our spreadsheet and worksheet ID’s which we can use to manipulate data.

One of the first things I ran into while trying to insert a row was the format of the column names. Something that was missing out of the documentation is how the column names need to be formatted. Column names need to be lower-case, alpha-numeric, and no-whitespace. I created a class GSpreadsheetRow which is basically a wrapper for an array. I have one function available called addColumn($name, $data = null). This fixes the name and adds it to the $columns array. We then use GSpreadsheetRow->columns to pass to the insertRow function.

The GSpreadsheetRow class:

class GSpreadsheetRow
{
  public $columns = array();
  public function addColumn($name, $data = null)
  {
    // Fix the column name to be only alpha-numeric/no-whitespace/lowercase
    $name = strtolower(preg_replace(’/[^A-Za-z0-9]/’, ”, $name));          
    $this->columns[$name] = $data;
    return $this;
  }
}

After we create our row to insert, we need to pass that array to the client:

if ((empty($this->_spreadsheetId)) && (empty($this->_worksheetId))) $this->_init();
$entry = $this->gClient->insertRow($rowData, $this->_spreadsheetId, $this->_worksheetId);
return ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry) ? $entry : null;

The code above simply checks to make sure we have a spreadsheet/worksheet ID and then passes the $rowData to the Zend_Gdata_Spreadsheets->insertRow(). We then return the entry of null if it was successful.

Using the provided class, I have provided a sample to use:

$gs = new GSpreadsheet(’username’, ‘password’, ’spreadsheet-name’, ‘worksheet-name’);
 
$row = new GSpreadsheetRow();
$row->addColumn(’My Column 1′, ($gs->getWorksheetItemCount() + 1))
    ->addColumn(’My Column 2′, date(’m/d/Y’))
    ->addColumn(’My Column (3)’, 100000);
 
if ($gs->insertRow($row->columns))
  echo “Row inserted successfully.”;
else
  echo “Errors while inserting row!”;

Here we simply create a GSpreadsheet object with the username, password, spreadsheet-name, and worksheet-name specified. This will authenticate and get the spreadsheet/worksheet ID’s. Next we create a GSpreadsheetRow to insert into the worksheet. Finally, we call GSpreadsheet->insertRow() with the GSpreadsheetRow->columns.

Again, you can download the GSpreadsheet Example here.

- Joe