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温泉アンケート
大文字/小文字は識別されないようですね。
ソースコード
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
Feed#getEntries();
所感
同じようなコードをたくさん書いて疲れた、、。
とりあえず参照はこれだけあればできるはず。
他にRecordとTableってクラスがあるけど使ったことないなあ。
*1:試していません。いつかやりたいな