Hatena::ブログ(Diary)

明日の鍵

2011-08-09

Google Spreadsheets Data APIを使う 参照編

**Google Spreadsheet Data APIとは

GoogleのサービスをプログラムからアクセスできるGoogle Data APIのGoogle SpreadsheetsにアクセスするためのAPI

Google Data API - Google Code 
http://code.google.com/intl/ja/apis/gdata/
Google スプレッドシートの API とツール - Google Code 
http://code.google.com/intl/ja/apis/spreadsheets/

Google Data APIサイトには各言語から簡単にアクセスするためのライブラリが公開されています。

Java JavaScript Python .NETなど

ライブラリのダウンロード

Google Data APIサイトからライブラリをダウンロードします。

Java用ライブラリはここからダウンロード

Downloads - gdata-java-client - Google Data Java Client Library - Google Project Hosting 
http://code.google.com/p/gdata-java-client/downloads/list

サンプル付きとソースコード付きの2つがあります。

ソースコード付きの gdata-src.java-1.45.0.zip をダウンロードします。

ライブラリのインポート

ダウンロードしたzipファイルを解凍し、jarファイルをビルドパスに追加します。

Google Spreadsheets Data APIにアクセスするためには以下のライブラリが必要です。

  • /gdata/java/lib/

gdata-core-1.0.jar

gdata-client-meta-1.0.jar

gdata-client-1.0.jar

gdata-media-1.0.jar

gdata-spreadsheet-meta-3.0.jar

gdata-spreadsheet-3.0.jar

gdata-docs-meta-3.0.jar

gdata-docs-3.0.jar

  • /gdata/java/deps/

google-collect-1.0-rc1.jar

認証

非公開のspreadsheetsにアクセスするためには認証をする必要があります。

認証方式にはOAuthとAuthStubとClientLoginがあります。

今回はデスクトップアプリケーション向けにClientLoginを使用します。

WebアプリケーションならばOAuthかAuthStubを使うべきです。

OAuthはここが参考になるかもしれません。*1

Google Data APIのOAUTHに挑戦 - 気楽なC#工房 
http://csfun.blog49.fc2.com/blog-entry-46.html
ClientLogin
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.util.AuthenticationException;

public class ClientLogin {

    private static final String APPLICATION_NAME = "tomorrowkey-" + ClientLogin.class.getSimpleName() + "-v1";

    public static void main(String[] args) {
        SpreadsheetService client = new SpreadsheetService(APPLICATION_NAME);
        String username = ArgumentUtil.getUsernameFromArgument(args);
        String password = ArgumentUtil.getPasswordFromArgument(args);
        try {
            client.setUserCredentials(username, password);
        } catch (AuthenticationException e) {
            e.printStackTrace();
        }
    }
}

アプリケーション名(APPLICATION_NAME)は

[会社名]-[アプリケーション名]-[バージョン]

という命名規則に従って設定します。

この規則に従っていなくても動作はしますが…。

Google Spreadsheetにアクセスする場合は常にSpreadsheetServiceを使用します。

スプレッドシート一覧を取得する

自分のDocsに保存されているSpreadsheetsの名前を表示します。

ソースコード
import java.io.IOException;
import java.net.URL;
import java.util.List;

import com.google.gdata.client.spreadsheet.FeedURLFactory;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;

public class PrintAllSpreadsheet {

    private static final String APPLICATION_NAME = "tomorrowkey-" + PrintAllSpreadsheet.class.getSimpleName() + "-v1";

    public static final void main(String[] args) {
        String username = ArgumentUtil.getUsernameFromArgument(args);
        String password = ArgumentUtil.getPasswordFromArgument(args);
        PrintAllSpreadsheet printAllSpreadsheet = new PrintAllSpreadsheet();
        printAllSpreadsheet.start(username, password);
    }

    private SpreadsheetService client;

    private void start(String username, String password) {
        try {
            client = new SpreadsheetService(APPLICATION_NAME);
            client.setUserCredentials(username, password);

            URL url = FeedURLFactory.getDefault().getSpreadsheetsFeedUrl();
            SpreadsheetFeed feed = client.getFeed(url, SpreadsheetFeed.class);
            List<SpreadsheetEntry> spreadsheetEntryList = feed.getEntries();
            for (SpreadsheetEntry spreadsheetEntry : spreadsheetEntryList) {
                System.out.println(spreadsheetEntry.getTitle().getPlainText());
            }
        } catch (AuthenticationException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ServiceException e) {
            e.printStackTrace();
        }
    }
}
実行結果
TEST
投票シート
CharSettings作成シート
第2回Android温泉 会計
第2回Android温泉企画
第2回Android温泉参加者一覧
第2回Android温泉アンケート
集まれGDDのdeb仲間
デコ美統計
やることリスト
都道府県別DL数
デ部T申し込みフォーム
検索データ
ガソリン
北海道日程
北海道日程

スプレッドシート名で検索して取得する

ソースコード
import java.io.IOException;
import java.net.URL;
import java.util.List;

import com.google.gdata.client.spreadsheet.FeedURLFactory;
import com.google.gdata.client.spreadsheet.SpreadsheetQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;

public class PrintSearhedSpreadsheet {

    private static final String APPLICATION_NAME = "tomorrowkey-" + PrintSearhedSpreadsheet.class.getSimpleName() + "-v1";

    public static final void main(String[] args) {
        String username = ArgumentUtil.getUsernameFromArgument(args);
        String password = ArgumentUtil.getPasswordFromArgument(args);
        PrintSearhedSpreadsheet printSearhedSpreadsheet = new PrintSearhedSpreadsheet();
        printSearhedSpreadsheet.start(username, password);
    }

    private SpreadsheetService client;

    private void start(String username, String password) {
        try {
            client = new SpreadsheetService(APPLICATION_NAME);
            client.setUserCredentials(username, password);

            URL url = FeedURLFactory.getDefault().getSpreadsheetsFeedUrl();
            SpreadsheetQuery query = new SpreadsheetQuery(url);
            query.setTitleQuery("android");
            SpreadsheetFeed feed = client.query(query, SpreadsheetFeed.class);
            List<SpreadsheetEntry> spreadsheetEntryList = feed.getEntries();
            for (SpreadsheetEntry spreadsheetEntry : spreadsheetEntryList) {
                System.out.println(spreadsheetEntry.getTitle().getPlainText());
            }
        } catch (AuthenticationException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ServiceException e) {
            e.printStackTrace();
        }

    }
}
実行結果
第2回Android温泉 会計
第2回Android温泉企画
第2回Android温泉参加者一覧
第2回Android温泉アンケート

大文字/小文字は識別されないようですね。

ワークシート一覧を取得する

テスト用の「TEST」という名前のSpreadsheetと作成しました。

f:id:tomorrowkey:20110809122458p:image

ソースコード

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

import com.google.gdata.client.spreadsheet.FeedURLFactory;
import com.google.gdata.client.spreadsheet.SpreadsheetQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.data.spreadsheet.WorksheetFeed;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;

public class PrintAllWorksheet {

    private static final String APPLICATION_NAME = "tomorrowkey-" + PrintAllWorksheet.class.getSimpleName() + "-v1";

    public static final void main(String[] args) {
        String username = ArgumentUtil.getUsernameFromArgument(args);
        String password = ArgumentUtil.getPasswordFromArgument(args);
        PrintAllWorksheet printAllWorksheet = new PrintAllWorksheet();
        printAllWorksheet.start(username, password);
    }

    private SpreadsheetService client;

    private void start(String username, String password) {
        try {
            client = new SpreadsheetService(APPLICATION_NAME);
            client.setUserCredentials(username, password);
            SpreadsheetEntry spreadsheet = getSpreadsheet("TEST");

            URL url = spreadsheet.getWorksheetFeedUrl();
            WorksheetFeed feed = client.getFeed(url, WorksheetFeed.class);
            List<WorksheetEntry> worksheetEntryList = feed.getEntries();
            for (WorksheetEntry worksheetEntry : worksheetEntryList) {
                System.out.println(worksheetEntry.getTitle().getPlainText());
            }
        } catch (AuthenticationException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ServiceException e) {
            e.printStackTrace();
        }

    }

    private SpreadsheetEntry getSpreadsheet(String spreadsheetName) throws IOException,
            ServiceException {
        URL url = FeedURLFactory.getDefault().getSpreadsheetsFeedUrl();
        SpreadsheetQuery query = new SpreadsheetQuery(url);
        query.setTitleQuery(spreadsheetName);
        SpreadsheetFeed feed = client.query(query, SpreadsheetFeed.class);
        List<SpreadsheetEntry> spreadsheetEntryList = feed.getEntries();
        if (spreadsheetEntryList.isEmpty()) {
            throw new RuntimeException("not found spreadsheet '" + spreadsheetName + "'");
        }
        return spreadsheetEntryList.get(0);
    }
}
実行結果
東京
品川
新宿

ワークシート名で検索して取得する

ソースコード
import java.io.IOException;
import java.net.URL;
import java.util.List;

import com.google.gdata.client.spreadsheet.FeedURLFactory;
import com.google.gdata.client.spreadsheet.SpreadsheetQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.client.spreadsheet.WorksheetQuery;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.data.spreadsheet.WorksheetFeed;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;

public class PrintSearchedWorksheet {

    private static final String APPLICATION_NAME = "tomorrowkey-" + PrintSearchedWorksheet.class.getSimpleName() + "-v1";

    public static final void main(String[] args) {
        String username = ArgumentUtil.getUsernameFromArgument(args);
        String password = ArgumentUtil.getPasswordFromArgument(args);
        PrintSearchedWorksheet printAllWorksheet = new PrintSearchedWorksheet();
        printAllWorksheet.start(username, password);
    }

    private SpreadsheetService client;

    private void start(String username, String password) {
        try {
            client = new SpreadsheetService(APPLICATION_NAME);
            client.setUserCredentials(username, password);
            SpreadsheetEntry spreadsheet = getSpreadsheet("TEST");

            URL url = spreadsheet.getWorksheetFeedUrl();
            WorksheetQuery query = new WorksheetQuery(url);
            query.setTitleQuery("品川");
            WorksheetFeed feed = client.query(query, WorksheetFeed.class);
            List<WorksheetEntry> worksheetEntryList = feed.getEntries();
            for (WorksheetEntry worksheetEntry : worksheetEntryList) {
                System.out.println(worksheetEntry.getTitle().getPlainText());
            }
        } catch (AuthenticationException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ServiceException e) {
            e.printStackTrace();
        }
    }

    private SpreadsheetEntry getSpreadsheet(String spreadsheetName) throws IOException,
            ServiceException {
        URL url = FeedURLFactory.getDefault().getSpreadsheetsFeedUrl();
        SpreadsheetQuery query = new SpreadsheetQuery(url);
        query.setTitleQuery(spreadsheetName);
        SpreadsheetFeed feed = client.query(query, SpreadsheetFeed.class);
        List<SpreadsheetEntry> spreadsheetEntryList = feed.getEntries();
        if (spreadsheetEntryList.isEmpty()) {
            throw new RuntimeException("not found spreadsheet '" + spreadsheetName + "'");
        }
        return spreadsheetEntryList.get(0);
    }
}
実行結果
品川

ワークシートからリスト一覧を取得する

リストとは行のことであり、spreadsheetをデータベースのように扱う場合、レコードのようなイメージになります。

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

import com.google.gdata.client.spreadsheet.FeedURLFactory;
import com.google.gdata.client.spreadsheet.SpreadsheetQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.client.spreadsheet.WorksheetQuery;
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.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.data.spreadsheet.WorksheetFeed;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;

public class PrintAllList {

    private static final String APPLICATION_NAME = "tomorrowkey-" + PrintAllList.class.getSimpleName() + "-v1";

    public static final void main(String[] args) {
        String username = ArgumentUtil.getUsernameFromArgument(args);
        String password = ArgumentUtil.getPasswordFromArgument(args);
        PrintAllList printAllWorksheet = new PrintAllList();
        printAllWorksheet.start(username, password);
    }

    private SpreadsheetService client;

    private void start(String username, String password) {
        try {
            client = new SpreadsheetService(APPLICATION_NAME);
            client.setUserCredentials(username, password);
            WorksheetEntry worksheet = getWorksheet("TEST", "東京");

            URL url = worksheet.getListFeedUrl();
            ListFeed feed = client.getFeed(url, ListFeed.class);
            List<ListEntry> listEntryList = feed.getEntries();
            for (ListEntry listEntry : listEntryList) {
                CustomElementCollection customElements = listEntry.getCustomElements();
                StringBuilder sb = new StringBuilder();
                sb.append("品目:").append(customElements.getValue("品目")).append(",");
                sb.append("価格:").append(customElements.getValue("価格")).append(",");
                sb.append("在庫:").append(customElements.getValue("在庫"));
                System.out.println(sb.toString());
            }
        } catch (AuthenticationException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ServiceException e) {
            e.printStackTrace();
        }

    }

    private WorksheetEntry getWorksheet(String spreadsheetName, String worksheetName)
            throws IOException, ServiceException {
        SpreadsheetEntry spreadsheet = getSpreadsheet(spreadsheetName);
        URL url = spreadsheet.getWorksheetFeedUrl();
        WorksheetQuery query = new WorksheetQuery(url);
        WorksheetFeed feed = client.query(query, WorksheetFeed.class);
        List<WorksheetEntry> worksheetEntryList = feed.getEntries();
        if (worksheetEntryList.isEmpty()) {
            throw new RuntimeException("not found worksheet '" + worksheetName + "' in '"
                    + spreadsheetName + "'");
        }
        return worksheetEntryList.get(0);
    }

    private SpreadsheetEntry getSpreadsheet(String spreadsheetName) throws IOException,
            ServiceException {
        URL url = FeedURLFactory.getDefault().getSpreadsheetsFeedUrl();
        SpreadsheetQuery query = new SpreadsheetQuery(url);
        query.setTitleQuery(spreadsheetName);
        SpreadsheetFeed feed = client.query(query, SpreadsheetFeed.class);
        List<SpreadsheetEntry> spreadsheetEntryList = feed.getEntries();
        if (spreadsheetEntryList.isEmpty()) {
            throw new RuntimeException("not found spreadsheet '" + spreadsheetName + "'");
        }
        return spreadsheetEntryList.get(0);
    }
}
実行結果
品目:りんご,価格:100,在庫:10
品目:みかん,価格:80,在庫:20
品目:もも,価格:160,在庫:3

ワークシートを検索してリストを取得する

ソースコード
import java.io.IOException;
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.SpreadsheetQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.client.spreadsheet.WorksheetQuery;
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.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.data.spreadsheet.WorksheetFeed;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;

public class PrintSearchedList {

    private static final String APPLICATION_NAME = "tomorrowkey-"
            + PrintSearchedList.class.getSimpleName() + "-v1";

    public static final void main(String[] args) {
        String username = ArgumentUtil.getUsernameFromArgument(args);
        String password = ArgumentUtil.getPasswordFromArgument(args);
        PrintSearchedList printAllWorksheet = new PrintSearchedList();
        printAllWorksheet.start(username, password);
    }

    private SpreadsheetService client;

    private void start(String username, String password) {
        try {
            client = new SpreadsheetService(APPLICATION_NAME);
            client.setUserCredentials(username, password);
            WorksheetEntry worksheet = getWorksheet("TEST", "東京");

            URL url = worksheet.getListFeedUrl();
            ListQuery query = new ListQuery(url);
            query.setFullTextQuery("*ん");
            ListFeed feed = client.getFeed(query, ListFeed.class);
            List<ListEntry> listEntryList = feed.getEntries();
            for (ListEntry listEntry : listEntryList) {
                CustomElementCollection customElements = listEntry.getCustomElements();
                StringBuilder sb = new StringBuilder();
                sb.append("品目:").append(customElements.getValue("品目")).append(",");
                sb.append("価格:").append(customElements.getValue("価格")).append(",");
                sb.append("在庫:").append(customElements.getValue("在庫"));
                System.out.println(sb.toString());
            }
        } catch (AuthenticationException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ServiceException e) {
            e.printStackTrace();
        }

    }

    private WorksheetEntry getWorksheet(String spreadsheetName, String worksheetName)
            throws IOException, ServiceException {
        SpreadsheetEntry spreadsheet = getSpreadsheet(spreadsheetName);
        URL url = spreadsheet.getWorksheetFeedUrl();
        WorksheetQuery query = new WorksheetQuery(url);
        WorksheetFeed feed = client.query(query, WorksheetFeed.class);
        List<WorksheetEntry> worksheetEntryList = feed.getEntries();
        if (worksheetEntryList.isEmpty()) {
            throw new RuntimeException("not found worksheet '" + worksheetName + "' in '"
                    + spreadsheetName + "'");
        }
        return worksheetEntryList.get(0);
    }

    private SpreadsheetEntry getSpreadsheet(String spreadsheetName) throws IOException,
            ServiceException {
        URL url = FeedURLFactory.getDefault().getSpreadsheetsFeedUrl();
        SpreadsheetQuery query = new SpreadsheetQuery(url);
        query.setTitleQuery(spreadsheetName);
        SpreadsheetFeed feed = client.query(query, SpreadsheetFeed.class);
        List<SpreadsheetEntry> spreadsheetEntryList = feed.getEntries();
        if (spreadsheetEntryList.isEmpty()) {
            throw new RuntimeException("not found spreadsheet '" + spreadsheetName + "'");
        }
        return spreadsheetEntryList.get(0);
    }
}
実行結果
品目:みかん,価格:80,在庫:20

検索条件には完全一致はもちろん、"*"を使って簡単なあいまい検索もできるみたいです。

ワークシートからすべてのセルを取得する

ソースコード
import java.io.IOException;
import java.net.URL;
import java.util.List;

import com.google.gdata.client.spreadsheet.FeedURLFactory;
import com.google.gdata.client.spreadsheet.SpreadsheetQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.client.spreadsheet.WorksheetQuery;
import com.google.gdata.data.spreadsheet.CellEntry;
import com.google.gdata.data.spreadsheet.CellFeed;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.data.spreadsheet.WorksheetFeed;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;

public class PrintAllCell {

    private static final String APPLICATION_NAME = "tomorrowkey-"
            + PrintAllCell.class.getSimpleName() + "-v1";

    public static final void main(String[] args) {
        String username = ArgumentUtil.getUsernameFromArgument(args);
        String password = ArgumentUtil.getPasswordFromArgument(args);
        PrintAllCell printAllWorksheet = new PrintAllCell();
        printAllWorksheet.start(username, password);
    }

    private SpreadsheetService client;

    private void start(String username, String password) {
        try {
            client = new SpreadsheetService(APPLICATION_NAME);
            client.setUserCredentials(username, password);
            WorksheetEntry worksheet = getWorksheet("TEST", "東京");

            URL url = worksheet.getCellFeedUrl();
            CellFeed feed = client.getFeed(url, CellFeed.class);
            List<CellEntry> cellEntryList = feed.getEntries();
            for (CellEntry cellEntry : cellEntryList) {
                System.out.println(cellEntry.getCell().getValue());
            }
        } catch (AuthenticationException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ServiceException e) {
            e.printStackTrace();
        }
    }

    private WorksheetEntry getWorksheet(String spreadsheetName, String worksheetName)
            throws IOException, ServiceException {
        SpreadsheetEntry spreadsheet = getSpreadsheet(spreadsheetName);
        URL url = spreadsheet.getWorksheetFeedUrl();
        WorksheetQuery query = new WorksheetQuery(url);
        WorksheetFeed feed = client.query(query, WorksheetFeed.class);
        List<WorksheetEntry> worksheetEntryList = feed.getEntries();
        if (worksheetEntryList.isEmpty()) {
            throw new RuntimeException("not found worksheet '" + worksheetName + "' in '"
                    + spreadsheetName + "'");
        }
        return worksheetEntryList.get(0);
    }

    private SpreadsheetEntry getSpreadsheet(String spreadsheetName) throws IOException,
            ServiceException {
        URL url = FeedURLFactory.getDefault().getSpreadsheetsFeedUrl();
        SpreadsheetQuery query = new SpreadsheetQuery(url);
        query.setTitleQuery("TEST");
        SpreadsheetFeed feed = client.query(query, SpreadsheetFeed.class);
        List<SpreadsheetEntry> spreadsheetEntryList = feed.getEntries();
        if (spreadsheetEntryList.isEmpty()) {
            throw new RuntimeException("not found spreadsheet '" + spreadsheetName + "'");
        }
        return spreadsheetEntryList.get(0);
    }
}
実行結果
品目
価格
在庫
りんご
100
10
みかん
80
20
もも
160
3

ワークシートを検索してセルを取得する

ソースコード
import java.io.IOException;
import java.net.URL;
import java.util.List;

import com.google.gdata.client.spreadsheet.CellQuery;
import com.google.gdata.client.spreadsheet.FeedURLFactory;
import com.google.gdata.client.spreadsheet.SpreadsheetQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.client.spreadsheet.WorksheetQuery;
import com.google.gdata.data.spreadsheet.CellEntry;
import com.google.gdata.data.spreadsheet.CellFeed;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.data.spreadsheet.WorksheetFeed;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;

public class PrintSearchedCell {

    private static final String APPLICATION_NAME = "tomorrowkey-"
            + PrintSearchedCell.class.getSimpleName() + "-v1";

    public static final void main(String[] args) {
        String username = ArgumentUtil.getUsernameFromArgument(args);
        String password = ArgumentUtil.getPasswordFromArgument(args);
        PrintSearchedCell printAllWorksheet = new PrintSearchedCell();
        printAllWorksheet.start(username, password);
    }

    private SpreadsheetService client;

    private void start(String username, String password) {
        try {
            client = new SpreadsheetService(APPLICATION_NAME);
            client.setUserCredentials(username, password);
            WorksheetEntry worksheet = getWorksheet("TEST", "東京");

            URL url = worksheet.getCellFeedUrl();
            CellQuery query = new CellQuery(url);
            query.setFullTextQuery("*0");
            CellFeed feed = client.getFeed(query, CellFeed.class);
            List<CellEntry> cellEntryList = feed.getEntries();
            for (CellEntry cellEntry : cellEntryList) {
                System.out.println(cellEntry.getCell().getValue());
            }
        } catch (AuthenticationException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ServiceException e) {
            e.printStackTrace();
        }
    }

    private WorksheetEntry getWorksheet(String spreadsheetName, String worksheetName)
            throws IOException, ServiceException {
        SpreadsheetEntry spreadsheet = getSpreadsheet(spreadsheetName);
        URL url = spreadsheet.getWorksheetFeedUrl();
        WorksheetQuery query = new WorksheetQuery(url);
        WorksheetFeed feed = client.query(query, WorksheetFeed.class);
        List<WorksheetEntry> worksheetEntryList = feed.getEntries();
        if (worksheetEntryList.isEmpty()) {
            throw new RuntimeException("not found worksheet '" + worksheetName + "' in '"
                    + spreadsheetName + "'");
        }
        return worksheetEntryList.get(0);
    }

    private SpreadsheetEntry getSpreadsheet(String spreadsheetName) throws IOException,
            ServiceException {
        URL url = FeedURLFactory.getDefault().getSpreadsheetsFeedUrl();
        SpreadsheetQuery query = new SpreadsheetQuery(url);
        query.setTitleQuery("TEST");
        SpreadsheetFeed feed = client.query(query, SpreadsheetFeed.class);
        List<SpreadsheetEntry> spreadsheetEntryList = feed.getEntries();
        if (spreadsheetEntryList.isEmpty()) {
            throw new RuntimeException("not found spreadsheet '" + spreadsheetName + "'");
        }
        return spreadsheetEntryList.get(0);
    }
}
実行結果
100
10
80
20
160

まとめ

1. FeedのURLを取得する

・Spreadsheet

→FeedURLFactory.getDefault().getSpreadsheetsFeedUrl();

・Worksheet

→SpreadsheetEntry#getWorksheetFeedUrl();

・List

→WorksheetEntry#getListFeedUrl();

Cell

→WorksheetEntry#getCellFeedUrl();

2. 検索する場合はQueryを作成する

・Spreadsheet

→SpreadsheetQuery

・Worksheet

→WorksheetQuery

・List

→ListQuery

・Cell

→CellQuery

3. URL/Queryを使い、Feedを作成する

・Spreadsheet

→SpreadsheetService#getFeed(#URL, SpreadsheetFeed.class);

→SpreadsheetService#getFeed(#SpreadsheetQuery, SpreadsheetFeed.class);

・Worksheet

→SpreadsheetService#getFeed(#URL, WorksheetFeed.class);

→SpreadsheetService#getFeed(#WorksheetQuery, WorksheetFeed.class);

・List

→SpreadsheetService#getFeed(#URL, ListFeed.class);

→SpreadsheetService#getFeed(#ListQuery, ListFeed.class);

・Cell

→SpreadsheetService#getFeed(#URL, CellFeed.class);

→SpreadsheetService#getFeed(#CellQuery, CellFeed.class);

4. List<Entry>を取得する

Feed#getEntries();

所感

同じようなコードをたくさん書いて疲れた、、。

とりあえず参照はこれだけあればできるはず。

他にRecordとTableってクラスがあるけど使ったことないなあ。

*1:試していません。いつかやりたいな