Developer's Guide: Java

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 Java client library. 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 Java Client Library to interact with the Spreadsheets Data API, please see the protocol tab.

Contents

Audience

This document is intended for programmers who want to write client applications that can interact with Google Spreadsheets. It provides a series of examples of basic data API interactions using the Java client library.

For Spreadsheets Data API reference information, see the reference guide. This document assumes that you understand the general ideas behind the Google Data APIs protocol.

Getting started

For help setting up the client library, see the Getting Started Guide. To use the Java client library, you must be running Java 1.5. After downloading the client library, you'll find the classes you need to get started in the java/lib/gdata-spreadsheet-1.0.jar and java/lib/gdataclient-1.0.jar jar files.

Working samples are available in the distribution, under the directory gdata/java/sample/spreadsheets/. There is a README.txt file in the same directory that includes an explanation of each sample, along with build and execution instructions.

The samples perform a number of operations on a spreadsheet to demonstrate the use of the Spreadsheets Data API.

To compile the examples in this document into your own code, you'll need to use the following import statements:

import com.google.gdata.client.spreadsheet.*;
import com.google.gdata.data.*;
import com.google.gdata.data.spreadsheet.*;
import com.google.gdata.util.*;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintStream;
import java.net.URL;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

The SpreadsheetService class represents a client connection (with authentication) to a Spreadsheets service. The general procedure for sending a query to a service using the client library consists of the following steps:

1. Obtain or construct the appropriate URL.
2. If you're sending data to a service (for example, if you're inserting a new entry), then transform the raw data into objects using the client library classes. (This step doesn't apply if you're just requesting a feed, as in this example.)
3. Create a new SpreadsheetService instance, setting your application's name (in the form companyName-applicationName-versionID).
4. Set the appropriate credentials.
5. Call a method to send the request and receive any results.

Create a spreadsheet

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 spreadsdheet manually or upload one.

Authenticating to the Spreadsheets service

The Java client library can be used to work with either public or private feeds. Public feeds are read-only, but do not require any authentication. 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.

Single-user "installed" client authentication

To use ClientLogin (also called "Authentication for Installed Applications"), invoke the setUserCredentials method of SpreadhsheetService, specifying the ID and password of the user on whose behalf your client is sending the query. For example:

SpreadsheetService myService = new SpreadsheetService("exampleCo-exampleApp-1");
myService.setUserCredentials("jo@gmail.com", "mypassword");

For more information about authentication systems, see the Google Account Authentication documentation.

Multiple-user web application client authentication

AuthSub proxy authentication is used by web applications which need to authenticate their users to Google accounts. The website operator does not need access to the username and password for the Google Spreadsheets user - only special AuthSub tokens are required.

To acquire an AuthSub token for a given Google Spreadsheets user, your application must redirect the user to the AuthSubRequest URL, which prompts them to log into their Google account. You may use the getRequestUrl method on the AuthSubUtil object to create this URL:

String requestUrl =
AuthSubUtil.getRequestUrl("http://www.coolspreadsheetssite.com/welcome",
                          "http://spreadsheets.google.com/feeds",
                          false,
                          true);

The getRequestUrl method takes several parameters (corresponding to the query parameters used by the AuthSubRequest handler):

• the next URL — URL that Google will redirect to after the user logs into their account and grants access; http://www.coolspreadsheetssite.com/welcome in the example above
• the scope — http://spreadsheets.google.com/feeds in the example above
• a boolean to indicate whether you will be digitally signing your request using asymetric key signatures or not; false in the example above
• a second boolean to indicate whether the token will later be exchanged for a session token or not; true in the example above

After constructing the "next" URL, your server-side application can use it in a variety of ways to send the user to the AuthSubRequest handler. The most common approach is to display a page that tells the user that they need to follow a link to authorize your application to access your Google account; then attach the request URL to the link. For example, you could display the following string on a page:

String suggestAuthorization = "<p>MyApp needs access to your Google Spreadsheets account. To authorize
MyApp to access your account, <a href=\"" + requestUrl + "\">log in to your account</a>.</p>";

The user follows the link to the AuthSub page at Google, and logs in. The AuthSub system then redirects the user back to your application, using the "next" URL you provided.

When Google redirects back to your application, the token is appended to the "next" URL as a query parameter. So in the case of the above "next" URL, after the user logs in, Google redirects the browser to a URL like http://www.coolspreadsheetssite.com/welcome?token=DQAADKEDE.

The servlet handling that URL should then examine the query parameters in the requested URL to retrieve the token set by Google. For example, the servlet can retrieve the token from the URL by using the convenience function getTokenFromReply from the Java client library:

String onetimeUseToken = 
AuthSubUtil.getTokenFromReply(httpServletRequest.getQueryString());

Your application can recognize which user has authenticated with Google's servers using AuthSub by setting an authentication cookie prior to having them click the AuthSub link, then reading this cookie after the user has been authenticated and returned to your webpage.

The token you retrieve with getTokenFromReply is always a one-time use token. You can exchange this token for a session token using the AuthSubSessionToken URL, as described in the AuthSub documentation. Your application can make this exchange using the Java client library as follows:

String sessionToken = AuthSubUtil.exchangeForSessionToken(onetimeUseToken,
                                                      null);

You pass your one-time use token to the exchangeForSessionToken method, along with either null, or with a private key for secure mode, and the AuthSub interface returns a session token. For more information about registered applications and private keys, refer to the AuthSub documentation.

You can use the session token to authenticate requests to the server by placing the token in the Authorization header, as described in the AuthSub documentation. To tell the Java client library to automatically send the Authorization header (containing the session token) with each request, you call the SpreadsheetService object's setAuthSubToken method:

SpreadsheetService myService = new SpreadsheetService("exampleCo-exampleApp-1");
service.setAuthSubToken(sessionToken, null);

If you're using secure mode, then you provide your private key instead of null.

After you've called setAuthSubToken, you can use the standard GData client library calls to interact with the service, without having to think about the token.

If you want to retrieve information about the token's parameters, you can pass the token to the getTokenInfo method, which returns a set of name-value pairs containing information about the token. For example:

Map<String, String> info = AuthSubUtil.getTokenInfo(sessionToken, null);  

AuthSub session tokens don't expire; your client can store the session token for as long as needed.

When your client is done using the session token, it can revoke the token using the AuthSubRevokeToken handler, as described in the AuthSub documentation.

For example, if you want to manage tokens in a traditional session-like way, then your client can get a token at the beginning of a user's session and revoke it at the end of the user's session.

To revoke a token using the Java client library, call revokeToken. Include your private key for registered mode, or null otherwise. For example:

AuthSubUtil.revokeToken(sessionToken, null);

Please see the AuthSub documentation for more detailed information.

Get a list of spreadsheets

You can get a feed containing a list of the currently authenticated user's spreadsheets by sending an authenticated GET request to the following URL:

http://spreadsheets.google.com/feeds/spreadsheets/private/full

The result is a "metafeed," 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 only accessible using an authentication token.

To request the metafeed using the Java client library, do the following:

URL metafeedUrl = new URL("http://spreadsheets.google.com/feeds/spreadsheets/private/full");
SpreadsheetFeed feed = service.getFeed(metafeedUrl, SpreadsheetFeed.class);
List spreadsheets = feed.getEntries();
for (int i = 0; i < spreadsheets.size(); i++) {
  SpreadsheetEntry entry = spreadsheets.get(i);
  System.out.println("\t" + entry.getTitle().getPlainText());
}

The getFeed method sends a request to the server and the SpreadsheetFeed object represents a response from the server. Among other things, this feed contains a list of SpreadsheetEntry objects, each of which represents a single spreadsheet. SpreadsheetEntry encapsulates the information shown in the protocol document.

Get a list of worksheets

A given spreadsheet may contain multiple worksheets. For each spreadsheet, there's a worksheets metafeed listing all the worksheets in that spreadsheet.

The following sections describe how to get a worksheet-based feed, add a worksheet to a spreadsheet, update the metadata of a worksheet, and remove a worksheet from the spreadsheet.

Get a worksheet-based feed

Given a SpreadsheetEntry you've already retrieved, you can print a list of all worksheets in this spreadsheet by simply calling the getWorksheets method and displaying the results:

List worksheets = spreadsheetEntry.getWorksheets();
for (int i = 0; i < worksheets.size(); i++) {
  WorksheetEntry worksheet = worksheets.get(i);
  String title = worksheet.getTitle().getPlainText();
  int rowCount = worksheet.getRowCount();
  int colCount = worksheet.getColCount();
  System.out.println("\t" + title + "- rows:" + rowCount + " cols: " + colCount);
}

This code is equivalent to sending an authenticated GET request to the worksheets feed URL:

http://spreadsheets.google.com/feeds/worksheets/key/private/full

where key is the permanent unique ID of a specific worksheet within the spreadsheet.

For further information about the worksheets metafeed, see the Worksheet-based feed section of the reference document.

Add a worksheet

To add a worksheet to the spreadsheet, start by creating a WorksheetEntry object containing the relevant data, then use your SpreadsheetService object to send an insertion request to the worksheet feed URL:

WorksheetEntry worksheet = new WorksheetEntry();
worksheet.setTitle(new PlainTextConstruct("MyTitle"));
worksheet.setRowCount(200);
worksheet.setColCount(30);

URL worksheetFeedUrl = spreadsheetEntry.getWorksheetFeedUrl()
service.insert(worksheetFeedUrl, worksheet);

This is equivalent to sending a HTTP POST request to:

POST http://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId

Spreadsheets inserts the new worksheet immediately after the last worksheet that appears in Spreadsheets UI.

Update the metadata of a worksheet

To change the metadata of a worksheet, begin by getting the desired worksheet from the worksheet feed. Then update the worksheet's metadata and use the update method to send the new metadata to the server. For example, the following code updates the title and size of the default "Sheet1" worksheet.

URL worksheetFeedUrl = spreadsheetEntry.getWorksheetFeedUrl()
WorksheetFeed worksheetFeed = service.getFeed(worksheetFeedUrl, WorksheetFeed.class);
    
for (WorksheetEntry worksheet : worksheetFeed.getEntries()) {
  String currTitle = worksheet.getTitle().getPlainText();
  if (currTitle.equals("Sheet1")) {
    worksheet.setTitle(new PlainTextConstruct("Sales Projections"));
    worksheet.setRowCount(500);
    worksheet.setColCount(30);
    worksheet.update();
    return;
  }
}

Note: Metadata includes the title of the worksheet and the number of rows and columns. Setting the number of rows or columns to less than the original will delete the cells and contents that do not exist within the new boundaries WITHOUT WARNING.

Remove a worksheet

To delete a worksheet, use its delete method after obtaining it as described above.

worksheet.delete();

Note: If you delete a worksheet, that worksheet is completely removed from the spreadsheet and all data contained on that worksheet is discarded.

Interacting with list-based feeds

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 Google Data 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.

Get a list-based feed

To retrieve a worksheet's list feed, you might do the following. This assumes that service is an authenticated SpreadsheetService object, and entry is a WorksheetEntry.

URL listFeedUrl = worksheetEntry.getListFeedUrl();
ListFeed feed = service.getFeed(listFeedUrl, ListFeed.class);
for (ListEntry entry : feed.getEntries()) {
  System.out.println(entry.getTitle().getPlainText());
  for (String tag : entry.getCustomElements().getTags()) {
    System.out.println("  " + entry.getCustomElements().getValue(tag) + "");
  }
}

First obtain the list feed URL from a WorksheetEntry and request this feed from our authenticated SpreadsheetService object. The SpreadsheetService returns a ListFeed, which contains a list of all the rows in this worksheet. Each row is represented as a ListEntry object.

To retrieve the contents of a single row, iterate over the ListEntry's CustomElementCollection member, where each element in the collection represents a cell in that row.

This code is equivalent to sending an authenticated GET request to the URL:

http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full

Reverse-sort rows

By default, rows in the feed appear in the same order as the corresponding position in the UI; that is, they're in order by row number. To get rows in reverse order, use the setReverse method of the ListQuery object:

ListQuery query = new ListQuery(listFeedUrl);
query.setReverse(true);
ListFeed feed = service.query(query, ListFeed.class);

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 pass the name of the header for the column to the the setSortColumn method of the ListQuery object. This is analogous to adding an orderby query parameter in the outbound GET request.

Send a structured query

You can set a ListQuery's SpreadsheetQuery property 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 SpreadsheetQuery as follows:

ListQuery query = new ListQuery(listFeedUrl);
query.setSpreadsheetQuery("name = John and age > 25");
ListFeed feed = service.query(query, ListFeed.class);

This is equivalent to sending an authenticated GET request to the URL:

http://spreadsheets.google.com/feeds/list/key/worksheetId/private/full?sq=yourQuery

For more information about the structured query syntax, see the List-feed query parameters reference section.

Add a row

To insert a new row in a list-based feed, first construct a new ListEntry and add the contents of each cell as a CustomElement. For example, the following example parses a nameValuePairs input string such as "name=Rosa,phone=555-1212" into a ListEntry object representing a row.

ListEntry newEntry = new ListEntry();

// Split first by the commas between the different fields.
for (String nameValuePair : nameValuePairs.split(",")) {
  // Then, split by the equal sign.
  String[] parts = nameValuePair.split("=", 2);
  String tag = parts[0]; // such as "name"
  String value = parts[1]; // such as "Rosa"
  newEntry.getCustomElements().setValueLocal(tag, value);
}

Then insert the new row in the ListFeed as follows:

ListEntry insertedRow = service.insert(listFeedUrl, newEntry);

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.

Edit a row

To change the contents of a row, first modify the desired CustomElements of the ListEntry object. For example, to change the cell in the "name" column to "John" and the cell in the "phone" column to "555-3254", do the following:

existingEntry.getCustomElements().setValueLocal("name", "John");
existingEntry.getCustomElements().setValueLocal("phone", "555-3254");

Then call the update method on the ListEntry object to perform the update:

ListEntry updatedRow = existingEntry.update()

This is equivalent to sending an authenticated PUT request to the entry's edit link, with the updated XML as PUT data.

Remove a row

To delete a row, simply invoke the delete method on the corresponding ListEntry object:

entry.delete();

This is equivalent to performing an HTTP DELETE request on the row's edit URL.

Note: If you delete a row, that row is completely removed from the worksheet.

Interacting with cell-based feeds

In a cell-based feed, each entry represents a single cell.

Note that it isn't recommended to interact with both a cell-based feed and a list-based feed for the same worksheet at the same time.

Get a cell-based feed

To retrieve a worksheet's cell-based feed, you might do the following. Again, assume that service is an authenticated SpreadsheetService object, and worksheetEntry is a WorksheetEntry from the spreadsheet.

URL cellFeedUrl = worksheetEntry.getCellFeedUrl();
CellFeed feed = service.getFeed(celFeedUrl, CellFeed.class);
for (CellEntry cell : feed.getEntries()) {
  System.out.println(entry.getTitle().getPlainText());
  String shortId = cell.getId().substring(cell.getId().lastIndexOf('/') + 1);
  System.out.println(" -- Cell(" + shortId + "/" + cell.getTitle().getPlainText()
      + ") formula(" + cell.getCell().getInputValue() + ") numeric("
      + cell.getCell().getNumericValue() + ") value("
      + cell.getCell().getValue() + ")");
}

You can obtain the cell feed URL from the worksheetEntry object using the getCellFeedUrl method. The SpreadsheetService returns a CellFeed, which contains a list of all the cells in this worksheet. Each CellEntry in the feed is a single cell.

You can easily access a cell's content or information about the cell, such as the row or column number, using the various getters provided by the CellEntry class.

This code is equivalent to sending an authenticated GET request to the URL:

http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full

Send a cell range query

Suppose you only want to retrieve the cells within a given range of rows and columns of a worksheet. You can request a cell feed containing only these cells by using a CellQuery object:

CellQuery query = new CellQuery(cellFeedUrl);
query.setMinimumRow(minRow);
query.setMaximumRow(maxRow);
query.setMinimumCol(minCol);
query.setMaximumCol(maxCol);
CellFeed feed = service.query(query, CellFeed.class);    

That requests all the data in rows from minRow to maxRow and columns from minCol to maxCol.

The setters in this example are equivalent to providing values for the parameters min-row, max-row, min-col, and max-col (respectively) in the HTTP GET request.

Change contents of a cell

To modify the contents of an existing CellEntry that you have retrieved from a feed, first change its InputValue property. Then execute the update request as follows:

existingCellEntry.changeInputValueLocal(formulaOrValue);
existingCell.update();

This is equivalent to sending an authenticated PUT request, with the updated cell's XML representation, to the URL:

http://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full/cell/version

Note: Use update() (i.e., 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 value instead of using a DELETE.

Update multiple cells with a batch request

Suppose you want to update a column, row, or other set of cells. You could simply use a for loop and update the cells one-by-one as described in the previous section, or you could minimize the number of HTTP requests by batching the updates together.

CellFeed cellFeed = service.getFeed(myWorksheet.getCellFeedUrl(), CellFeed.class);

// Create the first operation.
CellEntry toUpdate = cellFeed.getEntries().get(0);
toUpdate.changeInputValueLocal("new value");
BatchUtils.setBatchId(toUpdate, "1");
BatchUtils.setBatchOperationType(toUpdate, BatchOperationType.UPDATE);

// Create the second operation.
CellEntry toUpdate2 = cellFeed.getEntries().get(1);
toUpdate2.changeInputValueLocal("second new value");
BatchUtils.setBatchId(toUpdate2, "2");
BatchUtils.setBatchOperationType(toUpdate2, BatchOperationType.UPDATE);

// Add the operations to a batch feed.
CellFeed batchFeed = new CellFeed();
batchFeed.getEntries().add(toUpdate);
batchFeed.getEntries().add(toUpdate2);

// Submit the batch request.
Link batchLink = cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
CellFeed batchResultFeed = service.batch(new URL(batchLink.getHref()), batchFeed);

// Make sure all the operations were successful.
for (CellEntry entry : batchResultFeed.getEntries()) {
  String batchId = BatchUtils.getBatchId(entry);
  if (!BatchUtils.isSuccess(entry)) {
    BatchStatus status = BatchUtils.getBatchStatus(entry);
    System.err.println("Failed entry");
    System.err.println("\t" + batchId + " failed (" + status.getReason() + ") " + status.getContent());
  }
}

Here we first retrieve the cells feed of a worksheet. Now we update the first cell to have the value "new value", the second cell to have the value "second new value", and we retrieve the contents of the third cell in one operation. The results of each of these operations can be extracted from the batchResultFeed object. We show just doing three operations here, but you can add many updates or queries into a single batch transaction. The size of the request must be under a megabyte; on the order of 1000 to 2000 cells, depending on the length of cell content. More information about batch operations can be found on the Batch Processing page.

Back to top