Day: May 10, 2013

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).