Integrate Google Spreadsheets data with PHP, Part 2

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 add, modify, and delete 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

Also available in Chinese Japanese Portuguese

Introduction

Frequently used acronyms

  • API: Application program interface
  • DOM: Document Object Model
  • GUI: Graphical User Interface
  • PDO: PHP Data Objects
  • REST: Representational State Transfer
  • RSS: Really Simple Syndication
  • SQL:Structured Query Language
  • URL: Uniform Resource Locator
  • XML: Extensible Markup Language

In the first part of this article, I introduced you to the Google Spreadsheets Data API, a REST API that allows developers to easily build new applications around userspace spreadsheet data hosted in the Google Spreadsheets cloud. I explained the basics of spreadsheet, worksheet, list, and cell feeds, illustrating how you can use these feeds with the Zend Framework to quickly and efficiently pull spreadsheet content into a PHP application.

The Google Spreadsheets Data API, however, doesn't just support retrieving and searching spreadsheets. In this concluding part, I examine how you can use the Data API to remotely manipulate spreadsheet content, by adding, updating, and deleting rows, cells, and worksheets from a remote PHP application. The techniques outlined in this article are supplemented with two example applications that, respectively, read and import RSS feeds and SQL result sets into a Google Spreadsheet using the Data API.


Adding worksheets

Every spreadsheet contains one or more worksheets, and the Google Spreadsheets Data API allows you to programmatically access and manipulate these worksheets. To see how this works, head on over to the Google Spreadsheets service, log in, and create an empty spreadsheet. Note the spreadsheet key, which should be visible in the URL. Your empty spreadsheet resembles Figure 1.

Figure 1. A new, empty spreadsheet
Screen capture of a new, empty Google spreadsheet

To add a new worksheet to this spreadsheet, you need to generate a new worksheet entry in Atom format and then POST this entry to the worksheet URL of the specified spreadsheet. Consider Listing 1, which illustrates the process.

Listing 1. Adding a new 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>Creating 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 spreadsheet entry
      $ssEntry = $service->getSpreadsheetEntry(
        'https://spreadsheets.google.com/feeds/spreadsheets/ssid');

      // get worksheet feed for this spreadsheet
      $wsFeed = $service->getWorksheetFeed($ssEntry);

      // create new entry
      $doc  = new DOMDocument();
      $doc->formatOutput = true;
      $entry = $doc->createElement('atom:entry');
      $entry->setAttributeNS('http://www.w3.org/2000/xmlns/' ,
        'xmlns:atom', 'http://www.w3.org/2005/Atom');
      $entry->setAttributeNS('http://www.w3.org/2000/xmlns/' ,
        'xmlns:gs', 'http://schemas.google.com/spreadsheets/2006');
      $doc->appendChild($entry);

      // add title, row and column counts
      $title = $doc->createElement('atom:title', 'Jan 2011');
      $entry->appendChild($title);
      $rows = $doc->createElement('gs:rowCount', '10');
      $entry->appendChild($rows);
      $cols = $doc->createElement('gs:colCount', '10');
      $entry->appendChild($cols);

      // insert entry
      $entryResult = $service->insertEntry($doc->saveXML(), 
        $wsFeed->getLink('self')->getHref());
      echo 'The ID of the new worksheet entry is: ' . $entryResult->id;

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>

  </body>
<html>

It's important to note at the outset that the Zend_Gdata_Spreadsheets component doesn't (yet) provide native methods to add or update worksheets. It's still possible to do this. You must manually create the XML worksheet element and use the generic insertEntry() method to POST it to the correct URL.

Listing 1 first obtains a reference to the named spreadsheet entry and then obtains that entry's worksheet feed. Next, a DOMDocument object is created to represent the actual entry, and elements are added to it to specify the worksheet title, number of rows, and number of columns. Finally, the insertEntry() method sends the XML fragment to the URL of the worksheet feed, for the changes to be committed to the spreadsheet.

When you execute this script and flip back to the Google Spreadsheets GUI, you see a new worksheet inside your empty spreadsheet, as in Figure 2.

Figure 2. The spreadsheet with a new worksheet added
Screen capture of the spreadsheet with a new worksheet labeled 'Jan 2011'

Another approach to this task is to create an instance of a Zend_Gdata_Spreadsheets_WorksheetEntry object, set its properties, and then post this entry object to the worksheet feed URL. The end result is the same, but following this approach is somewhat simpler, as Zend_Gdata_Spreadsheets takes care of the details of generating the worksheet entry XML. Consider Listing 2, which demonstrates this alternative approach to produce output equivalent to Listing 1.

Listing 2. Adding a new 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>Creating 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 spreadsheet entry
      $ssEntry = $service->getSpreadsheetEntry(
        'https://spreadsheets.google.com/feeds/spreadsheets/ssid');

      // get worksheet feed for this spreadsheet
      $wsFeed = $service->getWorksheetFeed($ssEntry);

      // create new entry
      $wsEntry = new Zend_Gdata_Spreadsheets_WorksheetEntry();
      $title = new Zend_Gdata_App_Extension_Title('Jan 2011');
      $wsEntry->setTitle($title);
      $row = new Zend_Gdata_Spreadsheets_Extension_RowCount('10');
      $wsEntry->setRowCount($row);
      $col = new Zend_Gdata_Spreadsheets_Extension_ColCount('10');
      $wsEntry->setColumnCount($col);

      // insert entry
      $entryResult = $service->insertEntry($wsEntry, 
        $wsFeed->getLink('self')->getHref());
      echo 'The ID of the new worksheet entry is: ' . $entryResult->id;

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>

  </body>
<html>

Listing 2 creates Zend_Gdata_Spreadsheets_Extension objects for the worksheet title, row count, and column count and then attaches these objects to the base Zend_Gdata_Spreadsheets_WorksheetEntry object. This approach is both simpler to understand and less tedious to implement. The final entry object is then transmitted to the worksheet feed URL using the insertEntry() method, as described earlier.


Updating and deleting worksheets

In addition to adding worksheets, the Google Spreadsheets Data API also supports programmatically updating and removing worksheets. Both these operations require the user to retrieve the worksheet entry to be modified or deleted, make changes to the entry as required, and then transmit it back to the server for execution. The nature of the request—PUT or DELETE—determines whether the worksheet entry is updated or deleted.

To see this in action, consider Listing 3, which updates the title of the worksheet created in Listing 1. Note that for this and subsequent listings, it is necessary to have the worksheet key, which you can obtain from the spreadsheets feed or from the entry object returned by the insertEntry() method.

Listing 3. Updating 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>Updating worksheets</title>
    <style>
    body {
      font-family: Verdana;      
    }
    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 worksheet entry
      $query = new Zend_Gdata_Spreadsheets_DocumentQuery();
      $query->setSpreadsheetKey('ssid');
      $query->setWorksheetId('wsid');
      $wsEntry = $service->getWorksheetEntry($query);
      $title = new Zend_Gdata_App_Extension_Title('Feb 2012');
      $wsEntry->setTitle($title);

      // update entry
      $entryResult = $service->updateEntry($wsEntry, 
        $wsEntry->getLink('edit')->getHref());
      echo 'The ID of the updated worksheet entry is: ' . $entryResult->id;

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>

  </body>
<html>

Listing 3 uses the getWorksheetEntry() method seen in earlier examples to retrieve a specific worksheet entry by its unique identifier. The setTitle() method of the entry object then changes the worksheet title, and the revised entry is then transmitted back using the updateEnry() method. Note that the revision must be sent to the edit URL specified by the entry object.

Following this procedure's execution, you can see the revised worksheet in the Google Spreadsheets GUI in Figure 3.

Figure 3. The spreadsheet with the new worksheet updated
Screen capture of the spreadsheet with the name of the new worksheet updated to 'Feb 2012'

To delete a worksheet, simply invoke the delete() method of the corresponding worksheet entry. Listing 4 has an example of deleting the worksheet from Listing 3.

Listing 4. Deleting 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>Deleting 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 worksheet entry
      $query = new Zend_Gdata_Spreadsheets_DocumentQuery();
      $query->setSpreadsheetKey('ssid');
      $query->setWorksheetId('wsid');
      $wsEntry = $service->getWorksheetEntry($query);

      // delete entry
      $service->delete($wsEntry->getLink('edit')->getHref());
      echo 'The worksheet entry has been deleted';

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

As before, this script retrieves the worksheet entry and then invokes the delete() method to transmit a DELETE request to the entry's edit URL. This request is interpreted and executed by the Google API servers, resulting in the removal of the worksheet from the spreadsheet. This step is clearly visible in Figure 4, which shows the spreadsheet restored to its original state.

Figure 4. The spreadsheet after deleting the new worksheet
Screen capture of the spreadsheet after deleting the new worksheet

Adding worksheet rows

The Google Spreadsheets Data API also makes it possible to add rows to a worksheet, and this time, the Zend_Gdata_Spreadsheets component does have a native method to perform this operation. To add a row, create a new Zend_Gdata_Spreadsheets_ListEntry object representing the row, populate it with values, and then POST this object to the list feed URL. Listing 5 has an example.

Listing 5. Adding 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>Adding worksheet rows</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);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // create row content
      $row = array(
        "date" => "24-12-2010", 
        "task" => "Server reconfiguration", 
        "hours" => "3.5"
      );

      // insert new row
      $entryResult = $service->insertRow($row, $ssKey, $wsKey);
      echo 'The ID of the new row entry is: ' . $entryResult->id;
      
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

Listing 5 sets the target spreadsheet and worksheet and then creates an array of row data containing column-value mappings. This array is then passed to the insertRow() method, which creates the necessary entry object and posts it to the feed URL. Figure 5 displays the result in the Google Spreadsheets GUI. (View a larger version of Figure 5.)

Figure 5. The worksheet with new rows added
Screen capture of the spreadsheet with a new column heading and a new data row added

Note these important caveats when you add rows programmatically:

  • The target worksheet must already exist and must already have the fields of the header row defined.
  • The row array passed to insertRow() must include keys corresponding to columns in the target worksheet; merely providing values does not suffice and instead produces a namespace error.
  • Only those fields with keys matching header rows in the target worksheet are inserted.
  • Keys of the row array should not be uppercase, as this format usually generates a server error and row insertion fails.

Updating and deleting worksheet rows

You also can update and delete rows using the Data API. To do this, you must retrieve the row entry to be modified, make changes to it, and then transmit it back to the list feed URL. Whether the request is sent as a PUT or DELETE decides whether the row is modified or deleted from the worksheet.

Listing 6 illustrates the process of updating rows in a worksheet.

Listing 6. Updating 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>Updating worksheet rows</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);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // get rows matching query
      $query = new Zend_Gdata_Spreadsheets_ListQuery();
      $query->setSpreadsheetKey($ssKey);
      $query->setWorksheetId($wsKey);
      $query->setSpreadsheetQuery('salesunits > 25000')
      $listFeed = $service->getListFeed($query);

      // iterate over matching rows
      // increase sales units by 50%
      // write updated rows back to spreadsheet
      foreach ($listFeed as $listEntry) {
        $rowData = $listEntry->getCustom(); 
        $newRow = array();
        foreach($rowData as $field) {
          $newRow[$field->getColumnName()] = $field->getText();
          if ($field->getColumnName() == 'salesunits') {
            $newRow[$field->getColumnName()] = $field->getText()*1.5;
          }
        }
        $entryResult = $service->updateRow($listEntry, $newRow);
      }
      echo 'Rows successfully updated.';

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

Listing 6 creates a list query that returns all rows in the worksheet with sales units above a particular limit as a list feed. It then iterates over this feed, processing each row in turn and adjusting the value of certain cells in each row. The updated row is then written back to the spreadsheet through the updateRow() method.

Figure 6 illustrates the spreadsheet before and after the update. (View a larger version of Figure 6.)

Figure 6. The worksheet with some rows updated
Screen capture of the worksheet with some rows updated

Deleting rows follows a similar process, except that instead of calling the updateRow() method of the service object, you call the delete() method of the row entry. Listing 7 illustrates, by deleting the last row of a specified worksheet.

Listing 7. Deleting 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>Deleting worksheet rows</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);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // get list feed
      $query = new Zend_Gdata_Spreadsheets_ListQuery();
      $query->setSpreadsheetKey($ssKey);
      $query->setWorksheetId($wsKey);
      $listFeed = $service->getListFeed($query);  

      // get and delete last row
      $lastEntry = $listFeed->offsetGet($listFeed->count()-1);
      $lastEntry->delete(); 
      echo 'Row successfully deleted.';

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

Figure 7 illustrates the spreadsheet before and after the update. (View a larger version of Figure 7.)

Figure 7. The worksheet with a row deleted
Screen caputure of the worksheet with a row deleted

Example application: RSS converter

Now that you know how to add and delete rows, let's try building a simple application that demonstrates this in practice. You'll create an RSS-to-spreadsheet converter, which retrieves all the news stories from a remote RSS feed, extracts story metadata from the feed, and places it in spreadsheet rows and columns.

Before you begin, go back to the Google Spreadsheets GUI and create an empty spreadsheet with a header row corresponding to the fields in a typical RSS feed. The results should resemble Figure 8.

Figure 8. An empty worksheet
Screen capture of an empty worksheet with column headings and no data

The example feed in this case is the BBC's United Kingdom (UK) news feed, which contains brief descriptions of top UK news stories. With the Zend Framework's Zend_Feed component, it's quite easy to parse the news feed, extract information from it, and then write it to a Google Spreadsheet using the Data API. Listing 8 has the code.

Listing 8. Importing worksheet rows from an RSS 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>Importing worksheet rows from an RSS feed</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');
    Zend_Loader::loadClass('Zend_Feed_Rss');

    // 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);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // consume RSS feed
      // insert each channel item as a spreadsheet row
      // if large feed, increase PHP script execution time
      $channel = new Zend_Feed_Rss('http://feeds.bbci.co.uk/news/uk/rss.xml');
      foreach ($channel as $item) {
          $row = array();
          $row['title'] = $item->title();
          $row['description'] = $item->description();
          $row['link'] = $item->link();
          $row['date'] = $item->pubDate();
          $entryResult = $service->insertRow($row, $ssKey, $wsKey);
      }    

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

Parsing RSS feeds is very easy with the Zend_Feed component: Simply pass the RSS feed URL to the object constructor, and the constructor retrieves the feed, parses it, and converts it into a series of objects that can be iterated over. Listing 8 does just this, iterating over the items in the feed and creating an array for each. This array is then inserted into the spreadsheet as a row using the insertRow() method. The process continues until all the items in the RSS feed are processed.

Figure 9 shows what the result looks like.

Figure 9. A worksheet with rows from an RSS feed
Screen capture of a worksheet with data rows populated from an RSS feed

Note that the Data API doesn't currently allow batch insertions of row data. If the order of the rows is not important, however, you can consider sending requests in parallel, to expedite processing.


Modifying worksheet cells

Just as you can modify worksheet rows, you also can modify worksheet cells. As you might have guessed, to accomplish this modification, you send a revised entry to the cell feed of the worksheet, containing details of the cell to be updated.

The updateCell() method of the service object updates cells. This method accepts five arguments: the row index, the column index, the cell value, the spreadsheet key, and the worksheet key. Row and column indices begin at 1. Listing 9 illustrates how it works.

Listing 9. Updating 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>Updating worksheet cells</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);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // update cell at row 6, column 5
      $entry = $service->updateCell('6', '5', 'Hello, world', $ssKey, $wsKey);
      echo 'Updated cell ' . $entry->getTitle()->getText() . '
'; // clear cell at row 1, column 1 $entry = $service->updateCell('1', '1', '', $ssKey, $wsKey); echo 'Cleared cell ' . $entry->getTitle()->getText(); } catch (Exception $e) { die('ERROR: ' . $e->getMessage()); } ?> </body> <html>

The call to updateCell() returns a cell entry object, which can be parsed for specific information. In particular, notice that the title of the cell entry object returns the alphanumeric coordinates of the cell that was updated.

Figure 10 illustrates the output of the script.

Figure 10. The result of updating a cell
Screen capure of the result of updating a cell indicates: Updated cell E6, Cleared cell A1

Figure 11 shows the result in the Google Spreadsheets GUI.

Figure 11. A worksheet with updated cells
Screen capure of a worksheet with updated cells (E6 and A1)

Another approach is to use a cell query to return a specific cell, then update the cell using the setInputValue() method. Note that you can also input formulae, instead of static values. Listing 10 demonstrates.

Listing 10. Updating 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>Updating worksheet cells</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);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // get cell feed
      // restrict feed to single cell E4
      $query = new Zend_Gdata_Spreadsheets_CellQuery();
      $query->setSpreadsheetKey($ssKey);
      $query->setWorksheetId($wsKey);
      $query->setMinRow(4);      
      $query->setMaxRow(4);      
      $query->setMinCol(5);      
      $query->setMaxCol(5);      
      $cellFeed = $service->getCellFeed($query);  

      // get cell from query
      $cellEntry = $cellFeed->offsetGet(0); 

      // update cell value and save back to spreadsheet
      $cellEntry->getCell()->setInputValue('=B2');
      $service->updateEntry($cellEntry, $cellEntry->getLink('edit')->getHref());
      echo 'Updated cell ' . $cellEntry->getTitle()->getText();      
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

In this case, the cell value is set to a formula referencing another cell, and the revised entry is written back using the updateEntry() method of the service object.


Example application: Database importer

With all this information at hand, look at another example: importing spreadsheet data from a MySQL database. Consider that you have the database structure in Figure 12. (View a text-only version of Figure 12.)

Figure 12. An example database
Screen capture of an example database

It's quite easy to execute an SQL query and then create spreadsheet cells from each field of the query result set. Consider Listing 11, which has a working example.

Listing 11. Importing worksheet cells from a database
<!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>Importing worksheet cells from a database</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";

    // create PDO connection
    $dbh = new PDO('mysql:host=localhost;dbname=library', 'user', 'pass');
    $sql = "SELECT title, author FROM library";

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

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // get results from database
      // insert header row
      $rowCount = 1;
      $result = $dbh->query($sql, PDO::FETCH_ASSOC);
      for ($x=0; $x<$result->columnCount(); $x++) {
        $col = $result->getColumnMeta($x);
          $service->updateCell($rowCount, ($x+1), $col['name'], $ssKey, $wsKey);
      }
      $rowCount++;
      // insert each field of each row as a spreadsheet cell
      // if large result set, increase PHP script execution time
      foreach($result as $row) {
        $colCount=1;
        foreach ($row as $k=>$v) {        
          $service->updateCell($rowCount, $colCount, $v, $ssKey, $wsKey);
          $colCount++;
        }
        $rowCount++;
      }

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

Listing 11 includes the required classes and sets Data API credentials. It then creates a PDO connection to the MySQL database and formulates a query that returns a list of authors and titles. It also connects to the Spreadsheets Data API and creates an authenticated connection.

Next, the script uses the PDO getColumnMeta() method to return the column names of the result set, and it writes these column names to the spreadsheet as a header row, using the updateCell() method. It then proceeds to iterate through the result set, writing each field to the spreadsheet as a separate cell and jumping to the next row when done. In this manner, the contents of the entire result set are transferred to the spreadsheet.

Figure 13 demonstrates the result in the Google Spreadsheets GUI.

Figure 13. A Google spreadsheet generated from a database result set
Screen capture of a Google spreadsheet generated from a database result set in Figure 12

Conclusion

The Google Spreadsheets Data API makes it possible to modify data in the Google Spreadsheets cloud from a PHP application. Common tasks, such as adding, modifying, and deleting worksheets, rows, and cells, are all supported and can be performed using Zend_Gdata. This article provided examples of all of these tasks, together with two example applications: spreadsheet importers for RSS feeds and database query results.

As these examples illustrate, the Google Spreadsheets Data API is a powerful and flexible tool for developers looking to build creative new applications around spreadsheet data in the cloud. The Data API is currently under development, so expect even more interesting additions to it in the future.

Resources

Learn

Get products and technologies

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
ArticleID=600045
ArticleTitle=Integrate Google Spreadsheets data with PHP, Part 2
publish-date=12142010