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.
See http://code.google.com/apis/spreadsheets/overview.html for more information about the Google Spreadsheets API.
The Spreadsheets data API does not currently provide a way to programmatically create or delete a spreadsheet.
You can get a list of spreadsheets for a particular user by using
the getSpreadsheetFeed method of the Spreadsheets
service. The service will return a
Zend_Gdata_Spreadsheets_SpreadsheetFeed object
containing a list of spreadsheets associated with the authenticated
user.
$service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
$client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
$spreadsheetService = new Zend_Gdata_Spreadsheets($client);
$feed = $spreadsheetService->getSpreadsheetFeed();
A given spreadsheet may contain multiple worksheets. For each spreadsheet, there's a worksheets metafeed listing all the worksheets in that spreadsheet.
Given the spreadsheet key from the <id> of a
Zend_Gdata_Spreadsheets_SpreadsheetEntry
object you've already retrieved, you can fetch a feed
containing a list of worksheets associated with that spreadsheet.
$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$feed = $spreadsheetService->getWorksheetFeed($query);
The resulting Zend_Gdata_Spreadsheets_WorksheetFeed
object feed represents the 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.
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 cell-based feed, in which each entry represents a single cell. For information on cell-based feeds, see Interacting with cell-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, the list feed treats the first row of the worksheet as a header row; Spreadsheets dynamically creates XML elements named after the contents of header-row cells. Users who want to provide Gdata feeds should not put any data other than column headers in the first row of a worksheet.
The list feed contains all rows after the first row up to the first blank row. The first blank row terminates the data set. If 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. In particular, if the second row of the spreadsheet is blank, then the list feed will contain no data.
A row in a list feed is as many columns wide as the worksheet itself.
To retrieve a worksheet's list feed, use the
getListFeed method of the Spreadsheets service.
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$query->setWorksheetId($worksheetId);
$listFeed = $spreadsheetService->getListFeed($query);
The resulting Zend_Gdata_Spreadsheets_ListFeed
object $listfeed represents a response from the
server. Among other things, this feed contains an array of
Zend_Gdata_Spreadsheets_ListEntry objects
($listFeed->entries), each of which represents
a single row in a worksheet.
Each Zend_Gdata_Spreadsheets_ListEntry contains an
array, custom, which contains the data for that
row. You can extract and display this array:
$rowData = $listFeed->entries[1]->getCustom();
foreach($rowData as $customEntry) {
echo $customEntry->getColumnName() . " = " . $customEntry->getText();
}
An alternate version of this array, customByName,
allows direct access to an entry's cells by name. This is
convenient when trying to access a specific header:
$customEntry = $listFeed->entries[1]->getCustomByName('my_heading');
echo $customEntry->getColumnName() . " = " . $customEntry->getText();
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 = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$query->setWorksheetId($worksheetId);
$query->setReverse('true');
$listFeed = $spreadsheetService->getListFeed($query);
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>.
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 = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$query->setWorksheetId($worksheetId);
$query->setSpreadsheetQuery('name=John and age>25');
$listFeed = $spreadsheetService->getListFeed($query);
Rows can be added to a spreadsheet by using the
insertRow method of the Spreadsheet service.
$insertedListEntry = $spreadsheetService->insertRow($rowData,
$spreadsheetKey,
$worksheetId);
The $rowData parameter contains an array of column
keys to data values. The method returns a
Zend_Gdata_Spreadsheets_SpreadsheetsEntry object
which represents the inserted row.
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.
Once a Zend_Gdata_Spreadsheets_ListEntry object
is fetched, its rows can be updated by using the
updateRow method of the Spreadsheet service.
$updatedListEntry = $spreadsheetService->updateRow($oldListEntry,
$newRowData);
The $oldListEntry parameter contains the list entry
to be updated. $newRowData contains an array of
column keys to data values, to be used as the new row data.
The method returns a
Zend_Gdata_Spreadsheets_SpreadsheetsEntry object
which represents the updated row.
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 feed, use the
getCellFeed method of the Spreadsheets service.
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$query->setWorksheetId($worksheetId);
$cellFeed = $spreadsheetService->getCellFeed($query);
The resulting Zend_Gdata_Spreadsheets_CellFeed
object $cellFeed represents a response from the
server. Among other things, this feed contains an array of
Zend_Gdata_Spreadsheets_CellEntry objects
($cellFeed>entries), each of which represents
a single cell in a worksheet. You can display this information:
foreach($cellFeed as $cellEntry) {
$row = $cellEntry->cell->getRow();
$col = $cellEntry->cell->getColumn();
$val = $cellEntry->cell->getText();
echo "$row, $col = $val\n";
}
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->setMinCol(1);
$query->setMaxCol(1);
$query->setMinRow(2);
$feed = $spreadsheetService->getCellsFeed($query);
This requests all the data in column 1, starting with row 2.
To modify the contents of a cell, call
updateCell with the row, column,
and new value of the cell.
$updatedCell = $spreadsheetService->updateCell($row,
$col,
$inputValue,
$spreadsheetKey,
$worksheetId);
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 to change
the data in a cell, even if the cell is empty.