Blog: Zend Framework, Google Spreadsheets, and Me Example

gistfile1.php

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

  class GSpreadsheet
  {
    public $gClient;

    public $username;
    public $password;
    public $spreadsheetName;
    public $worksheetName;

    private $_spreadsheetId;
    private $_worksheetId;

    function __construct($username, $password, $spreadsheetName, $worksheetName)
    {
      $this->username = $username;
      $this->password = $password;
      $this->spreadsheetName = $spreadsheetName;
      $this->worksheetName = $worksheetName;

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

      $this->gClient = new Zend_Gdata_Spreadsheets($httpClient);

      $this->_init();
    }

    private function _init()
    {
      // 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];
        }
      }

      return (empty($this->_spreadsheetId) || empty($this->_worksheetId)) ? false : true;
    }

    public function insertRow($rowData)
    {
      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;
    }

    public function getWorksheetItemCount()
    {
      $listQuery = new Zend_Gdata_Spreadsheets_ListQuery();
      $listQuery->setSpreadsheetKey($this->_spreadsheetId);
      $listQuery->setWorksheetId($this->_worksheetId);
      $feed = $this->gClient->getListFeed($listQuery);

      return $feed->count();
    }
  }

  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;
    }
  }

  /////////////////////////////////////////////////////////////////////////////
  // Sample Usage of GSpreadSheet
  $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!";
?>