Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get public Google Spreadsheet data using Google Spreadsheet API Java library without authentication

-what I want to do

I would like to get data from Google Spreadsheet using Google Spreadsheet API Java library without authentication. The Google Spreadsheet is published with public. I would like to use the following method: com.google.gdata.data.spreadsheet.CustomElementCollection

-Issue

CustomElementCollection return collect data with authentication. But CustomElementCollection return null without authentication.

As listEntry.getPlainTextContent() shows data, so I think I should be able to get the data in any ways.

-Source code attached

With authentication: Auth.java

import java.net.URL;
import java.util.List;
import com.google.gdata.client.spreadsheet.ListQuery;
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.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.WorksheetEntry;

public class Auth {
    public static void main(String[] args) throws Exception{
        String applicationName = "AppName";
        String user = args[0];
        String pass = args[1];
        String key = args[2];
        String query = args[3];

        SpreadsheetService service = new SpreadsheetService(applicationName);
        service.setUserCredentials(user, pass); //set client auth 

        URL entryUrl = new URL("http://spreadsheets.google.com/feeds/spreadsheets/" + key);
        SpreadsheetEntry spreadsheetEntry = service.getEntry(entryUrl, SpreadsheetEntry.class);
        WorksheetEntry worksheetEntry = spreadsheetEntry.getDefaultWorksheet();

        ListQuery listQuery = new ListQuery(worksheetEntry.getListFeedUrl());
        listQuery.setSpreadsheetQuery( query );

        ListFeed listFeed = service.query(listQuery, ListFeed.class);
        List<ListEntry> list = listFeed.getEntries();
        for( ListEntry listEntry : list )
        {
            System.out.println( "content=[" + listEntry.getPlainTextContent() + "]");
            CustomElementCollection elements = listEntry.getCustomElements();
            System.out.println(
                    " name=" + elements.getValue("name") + 
                    " age="  + elements.getValue("age") );
        }
    }
}

Without authentication: NoAuth.java

import java.net.URL;
import java.util.List;
import com.google.gdata.client.spreadsheet.FeedURLFactory;
import com.google.gdata.client.spreadsheet.ListQuery;
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.data.spreadsheet.WorksheetEntry;
import com.google.gdata.data.spreadsheet.WorksheetFeed;

public class NoAuth {
    public static void main(String[] args) throws Exception{
        String applicationName = "AppName";
        String key = args[0];
        String query = args[1];

        SpreadsheetService service = new SpreadsheetService(applicationName);

        URL url = FeedURLFactory.getDefault().getWorksheetFeedUrl(key, "public", "basic");

        WorksheetFeed feed = service.getFeed(url, WorksheetFeed.class);
        List<WorksheetEntry> worksheetList = feed.getEntries();
        WorksheetEntry worksheetEntry = worksheetList.get(0);

        ListQuery listQuery = new ListQuery(worksheetEntry.getListFeedUrl());
        listQuery.setSpreadsheetQuery( query );

        ListFeed listFeed = service.query( listQuery, ListFeed.class );
        List<ListEntry> list = listFeed.getEntries();
        for( ListEntry listEntry : list )
        {
            System.out.println( "content=[" + listEntry.getPlainTextContent() + "]");
            CustomElementCollection elements = listEntry.getCustomElements();
            System.out.println(
                    " name=" + elements.getValue("name") + 
                    " age="  + elements.getValue("age") );
        }
    }
}

Google Spreadsheet:

https://docs.google.com/spreadsheet/pub?key=0Ajawooo6A9OldHV0VHYzVVhTZlB6SHRjbGc5MG1CakE&output=html

-Result

Without authentication

   content=[age: 23]    name=null age=null

With authentication

   content=[age: 23]    name=Taro age=23

Please let me know the useful information to avoid the issue.

like image 247
Junya Sato Avatar asked Mar 03 '13 09:03

Junya Sato


2 Answers

I don't know why it works like that, but when you don't access request with credentials, you are not able to retrieve cells via:

CustomElementCollection elements = listEntry.getCustomElements();
System.out.println(" name=" + elements.getValue("name") + " age="  + elements.getValue("age") );

I've tested it and I have found only this way to retrieve data:

List<ListEntry> list = listFeed.getEntries();
for (ListEntry row : list) {
    System.out.println(row.getTitle().getPlainText() + "\t"
            + row.getPlainTextContent());
}

It prints:

Taro    age: 23
Hanako  age: 16

As you see, you should parse text and retrieve age from raw String.

like image 83
Michał Ziober Avatar answered Oct 05 '22 02:10

Michał Ziober


I believe the problem is that you are using the "basic" projection for your spreadsheet. If you use the "values" projection, everything should work as expected.

like image 28
euclio Avatar answered Oct 05 '22 03:10

euclio