AppsのスプレッドシートデータをApp Engineから読み取る

クラウド

技術解説

連載 :

Excel使用をそのままに、本格クラウド利用を実現する。

2013年11月13日(水)

清野克行

2.4 ワークシートのデータを簡易形式で一覧表示する

Web画面での簡易形式表示は、JavaScriptの記述が容易で、Spreadsheet検索データをWeb画面から確認する場合に便利です。スプレッドシート風の画面表示もありますが、これについては次回の連載で紹介します。

リスト4　全店舗一覧表示（getSpreadAllShopYm.htm）

01<!DOCTYPE html>
02<html>
03<head>
04<meta charset="utf-8"/>
05<title> getSpreadAllShopYm </title>
06<script type="text/javascript" src="../js/jquery-2.0.3.min.js"></script>
07<script type="text/javascript">
08$(function() {
09  $("#rev").click(function(e) {
10    var query = {};
11    query["mode"] = "getspreadallshopym"; 
12    //　実行するビーンズメソッドを指定して連想配列（query）にセット
13    query["sheet"] = $("#year").val() + $("#month").val(); 
14    //セレクトメニューで指定された年月からシート名を組み立ててqueryにセット
15    $.get("/spread", query, function(res) { 
16    //　jQueryの$.getでサーブレットにリクエストを送信　
17      var r = $.parseJSON(res);
18      //サーブレットからレスポンス受信したJSON形式の文字列をJavaScript オブジェクトに変換
19      for (var i = 0; i < r.sheet.length -1; i++) {
20        items.options[items.options.length] = new Option(r.sheet[i].data);
21        //受信したワークシートのデータを、セレクトメニューのオプション項目として表示 
22      }
23    });
24  }); 
25});
26</script>
27</head>
28<body>
29<h3>Spread Sheet 店舗個別読取</h3>
30年：<select id="year">
31  <option value="">=年選択=</option>
32  <option value="2013">２０１３年</option>
33  <option value="2012">２０１２年</option>
34  <option value="2011">２０１１年</option>
35</select>
36月：<select id="month">
37  <option value="">=月選択=</option>
38  <option value="01">１月</option>
39  <option value="02">２月</option>
40  <option value="03">３月</option>
41  <option value="04">４月</option>
42  <option value="05">５月</option>
43  <option value="06">６月</option>
44  <option value="07">７月</option>
45  <option value="08">８月</option>
46  <option value="09">９月</option>
47  <option value="10">１０月</option>
48  <option value="11">１１月</option>
49  <option value="12">１２月</option>
50</select>
51<input type="button"  id="rev"  value=" 参照 "/><hr/>
52<select id="items"  size="15"  style="width: 490px"></select><br/>
53</body>
54</html>

リスト4は、ワークシート1枚のデータすべてを一覧で表示しますが、サーブレットからの受信データをセレクトメニューのオプション項目として行単位で追加表示しています。この表示方式は簡単で、スプレッドデータの検索確認結果用としては便利ですが、表示内容が見やすいとは言えません。
→　Spread Sheet 年月別店舗売上

図13：Spread Sheet　年月別店舗売上（簡易表示）（クリックで拡大）

次に、画面から店舗名を指定して店舗単位のスプレッドデータを表示するサンプルを紹介します。またこのサンプルではスプレッドのデータを取得する方法として、スプレッドシートのKey値を使用しています。

2.5 ワークシート店舗別表示のビーンズメソッドを記述する

リスト5　店舗別表示（getSpreadShopYmメソッド）

01public String getSpreadShopYm(String sheet, String shop) { 
02  String appliName = "cyberspace-SpreadsheetSearch-1";
03  String user = "xxxxxxxxxxxxxxxxxxx";
04  String pass = "yyyyyyyyyyy";
05    try {
06    ///////////////////////////////////////////////////////
07    //        [I] 認証処理
08    SpreadsheetService spreadsheetservice = new SpreadsheetService(appliName);
09    spreadsheetservice.setUserCredentials(user, pass);
10    ///////////////////////////////////////////////////////
11    //        [II] 検索対象のスプレッドシートを特定
12    String key = "0Al8sybupdpoxxxxxxxxxxxxxxxxxzcUFmRnc";
13    URL spurl = new URL("http://spreadsheets.google.com/feeds/spreadsheets/" + key);
14    SpreadsheetEntry spreadsheetentry 
15      = spreadsheetservice.getEntry(spurl, SpreadsheetEntry.class);
16    ///////////////////////////////////////////////////////
17    //        [III] 検索対象のワークシートを特定
18    WorksheetEntry worksheetentry = spreadsheetentry.getDefaultWorksheet(); 
19    ///////////////////////////////////////////////////////
20    //        [IV] クエリでワークシートデータの検索
21    ListQuery listquery = new ListQuery(worksheetentry.getListFeedUrl());
22    ListFeed listfeed = spreadsheetservice.query(listquery, ListFeed.class); 
23    ListEntry listentry = listfeed.getEntries().get(Integer.parseInt(shop));
24    CustomElementCollection elements = listEntry.getCustomElements();
25    ///////////////////////////////////////////////////////
26    //        [V] ワークシートデータをJSONフォーマットで返す
27    String rv = "{\"stat\": \"" + "参照成功" 
28           + "\", \"food\": \"" + elements.getValue("食品") 
29           + "\", \"electric\": \"" + elements.getValue("家電") 
30           + "\", \"bedding\": \"" + elements.getValue("寝具") 
31           + "\", \"other\": \"" + elements.getValue("その他") 
32           + "\", \"total\": \"" + elements.getValue("合計") 
33           + "\"}";
34    return rv; 
35  } catch (AuthenticationException e) {
36    e.printStackTrace();
37    return "参照不成功　：" + e;
38  } catch (IOException e) {
39    e.printStackTrace();
40    return "参照不成功　：" + e;
41  } catch (ServiceException e) {
42    e.printStackTrace();
43    return "参照不成功　：" + e;
44  } 
45} 

リスト5のビーンズメソッドは、リスト3と、[I]の認証処理と[III]の検索対象のワークシート特定は同じです。

[II] 検索対象のスプレッドシートを特定する

1String key = "0Al8sybupdpoFdEdOTUtPLWxjOGRGckVvaklzcUFmRnc";                    （1）
2URL spurl = new URL("http://spreadsheets.google.com/feeds/spreadsheets/" + key);        （2）
3SpreadsheetEntry spreadsheetentry 
4  = spreadsheetservice.getEntry(spurl, SpreadsheetEntry.class);                     （3）

[II] の検索対象のスプレッドシート特定　ではキーを使用しています。このキー値はシート画面を開いた時のアドレスバーに表示されます。

図14：Spreadsheet画面とアドレスバーに表示されるキー値（クリックで拡大）

（1）では図15の、URLキー　key=0Al8sybupdpoFdEdOTUtPLWxjOGRGckVvaklzcUFmRnc　を文字列にセットし、（2）でこのキー値を使用してURLオブジェクトを生成しています。
（3）次に　getEntry　メソッドの第一引数に（2）で生成したURLオブジェクトして実行することにより、アクセス可能なスプレッドシートの中から１つのスプレッドシートを選択してspreadsheetentryに返します。

[IV] クエリでワークシートデータの検索

1ListQuery listquery = new ListQuery(worksheetentry.getListFeedUrl());       （1）
2ListFeed listfeed = spreadsheetservice.query(listquery, ListFeed.class);    （2）
3ListEntry listentry = listfeed.getEntries().get(Integer.parseInt(shop));    （3）
4CustomElementCollection elements = listEntry.getCustomElements();           （4）

（1）と（2）はリスト3の処理と同様です。
（3）ではListFeedのgetEntries()メソッドでワークシートのデータエントリをアクセス可能な形式で取得し、getメソッドで店舗名に対応するインデックス値を指定することによって、店舗名に対応するデータエントリを取得します。
（4）ワークシート行に含まれるデータ項目をコレクション形式で取得します。

[V] ワークシートデータをJSONフォーマットで返す

1String rv = "{\"stat\": \"" + "参照成功" 
2  + "\", \"food\": \"" + elements.getValue("食品")                    （1）
3  + "\", \"electric\": \"" + elements.getValue("家電")            （2）
4  + "\", \"bedding\": \"" + elements.getValue("寝具")                 （3）
5  + "\", \"other\": \"" + elements.getValue("その他")              （4）
6  + "\", \"total\": \"" + elements.getValue("合計")               （5）
7  + "\"}";
8  return rv; 

クライアントに返すJSONデータを作っていますが、（1）～（5）では[IV]で生成したelementsのgetValueメソッドでスプレッドシートのカラム名に対応するテキストデータが返されます。

2.6 ワークシートの店舗別データを簡易形式で表示する

リスト6　店舗別表示（getSpreadShopYm.htm）

01<!DOCTYPE html>
02<html>
03<head>
04<meta charset="utf-8"/>
05<title> getSpreadShopYm </title>
06<style>
07.num {text-align: right; }
08</style>
09<script type="text/javascript" src="../js/jquery-2.0.3.min.js"></script>
10<script type="text/javascript">
11$(function() {
12  $("#rev").click(function(e) {
13    var query = {};
14    query["mode"] = "getspreadshopym";
15    query["shop"] = $("#shop").val();
16    query["sheet"] = $("#year").val() + $("#month").val();
17    $.get("/spread", query, function(res) {
18      var r = $.parseJSON(res);
19      $("#food").val(r.food);
20      $("#electric").val(r.electric);
21      $("#bedding").val(r.bedding); 
22      $("#other").val(r.other); 
23      $("#total").val(r.total); 
24    });
25  }); 
26});
27</script>
28</head>
29<body>
30<h3>Spread Sheet 店舗別読取</h3>
31店舗名<select id="shop">
32  <option value="">=選択=</option>
33  <option value="0">水戸</option>
34  <option value="1">浦和</option>
35  <option value="2">千葉</option>
36  <option value="3">川崎</option>
37  <option value="4">小田原</option>
38  <option value="5">大阪</option>
39  <option value="6">神戸</option>
40  <option value="7">京都</option>
41  <option value="8">滋賀</option>
42  <option value="9">和歌山</option>
43  <option value="10">鳥取</option>
44  <option value="11">岡山</option>
45  <option value="12">島根</option>
46  <option value="13">広島</option>
47  <option value="14">山口</option>
48</select>
49<input type="button" id="rev" value=" 　参照 　"/>
50<br /><br />
51年：<select id="year">
52  <option value="">=年選択=</option>
53  <option value="2013">２０１３年</option>
54  <option value="2012">２０１２年</option>
55  <option value="2011">２０１１年</option>
56</select>
57月：<select id="month">
58  <option value="">=月選択=</option>
59  <option value="01">１月</option>
60  <option value="02">２月</option>
61  <option value="03">３月</option>
62  <option value="04">４月</option>
63  <option value="05">５月</option>
64  <option value="06">６月</option>
65  <option value="07">７月</option>
66  <option value="08">８月</option>
67  <option value="09">９月</option>
68  <option value="10">１０月</option>
69  <option value="11">１１月</option>
70  <option value="12">１２月</option>
71</select>
72<hr />
73食品　　：<input type="text"  size="12"  class="num"  id="food" /><br/>
74家電　　：<input type="text"  size="12"  class="num"  id="electric" /><br/>
75寝具　　：<input type="text"  size="12"  class="num"  id="bedding" /><br/>
76その他　：<input type="text"  size="12"  class="num"  id="other" /><br/>
77合計　　：<input type="text"  size="12"  class="num"  id="total" /><br/>
78</body>
79</html>

リスト6の店舗別表示では店舗選択用のセレクトメニューが追加され、ビーンズメソッドでの検索方式に合わせて（[IV]の（3））value値を整数にしています。

図15：Spreadsheet店舗別読み取り表示（クリックで拡大）

図15はSpreadsheet店舗別読み取り表示画面で、店舗名、売上の年月を入力後「参照」ボタンのクリックで表示されます。

今回は、Google　Drive上のspreadsheetをApp Engineから読み取って画面表示するサンプルを紹介しました。ここで画面は簡易形式で表示していましたが、次回は画面表示をスプレッド形式で行うサンプルと、読み取ったスプレッドデータをDatastoreに書き込むサンプルを紹介する予定です。

著者

清野克行

この著者の記事一覧この著者の
記事一覧

有限会社サイバースペース

慶應義塾大学工学部電気科卒。日本IBM、日本HPなどにおいて、製造装置業を中心とした業務系／基幹業務系システムのSE／マーケティングや、3階層C/Sアーキテクチャによる社内業務システム開発などに携わる。現在は、Ajax/Web 2.0関連のセミナー講師／コンサルティング、書籍執筆などを行っている。情報処理学会会員。http://www.at21.net/