Re: How to access a private google spreadsheet from google app engine ?

[Vinny P]

On Monday, April 29, 2013 11:18:36 AM UTC-5, David Bou wrote:

   I would like to know how to read a private google spreadsheet from google app engine.

Hello David,

Is this "private google spreadsheet" private to you or to another user? If it's private to another user, you'll need an OAuth token from them. The OAuth documentation for Google Spreadsheets is here: https://developers.google.com/google-apps/spreadsheets/#authorizing_requests_with_oauth_20

If the spreadsheet is private to you, then you can use ClientLogin or OAuth authentication to access your spreadsheets. Below is a simple app I wrote for another user on this mailing list ( https://groups.google.com/forum/?fromgroups=#!topic/google-appengine/eYVriyuoeeI ), it opens up your Google Drive, collects all the spreadsheets, and searches for the content of the find_word variable in the first worksheet of all the spreadsheets. This app uses ClientLogin, so you'll need to put in your Google user/pass into the appropriate variables. There's more documentation and examples here: https://developers.google.com/google-apps/spreadsheets/#retrieving_a_list-based_feed 

 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

My Go side project: http://invalidmail.com/

[Nijin Narayanan]

Is this Oauth token for one time use or permanent ? 

Using the same oauth token, can i able access it on future ?

Is that possible to give an access to a specific spreadsheet or this will give a permission for accessing all spreadsheet ?

-Nijin Narayanan

--
You received this message because you are subscribed to the Google Groups "Google App Engine" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-appengi...@googlegroups.com.
To post to this group, send email to google-a...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-appengine?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Vinny P]

On Wednesday, May 1, 2013 2:19:01 AM UTC-5, Nijin Narayanan wrote:

Is this Oauth token for one time use or permanent ? 

Permanent use, unless the user revokes application access. See http://support.google.com/accounts/bin/answer.py?hl=en&answer=41236

On Wednesday, May 1, 2013 2:19:01 AM UTC-5, Nijin Narayanan wrote:

Using the same oauth token, can i able access it on future ?

Yes, as long as the user hasn't revoked application access (see above link). 

 

On Wednesday, May 1, 2013 2:19:01 AM UTC-5, Nijin Narayanan wrote:

Is that possible to give an access to a specific spreadsheet or this will give a permission for accessing all spreadsheet ?

It gives access to all spreadsheets. You could also make the spreadsheet public; then you don't need to grant access to all other spreadsheets. 

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

-Vinny P

Technology & Media Advisor

Chicago, IL

My Go side project: http://invalidmail.com/

[David Bou]

Hello,

   Thanks very much for your answer, which clarified many things. The spreadsheet is private to another user, so the best option here is OAuth2, which I first thought about.

   The workaround with ClientLogin is very rude since you have to protect the hardcoded password, and anyway not adapted to my situation since the spreadsheet is not private to me : so I will stay on the option OAuth2.

   But, I have a problem with this solution : there is no Java example. This is the only part of the documentation where the sample is only in .Net. So I tried to pick   Java equivalent by googling around, with no real good answer.

   I think a Java sample will be benefit for everybody.

Best regards,

David.