![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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.
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
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
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
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
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
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
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
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
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
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.
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() . ' |
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
Figure 11 shows the result in the Google Spreadsheets GUI.
Figure 11. A worksheet with updated cells
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
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
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.
Learn
- Part 1: Integrate Google
Spreadsheets data with PHP (Vikram Vaswani, developerWorks,December 2010): Google Spreadsheets is a well-known cloud-based spreadsheet application. Access and search data from online spreadsheets through the Google Spreadsheets Data API. Then use the data in a PHP application, as you search and retrieve different elements of spreadsheet content.
- Google Account: Register for your free account and get started.
- Developer's Guide and Reference Guide: Learn more about the Google Spreadsheets Data API.
- Google Data API authentication: Learn more about authentication methods and which one is best for your client.
- Google Apps APIs Help: Participate in discussions related to Google Data APIs development.
- The Google Apps Developer Blog: Track Google Spreadsheets news.
- The Zend_Gdata_Spreadsheets component: Read documentation on how your client applications can view and update Spreadsheets content from Google data API feeds.
- The Zend_Feed component: Read more about Zend_Feed functionality for consuming RSS and Atom feeds. Zend_Feed provides a natural syntax for accessing elements of feeds, feed attributes, and entry attributes
- More articles by this author (Vikram Vaswani, developerWorks, August 2007-current): Read articles about XML, additional Google APIs, and other technologies.
- XML area on developerWorks: Get the resources you need to advance your skills in the XML arena.
- My developerWorks: Personalize your developerWorks experience.
- IBM XML certification: Find out how you can become an IBM-Certified Developer in XML and related technologies.
- XML technical library: See the developerWorks XML Zone for a wide range of technical articles and tips, tutorials, standards, and IBM Redbooks. Also, read more XML tips.
- developerWorks technical events and webcasts: Stay current with technology in these sessions.
- developerWorks on Twitter: Join today to follow developerWorks tweets.
- developerWorks podcasts: Listen to interesting interviews and discussions for software developers.
- developerWorks on-demand demos: Watch demos ranging from product installation and setup for beginners to advanced functionality for experienced developers.
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.
- 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
- XML zone discussion forums: Participate in any of several XML-related discussions.
- The developerWorks community: Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.
Vikram 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.
Table of contents
Next steps from IBM
Learn more, join the cloud community, and get started with cloud computing tools from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
