The Google Spreadsheets Data API allows client applications to view and update Spreadsheets content in the form of Google Data API feeds. Your client application can request a list of a user's spreadsheets, edit or delete content in an existing Spreadsheets worksheet, and query the content in an existing Spreadsheets worksheet.
In addition to providing some background on the capabilities of the Spreadsheets Data API, this document provides examples for interacting with the API using the PHP Client Library. You can download the client library as a standalone release (distributed by Zend) or as part of the Zend Framework. For help setting up the client library, see the Getting Started Guide.
If you're interested in understanding more about the underlying protocol used by the PHP Client Library to interact with the Spreadsheets Data API, please see the protocol tab.
This document is intended for programmers who want to write client applications using the Zend Google Data API PHP client library that can interact with Google Spreadsheets.
For help setting up the client library, see the
Getting Started Guide. To use the Zend Google Data API PHP client library,
you'll need the Zend Framework 1.5+. See the
Zend Framework for further details.
After downloading
the framework, you'll find the sample explained in this guide in
the demos/Zend/Gdata subdirectory of the distribution.
Run the interactive command-line sample as follows:
php Spreadsheet-ClientLogin.php Please enter your email address [example: username@gmail.com]: myemail@gmail.com Please enter your password [example: mypassword]: mypassword
The email and password values are the
same credentials that you use to login to Google Spreadsheets.
The application will then automatically retrieve an enumerated list of all
the Spreadsheets in your account, from which you can then choose a worksheet to
operate on. Please refer to the run method of the example for more
details.
To include the examples in this document into your own code, you'll need to include the Zend Framework library in your include_path and use the following statements:
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Http_Client');
Zend_Loader::loadClass('Zend_Gdata');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
The Zend_Gdata_Spreadsheets
class represents a client connection (with optional authentication credentials) to a
Spreadsheets service. The general procedure for sending a query to a
service using the client library consists of the following steps:
|
Zend_Http_Client
instance with the appropriate authentication method and credentials.
This is done using the factory methods available in Zend_Gdata_ClientLogin and
Zend_Gdata_AuthSub. Zend_Gdata_Spreadsheets instance with
the Zend_Http_Client
instance.It is possible to create a new spreadsheet by uploading a spreadsheet file via the Google Documents List Data API. The Spreadsheets Data API does not currently provide a way to delete a spreadsheet. For testing purposes, you may create a spreadsheet manually or upload one.
The Zend Google Data API client library in general can be used to work with either public or private feeds. Public feeds do not require authentication and are read-only. The Spreadsheets API permits unauthenticated requests to obtain spreadsheet, worksheet and cells or list feed level contents on spreadsheets that have explicitly been set to public through the user-interface. Please refer to the reference guide for more information. Private feeds require that you authenticate to the Spreadsheets servers. This can be done via ClientLogin username/password authentication or AuthSub proxy authentication.
Please see the authentication documentation for more information on AuthSub and ClientLogin.
To use ClientLogin (also called "Authentication for Installed
Applications"), invoke the getHttpClient
method of Zend_Gdata_ClientLogin, specifying the username/email and password of the user on whose behalf your client is sending the query,
along with the name of the Spreadsheets service. Then, use the
HttpClient returned by this method to construct a Zend_Gdata_Spreadsheets
object. For example:
$authService = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
$httpClient = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $authService);
$gdClient = new Zend_Gdata_Spreadsheets($httpClient);
The Spreadsheet-ClientLogin demo application performs this authentication in the constructor which is called after credentials have been entered at the command line. For more information about authentication systems, see the Google
Account Authentication documentation.
AuthSub proxy authentication is used by web applications which need to authenticate their users to Google accounts. The operator does not need access to the username and password for the Spreadsheets user – only special AuthSub tokens are required. Please see the AuthSub documentation for more detailed information.
When the user first visits your application, they have not yet been authenticated. In this case, you need to print some text and and a link directing the user to Google to authenticate your request for access to their spreadsheets. The Zend Framework PHP Google Data API client library provides a function to generate this URL. The code below sets up a link to the AuthSubRequest page.
function getAuthSubUrl() {
$next = 'http://www.coolspreadsheetsite.com/welcome.php';
$scope = 'http://spreadsheets.google.com/feeds/';
$secure = false;
$session = true;
return Zend_Gdata_AuthSub::getAuthSubTokenUri($next, $scope, $secure, $session);
}
$authSubUrl = getAuthSubUrl();
echo '<a href="' . $authSubUrl . '">Click here to authenticate</a>';
Notice the parameters sent to the getAuthSubTokenUri
method:
The URL will look something like this:
https://www.google.com/accounts/AuthSubRequest?scope=http%3A%2F%2Fspreadsheets.google.com%2Ffeeds%2F&session=1&secure=0&next=http%3A%2F%2Fwww.coolspreadsheetsite.com%2Fwelcome.php
The user will then follow the link to Google's site and authenticate to their Google account.
After the user authenticates, they will be redirected back to the next
URL. The URL will have a single-use token value appended to it as a
query parameter. The URL the user will be redirected to will look
something like this:
http://www.coolspreadsheetsite.com/welcome.php?token=14a87fe98219731acd516
For security, this token is single-use only, so now we need to exchange this single-use token for a session token. The following code snippet shows how to upgrade the token.
$sessionToken = Zend_Gdata_AuthSub::getAuthSubSessionToken($_GET['token']);
In this snippet, the $_GET['token'] variable contains
the value from the token query parameter in the
URL. This token value represents a single-use AuthSub token.
Since we specified $session = true above, this token
can be exchanged for an AuthSub session token, as is being done above.
You can get a feed containing a list of the currently authenticated
user's spreadsheets by using the Zend_Gdata_Spreadsheets->getSpreadsheetFeed() method.
The result is a "meta-feed," a feed that lists all of that user's spreadsheets; each entry in the feed represents a spreadsheet associated with the user. This feed is accessible only when using an authenticated client.
The following method from the sample code retrieves the list of spreadsheets, prints out the names of the spreadsheets and prompts the user to select a spreadsheet from the list:
public function promptForSpreadsheet()
{
$feed = $this->gdClient->getSpreadsheetFeed();
print "== Available Spreadsheets ==\n";
$this->printFeed($feed);
$input = getInput("\nSelection");
$currKey = split('/', $feed->entries[$input]->id->text);
$this->currKey = $currKey[5];
}
Sample output:
== Available Spreadsheets == 0 Programming languages 1 Inventory 3/08 2 To-do list 4/08 3 other.spreadsheet
The resulting Zend_Gdata_Spreadsheets_SpreadsheetFeed
object $feed represents a response from the server. Among
other things, this feed contains a list of Zend_Gdata_Spreadsheets_SpreadsheetEntry
objects ($feed->entries), each of which represents a
single spreadsheet. Zend_Gdata_Spreadsheets_SpreadsheetEntry
encapsulates the information shown in the protocol
document.
The printFeed definition is as follows:
public function printFeed($feed)
{
$i = 0;
foreach($feed->entries as $entry) {
if ($entry instanceof Zend_Gdata_Spreadsheets_CellEntry) {
print $entry->title->text .' '. $entry->content->text . "\n";
} else if ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry) {
print $i .' '. $entry->title->text .' | '. $entry->content->text . "\n";
} else {
print $i .' '. $entry->title->text . "\n";
}
$i++;
}
}
The promptForSpreadsheet method, stores the key
of the spreadsheet at the selected index. The key is the
permanent unique ID of a specific spreadsheet. It is the last value in the path of the id
(corresponding to the <id> element in the spreadsheet entry)
attribute of the Zend_Gdata_Spreadsheets_SpreadsheetEntry
object.
These methods would be equivalent to performing a HTTP request for the following URL, parsing the response and outputting the value of the <title> elements.
http://spreadsheets.google.com/feeds/spreadsheets/private/full
A given spreadsheet may contain multiple worksheets. For each
spreadsheet, there's a worksheets metafeed listing all
the worksheets in that spreadsheet.
Given a key from the <id> of
Zend_Gdata_Spreadsheets_SpreadsheetEntry object you've
already retrieved, you can print a list of all worksheets
and prompt for a selection as follows:
public function promptForWorksheet()
{
$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
$query->setSpreadsheetKey($this->currKey);
$feed = $this->gdClient->getWorksheetFeed($query);
print "== Available Worksheets ==\n";
$this->printFeed($feed);
$input = getInput("\nSelection");
$currWkshtId = split('/', $feed->entries[$input]->id->text);
$this->currWkshtId = $currWkshtId[8];
}
As you can see the function above builds a Zend_Gdata_Spreadsheets_DocumentQuery and sets the $this->currKey (the current spreadsheet key) obtained earlier. The resulting Zend_Gdata_Spreadsheets_WorksheetFeed
object $feed represents a response from the server. Among
other things, this feed contains a list of
Zend_Gdata_Spreadsheets_WorksheetEntry
objects ($feed->entries), each of which represents a
single worksheet. Zend_Gdata_Spreadsheets_WorksheetEntry
encapsulates the information shown in the protocol
document.
Sample output:
== Available Worksheets == 0 Programming language links
The retrieval of the "meta-feed" is equivalent to sending an
authenticated GET request to the URL:
http://spreadsheets.google.com/feeds/worksheets/key/private/full
The promptForWorksheet function then stores the worksheetId of the worksheet at the selected index. The worksheetId is the permanent, unique ID of a specific worksheet within the spreadsheet. It is also the last value in the path of the id
(corresponding to the <id> element in the worksheet entry)
attribute of the Zend_Gdata_Spreadsheets_WorksheetEntry
object.
A given worksheet generally contains multiple rows, each containing multiple cells. You can request data from the worksheet either as a list-based feed, in which each entry represents a row, or as a cells-based feed, in which each entry represents a single cell. For information on cells-based feeds, see Interacting with cells-based feeds.
The following sections describe how to get a list-based feed, add a row to a worksheet, and send queries with various query parameters.
The list feed makes some assumptions about how the data is laid out in the spreadsheet. In particular, it treats the first row of the worksheet as a header row. The Spreadsheets API dynamically creates XML elements named after the contents of header-row cells. Users who want to provide Data API feeds should not put any data other than column headers in the first row of a worksheet. Please also note that the column headers will be converted into valid XML namespaces.
The list feed contains all rows after the first row up to the first blank row, which terminates the data set. If the expected data isn't appearing in a feed, check the worksheet manually to see whether there's an unexpected blank row in the middle of the data. The list feed will contain no data in the case where the second row of the spreadsheet is blank.
A row in a list feed is as many columns wide as the worksheet itself.
The demo application will ask the user to select either the list feed or the cells feed. After a selection has been made, a number of options will be presented to the user:
Select to use either the cell or the list feed [cells or list]: list
== Options ==
dump -- dump row information
insert {row_data} -- insert data in the next available cell in a given column (example: insert column_header=content)
update {row_index} {row_data} -- update data in the row provided (example: update row-number column-header=newdata
delete {row_index} -- delete a row
The demo application's promptForFeedtype function asks a user to select either the cells or the list feed. If the list feed is selected, as in the example above, the promptForListAction function is used to make requests to the list feed. By default this function presents a number of options (as shown in the output above). Based on the user's selection, one of the following functions is called:
listGetAction — to show the contents of the current worksheetlistInsertAction — to insert new datalistUpdateAction — to perform an update on existing datalistDeleteAction — to delete data
public function promptForListAction()
{
echo "\n== Options ==\n".
"dump -- dump row information\n".
"insert {row_data} -- insert data in the next available cell in a given column (example: insert column_header=content)\n".
"update {row_index} {row_data} -- update data in the row provided (example: update row-number column-header=newdata\n".
"delete {row_index} -- delete a row\n\n";
$input = getInput('Command');
$command = split(' ', $input);
if ($command[0] == 'dump') {
$this->listGetAction();
} else if ($command[0] == 'insert') {
$this->listInsertAction(array_slice($command, 1));
} else if ($command[0] == 'update') {
$this->listUpdateAction($command[1], array_slice($command, 2));
} else if ($command[0] == 'delete') {
$this->listDeleteAction($command[1]);
} else {
$this->invalidCommandError($input);
}
}
All of the functions below assume that we are using an authenticated Zend_Gdata_Spreadsheets object. To retrieve worksheet contents, the listGetAction function is called:
public function listGetAction()
{
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($this->currKey);
$query->setWorksheetId($this->currWkshtId);
$this->listFeed = $this->gdClient->getListFeed($query);
print "entry id | row-content in column A | column-header: cell-content\n".
"Please note: The 'dump' command on the list feed only dumps data until ".
"the first blank row is encountered.\n\n";
$this->printFeed($this->listFeed);
print "\n";
}
As you can see, we are using the key and worksheetId to build a Zend_Gdata_Spreadsheets_ListQuery which we then pass to the getListFeed method of our spreadsheet service object. The resulting Zend_Gdata_Spreadsheets_ListFeed contains an array of Zend_Gdata_Spreadsheets_ListEntry objects ($this->listFeed->entries), each of which represents a single row in a worksheet. The Zend_Gdata_Spreadsheets_ListEntry encapsulates the information shown in the protocol document. This code is equivalent to sending an authenticated GET request to the URL:
http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full
Sample output would be:
Command: dump entry id | row-content in column A | column-header: cell-content Please note: The 'dump' command on the list feed only dumps data until the first blank row is encountered. 0 java | website: http://java.com 1 php | website: http://php.net
Below is an excerpt of the XML that is returned by the request above:
<?xml version='1.0' encoding='UTF-8'?>
<feed xmlns='http://www.w3.org/2005/Atom' ...
...
<entry>
<id>http://spreadsheets.google.com/feeds/list/...</id>
<updated>2008-03-27T15:48:48.470Z</updated>
<category ... />
<title type='text'>java</title>
<content type='text'>website: http://java.com</content>
<link rel='self' ... />
<link rel='edit' ... />
<gsx:language>java</gsx:language>
<gsx:website>http://java.com</gsx:website>
</entry>
<entry>
...
<gsx:language>php</gsx:language>
<gsx:website>http://php.net</gsx:website>
</entry>
</feed>
By default, rows in the feed appear in the same order as the
corresponding rows in the GUI; that is, they're in order by row number.
To get rows in reverse order, set the reverse properties
of the Zend_Gdata_Spreadsheets_ListQuery object to true:
$query->reverse = 'true';
This is equivalent to sending an authenticated GET
request to the URL:
http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full?reverse=true
Note that if you want to order (or reverse sort) by a particular
column, rather than by position in the worksheet, you can set the orderby
value of the Zend_Gdata_Spreadsheets_ListQuery
object to column:<the
header of that column>. This is analogous to adding an orderby
query parameter in the outbound GET request.
You can set a Zend_Gdata_Spreadsheets_ListQuery's
sq value to produce a feed with entries that meet the specified criteria.
For example, suppose you have a worksheet containing personnel data, in
which each row represents information about a single person. You wish
to retrieve all rows in which the person's name is "John" and the
person's age is over 25. To do so, you would set sq as
follows:
$query->spreadsheetQuery = 'name=John and age>25';
This is equivalent to sending an authenticated GET
request to the URL:
http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full?sq=name%3DJohn%20and%20age%3E25
For more information about the structured query syntax, see the List-feed query parameters reference section.
To insert a new row in a list feed, assuming that the $this->gdClient
is an authenticated Zend_Gdata_Spreadsheets
object, $this->currKey is a valid unique ID for a spreadsheet that
the user has access to, and $this->currWkshtId is a
valid unique ID for a worksheet in the spreadsheet corresponding to the provided $this->currKey, our demo application uses the listInsertAction function which takes a $rowData parameter.
public function listInsertAction($rowData)
{
$rowArray = $this->stringToArray($rowData);
$entry = $this->gdClient->insertRow($rowArray, $this->currKey,
$this->currWkshtId);
if ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry) {
foreach ($rowArray as $column_header => $value) {
echo "Success! Inserted '$value' in column '$column_header' at row ".
substr($entry->getTitle()->getText(), 5) ."\n";
}
}
}
From the XML output above we know that the worksheet contains a column-header named language and another one named website. To insert information about the Ruby programming language we give the following command:
Command: insert language=ruby website=http://ruby-lang.org
Spreadsheets inserts the new row immediately after the last row that
appears in the list-based feed, which is to say immediately before the
first entirely blank row. This code is equivalent to sending an
authenticated POST request to the URL:
http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full
with the corresponding XML document in the POST body.
To update an existing row in a list feed, assuming that $gdClient
is an authenticated Zend_Gdata_Spreadsheets
object, $this->currKey is a valid unique ID for a
spreadsheet that
the user has access to, and $this->currWkshtId is a
valid unique ID
for a worksheet in the corresponding to the provided $this->currKey, our demo application uses the following function:
public function listUpdateAction($index, $rowData)
{
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($this->currKey);
$query->setWorksheetId($this->currWkshtId);
$this->listFeed = $this->gdClient->getListFeed($query);
$rowArray = $this->stringToArray($rowData);
$entry = $this->gdClient->updateRow($this->listFeed->entries[$index], $rowArray);
if ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry) {
echo "Success!\n"; $response = $entry->save();
}
}
The function takes an $index and a $rowData
parameter. The index determines the row to be updated and the
$rowData parameter works the same was as in the previous example.
Notice that the updateRow definition takes a
Zend_Gdata_Spreadsheets_SpreadsheetEntry
object which corresponds to an existing list entry, and an array where
the key corresponds to the column name and the value
corresponds to the value of that column in the new row.
This is equivalent to sending an authenticated PUT
request to the entry's edit link with the updated XML as PUT
data.
To delete a row, simply invoke deleteRow
on the Zend_Gdata_Spreadsheets object with the existing
entry to be deleted. Our demo application invokes that function using the $index for the row to be deleted:
public function listDeleteAction($index)
{
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($this->currKey);
$query->setWorksheetId($this->currWkshtId);
$this->listFeed = $this->gdClient->getListFeed($query);
$this->gdClient->deleteRow($this->listFeed->entries[$index]);
}
To delete the information we entered above, we can just use the following command:
Command: delete 2
To delete a row, perform a DELETE request on the edit URL after obtaining it as described above.
Note: If you delete a row, that row is completely removed from the worksheet.
In a cell-based feed, each entry represents a single cell.
Note that we don't recommend interacting with both a cell-based feed and a list-based feed for the same worksheet at the same time.
To retrieve a worksheet's cell-based feed, you might do the
following. Again, we are assuming that $this->gdClient
is an authenticated Zend_Gdata_Spreadsheets
object, $this->currKey is a valid unique ID for a
spreadsheet that
the user has access to, and $this->currWkshtId
is a valid unique ID
for a worksheet in the spreadsheet corresponding to the provided $this->currKey.
public function cellsGetAction()
{
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($this->currKey);
$query->setWorksheetId($this->currWkshtId);
$feed = $this->gdClient->getCellFeed($query);
$this->printFeed($feed);
}
As you can see the cellsGetAction function creates a Zend_Gdata_Spreadsheets_CellQuery object which is then passed to the getCellFeed method on the authenticated $gdClient object. As in the list Feed example above, the demo application provides a number of actions that can be performed with the cells feed.
Pick a command:
dump -- dump cell information
update {row} {col} {input_value} -- update cell information
To retrieve the content of the worksheet's cells feed, we can use the dump command, which uses the cellsGetAction function described above:
Command: dump A1 language B1 website A2 java B2 http://java.com A3 php B3 http://php.net
Relevant part of the returned XML for this response are shown below:
<?xml version='1.0' encoding='UTF-8'?>
<feed xmlns='http://www.w3.org/2005/Atom' ... />
<entry>
<id>http://spreadsheets.google.com/feeds/cells/.../R1C1</id>
<updated>2008-04-01T15:01:59.654Z</updated>
<category ... />
<title type='text'>A1</title>
<content type='text'>language</content>
<link rel='self' ... />
<link rel='edit' ... />
<gs:cell row='1' col='1' inputValue='language'>language</gs:cell>
</entry>
<entry>
...
<gs:cell row='1' col='2' inputValue='website'>website</gs:cell>
</entry>
<entry>
...
<gs:cell row='2' col='1' inputValue='java'>java</gs:cell>
</entry>
<entry>
...
<gs:cell row='2' col='2' inputValue='http://java.com'>http://java.com</gs:cell>
</entry>
<entry>
...
<gs:cell row='3' col='1' inputValue='php'>php</gs:cell>
</entry>
<entry>
...
<gs:cell row='3' col='2' inputValue='http://php.net'>http://php.net</gs:cell>
</entry>
</feed>
Suppose you wanted to retrieve the cells in the first column of a worksheet. You can request a cell feed containing only this column as follows:
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($this->currKey);
$query->setWorksheetId($this->currWkshtId);
$query->setMinCol(1);
$query->setMaxCol(1);
$query->setMinRow(2);
$feed = $gdClient->getCellFeed($query);
This requests all the data in column 1, starting with row 2.
Setting the 'MinCol', 'MaxCol', 'MinRow', and 'MaxRow'
values of a Zend_Gdata_Spreadsheets_CellQuery object is
equivalent to providing values for the paramaeters min-col,
max-col, min-row, and max-row (respectively) in
the HTTP GET request.
To modify the contents of a cell, our demo application uses the cellsUpdateAction function:
public function cellsUpdateAction($row, $col, $inputValue)
{
if (($row > $this->rowCount) || ($col > $this->columnCount)) {
print "Current worksheet only has $this->rowCount rows and $this->columnCount columns.\n";
if (!$this->promptToResize($row, $col)) {
return;
}
}
$entry = $this->gdClient->updateCell($row, $col, $inputValue,
$this->currKey, $this->currWkshtId);
if ($entry instanceof Zend_Gdata_Spreadsheets_CellEntry) {
echo "Success!\n";
}
}
The function above calls the updateCell method on the $gdClient object, passing in the row, column and input value along with the spreadsheet's key and the worksheetId. If we wanted to change the column header called language to programming-language, we could use the following command:
Command: update 1 1 programming-language
This is equivalent to sending an authenticated PUT
request with the updated cell's XML representation to the URL:
PUT http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/cell/version
The version part of the URL is a unique version string that is appended to each cell. This version string must be provided in the update request. It can be either be retrieved manually by examining the individual 'edit' links for each returned cell, or you can set this to latest to automatically overwrite the last change.
The new data is placed in the specified cell in the worksheet. If the specified cell contains data already, it will be overwritten.
Note: Use updateCell
(ie., HTTP PUT) to change the data in a cell, even if it is empty; the
use of POST on empty cells is no longer recommended. To
clear a cell, send an empty string as the inputValue instead of using a
DELETE.