Re: App Engine (Java) application - Browse and search Google Spreadsheets content

[Vinny P]

Hi David, 

Welcome to GAE. A Java application to process a Google Doc spreadsheet is fairly simple to write, there's documentation and Java sample code here at: https://developers.google.com/google-apps/spreadsheets/ 

With that said, there are other, better ways to access and search spreadsheet-style data. Try Google Fusion Tables ( http://support.google.com/fusiontables/answer/2571232?hl=en ); the data is presented in a spreadsheet style format, but can be queried using SQL syntax ( https://developers.google.com/fusiontables/docs/v1/sql-reference ). Documentation and example code is here: https://developers.google.com/fusiontables/

-Vinny P

On Tuesday, March 19, 2013 4:38:25 AM UTC-5, in...@fajneveci.cz wrote:

Hi, I'm beginner on GAE.

I need make an Java application, that will search the Google spreadsheet and return corresponding row.

Can you direct me, Is there a tutorial? Or other useful links.

Thanks, David.

[in...@fajneveci.cz]

Thanks, for your answer.

is there anyone who can help me? I can pay for it - I need solution quickly.

Dne úterý, 19. března 2013 21:20:03 UTC+1 Vinny P napsal(a):

[Vinny P]

On Tuesday, April 2, 2013 9:07:50 AM UTC-5, in...@fajneveci.cz wrote:

is there anyone who can help me? I can pay for it - I need solution quickly.

Hi David,

 

I had some free time last night, so I knocked together a sample app based on this documentation: https://developers.google.com/google-apps/spreadsheets/#working_with_list-based_feeds

 

To set up this example, I placed this spreadsheet file into my Google Docs: http://i.imgur.com/xp1XO0M.png The below code generated this results file: http://i.imgur.com/gEwVFz4.png

 

Just so you understand what's going on, the program searched for the phrase "GOOG" which matched twice, once on the first cell of row 3 and secondly on the second cell of row 3 (it matched the first part of GOOGle inc). You place the word you want to find into the variable find_word. You can extend this code by taking the row_index variable and linking it to whatever you need the row data for. It searches every spreadsheet file saved in your Google Docs, but only the first worksheet (you can change that in the code).

Now, a word of notice: I'm using ClientLogin authorization for this example. ClientLogin is deprecated and is being removed from production in April 2015. If you move this code into a production service, you need to implement OAuth. There are plenty of other examples of OAuth based authentication out there, just Google it. If you're just doing this for a school project or personal, for-your-own-use service, then ClientLogin is fine.

@Takashi, Euphrosine, Ikai, other Google people: Can you call up the Google Spreadsheets people and tell them they have an error in their documentation? Go to https://developers.google.com/google-apps/spreadsheets/#retrieving_a_list-based_feed and click on the Java example code tab. Where it says for (ListEntry row : feed.getEntries()) { that needs to be changed to for (ListEntry row : listFeed.getEntries()) {. (There's no feed variable, it's supposed to be the feed from the cells, which is listFeed). There are a couple of other Java errors in the other documentation samples. I fixed it in the below code.

package com.example.gaegroupsexample;

import java.io.IOException;
import java.net.URI;
import java.net.URL;
import java.util.Iterator;
import java.util.List;

import com.google.gdata.client.spreadsheet.*;
import com.google.gdata.data.spreadsheet.*;
import com.google.gdata.util.*;
import javax.servlet.http.*;

@SuppressWarnings("serial")
public class GAEGroupsExampleServlet extends HttpServlet {
 public void doGet(HttpServletRequest req, HttpServletResponse resp)
   throws IOException {
  resp.setContentType("text/html");
  
  resp.getWriter().println("<pre>");
  
  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());
  }
  resp.getWriter().println("</pre>");
 }//end doGet

}

-----------------

-Vinny P

Technology & Media Advisor

Chicago, IL

@GOV on AppDotNet: https://alpha.app.net/gov