Creating Add/Update/Delete/View Controller using Zend_Gdata_Spreadsheets

This article shows code of CRUD controller that uses google spreadsheet as data source.

Create the file library/My/Controller/GSheets.php with the following content.

<?php
require_once 'My/Controller/Simple.php';
require_once 'Zend/Controller/Action.php';
require_once 'Zend/Gdata.php';
require_once 'Zend/Gdata/AuthSub.php';
require_once 'Zend/Gdata/Spreadsheets.php';
require_once 'Zend/Gdata/Spreadsheets/DocumentQuery.php';
require_once 'Zend/Gdata/Spreadsheets/ListQuery.php';
require_once 'Zend/Session/Namespace.php';
 
class My_Controller_Gsheets extends My_Controller_Simple
{
    protected $_table_class = null;
    protected $_spreadsheet_name = null;
    protected $_worksheet_name = null;
    protected $_service = null;
 
    public function getTable()
    {
        return null;
    }
 
    public function getService()
    {
        $session = $this->getSession();
        if ($this->_service)
        {
            return $this->_service;
        }
        if (isset($session->sheet_token))
        {
            $client = Zend_Gdata_AuthSub::getHttpClient($session->sheet_token);
            $this->_service = new Zend_Gdata_Spreadsheets($client);
            return $this->_service;
        }
        return null;
    }
 
    public function getSpreadsheetName()
    {
        return $this->_spreadsheet_name;
    }
 
    public function getWorksheetName()
    {
        return $this->_worksheet_name;
    }
 
    public function logonAction()
    {
        $session = $this->getSession();
        if (!isset($session->sheet_token)) {
            if (isset($_GET['token'])) {
                // You can convert the single-use token to a session token.
                $token = $_GET['token'];
                $session_token =  Zend_Gdata_AuthSub::getAuthSubSessionToken($token);
                // Store the session token in our session.
                $session->sheet_token = $session_token;
            } else {
                // Display link to generate single-use token
                $url = 'http://'. $_SERVER['SERVER_NAME'] . $_SERVER['REQUEST_URI'];
                $g_scope = 'http://spreadsheets.google.com/feeds';
                $g_secure = false;
                $g_session = true;
                $googleUri = Zend_Gdata_AuthSub::getAuthSubTokenUri(
                    $url, $g_scope, $g_secure, $g_session);
                $this->view->googleUri = $googleUri;
            }
        }
    }
 
    public function logoutAction()
    {
        $session = $this->getSession();
        if (isset($session->sheet_token))
        {
            Zend_Gdata_AuthSub::AuthSubRevokeToken($session->sheet_token);
            unset($session->sheet_token);
        }
        $this->_helper->redirector('index');
    }
 
    protected function _getSpreadsheetId()
    {
        $session = $this->getSession();
        $service = $this->getService();
        if (isset($session->spreadsheet_id))
        {
            return $session->spreadsheet_id;
        }
        $title = $this->getSpreadsheetName();
        $feed = $service->getSpreadsheetFeed();
        foreach ($feed->entries as $entry)
        {
            if ($entry->title->text == $title)
            {
                $splits = split('/', $entry->id->text);
                $session->spreadsheet_id = $splits[5];
                return $session->spreadsheet_id;
            }
        }
        return null;
    }
 
    protected function _getWorksheetId($doc_id)
    {
        $service = $this->getService();
        $session = $this->getSession();
        if (isset($session->worksheet_id))
        {
            return $session->worksheet_id;
        }
        if ($doc_id)
        {
            $title = $this->getWorksheetName();
            $query = new Zend_Gdata_Spreadsheets_DocumentQuery();
            $query->setSpreadsheetKey($doc_id);
            $feed = $service->getWorksheetFeed($query);
            if (!$title && $feed->count() > 0)
            {
                $splits = split('/', $feed->entries[0]->id->text);
                $session->worksheet_id = $splits[8];
                return $session->worksheet_id;
            }
            else
            {
                foreach ($feed->entries as $entry)
                {
                    if ($entry->title->text == $title)
                    {
                        $splits = split('/', $entry->id->text);
                        $session->worksheet_id = $splits[8];
                        return $session->worksheet_id;
                    }
                }
            }
        }
        return null;
    }
 
    protected function _executeList($table)
    {
        $service = $this->getService();
        $doc_id = $this->_getSpreadsheetId();
        $sheet_id = $this->_getWorksheetId($doc_id);
        if ($doc_id && $sheet_id)
        {
            $query = new Zend_Gdata_Spreadsheets_ListQuery();
            $query->setSpreadsheetKey($doc_id);
            $query->setWorksheetId($sheet_id);
            return $service->getListFeed($query);
        }
        return null;
    }
 
    protected function _executeInsert($table, $values)
    {
        $service = $this->getService();
        $doc_id = $this->_getSpreadsheetId();
        $sheet_id = $this->_getWorksheetId($doc_id);
        if ($doc_id && $sheet_id)
        {
            $service->insertRow($values, $doc_id, $sheet_id);
        }
    }
 
    protected function _executeUpdate($table, $id, $values)
    {
        $service = $this->getService();
        $feed = $this->_executeList(null);
        if ($feed && $feed->count() >= $id)
        {
            $entry = $feed->entries[$id-1];
            $service->updateRow($entry, $values);
        }
    }
 
    protected function _executeDelete($table, $id)
    {
        $service = $this->getService();
        $feed = $this->_executeList(null);
        if ($feed && $feed->count() >= $id)
        {
            $entry = $feed->entries[$id-1];
            $service->deleteRow($entry);
        }
    }
 
    protected function _executeFind($table, $id)
    {
        $feed = $this->_executeList(null);
        if ($feed && $feed->count() >= $id)
        {
            $entry = $feed->entries[$id-1];
            $rowData = $entry->getCustom();
            $values = array();
            foreach($rowData as $customEntry)
            {
                $values[$customEntry->getColumnName()] = $customEntry->getText();
            }
            return $values;
        }
        return null;
    }
}

Next article shows how to use this controller.

Date	Content
2008/5/7	Published