Displaying Google Spreadsheet using Zend_Gdata_Spreadsheet

intro

This article shows code to display google spreadsheet that was created in the previous article using Zend_Gdata_Spreadsheet.
Please see Google Spreadsheet Data API for more detail information.
To execute the following code, you need to have a google account that can use google document.
You can check the screen from demonstration site(though I don’t know about security).

1.Create Controller

Create the file application/constorllers/GsheetController.php with the following content.

<?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 GsheetsController extends Zend_Controller_Action
{
    protected $_session;
    protected $_title = 'Test Spreadsheet';

    public function preDispatch()
    {
        $this->_session = new Zend_Session_Namespace('GsheetsController');
    }

    public function indexAction()
    {
        if (!isset($this->_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.
                $this->_session->sheet_token = $session_token;
            } else {
                // Display link to generate single-use token
                $url = 'http://'. $_SERVER['SERVER_NAME'] . $_SERVER['REQUEST_URI'];
                $scope = 'http://spreadsheets.google.com/feeds';
                $secure = false;
                $session = true;
                $googleUri = Zend_Gdata_AuthSub::getAuthSubTokenUri(
                    $url, $scope, $secure, $session);
                $this->view->message = 
                    "Go <a href='$googleUri'>Google Login Screen</a>";
                return $this->render();
            }
        }
    }

    public function viewAction()
    {
        $client = Zend_Gdata_AuthSub::getHttpClient($this->_session->sheet_token);
        $service = new Zend_Gdata_Spreadsheets($client);
        $feed = $service->getSpreadsheetFeed();
        $this->view->message =
            'Spreadsheet[title=' . $this->_title . '] was not found.'; 
        foreach ($feed->entries as $entry)
        {
            if ($entry->title->text == $this->_title)
            {
                $splits = split('/', $entry->id->text);
                $doc_id = $splits[5];
                $query = new Zend_Gdata_Spreadsheets_DocumentQuery();
                $query->setSpreadsheetKey($doc_id);
                $feed = $service->getWorksheetFeed($query);
                if (count($feed->entries) > 0)
                {
                    $splits = split('/', $feed->entries[0]->id->text);
                    $sheet_id = $splits[8];
                    $query = new Zend_Gdata_Spreadsheets_ListQuery();
                    $query->setSpreadsheetKey($doc_id);
                    $query->setWorksheetId($sheet_id);
                    $this->view->listFeed = $service->getListFeed($query);
                    unset($this->view->message);
                }
            }
        }
    }

    public function logoutAction()
    {
        if (isset($this->_session->sheet_token))
        {
            Zend_Gdata_AuthSub::AuthSubRevokeToken($this->_session->sheet_token);
            unset($this->_session->sheet_token);
        }
        $this->_helper->redirector('index');
    }
}

<?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 GsheetsController extends Zend_Controller_Action

{

protected $_session;

protected $_title = 'Test Spreadsheet';

public function preDispatch()

{

$this->_session = new Zend_Session_Namespace('GsheetsController');

}

public function indexAction()

{

if (!isset($this->_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.

$this->_session->sheet_token = $session_token;

} else {

// Display link to generate single-use token

$url = 'http://'. $_SERVER['SERVER_NAME'] . $_SERVER['REQUEST_URI'];

$scope = 'http://spreadsheets.google.com/feeds';

$secure = false;

$session = true;

$googleUri = Zend_Gdata_AuthSub::getAuthSubTokenUri(

$url, $scope, $secure, $session);

$this->view->message =

"Go <a href='$googleUri'>Google Login Screen</a>";

return $this->render();

}

public function viewAction()

{

$client = Zend_Gdata_AuthSub::getHttpClient($this->_session->sheet_token);

$service = new Zend_Gdata_Spreadsheets($client);

$feed = $service->getSpreadsheetFeed();

$this->view->message =

'Spreadsheet[title=' . $this->_title . '] was not found.';

foreach ($feed->entries as $entry)

{

if ($entry->title->text == $this->_title)

{

$splits = split('/', $entry->id->text);

$doc_id = $splits[5];

$query = new Zend_Gdata_Spreadsheets_DocumentQuery();

$query->setSpreadsheetKey($doc_id);

$feed = $service->getWorksheetFeed($query);

if (count($feed->entries) > 0)

{

$splits = split('/', $feed->entries[0]->id->text);

$sheet_id = $splits[8];

$query = new Zend_Gdata_Spreadsheets_ListQuery();

$query->setSpreadsheetKey($doc_id);

$query->setWorksheetId($sheet_id);

$this->view->listFeed = $service->getListFeed($query);

unset($this->view->message);

}

public function logoutAction()

{

if (isset($this->_session->sheet_token))

{

Zend_Gdata_AuthSub::AuthSubRevokeToken($this->_session->sheet_token);

unset($this->_session->sheet_token);

}

$this->_helper->redirector('index');

}

In the controller, the scope of AuthSub is specified like the following content.

                $scope = 'http://spreadsheets.google.com/feeds';

1	$scope = 'http://spreadsheets.google.com/feeds';

2.Create View

Create the file application/views/scripts/gsheets/index.phtml with the following content.

<?php if (isset($this->message)) : ?>
<?= $this->message ?>
<?php else : ?>
<ul>
<li><a href="<?= $this->url(array('action'=>'view'))?>">
view spreadsheet</a></li>
</ul>
<div>
<a href="<?= $this->url(array('action'=>'logout'))?>">Logout</a>
</div>
<?php endif; ?>

<?php if (isset($this->message)) : ?>

<?= $this->message ?>

<?php else : ?>

<ul>

<li><a href="<?= $this->url(array('action'=>'view'))?>">

view spreadsheet</a></li>

</ul>

<div>

<a href="<?= $this->url(array('action'=>'logout'))?>">Logout</a>

</div>

<?php endif; ?>

Create the file application/views/scripts/gsheets/view.phtml with the following content.

<?php if (isset($this->message)) : ?>
<div><?= $this->message ?></div>
<?php endif; ?>
<?php if (isset($this->listFeed)) : ?>
<table>
<tr>
<?php   $headerData = $this->listFeed->entries[1]->getCustom(); ?>
<?php   foreach($headerData as $customEntry) : ?>
<th><?= $customEntry->getColumnName() ?></th>
<?php   endforeach; ?>
</tr>
<?php   foreach($this->listFeed->entries as $entry) : ?>
<tr>
<?php     $rowData = $entry->getCustom(); ?>
<?php     foreach($rowData as $customEntry) : ?>
<td><?= $customEntry->getText() ?></td>
<?php     endforeach; ?>
</tr>
<?php   endforeach; ?>
</table>
<?php endif; ?>
<div>
<a href="<?= $this->url(array('action'=>'index'))?>">Back</a>
</div>

<?php if (isset($this->message)) : ?>

<div><?= $this->message ?></div>

<?php endif; ?>

<?php if (isset($this->listFeed)) : ?>

<table>

<tr>

<?php $headerData = $this->listFeed->entries[1]->getCustom(); ?>

<?php foreach($headerData as $customEntry) : ?>

<th><?= $customEntry->getColumnName() ?></th>

<?php endforeach; ?>

</tr>

<?php foreach($this->listFeed->entries as $entry) : ?>

<tr>

<?php $rowData = $entry->getCustom(); ?>

<?php foreach($rowData as $customEntry) : ?>

<td><?= $customEntry->getText() ?></td>

<?php endforeach; ?>

</tr>

<?php endforeach; ?>

</table>

<?php endif; ?>

<div>

<a href="<?= $this->url(array('action'=>'index'))?>">Back</a>

</div>

3.Check

Access the web server and execute reading of google spreadsheet after you logged in from google’s login screen.

History

Date	Content
2008/5/3	Published

3 Comments

Ericj

2009 年 2 月 5 日

Do you have an example in simple PHP, in one file, instead of this view and controller crap happening? I don’t want to use the whole Zend framework MVC stuff, just the Zend Gdata libraries. Thanks!
oplabo

2009 年 2 月 5 日

I’m sorry. I don’t have it.
sonal

2014 年 2 月 28 日

Awesome … this has helped me a lot
thanks!!!

3 Comments

Leave a Reply