Recently I answered a question on how to access the Google Spreadsheet API. The poster was asking how to search the contents of a row/cell for a specific phrase, so I put together a quick example in Java.
The source code is below. Note that you need to replace the find_word variable with the phrase that you’re searching for, and the username and password variables with your Google username/password.
try {
String USERNAME = "username";
String PASSWORD = "password";
String find_word = "GOOG";
find_word = find_word.toLowerCase();
SpreadsheetService service = new SpreadsheetService(
"GAEGROUPSEXAMPLENOPROD");
service.setUserCredentials(USERNAME, PASSWORD);
URL SPREADSHEET_FEED_URL = new URL(
"https://spreadsheets.google.com/feeds/spreadsheets/private/full");
// Make a request to the API and get all spreadsheets.
SpreadsheetFeed feed = service.getFeed(SPREADSHEET_FEED_URL,
SpreadsheetFeed.class);
List<SpreadsheetEntry> spreadsheets = feed.getEntries();
if (spreadsheets.size() == 0) {
resp.getWriter().println(
"There are no spreadsheets to inspect!");
}
// Retrieve an iterator over all spreadsheets contained in
// this user's Google Drive
Iterator<SpreadsheetEntry> spreadsheet_iterator = spreadsheets
.iterator();
while (spreadsheet_iterator.hasNext()) {
SpreadsheetEntry spreadsheet = spreadsheet_iterator.next();
String spreadsheet_name = spreadsheet.getTitle().getPlainText();
resp.getWriter().println(
"Currently searching spreadsheet " + spreadsheet_name);
// Search only the first worksheet of the spreadsheet.
WorksheetFeed worksheetFeed = service.getFeed(
spreadsheet.getWorksheetFeedUrl(), WorksheetFeed.class);
List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
WorksheetEntry worksheet = worksheets.get(0);
// Fetch the cell feed of the worksheet.
URL cellFeedUrl = worksheet.getCellFeedUrl();
CellFeed cellFeed = service
.getFeed(cellFeedUrl, CellFeed.class);
for (CellEntry cell : cellFeed.getEntries()) {
// Retrieve the contents of each cell.
String cell_contents = cell.getCell().getInputValue()
.toLowerCase();
// Match the word with the cell contents. Ignoring case.
if (cell_contents.indexOf(find_word) != -1) {
// Found the word.
resp.getWriter().println(
"Cell with contents " + cell_contents
+ " matches your phrase!");
// Find the row
Integer row_index = new Integer(cell.getCell().getRow());
resp.getWriter().println(
"<b>Row " + row_index.toString() + "</b> in "
+ spreadsheet_name
+ " matches your query.");
String rowCollect = "";
// Print row data
URL rowCellFeedUrl = new URI(worksheet.getCellFeedUrl()
.toString()
+ "?min-row="
+ row_index
+ "&max-row=" + row_index).toURL();
CellFeed rowCellFeed = service.getFeed(rowCellFeedUrl,
CellFeed.class);
// Iterate through each cell, printing its value.
for (CellEntry rowCell : rowCellFeed.getEntries()) {
// Print the cell's formula or text value
rowCollect += rowCell.getCell().getInputValue()
+ "\t";
}
resp.getWriter().println(
"<b>Row Data: </b>" + rowCollect);
}
}// end for looping through cells
}// end while looping through spreadsheets
} catch (Exception e) {
resp.getWriter().println("Exception: " + e.getMessage());
}
Also, don’t put this example into production use until you switch out the authentication service to OAuth (this example uses ClientLogin to authenticate).