In a previous article (here), we saw how to display Google Spreadsheets content with the Google Data API Java client and CellFeed objects.

This time, the proof of concept application shown below uses ListEntry objects to fetch our worksheets content, using header names (see (1) and (2) in the following screenshot) and unique identifiers (see (3)) instead of x and y positioning system.

You will find the source code at the bottom.

The following piece of code shows how to fetch every row entries in a worksheet into a HashMap which maps each row identifier to its ListEntry object:


  1         Map<String, ListEntry> listEntries =
  2                 new HashMap<String, ListEntry>();
  3         List<WorksheetRow> rows = new ArrayList<WorksheetRow>();
  4         try
  5         {
  6             ListFeed feed = sService.getFeed(new URL(sheetFeedURL),
  7                     ListFeed.class);
  8             for (ListEntry entry : feed.getEntries())
  9             {
 10                 WorksheetRow clonedRow = new WorksheetRow();
 11                 clonedRow.setTitle(entry.getTitle().getPlainText());
 12                 clonedRow.setId(entry.getId().substring(
 13                         entry.getId().lastIndexOf('/') + 1));
 14                 listEntries.put(clonedRow.getId(), entry);
 15                 CustomElementCollection elementColl =
 16                         entry.getCustomElements();
 17                 WorksheetRow.CustomElement rowElement =
 18                         new WorksheetRow.CustomElement();
 19                 for (String value : elementColl.getTags())
 20                 {
 21                     rowElement.put(value,
 22                             elementColl.getValue(value));
 23                 }
 24                 clonedRow.setElement(rowElement);
 25                 rows.add(clonedRow);
 26             }
 27             getSession().setAttribute("listEntries", listEntries);
 28         } catch (MalformedURLException e) ...

Each ListEntry object has a list of «Tag» (column names) and a HashMap which maps each tag name to its value in the current entry. Note that each column name is automatically made unique.

To update a row with a modified WorkSheetRow object, we get its associated ListEntry object and set every value of the WorkSheetRow object with the «setValueLocal» method.


  1     public void updateWorkSheetRow(WorksheetRow row) throws AuthException
  2     {
  3         Map<String, ListEntry> listEntries = (Map<String, ListEntry>) getSession().getAttribute("listEntries");
  6         ListEntry entry = listEntries.get(row.getId());
  7         for (String tag : row.getElement().keySet())
  8         {
  9             entry.getCustomElements().setValueLocal(tag, row.getElement().get(tag));
 10             try
 11             {
 12                 entry.update();
 13             } catch (IOException e)...

Then we simply have to call the modified entry update method.

Going further

Row editing in Spreadsheets API is not limited to the ListEntry class: Indeed, there is also a ListQuery class which enables advanced selection for your Worksheets.

Listing in reverse order:

In order to list the rows in reverse order, you just have to set the «reverse» property of a ListQuery object to «true» and pass this object to the SpreadsheetService object


  1     ListQuery query = new ListQuery(listFeedUrl);
  2     query.setReverse(true);
  3     ListFeed feed = service.query(query, ListFeed.class);
  4     for (ListEntry entry : feed.getEntries())
  5     { ...

Full-text search:

For full-text search, use the «setFullTextQuery» method. Note that you can pass several strings.


  1     ListQuery query = new ListQuery(listFeedUrl);
  2     query.setFullTextQuery("Florent Tue");
  3     ListFeed feed = service.query(query, ListFeed.class);
  4     for (ListEntry entry : feed.getEntries())
  5     { ...

Structured query:

Structured queries can filter the list by a particular value to a column name. You can also set the ordering of the listing with a column name.


  1     ListQuery query = new ListQuery(listFeedUrl);
  2     query.setSpreadsheetQuery("name = 'Florent'");
  3     query.setOrderBy("column:name");
  4     ListFeed feed = service.query(query, ListFeed.class);
  5     for (ListEntry entry : feed.getEntries())
  6     { ...

Useful Links:

Article related downloads: