Integrate Google Spreadsheets data with PHP, Part 1

Use PHP to process and integrate data feeds from Google Spreadsheets with a web application

Google Spreadsheets is a well-known cloud-based spreadsheet application. Web application developers are able to access and search data from online spreadsheets through the Google Spreadsheets Data API. This article introduces the Google Spreadsheets Data API and demonstrates it in the context of a PHP application, explaining how to search and retrieve different elements of spreadsheet content.

Share:

Vikram Vaswani, Founder, Melonfire

Photo of Vikram VaswaniVikram Vaswani is the founder and CEO of Melonfire, a consulting services firm with special expertise in open-source tools and technologies. He is also the author of the books Zend Framework: A Beginners Guide and PHP: A Beginners Guide.



14 December 2010 (First published 07 December 2010)

Also available in Chinese Japanese Portuguese

14 Dec 2010 - Added sidebar with link to Part 2 in Introduction and Conclusion. Also added entry for Part 2 in Resources.

Introduction

Frequently used acronyms

  • API: Application program interface
  • HTML: HyperText Markup Language
  • HTTP: Hypertext Transfer Protocol
  • URL: Uniform Resource Locator
  • XML: Extensible Markup Language

Like most people, I use spreadsheets a lot. They're handy for balancing a checkbook, setting up a monthly budget, and estimating and controlling project schedules. About a year ago, however, I switched over from my desktop spreadsheet application to Google Spreadsheets, a cloud-based service that supports all the spreadsheet features I commonly use. This switch offered me two key advantages:

  • I could access my spreadsheet data on the move.
  • Sharing and collaborative editing were significantly easier than when using a desktop application.

What makes Google Spreadsheets particularly interesting, from a developer's perspective, is that it provides a Data API that allows developers to easily build new applications around user space spreadsheet data. You can access this API through any XML-capable development toolkit, and Google provides client libraries for both PHP and Java™ technologies.

In this two-part article, I introduce you to the Google Spreadsheets Data API and show you how to integrate and use spreadsheet data from the cloud with a PHP application. In this first article, I introduce the various feeds supported by the Data API and illustrate how you can read data from these feeds using the PHP client library.


Understanding Google Spreadsheets feeds

Before you get into the PHP code, you need to understand how the Google Spreadsheets Data API works. The API accepts HTTP requests that contain one or more input arguments and returns an Atom feed that contains the requested information.

The Google Spreadsheets Data API makes a number of different feeds available. Note that for some of these feeds, you need the spreadsheet ID (ssid) or worksheet ID (wsid) to formulate the feed URL.

  • The spreadsheets feed contains a list of spreadsheets available to the user. It is accessible at the URL: https://spreadsheets.google.com/feeds/spreadsheets/private/full
  • The worksheets feed is tied to a specific spreadsheet and contains a list of worksheets in that spreadsheet. It is accessible at a URL such as: https://spreadsheets.google.com/feeds/worksheets/<ssid>/private/full
  • The list and cell feeds are tied to a specific worksheet and contain the rows and cells that make up that worksheet. They are accessible at URLs such as: https://spreadsheets.google.com/feeds/list/<ssid>/<wsid>/private/full

The spreadsheets feed is available only to authenticated users. Spreadsheet feeds, such as worksheet, list, and cell feeds, might even be accessed without authentication, depending on the security settings of the corresponding spreadsheet. For example, it is possible to obtain a list or cell feed for spreadsheets that are publicly visible, without authentication.

Listing 1 demonstrates what a typical spreadsheet feed looks like.

Listing 1. An example spreadsheet feed
<?xml version='1.0' encoding='UTF-8'?>
<feed xmlns='http://www.w3.org/2005/Atom' 
  xmlns:openSearch='http://a9.com/-/spec/opensearchrss/1.0/'>
  <id>https://spreadsheets.google.com/feeds/spreadsheets/private/full</id>
  <updated>2010-11-05T17:11:33.703Z</updated>
  <category scheme='http://schemas.google.com/spreadsheets/2006'
    term='http://schemas.google.com/spreadsheets/2006#spreadsheet'/>
  <title type='text'>Available Spreadsheets - someuser@gmail.com
  </title>
  <link rel='alternate' type='text/html' href='http://docs.google.com'/>
  <link rel='http://schemas.google.com/g/2005#feed' type='application/atom+xml'
    href='https://spreadsheets.google.com/feeds/spreadsheets/private/full'/>
  <link rel='self' type='application/atom+xml'
    href='https://spreadsheets.google.com/feeds/spreadsheets/private/full'/>
  <openSearch:totalResults>3</openSearch:totalResults>
  <openSearch:startIndex>1</openSearch:startIndex>
  <entry>
    <id>https://spreadsheets.google.com/feeds/spreadsheets/private/full/ssid</id>
    <updated>2010-11-04T07:24:42.388Z</updated>
    <category scheme='http://schemas.google.com/spreadsheets/2006'
      term='http://schemas.google.com/spreadsheets/2006#spreadsheet'/>
    <title type='text'>Overtime Hours</title>
    <content type='text'>Overtime Hours</content>
    <link rel='http://schemas.google.com/spreadsheets/2006#worksheetsfeed' 
      type='application/atom+xml' 
      href='https://spreadsheets.google.com/feeds/worksheets/ssid/private/full'/>
    <link rel='alternate' type='text/html' 
      href='https://spreadsheets.google.com/ccc?key'/>
    <link rel='self' type='application/atom+xml'
      href='https://spreadsheets.google.com/feeds/spreadsheets/private/full/ssid'/>
    <author>
      <name>someuser</name>
      <email>someuser@gmail.com</email>
    </author>
  </entry>
  <entry>
  ...
  </entry>
</feed>

Every spreadsheet feed opens with a <feed> element as the root element. The <feed> element encloses one or more <entry> elements, each representing a different spreadsheet. Each <entry> contains further detail, including the title, last update date, and author of each spreadsheet.

Notice that each <entry> also contains a <link rel='http://schemas.google.com/spreadsheets/2006#worksheetsfeed' ...> element. This element specifies the URL to the worksheet feed for the spreadsheet and provides a way to drill down to the next level of detail. Requesting this URL generates a worksheet feed that contains information on the worksheets within the selected spreadsheet. Listing 2 has an example of one such worksheet feed.

Listing 2. An example worksheet feed
<?xml version='1.0' encoding='UTF-8'?>
<feed xmlns='http://www.w3.org/2005/Atom' 
  xmlns:openSearch='http://a9.com/-/spec/opensearchrss/1.0/' 
  xmlns:gs='http://schemas.google.com/spreadsheets/2006'>
  <id>https://spreadsheets.google.com/feeds/worksheets/ssid/private/full</id>
  <updated>2010-11-04T07:23:37.513Z</updated>
  <category scheme='http://schemas.google.com/spreadsheets/2006'
    term='http://schemas.google.com/spreadsheets/2006#worksheet'/>
  <title type='text'>Estimate: Home Renovation</title>
  <link rel='alternate' type='text/html' href='https://spreadsheets.google.com/ccc
    ?key=ssid'/>
  <link rel='http://schemas.google.com/g/2005#feed' type='application/atom+xml'
    href='https://spreadsheets.google.com/feeds/worksheets/ssid/private/full'/>
  <link rel='http://schemas.google.com/g/2005#post' type='application/atom+xml'
    href='https://spreadsheets.google.com/feeds/worksheets/ssid/private/full'/>
  <link rel='self' type='application/atom+xml'
    href='https://spreadsheets.google.com/feeds/worksheets/ssid/private/full'/>
  <author>
    <name>vikram.melonfire</name>
    <email>someuser@gmail.com</email>
  </author>
  <openSearch:totalResults>7</openSearch:totalResults>
  <openSearch:startIndex>1</openSearch:startIndex>
  <entry>
    <id>https://spreadsheets.google.com/feeds/worksheets/ssid/private/full/wsid
    </id>
    <updated>2010-11-04T07:22:22.440Z</updated>
    <category scheme='http://schemas.google.com/spreadsheets/2006'
      term='http://schemas.google.com/spreadsheets/2006#worksheet'/>
    <title type='text'>Living Room</title>
    <content type='text'>Living Room</content>
    <link rel='http://schemas.google.com/spreadsheets/2006#listfeed' 
      type='application/atom+xml' href='https://spreadsheets.google.com/feeds/list/ssid/
      wsid/private/full'/>
    <link rel='http://schemas.google.com/spreadsheets/2006#cellsfeed' 
      type='application/atom+xml' href='https://spreadsheets.google.com/feeds/cells/ssid/
      wsid/private/full'/>
    <link rel='http://schemas.google.com/visualization/2008#visualizationApi' 
      type='application/atom+xml' href='https://spreadsheets.google.com/tq?
      key=ssid&sheet=wsid'/>
    <link rel='self' type='application/atom+xml'
      href='https://spreadsheets.google.com/feeds/worksheets/ssid/private/full/wsid'/>
    <link rel='edit' type='application/atom+xml'
      href='https://spreadsheets.google.com/feeds/worksheets/ssid/private/full/wsid/key'
    />
    <gs:rowCount>100</gs:rowCount>
    <gs:colCount>20</gs:colCount>
  </entry>
  <entry>
  </entry>
</feed>

Within the worksheet feed, each <entry> contains a set of <link> elements. These elements specify the URLs to the list feed of the worksheet, cell feed, visualization endpoint, and edit endpoint. They are useful if you need to view or edit the information in the worksheet. You can see examples of the list and cell feeds later in this article.


Retrieving spreadsheets

Google Spreadsheets feeds are encoded in XML, and it's quite easy to integrate them with a PHP web application. One method is to use the SimpleXML extension in PHP to parse the Atom feed returned by the API and extract relevant information from it. But why do that, when the Zend Framework includes Zend_Gdata, a PHP client library designed specifically for developers trying to integrate PHP applications with the Google Data API? This library, which you can download separately (see Resources for a link), provides a convenient, object-oriented interface to the Google Data API, encapsulating most common tasks, including authentication, and thus leaving you free to focus on the core functions of your application.

After you install Zend's GData library, you can proceed to an example of processing a Google Spreadsheets Data API feed using PHP. Listing 3 takes the feed from Listing 1 and uses Zend_Gdata to extract relevant fragments of data from it and format it into a web page. Note that you need to update the user credentials in the script with valid values before you execute it.

Listing 3. Listing spreadsheets
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Listing spreadsheets</title>
    <style>
    body {
      font-family: Verdana;      
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {  
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get list of available spreadsheets
      $feed = $service->getSpreadsheetFeed();
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  
    <h2><?php echo $feed->title; ?></h2>
    <div>
    <?php echo $feed->totalResults; ?> spreadsheet(s) found.
    </div>  

    <ul>
    <?php foreach($feed as $entry): ?>
      <li class="name"><?php echo $entry->getTitle(); ?></li>
    <?php endforeach; ?>
    </ul>

  </body>
<html>

Listing 3 first loads the Zend class libraries and then initializes an instance of the Zend_Gdata service class. This class makes use of a Zend_Http_Client object, which is provided with the necessary user authentication information and opens an authenticated connection to the Google Spreadsheets service. After an authenticated connection is opened, the getSpreadsheetFeed() method retrieves the spreadsheet feed.

The response to the getSpreadsheetFeed() API call is an Atom feed similar to that in Listing 1, which is then parsed and converted into an array of Zend_Gdata_Spreadsheets_SpreadsheetEntry objects, each representing one <entry> in the feed. It's now possible to iterate over the array, printing the title of each spreadsheet by invoking the corresponding entry object's getTitle() method.

Figure 1 illustrates what the output looks like, with a list of three available spreadsheets (Overtime Hours, Estimate: Home Renovation, and Sales by Region).

Figure 1. A list of user spreadsheets
Screen capture of a list of user spreadsheets (Overtime Hours, Estimate: Home Renovation, and Sales by Region)

Retrieving worksheets

Each Zend_Gdata_Spreadsheets_SpreadsheetEntry object also exposes a getWorksheets() method, which accesses the spreadsheet's worksheets feed and returns a list of worksheets as Zend_Gdata_Spreadsheets_WorksheetEntry objects. Take a look at Listing 4, which revises Listing 3 and lists the worksheets within a spreadsheet.

Listing 4. Listing spreadsheets and worksheets
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Listing spreadsheets and worksheets</title>
    <style>
    body {
      font-family: Verdana;      
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {  
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get list of available spreadsheets
      $feed = $service->getSpreadsheetFeed();
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>

    <h2><?php echo $feed->title; ?></h2>
    <div>
    <?php echo $feed->totalResults; ?> spreadsheet(s) found.
    </div>  

    <ul>
    <?php foreach($feed as $entry): ?>
      <li class="name"><?php echo $entry->getTitle();  ?></li>
      <ul>
      <?php foreach($entry->getWorksheets() as $ws): ?>
        <li class="name"><?php echo $ws->getTitle(); ?></li>      
      <?php endforeach; ?>
      </ul>
    <?php endforeach; ?>
    </ul>

  </body>
<html>

Notice the call to the getWorksheets() method of each entry object. This method requests the worksheet feed for each spreadsheet and converts the result into a collection of Zend_Gdata_Spreadsheets_WorksheetEntry objects. As before, it's easy to iterate over this collection and display the title of each worksheet entry using its getTitle() method. (View a text-only version of Figure 2.)

Figure 2 illustrates the revised output.

Figure 2. A list of user spreadsheets and worksheets
Screen capture of a list of user spreadsheets and worksheets

Retrieving worksheet rows

Now that you know how to get spreadsheets and worksheets, consider how you can retrieve the actual contents of a worksheet. The Google Spreadsheets Data API offers two ways to do this:

  • The list feed, which returns a feed containing the rows of a worksheet
  • The cell feed, which returns a feed containing the individual cells of a worksheet

To illustrate, consider Figure 3, which shows an example worksheet for the Sales by Region spreadsheet. (The spreadsheet shows the sectors and lists sales for each section by units and dollars.)

Figure 3. The example worksheet used in this article
Screen capture of the example worksheet used in this article

Listing 5 shows the corresponding list feed for this worksheet.

Listing 5. An example list feed
<?xml version='1.0' encoding='UTF-8'?>
<feed xmlns='http://www.w3.org/2005/Atom' 
  xmlns:openSearch='http://a9.com/-/spec/opensearchrss/1.0/'
  xmlns:gsx='http://schemas.google.com/spreadsheets/2006/extended'>
  <id>https://spreadsheets.google.com/feeds/list/ssid/wsid/private/full</id>
  <updated>2010-11-04T07:21:19.158Z</updated>
  <category scheme='http://schemas.google.com/spreadsheets/2006'
    term='http://schemas.google.com/spreadsheets/2006#list'/>
  <title type='text'>North</title>
  <link rel='alternate' type='text/html' 
    href='https://spreadsheets.google.com/ccc?key=ssid'/>
  <link rel='http://schemas.google.com/g/2005#feed' type='application/atom+xml'
    href='https://spreadsheets.google.com/feeds/list/ssid/wsid/private/full'/>
  <link rel='http://schemas.google.com/g/2005#post' type='application/atom+xml'
    href='https://spreadsheets.google.com/feeds/list/ssid/wsid/private/full'/>
  <link rel='self' type='application/atom+xml'
    href='https://spreadsheets.google.com/feeds/list/ssid/wsid/private/full'/>
  <author>
    <name>someuser</name>
    <email>someuser@gmail.com</email>
  </author>
  <openSearch:totalResults>3</openSearch:totalResults>
  <openSearch:startIndex>1</openSearch:startIndex>
  <entry>
    <id>https://spreadsheets.google.com/feeds/list/ssid/wsid/private/full/cokwr
    </id>
    <updated>2010-11-04T07:21:19.158Z</updated>
    <category scheme='http://schemas.google.com/spreadsheets/2006'
      term='http://schemas.google.com/spreadsheets/2006#list'/>
    <title type='text'>Sector 1</title>
    <content type='text'>salesunits: 63274, sales: 2214590</content>
    <link rel='self' type='application/atom+xml'
      href='https://spreadsheets.google.com/feeds/list/ssid/wsid/private/full/cokwr'/>
    <link rel='edit' type='application/atom+xml'
      href='https://spreadsheets.google.com/feeds/list/ssid/wsid/private/full/cokwr/
      2ed6e01i15gc7'/>
    <gsx:name>Sector 1</gsx:name>
    <gsx:salesunits>63274</gsx:salesunits>
    <gsx:sales>2214590</gsx:sales>
  </entry>
  <entry>
    <id>https://spreadsheets.google.com/feeds/list/ssid/wsid/private/full/cpzh4
    </id>
    <updated>2010-11-04T07:21:19.158Z</updated>
    <category scheme='http://schemas.google.com/spreadsheets/2006'
      term='http://schemas.google.com/spreadsheets/2006#list'/>
    <title type='text'>Sector 2</title>
    <content type='text'>salesunits: 7263, sales: 254205</content>
    <link rel='self' type='application/atom+xml'
      href='https://spreadsheets.google.com/feeds/list/ssid/wsid/private/full/cpzh4'/>
    <link rel='edit' type='application/atom+xml'
      href='https://spreadsheets.google.com/feeds/list/ssid/wsid/private/full/
      cpzh4/2ed58j9il57e0'/>
    <gsx:name>Sector 2</gsx:name>
    <gsx:salesunits>7263</gsx:salesunits>
    <gsx:sales>254205</gsx:sales>
  </entry>
  <entry>
  ...
  </entry>
</feed>

Listing 6 shows the cell feed for this worksheet.

Listing 6. An example cell feed
<?xml version='1.0' encoding='UTF-8'?>
<feed xmlns='http://www.w3.org/2005/Atom' 
  xmlns:openSearch='http://a9.com/-/spec/opensearchrss/1.0/' 
  xmlns:gs='http://schemas.google.com/spreadsheets/2006' 
  xmlns:batch='http://schemas.google.com/gdata/batch'>
  <id>https://spreadsheets.google.com/feeds/cells/ssid/wsid/private/full</id>
  <updated>2010-11-04T07:20:36.606Z</updated>
  <category scheme='http://schemas.google.com/spreadsheets/2006'
    term='http://schemas.google.com/spreadsheets/2006#cell'/>
  <title type='text'>North</title>
  <link rel='alternate' type='text/html' 
    href='https://spreadsheets.google.com/ccc?key=ssid'/>
  <link rel='http://schemas.google.com/g/2005#feed' type='application/atom+xml'
    href='https://spreadsheets.google.com/feeds/cells/ssid/wsid/private/full'/>
  <link rel='http://schemas.google.com/g/2005#post' type='application/atom+xml'
    href='https://spreadsheets.google.com/feeds/cells/ssid/wsid/private/full'/>
  <link rel='http://schemas.google.com/g/2005#batch' type='application/atom+xml'
    href='https://spreadsheets.google.com/feeds/cells/ssid/wsid/private/full/batch'/>
  <link rel='self' type='application/atom+xml'
    href='https://spreadsheets.google.com/feeds/cells/ssid/wsid/private/full'/>
  <author>
    <name>vikram.melonfire</name>
    <email>someuser@gmail.com</email>
  </author>
  <openSearch:totalResults>12</openSearch:totalResults>
  <openSearch:startIndex>1</openSearch:startIndex>
  <gs:rowCount>100</gs:rowCount>
  <gs:colCount>20</gs:colCount>
  <entry>
    <id>https://spreadsheets.google.com/feeds/cells/ssid/wsid/private/full/R1C1
    </id>
    <updated>2010-11-04T07:20:36.606Z</updated>
    <category scheme='http://schemas.google.com/spreadsheets/2006'
      term='http://schemas.google.com/spreadsheets/2006#cell'/>
    <title type='text'>A1</title>
    <content type='text'>Name</content>
    <link rel='self' type='application/atom+xml'
      href='https://spreadsheets.google.com/feeds/cells/ssid/wsid/private/full/R1C1'/>
    <link rel='edit' type='application/atom+xml'
      href='https://spreadsheets.google.com/feeds/cells/ssid/wsid/private/full/R1C1/1fvl7'
    />
    <gs:cell row='1' col='1' inputValue='Name'>Name</gs:cell>
  </entry>
  <entry>
    <id>https://spreadsheets.google.com/feeds/cells/ssid/wsid/private/full/R1C2
    </id>
    <updated>2010-11-04T07:20:36.606Z</updated>
    <category scheme='http://schemas.google.com/spreadsheets/2006'
      term='http://schemas.google.com/spreadsheets/2006#cell'/>
    <title type='text'>B1</title>
    <content type='text'>Sales (Units)</content>
    <link rel='self' type='application/atom+xml'
      href='https://spreadsheets.google.com/feeds/cells/ssid/wsid/private/full/R1C2'/>
    <link rel='edit' type='application/atom+xml'
      href='https://spreadsheets.google.com/feeds/cells/ssid/wsid/private/full/R1C2
      /rkxyni'/>
    <gs:cell row='1' col='2' inputValue='Sales (Units)'>Sales (Units)</gs:cell>
  </entry>
  <entry>
  ...
  </entry>
</feed>

The difference between Listing 5 and Listing 6 should be quite clear. Listing 5 represents each row of the worksheet as an entry, while Listing 6 represents each cell of the worksheet as an entry. Depending on how you want to process the data from the worksheet, you need to select the appropriate feed for your purposes.

Listing 7 illustrates how you can retrieve and process a list feed using Zend_Gdata. Note that for this, and subsequent, listings, you need to update the script with a spreadsheet or worksheet ID, or both, by replacing the ssid and wsid variables in the listing code with actual values.

Listing 7. Processing a list feed
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Listing worksheet contents</title>
    <style>
    body {
      font-family: Verdana;      
    }
    div.sheet {
      margin: 10px;
      padding: 10px;
      border: silver 2px dotted;
    }
    div.name {
      color: red; 
      text-decoration: none;
      font-weight: bolder;  
    }    
    table, td {
      border: 1px solid black;
      vertical-align: top;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {  
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get spreadsheet entry
      $ssEntry = $service->getSpreadsheetEntry(
        'https://spreadsheets.google.com/feeds/spreadsheets/ssid');
      
      // get worksheets in this spreadsheet
      $wsFeed = $ssEntry->getWorksheets();
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }    
    ?>

    <h2><?php echo $ssEntry->title; ?></h2>

    <?php foreach($wsFeed as $wsEntry): ?>
    <div class="sheet">
      <div class="name">Worksheet: 
        <?php echo $wsEntry->getTitle(); ?></div>
      <table>
      <?php 
      // get list feed for worksheet
      // get individual entries in list
      $listFeed = $service->getListFeed($wsEntry); 
      foreach ($listFeed as $listEntry) {
        echo '<tr>';
        $rowData = $listEntry->getCustom();
        foreach($rowData as $customEntry) {
          echo '<td>' .  $customEntry->getText() . '</td>';
        }
        echo '</tr>';
      }
      ?>
      </table>
    </div>
    <?php endforeach; ?>

  </body>
<html>

Listing 7 uses the getWorksheets() method explained in Listing 4 to retrieve all the worksheets of the named spreadsheet, as a collection of Zend_Gdata_Spreadsheets_WorksheetEntry objects. The getListFeed() method of the service object is then passed these entry objects, to retrieve the list feed corresponding to each worksheet.

Rows of a worksheet are represented as Zend_Gdata_Spreadsheets_ListEntry objects, each of which exposes a getCustom() method. This method returns the individual elements (cells) of each row as an array. Thereafter, it's quite easy to iterate over this array, printing the contents of each cell in a table.

Figure 4 illustrates the results with individual Worksheets (North, South, East) that list sales units and dollars for individual sectors.

Figure 4. Worksheet contents, retrieved from a list feed
Screen capture of worksheet contents, retrieved from a list feed

Retrieving worksheet cells

Just as you can get a list feed using the getListFeed() method of a service object, you also can get a cell feed using the object getCellFeed() method. Listing 8 illustrates.

Listing 8. Processing a cell feed
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Listing worksheet contents</title>
    <style>
    body {
      font-family: Verdana;      
    }
    div.sheet {
      margin: 10px;
      padding: 10px;
      border: silver 2px dotted;
    }
    div.name {
      color: red; 
      text-decoration: none;
      font-weight: bolder;  
    }    

    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {  
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get spreadsheet entry
      $ssEntry = $service->getSpreadsheetEntry(
        'https://spreadsheets.google.com/feeds/spreadsheets/ssid');

      // get worksheets in this spreadsheet
      $wsFeed = $ssEntry->getWorksheets();
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }    
    ?>
  
    <h2><?php echo $ssEntry->title; ?></h2>

    <?php foreach($wsFeed as $wsEntry): ?>
    <div class="sheet">
      <div class="name">Worksheet: 
        <?php echo $wsEntry->getTitle(); ?></div>
      <table>
      <?php 
      // get cell feed for worksheet
      // display individual rows and columns from cell feed
      $cellFeed = $service->getCellFeed($wsEntry); 
      echo '<ul>';
      foreach ($cellFeed as $cellEntry) {
        $row = $cellEntry->getCell()->getRow();
        $column = $cellEntry->getCell()->getColumn();
        $value = $cellEntry->getCell()->getText();
        echo "<li>($row, $column) = $value</li>";
      }
      echo '</ul>';
      ?>
      </table>
    </div>
    <?php endforeach; ?>

  </body>
<html>

Listing 8 operates on a cell feed, and so the method of extracting data from it differs slightly from that in Listing 7. Consider that in Listing 8, the individual cells of a worksheet are represented as Zend_Gdata_Spreadsheets_CellEntry objects, each of which exposes a getCell() method that returns a Cell object. This cell object, in turn, exposes methods to return the row number, column number, and value of the corresponding cell. It's now quite easy to iterate over the collection and print the contents of each cell in a list.

Figure 5 illustrates what the output looks like.

Figure 5. Worksheet contents, retrieved from a cell feed
Screen capture of worksheet contents, retrieved from a cell feed

Retrieving worksheet data as arrays

Zend_Gdata_Spreadsheets also comes with some shortcut methods that convert list and cell feeds into native PHP arrays for easier processing. For example, the getContentsAsRows() method retrieves the list feed of a worksheet and converts it into a nested PHP array. Listing 9 illustrates.

Listing 9. Processing an array of worksheet rows
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Listing worksheet contents</title>
    <style>
    body {
      font-family: Verdana;      
    }
    div.sheet {
      margin: 10px;
      padding: 10px;
      border: silver 2px dotted;
    }
    div.name {
      color: red; 
      text-decoration: none;
      font-weight: bolder;  
    }    
    table, td {
      border: 1px solid black;
      vertical-align: top;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {  
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get spreadsheet entry
      $ssEntry = $service->getSpreadsheetEntry(
        'https://spreadsheets.google.com/feeds/spreadsheets/ssid');
      
      // get worksheets in this spreadsheet
      $wsFeed = $ssEntry->getWorksheets();
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  
    <h2><?php echo $ssEntry->title; ?></h2>

    <?php foreach($wsFeed as $wsEntry): ?>
    <div class="sheet">
      <div class="name">Worksheet: 
        <?php echo $wsEntry->getTitle(); ?></div>
      <?php $rows = $wsEntry->getContentsAsRows(); ?>
      <table>
      <?php foreach ($rows as $row): ?>
        <tr>
          <?php foreach($row as $key => $value): ?>
          <td><?php echo $value; ?></td>
          <?php endforeach; ?>
        </tr>
      <?php endforeach; ?>
      </table>
    </div>
    <?php endforeach; ?>

  </body>
<html>

Listing 9 iterates over the collection of Zend_Gdata_Spreadsheets_WorksheetEntry objects returned by the getWorksheets() method and invokes the getContentsAsRows() method of each entry object to retrieve the content of each worksheet row as a nested array. Each row is represented as an associative array of key-value pairs.

Figure 6 displays the output.

Figure 6. Worksheet contents, retrieved from a list feed array
Screen capture of worksheet contents, retrieved from a list feed array

If cell data is more prevalent in your work, replace the call to getContentsAsRows() with a call to getContentsAsCells(), as in Listing 10. This method returns a nested array of cell data, with each cell indexed by its row and column coordinates. The formula and final value for each cell are also included in the nested array.

Listing 10. Processing an array of worksheet cells
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Listing spreadsheet contents</title>
    <style>
    body {
      font-family: Verdana;      
    }
    div.sheet {
      margin: 10px;
      padding: 10px;
      border: silver 2px dotted;
    }
    div.name {
      color: red; 
      text-decoration: none;
      font-weight: bolder;  
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {  
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get spreadsheet entry
      $ssEntry = $service->getSpreadsheetEntry(
        'https://spreadsheets.google.com/feeds/spreadsheets/ssid');

      // get worksheets in this spreadsheet
      $wsFeed = $ssEntry->getWorksheets();
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  
    <h2><?php echo $ssEntry->title; ?></h2>

    <?php foreach($wsFeed as $wsEntry): ?>
    <div class="sheet">
      <div class="name">Worksheet: 
        <?php echo $wsEntry->getTitle(); ?></div>
      <?php $cells = $wsEntry->getContentsAsCells(); ?>
      <ul>
      <?php foreach ($cells as $location => $data): ?>
        <li>
          <?php echo $location; ?> = <?php echo $data['value']; ?>
          <?php echo ($data['formula'] != $data['value']) ? ' *' : ''; ?>
        </li>
      <?php endforeach; ?>
    </div>
    <?php endforeach; ?>

  </body>
<html>

Figure 7 displays the data returned by Listing 10. Where a cell value is generated from a formula, that value is highlighted with an asterisk. (View a text-only version of Figure 7.)

Figure 7. Worksheet contents, retrieved from a cell feed array
Screen capture of worksheet contents, retrieved from a cell feed array

Filtering worksheet data

You also can filter the contents of a list feed or a cell feed, such that the feed returns only rows or cells matching specific criteria. To illustrate, consider Listing 11, which filters the list feed to return only those entries that show more than 25,000 units of sales.

Listing 11. Filtering a worksheet list feed
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Filtering worksheet contents</title>
    <style>
    body {
      font-family: Verdana;      
    }
    div.sheet {
      margin: 10px;
      padding: 10px;
      border: silver 2px dotted;
    }
    div.name {
      color: red; 
      text-decoration: none;
      font-weight: bolder;  
    }    
    table, td {
      border: 1px solid black;
      vertical-align: top;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {  
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // define worksheet query
      // get list feed for query
      $query = new Zend_Gdata_Spreadsheets_ListQuery();
      $query->setSpreadsheetKey('ssid');
      $query->setWorksheetId('wsid');
      $query->setSpreadsheetQuery('salesunits > 25000');
      $listFeed = $service->getListFeed($query);  
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    } 
    ?>
  
    <div class="sheet">
      <div class="name">Worksheet: 
        <?php echo $listFeed->getTitle(); ?></div>
      <table>
      <?php 
      foreach ($listFeed as $listEntry) {
        echo '<tr>';
        $rowData = $listEntry->getCustom();
        foreach($rowData as $customEntry) {
          echo '<td>' .  $customEntry->getText() . '</td>';
        }
        echo '</tr>';
      }
      ?>
      </table>
    </div>

  </body>
<html>

Listing 11 initializes a Zend_Gdata_Spreadsheets_ListQuery object, which can be passed to the getListFeed() method of the service object as an additional argument to filter the data returned in the list feed. This object must be initialized with the three inputs:

  • The spreadsheet ID
  • The worksheet ID
  • The filter criteria

The result is a list feed containing only those worksheet rows that match the filter criteria.

Filter criteria are specified as the column name, followed by an equality, inequality, or Boolean operator, followed by the filter value. Zend_Gdata automatically URL-encodes the filter criteria when transmitting the query to the Data API server.

Figure 8 illustrates the result.

Figure 8. Worksheet contents, filtered by sales units
Screen capture of worksheet contents, filtered by sales units

In a similar vein, it's possible to restrict the data returned in a cell feed, by initializing a Zend_Gdata_Spreadsheets_CellQuery object and passing this object to the getCellFeed() method. Consider Listing 12, which demonstrates by retrieving only the header row of a specified worksheet:

Listing 12. Filtering the cell feed of a worksheet
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Filtering worksheet contents</title>
    <style>
    body {
      font-family: Verdana;
    }
    div.sheet {
      margin: 10px;
      padding: 10px;
      border: silver 2px dotted;
    }
    div.name {
      color: red; 
      text-decoration: none;
      font-weight: bolder;  
    }
    table, td {
      border: 1px solid black;
      vertical-align: top;
    }
    </style>
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {  
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // define worksheet query
      // get list feed for query
      $query = new Zend_Gdata_Spreadsheets_CellQuery();
      $query->setSpreadsheetKey('ssid');
      $query->setWorksheetId('wsid');
      $query->setMinRow(1);
      $query->setMaxRow(1);
      $cellFeed = $service->getCellFeed($query);
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>

    <div class="sheet">
      <div class="name">Worksheet: <?php echo $cellFeed->getTitle(); ?>
      </div>
      <table>
      <?php 
      echo '<tr>';
      foreach ($cellFeed as $cellEntry) {
        echo '<td>' .  $cellEntry->getCell()->getText() . '</td>';
      }
      echo '</tr>';
      ?>
      </table>
    </div>

  </body>
<html>

Listing 12 initializes a Zend_Gdata_Spreadsheets_CellQuery and then defines a range of cells that should be returned in the feed, using the query object's setMinRow(), setMaxRow(), setMinColumn(), and setMaxColumn() methods. The resulting feed contains only the cells in the specified range. Figure 9 illustrates the result.

Figure 9. Worksheet contents, filtered by row number
Screen capture of worksheet contents, filtered by row number

Example application: Browser-based spreadsheet viewer

Now that you know the basics of retrieving data from the Google Spreadsheets Data API, look at a practical example that puts it all in context. This next application uses a combination of PHP and JavaScript to present a data grid that reflects the contents of an online worksheet. It makes use of jQuery and jqGrid (see Resources for download links) to generate the data grid and PHP to connect to Google Spreadsheets, retrieve worksheet contents, and format them into an XML document suitable for consumption by jqGrid.

Listing 13 has the PHP code that connects to the Google Spreadsheets Data API, executes a query to get the list feed of a worksheet, and converts the resulting data into jqGrid's XML format.

Listing 13. Retrieving and reformatting the list feed of a worksheet
<?php
// get page number from request
$page = $_GET['page']; 
if (empty($page)) {
  $page = 1;
}

// load Zend Gdata libraries
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

// set credentials for ClientLogin authentication
$user = "someuser@gmail.com";
$pass = "somepass";

// set records per page
$recordsPerPage = 5;
$startIndex = (($page-1) * $recordsPerPage)+1;

try {
  // connect to API
  $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
  $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
  $service = new Zend_Gdata_Spreadsheets($client);

  // get spreadsheet entry
  $query = new Zend_Gdata_Spreadsheets_ListQuery();
  $query->setSpreadsheetKey('ssid');
  $query->setWorksheetId('wsid');
  $query->setStartIndex($startIndex);
  $query->setMaxResults($recordsPerPage);
  $listFeed = $service->getListFeed($query); 
  $totalRecords = (string)$listFeed->getTotalResults()+0; // cast to integer

  // send XML headers
  header("Content-type: text/xml"); 
  echo "<?xml version='1.0'?" . ">\n";

  // send summary information
  echo '<rows>'; 
  echo '<page>' . $page . '</page>';
  echo '<total>' . ceil($totalRecords/$recordsPerPage) . '</total>';
  echo '<records>' . $totalRecords . '</records>';

  // send records
  foreach ($listFeed as $listEntry) {
    echo '<row>';
    $rowData = $listEntry->getCustom();
    foreach($rowData as $customEntry) {
      echo '<cell>' .  $customEntry->getText() . '</cell>';
    }
    echo '</row>';
  }
  echo '</rows>';
} catch (Exception $e) {
  die('ERROR: ' . $e->getMessage());
}    
?>

Notice that Listing 13 implements a simple paging system, which expects a page number in the request URL. This page number is then used to calculate the slice of records to be returned for the current page, and the setStartIndex() and setMaxResults() methods take care of configuring the list feed accordingly.

Figure 10 illustrates the XML format returned by Listing 13. (View a text-only version of Figure 10.)

Figure 10. The XML output of Listing 13
Screen capture of the XML output of Listing 13

jqGrid reads this XML format and converts it to a data grid, using the code in Listing 14.

Listing 14. Displaying a spreadsheet as a client-side data grid
<html>
  <head>
    <link rel="stylesheet" type="text/css" media="screen"
      href="jquery-ui-1.8.6.custom.css" />
    <link rel="stylesheet" type="text/css" media="screen" href="ui.jqgrid.css" />
    <script src="jquery-1.4.2.min.js" type="text/javascript"></script>
    <script src="jquery.jqGrid.min.js" type="text/javascript"></script>  
    <script src="grid.locale-en.js" type="text/javascript"></script>
  </head>
  <body>
    <table id="list1">
    </table> 
    <div id="pager1">
    </div> 
    <script type="text/javascript">
    // code based on XML example at: http://www.trirand.com/blog/jqgrid/jqgrid.html
    jQuery().ready(function (){
      jQuery("#list1").jqGrid({
        url:'generate.php',
        datatype: "xml",
        colNames:
          ['Sector','Sales (Units)', 'Sales ($)'],
        colModel:
          [
            {name:'sector'},
            {name:'salesunits'},
            {name:'sales'},
          ],
        pager: jQuery('#pager1'),
        rowNum:5,
        autowidth: true,
        viewrecords: true,
      }).navGrid('#pager1',{edit:false,add:false,del:false})
      });
    </script>
  </body>
</html>

Figure 11 illustrates the resulting output (each sector of a sales region displays its sales by unit and dollars in the spreadsheet).

Figure 11. A client-side data grid generated from a Google Spreadsheets feed
Screen capture of a client-side data grid generated from a Google Spreadsheets feed

Conclusion

As these examples illustrate, the Google Spreadsheets Data API makes it possible to easily integrate data from a Google Spreadsheet directly into a PHP application. This article focused on retrieving data from Google Spreadsheets, as it introduced you to the basic feed types available and illustrated how to use them with Zend_Gdata. It explained how to retrieve a user's spreadsheets, examine each spreadsheet to retrieve a list of available worksheets, and then use list and cell feeds to access the actual contents of a spreadsheet. It also introduced you to the basics of data filtering, explaining how to display spreadsheet rows or cells matching specific criteria or range constraints.

That's not all, though. With the Google Spreadsheets Data API, you can view spreadsheet data from a PHP application and change it, by adding, editing, and deleting cells, rows, and worksheets. The second part of this article examines these aspects of the Google Spreadsheets Data API in detail, so be sure to check back for that article.

Resources

Learn

Get products and technologies

  • The Zend Framework: Download and build more secure, reliable, and modern Web 2.0 apps and web services with widely available APIs.
  • jQuery: Get jQuery, a fast and concise JavaScript Library that simplifies HTML document traversing, event handling, animating, and Ajax interactions for rapid web development.
  • jqGrid: Download and try this Ajax-enabled JavaScript control to represent and manipulate tabular data on the web.
  • IBM product evaluation versions: Download or explore the online trials in the IBM SOA Sandbox and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into XML on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=XML, Open source, Cloud computing
ArticleID=593983
ArticleTitle=Integrate Google Spreadsheets data with PHP, Part 1
publish-date=12142010