Interacting with Google Spreadsheet with Java

Google Spreadsheet is an online tool to create cell-based documents and is particularly relevant to implement quickly simple databases. Spreadsheets can be managed from difference devices: on the Web in a browser or from tablets and mobiles. The product offers typical spreadsheet features, such as the ability to add, delete and query rows and columns. The application also enables multiple, geographically dispersed users to collaborate on a spreadsheet in real time.

We will describe here how to use the GSheet API client (provided by the GData API client) with Java to interact with them and manage the data they contain.

Configuring GData client

The simplest way to configure the GData client (the Java API from Google to use Google services, including Spreadsheets) to interact with spreadsheets is to use Maven and define the client as a dependency in the file pom.xml, as described below:

<?xml version="1.0" encoding="UTF-8"?>
<project (...)>
    <modelVersion>4.0.0</modelVersion>
    (...)
    <dependencies>
        <dependency>
            <groupId>com.google.gdata</groupId>
            <artifactId>core</artifactId>
            <version>1.47.1</version>
        </dependency>
    </dependencies>
    (...)
</project>

Maven can be used then to generate the configuration for your IDE. For example, for Eclipse, simply execute the following command:

mvn eclipse:eclipse

Now we have a configured project, lets start to implement the processing.

Initializing the GData Java client for Spreadsheet

The GData Java client provides the central class SpreadsheetService. This one will be used to interact with server resources. So we need first to instance it and set then our crendentials. Following code describes how to do this:

SpreadsheetService service = new SpreadsheetService("spreadsheetservice");
try {
    service.setUserCredentials(username, password);
} catch (AuthenticationException ex) {
    (...)
}

We can also define a constant for the URL corresponding to the spreadsheet service!

private static final String SPREADSHEET_SERVICE_URL
         = "https://spreadsheets.google.com/feeds/spreadsheets/private/full":

Now we configured the GData Java client, we can implement a set of utility methods to make our life easier to interact with our spreadsheets programmatically.

Implementing utility methods

Dome utility methods can help us when implementing processing to interact with our spreadsheets:

  • The first one (named getSpreadsheet) returns an instance of our spreadsheet using its name.
  • The next one (named getWorksheet) returns an instance of a worksheet (a tab) within a spreadsheet using its name. Its based on the previous method
  • The last ones (named getRowData) extracts data from a row, creates and fills rows from maps.

The naive approach for the first one consists in listing all the spreadsheets associated with our account, iterating on this list and getting the one the name matches with. This approach is inefficient and the GData client provides a way to get an instance to a particular spreadsheet based on a name.

private SpreadsheetEntry getSpreadsheet(String sheetName) {
    try {
        URL spreadSheetFeedUrl = new URL(SPREADSHEET_SERVICE_URL);

        SpreadsheetQuery spreadsheetQuery = new SpreadsheetQuery(
        spreadSheetFeedUrl);
        spreadsheetQuery.setTitleQuery(sheetName);
        spreadsheetQuery.setTitleExact(true);
        SpreadsheetFeed spreadsheet = service.getFeed(spreadsheetQuery,
                                               SpreadsheetFeed.class);

        if (spreadsheet.getEntries() != null
                 && spreadsheet.getEntries().size() == 1) {
            return spreadsheet.getEntries().get(0);
        } else {
            return null;
        }
    } catch (Exception ex) {
        (...)
    }

    return null;
}

Now we have an utility method to get an instance on a specific spreadsheet, we need a similar method to reference a worksheet. The method getWorkSheet aims to provide this:

private WorksheetEntry getWorkSheet(String sheetName, String workSheetName) {
    try {
        SpreadsheetEntry spreadsheet = getSpreadsheet(sheetName);

        if (spreadsheet != null) {
            WorksheetFeed worksheetFeed = service.getFeed(
                  spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class);
            List<WorksheetEntry> worksheets = worksheetFeed.getEntries();

            for (WorksheetEntry worksheetEntry : worksheets) {
                 String wktName = worksheetEntry.getTitle().getPlainText();
                 if (wktName.equals(workSheetName)) {
                     return worksheetEntry;
                 }
             }
         }
     } catch (Exception ex) {
         (...)
    }

    return null;
}

The last utility methods extracts from a row in a map, create and fill rows from maps.

private Map<String, Object> getRowData(ListEntry row) {
    Map<String, Object> rowValues = new HashMap<String, Object>();
    for (String tag : row.getCustomElements().getTags()) {
        Object value = row.getCustomElements().getValue(tag);
        rowValues.put(tag, value);
    }
    return rowValues;
}

private ListEntry createRow(Map<String, Object> rowValues) {
    ListEntry row = new ListEntry();
    for (String columnName : rowValues.keySet()) {
        Object value = rowValues.get(columnName);
        row.getCustomElements().setValueLocal(columnName,
                         String.valueOf(value));
    }
    return row;
}

private void updateRow(ListEntry row, Map<String, Object> rowValues) {
    for (String columnName : rowValues.keySet()) {
        Object value = rowValues.get(columnName);
        row.getCustomElements().setValueLocal(columnName,
                         String.valueOf(value));
    }
}

As you can see in the method getRowData, we use the expression row.getCustomElements().getTags() to get all data present in a row. We must be aware that the value of tags are internal values created by the spreadsheet and doesnt exactly match with the name of columns. For example, if the name of a column is first_name or firstName, the value of the tag will be firstname.

Based on these methods, we will now describe how to interact with a specific spreadsheet to get and manipulate data that it contains.

Get metadata

To identify and get a name to column, we defined a dedicated line (the first one) that contains no data and give them a name. Getting metadata corresponds to get these hints. For this, we choose to work with cell-based support of the GSheet API. Following code describes how to only get the first line of the worksheet:

WorksheetEntry worksheet = getWorkSheet(sheetName, workSheetName);
URL cellFeedUrl = new URI(worksheet.getCellFeedUrl().toString()).toURL();

CellQuery cellQuery = new CellQuery(cellFeedUrl);
cellQuery.setMinimumRow(1);
cellQuery.setMaximumRow(1);

CellFeed cellFeed = service.query(cellQuery, CellFeed.class);

List<String> worksheetColumns = new ArrayList<String>();
for (CellEntry cell : cellFeed.getEntries()) {
    worksheetColumns.add(cell.getPlainTextContent());
}

Now we will rather use the row-based approach of the GSheet API to get and manage data of a worksheet.

List data

The GSheet API client allows to get all data present in a worksheet. Following code describes how to do this:

WorksheetEntry worksheet = getWorkSheet(sheetName, workSheetName);
URL listFeedUrl = worksheet.getListFeedUrl();

ListFeed listFeed = service.getFeed(listFeedUrl, ListFeed.class);

List<Map<String,Object>> rows = new ArrayList<Map<String,Object>>();
for (ListEntry row : listFeed.getEntries()) {
    Map<String,Object> rowValues = getRowData(row);
    rows.add(rowValues);
}

We get here all data present in a worksheet but sometimes, we also want to get a subset of the data. For this, we must leverage the query feature of GSheet API client.

Query data

As we saw when getting metadata, GSheet API supports structured queries. We used them at cell level but queries for lists are also supported. This is what we will use to get data of the worksheet that match a set of criteria. In the following code, we will get the data with column id value between 1 and 4.

String sheetName = SHEET_NAME;
String workSheetName = WORK_SHEET_NAME;
WorksheetEntry worksheet = getWorkSheet(sheetName, workSheetName);
URL listFeedUrl = worksheet.getListFeedUrl();
ListQuery listQuery = new ListQuery(listFeedUrl);
listQuery.setSpreadsheetQuery("id > 1 and id < 4");

ListFeed listFeed = service.query(listQuery, ListFeed.class);
for (ListEntry row : listFeed.getEntries()) {
    Map<String, Object> rowValues = getRowData(row);
    (...)
}

We can notice that string parameters must be used as described below:

ListQuery listQuery = new ListQuery(listFeedUrl);
listQuery.setSpreadsheetQuery("name = \"a name\"");

We can also notice that the GSheet API client has a full text search feature. To use it, simply replace the method setSpreadsheetQuery with setFullTextQuery, as described below:

ListQuery listQuery = new ListQuery(listFeedUrl);
listQuery.setFullTextQuery("the content to search");

Now we saw how to get a set of data, lets focus now on how to handle a particular row. We will describe how to implement the CRUD (Create Retrieve Update Delete) operations.

Add data

To add a row, we need first to create and populate an instance of ListEntry. This instance is then added using the GSheet API client.

WorksheetEntry worksheet = getWorkSheet(sheetName, workSheetName);

URL listFeedUrl = worksheet.getListFeedUrl();
Map<String,Object> rowValues = (...)
ListEntry row = createRow(rowValues);
row = service.insert(listFeedUrl, row);

Load data corresponding to a row

Loading the data for a particular row is based on queries for rows. We must use a column that contains a distinct value accross all the worksheet. We first implement methods that creates and executes the row query and also gets the instance of the row.

private ListFeed getListFeedForRow(String sheetName, String workSheetName,
                             String pkColumnName, Object pkColumnValue) throws Exception {
    WorksheetEntry worksheet = getWorkSheet(sheetName, workSheetName);
    URL listFeedUrl = worksheet.getListFeedUrl();
    ListQuery listQuery = new ListQuery(listFeedUrl);
    if (pkColumnValue instanceof String) {
        listQuery.setSpreadsheetQuery(pkColumnName + " = \"" + pkColumnValue + "\"");
    } else {
        listQuery.setSpreadsheetQuery(pkColumnName + " = " + pkColumnValue);
    }

    return service.query(listQuery, ListFeed.class);
}

private ListEntry getRow(String sheetName, String workSheetName,
                              String pkColumnName, Object pkColumnValue) throws Exception {
    ListFeed listFeed = getListFeedForRow(sheetName, workSheetName,
                                      kColumnName, pkColumnValue);
    if (listFeed.getEntries().size()==1) {
        return listFeed.getEntries().get(0);
    } else {
        throw new Exception("Can't find row with id "+pkColumnName+" = "+pkColumnValue);
    }
}

Lets now use these methods to get the data of a row:

String sheetName = SHEET_NAME;
String workSheetName = WORK_SHEET_NAME;
ListEntry row = getRow(sheetName, workSheetName,
                              pkColumnName, pkColumnValue);
Map<String, Object> rowValues = getRowData(row);
(...)

Edit data

Based on what was described above, updating the data of a row is trivial. We need to load the row, then update its values and finally update it using the GSheet API client.

ListEntry row = getRow(sheetName, workSheetName,
                  pkColumnName, pkColumnValue);
updateRow(row, rowValues);
row.update();

Delete data

Based on what was described above, updating the data of a row is trivial. We need to load the row and then delete it using the GSheet API client.

ListEntry row = getRow(sheetName, workSheetName,
                  pkColumnName, pkColumnValue);
row.delete();

Note about column names

We can encounter some issues when using column names that containes spaces, underscores or uppercase characters. This comes from the fact that GSheet internally creates an identifier for each column based on the specified values into the first line of the worksheet.

When getting metadata for columns (see section ), we get the specified and visible values. Contrariwise when getting rows, instances of ListEntry use tags (i.e. the internal identifiers). They differ if specified column names contain spaces, underscores or uppercase characters. There is no way to link them and we need to deduce these identifiers from column names.

Lets take a sample to find out what actually occurs. We have a worksheet with column names: id, firstName, last_name, age and Age.

GSheet for testing

When getting the metadata, we have the following:

>> columns
- id
- firstName
- last_name
- age
- Age

No surprise here. Lets see what we get on a row-based query:

>> row
- column id = 1
- column age_2 = 12
- column age = 11
- column lastname = name2
- column firstname = name1

As we can see the internal identifiers are used here and we must handle by ourselves the correspondances.

We can however notice that when sending data, the GSheet API client automatically handle the name conversion.

This entry was posted in Spreadsheet and tagged , , , . Bookmark the permalink.

3 Responses to Interacting with Google Spreadsheet with Java

  1. Puneesh says:

    It use to work for me but it has now stopped working since last week. U think google has stopped supporting the oAuth1 and setUserCredentials(username, password).

    I am unable to get it working using Oauth2.0. Can you please rerite some of the code for authorising using Oauth 2.0

  2. templth says:

    Yes, I saw that! Sure, I planned to rewrite or write a new post regarding such issue… Stay tuned 😉

  3. hariprasad says:

    Hi , i want to interact with google spreadsheet with java but i was stuck Oauth2.0 authentication,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s