Thursday, April 11, 2013

Very simple Google Spreadsheet code

FYI - this blog post is from a long time ago, and I think things have changed quite a bit.  I'm leaving it here because some parts my still be useful, but definitely read the comments and don't expect it to work out of the box.   - James, 26 Nov 2013

(Update - see comment from Sean about this no longer working due to auth changes from Google)

I wanted to use data from a google docs spreadsheet in a project, and the sample code all talks about how to authenticate and get lists of documents for a user.
In my case, I already had a public document that I wanted to read. 
You’ll want to read the doc here first, but there were two gaps. 
  1. It wasn’t straightforward (at least for me) to figure out exactly what the URL for a public spreadsheet should be (the doc concentrates on private documents, and assumes you’re going to be iterating through a list of available spreadsheets, not going straight to a specific one).
  2. It wasn’t clear which Java classes mapped to the URLs I had.
So here’s a very simple sample, reading data from this public spreadsheet:

The output:
One
1
Two
2
Three
3
The code:
package com.banshee;

import java.io.IOException;
import java.net.URL;

import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.spreadsheet.CustomElementCollection;
import com.google.gdata.data.spreadsheet.ListEntry;
import com.google.gdata.data.spreadsheet.ListFeed;
import com.google.gdata.util.ServiceException;

public class SpreadsheetSucker {
  public static void main(String[] args) {
    SpreadsheetService service = new SpreadsheetService("com.banshee");
    try {
      // Notice that the url ends
      // with default/public/values.
      // That wasn't obvious (at least to me)
      // from the documentation.
      String urlString = "https://spreadsheets.google.com/feeds/list/0AsaDhyyXNaFSdDJ2VUxtVGVWN1Yza1loU1RPVVU3OFE/default/public/values";

      // turn the string into a URL
      URL url = new URL(urlString);

      // You could substitute a cell feed here in place of
      // the list feed
      ListFeed feed = service.getFeed(url, ListFeed.class);

      for (ListEntry entry : feed.getEntries()) {
        CustomElementCollection elements = entry.getCustomElements();
        String name = elements.getValue("name");
        System.out.println(name);
        String number = elements.getValue("Number");
        System.out.println(number);
      }
    } catch (IOException e) {
      e.printStackTrace();
    } catch (ServiceException e) {
      e.printStackTrace();
    }

  }
}

7 comments:

Sean Brown said...

This no longer works, as Google Drive requires a different type of authentication. It's unfortunate.

InstantWebApp said...

Great post about google spreadsheets.

another way is to download as csv

https://docs.google.com/spreadsheet/ccc?key=0AsaDhyyXNaFSdDJ2VUxtVGVWN1Yza1loU1RPVVU3OFE&output=csv

There are good examples here, http://gdata-java-client.googlecode.com/svn-history/r51/trunk/java/sample/spreadsheet/cell/CellDemo.java
But you need to create a throw away "Application" password for your google account, as they use a password and not OAuth.

Eddy.

MULTANI said...

nice post... but where u get that link??

https://spreadsheets.google.com/feeds/list/0AsaDhyyXNaFSdDJ2VUxtVGVWN1Yza1loU1RPVVU3OFE/default/public/values

how i can get links like that for my own spreadsheet?

James Moore said...

That was just a link from the "sharing" feature - but things might have changed since I posted this.

MULTANI said...

Hi James,

Yeah i check the shearing features and add "public for anyone" but when i try to get it, with some thing like this:

https://spreadsheets.google.com/feeds/list/0AsaDhy....

That display me an error

"The spreadsheet at this URL could not be found. Make sure that you have the right URL and that the owner of the spreadsheet hasn't deleted it."

The thing is when i press inside spreadsheet: "file-> Publish to the Web" i can get the cells and listfeed but... the problem is that i need to get this access with via Google Drive API or other api but i dont know how.. please if u know how let me know

Jelle Fresen said...

Hi, thanks for this example. I'd like to add some hints for those who are struggling with setting up their android project in Eclipse, since it took me quite some time to figure out how to setup mine.

Reading the available material on the Internet, you are directed more then once to add the Drive API through the Google Plugin for Eclipse. However, this is not necessary for this example (in fact, it will most likely lead to build problems), I'm guessing because we don't need authentication. So, don't add the Drive API.

So instead you probably think you would have to add the Google Spreadsheet API. And browsing through the managed APIs in the Google Plugin for Eclipse you find what you need, albeit v2.1 instead of the recommended v3. Guess what: underneath, v2.1 actually is v3. Confusing to say the least... But it gets even better! 'turns out the managed Google Spreadsheet API doesn't add the (correct) dependend libraries, and since it is a *managed* API, we can't manage this on our own. So in the end, don't add the Spreadsheets API. (and... we're back to the start)

So, what DO we need? We need to add the right jarfiles manually and we can find these files in gdata-src.java-1.47.1.zip here (or whatever version you find there when you read this): https://code.google.com/p/gdata-java-client/downloads/list
Unpack the zipfile and go to /gdata/java/
Now, copy the following jarfiles to your project/libs folder:
/gdata/java/deps/guava-11.0.2.jar
/gdata/java/deps/jsr305.jar
/gdata/java/lib/gdata-client-1.0.jar
/gdata/java/lib/gdata-client-meta-1.0.jar
/gdata/java/lib/gdata-core-1.0.jar
/gdata/java/lib/gdata-spreadsheet-3.0.jar
/gdata/java/lib/gdata-spreadsheet-meta-3.0.jar

And now you should be ready to rumble! (at least, at the time of writing; november 2013)

ChaZ said...

No longer supported as Google has mandated OAuth2.0 for accessing data from Sheets. There is still a workaround by accessing the Sheets data as JSON and parsing it at your app's end.

Access JSON using this URL: https://spreadsheets.google.com/tq?key=

E.g.: https://spreadsheets.google.com/tq?key=1tJ64Y8hje0ui4ap9U33h3KWwpxT_-JuVMSZzxD2Er8k