AppsスプレッドシートとApp Engineのデータ交換を完成させる

クラウド

技術解説

連載 :

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

2013年12月24日(火)

清野克行

本連載も今回が最終回となりますが、今回は次の2種類の処理について見ていきます。

1つはこれまで見てきた処理と反対方向のデータの流れで、Datastoreに書き込まれたスプレッドデータをGoogle Drive側から読み取りDrive上のスプレッドシートに書き込み再現する処理です（図1内の1）。
もう1つは前回までに紹介した処理の流れの自動化です。Drive上のスプレッドデータをApp　Engineから読み取ってDatastoreに書き込みますが、この処理を自動化して、人手を介さずに時間・期日などの指定でシステムが自動的に行うようにします（図1内の2）。

図1：連載システム構成の完成形（クリックで拡大）

1. App EngineのDatastoreからDrive上のSpread Sheetに読み取る

1. 1 画面処理と表示の流れ

最初はDatastoreにあるスプレッドデータをDrive上のスプレッドシートに書き込む処理の流れです。

図2：データを読み込む前のスプレッドシート（クリックで拡大）

図2はApp Engineからデータを読み取る前のスプレッド画面で、金額を表示するセル項目はすべてゼロクリアされています。また値が入っている場合は画面下の「値クリア」をクリックすれば、金額エリアは総てゼロクリアされます。

図3：データ読み込み後のスプレッドシート（クリックで拡大）

次に、App Engineからのスプレッドデータ読み取りです。図2の状態から読み取る売り上げデータがどの年月かを画面左下（A列17行）に、YYYYMMのフォーマットで入力して「Datastore参照」ボタンをクリックすればURLFetchでApp Engineからスプレッドデータ金額データがセル上に表示されます（図3）。

ここでスプレッドシートに「Datastore参照」などのボタンを表示する方法や、この後紹介するJavaScript関数を定義する手順などについては、連載の第2回で解説していますので、そちらを参照して下さい。

[App Engineのデータをスプレッド表示するメリット]

この画面操作では、App　EngineのDatastoreに書き込まれたデータをDrive上のスプレッドシートに読み込んで表示しています。この処理によって、データのないスプレッドシート1枚をDrive上に置いておけば、データが必要な年月を指定してスプレッドデータを再現できるようになります。また、そのスプレッドは前に見たように簡単にExcelデータに変換することもできます。

Driveではフォルダやファイル単位でアクセスできるユーザーを指定できますが、それでも設定ミスなどによる情報漏洩などのトラブルが目につきます。しかし、ここで紹介したようにDatastoreに書き込まれたデータからスプレッド表示を再現するようにすれば、Drive上にはデータセルがゼロクリアのスプレッドシートを置いておくだけでよく、また読み込み時にパスワード指定もできるので安全です。またデータ種類毎に読み込み用ファイルを１つだけ置いておけばよいので、ファイル管理も容易で効率的になります。

1. 2 Drive側

[1] JavaScriptのコード記述

App Engineのデータをスプレッド表示する場合はURLFetchを使用します。Drive上のJavaScriptアプリケーションから、 URL FetchによってHTTPまたはHTTPS リクエストを発行し、App Engineからのレスポンスを受け取ります。

リスト1　Drive上スプレッドシートのJavaScript関数

01function getalldata() {
02  var sheet = SpreadsheetApp.getActiveSheet();                  //（1）
03  var ymdat = sheet.getRange(17, 1).getValue();                     //（2）
04  var payload =                                                                     //（3）
05  {
06    "mode" : "sfetchall",
07    "sheet" : "" + ymdat 
08  }; 
09  var options =                                                                         //（4）
10  {
11    "method" : "post",
12    "payload" : payload
13  };
14  var res = UrlFetchApp.fetch("http://swsgaejpgm4.appspot.com/spread", options); 
15                                                                      //（5）
16  var r = JSON.parse(res);                                                                                   //（6）
17  var htext = [ "店舗名", "食品", "家電", "寝具", "その他", "合計"];       //（7）
18  var shops = ["水戸", "浦和", "千葉","川崎", "小田原", "大阪", "神戸", "京都", "滋賀", "和歌山", 
19  "鳥取", "岡山", "島根", "広島", "山口"];                                                     //（8）
20  for (var i = 0; i < r.ds.length; i++) {                                                                     //（９）
21    for (var j = 0; j < r.ds.length; j++) {
22      if (shops[i] == r.ds[j].shop) {
23        var total = 0;
24        sheet.getRange(i + 2, 2).setValue(r.ds[j].shop);
25        sheet.getRange(i + 2, 3).setValue(r.ds[j].food);
26        total += parseInt(r.ds[j].food);
27        sheet.getRange(i + 2, 4).setValue(r.ds[j].electric);
28        total += parseInt(r.ds[j].electric);
29        sheet.getRange(i + 2, 5).setValue(r.ds[j].bedding);
30        total += parseInt(r.ds[j].bedding);
31        sheet.getRange(i + 2, 6).setValue(r.ds[j].other);
32        total += parseInt(r.ds[j].other);
33        sheet.getRange(i + 2, 7).setValue(total);
34      } else {
35        continue;
36      } 
37    }
38  }
39}
40 
41function clearcell() {
42  var sheet = SpreadsheetApp.getActiveSheet();              //（10）
43  for (var i = 2; i < 17 ; i++) {
44    for (var j = 3; j < 8; j++) {
45      sheet.getRange(i, j).setValue(0);                             //（11）
46    } 
47  } 
48} 

[2]　App Engineをアクセスしてスプレッドデータを表示する

図2で1列17行に年月を入力して「Datastore参照」ボタンをクリックすると、リスト1のgetalldata関数が呼び出され次の処理手順でURL FetchによるApp Engineからのデータ取得を実行します。

（1）getActiveSheet()はスプレッドシート内のアクティブなシートを取得するメソッドです。アクティブなシートとは複数のワークシートの内一番上に見えているシートです。

（2）図3の検索では1列17行に検索対象の年月が201305と入力されています。　getRange(17, 1)ではこのセルを指定しgetValue()で年月の値を取得しています。

（3）payloadはHTTPプロトコルでのデータ本体で、modeでApp Engine側でアクセスするビーンズのメソッド名を指定し、sheetでYYYYMM書式のシート名を指定しています。ここで年月指定は全て数値で、型指定のないJavaScriptでは整数型とみなされるため、先頭に「""」を追加して文字列型に変換しています。

（4）は（5）のURLFetch実行で指定するオプション項目で、POSTメソッドでのアクセスと（3）のpayloadを指定しています。

（5）以上の準備後、fetchメソッドでアクセスを実行します。http://swsgaejpgm4.appspot.com でApp Engineのデフォルトドメインにあるswsgaejpgm4アプリケーションを指定し、url-patternが/spread　のサーブレット（com.google.gdata.SpreadServlet）をPOSTメソッドでアクセスします。POSTで送信されるデータは（3）のpayloadで指定した内容です。

（6）アクセス結果はresにJSONフォーマットで返されるので、JSON.parse でJavaScriptオブジェクトに変換し変数rにセットして表示処理で使用します。

表示処理では、固定表示部分を（7）、（8）で配列にセットした後、（9）からのfor文ネスト処理で表示処理を行います。

[3] スプレッドシートのデータセルをゼロクリアする

図2で「値クリア」ボタンをクリックするとリスト1のclearcell関数が呼び出されます。clearcell関数では（10）で表示されているアクティブシートを選択し、for文のネストでクリア対象のセルを選択しながら（11）のsetValue()メソッドに引数ゼロ（0）を指定してクリア処理を実行します。

1. 3 App Engineの処理

[1] サーブレットのコード記述

Drive上スプレッドシートのJavaScript で、リスト1のgetalldata関数から（5）のfecthメソッドが実行されるとリスト2のサーブレットが呼び出されます。

リスト2　サーブレット（SpreadServlet.java）

01package com.google.gdata;
02import java.io.IOException;
03import java.io.PrintWriter;
04import javax.servlet.http.*;
05import com.google.gdata.util.AuthenticationException;
06import com.google.gdata.util.ServiceException;
07import com.google.appengine.api.datastore.EntityNotFoundException;
08 
09@SuppressWarnings("serial")
10public class SpreadServlet extends HttpServlet {
11    :
12  public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
13    resp.setContentType("text/plain");
14    resp.setContentType("text/html; charset=utf-8");
15    PrintWriter out = resp.getWriter();
16    String mode = req.getParameter("mode"); 
17    String sheet = req.getParameter("sheet");
18    SpreadBean ss = new SpreadBean();                   //（1）
19    if (mode.equals("addspreadbyshoptodsym")) {
20      String shop = req.getParameter("shop");
21      String rv = ss.addSpreadByShopToDsYm(sheet, shop);
22      out.println(rv);
23        :
24    } else if (mode.equals("sfetchall")) { 
25      String rv = ss.sfetchall(sheet);                      //（2）
26      out.println(rv);                                                  //（3）
27    }
28  }
29} 

サーブレットの処理はこれまでと同じ処理内容です。リスト1の（1）でビーンズのインスタンスを生成し、modeパラメータの値（"sfetchall"）から、（2）でSpreadBeanのsfetchallメソッドが呼び出され、（3）でその戻り値（rv）がサーブレットに返されます。次に、ビーンズのコード記述を見ていきます。

[2]　ビーンズのコード記述

リスト3　ビーンズ（SpreadBean.java）

01package com.google.gdata;
02package com.google.gdata;
03   
04import java.io.IOException;
05import java.net.URL;
06import java.text.DateFormat;
07import java.text.SimpleDateFormat;
08import java.util.ArrayList;
09import java.util.Date;
10import java.util.Iterator;
11import java.util.List;
12 
13import com.google.appengine.api.datastore.DatastoreService;
14import com.google.appengine.api.datastore.DatastoreServiceFactory;
15import com.google.appengine.api.datastore.Entity;
16import com.google.appengine.api.datastore.EntityNotFoundException;
17import com.google.appengine.api.datastore.Key;
18import com.google.appengine.api.datastore.KeyFactory;
19import com.google.appengine.api.datastore.Query;
20import com.google.appengine.api.datastore.Query.Filter;
21import com.google.appengine.api.datastore.Query.FilterOperator;
22import com.google.appengine.api.datastore.Query.SortDirection;
23import com.google.gdata.client.spreadsheet.FeedURLFactory;
24import com.google.gdata.client.spreadsheet.ListQuery;
25import com.google.gdata.client.spreadsheet.SpreadsheetQuery;
26import com.google.gdata.client.spreadsheet.SpreadsheetService;
27import com.google.gdata.data.spreadsheet.CustomElementCollection;
28import com.google.gdata.data.spreadsheet.ListEntry;
29import com.google.gdata.data.spreadsheet.ListFeed;
30import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
31import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
32import com.google.gdata.data.spreadsheet.WorksheetEntry;
33import com.google.gdata.data.spreadsheet.WorksheetFeed;
34import com.google.gdata.util.AuthenticationException;
35import com.google.gdata.util.ServiceException;
36import com.google.appengine.api.datastore.Query.FilterPredicate;
37import com.google.appengine.api.datastore.PreparedQuery;
38   
39public class SpreadBean {
40    :
41  public String sfetchall(String yyyymm){                                                   //（1）
42    try {
43      String rv = "{ \"ds\":[";
44      DatastoreService ds = DatastoreServiceFactory.getDatastoreService();
45      Filter ymFilter = new FilterPredicate("yyyymm", FilterOperator.EQUAL, yyyymm);
46      Query q = new Query("salesym").setFilter(ymFilter);
47      PreparedQuery pq = ds.prepare(q); 
48      for (Entity res : pq.asIterable()) {
49        String shop = (String) res.getProperty("shop");
50        String food = (String) res.getProperty("food");
51        String electric = (String) res.getProperty("electric");
52        String bedding = (String) res.getProperty("bedding");
53        String other = (String) res.getProperty("other");
54        String moddate = (String) res.getProperty("moddate");
55        rv += "{\"shop\": \"" + shop                                            //（2）
56          + "\", \"food\": \"" + food 
57          + "\", \"electric\": \"" + electric 
58          + "\", \"bedding\": \"" + bedding 
59          + "\", \"other\": \"" + other 
60          + "\", \"moddate\": \"" + moddate 
61          + "\"},"; 
62       } 
63      rv = rv.substring(0, rv.length() - 1) + "]}";
64      return rv; 
65    } catch (Exception e) {
66      e.printStackTrace();
67      return "参照不成功　：" + e;
68    } 
69  } 
70} 

SpreadBeanには連載で見てきたメソッドがすべて記述されています。URLFetchではリスト2のSpreadServletから（1）のsfetchallメソッドが呼び出され、ここでDatastoreをアクセスして得られたデータがDrive上のスプレッドシートに表示されることになります。
処理内容は第3回、リスト3のgetDsAllShopYmメソッドと殆ど同じですが、Ｄatastoreをアクセスして得られた値をJSONデータとしてフォーマッティングする（2）の部分だけが異なります。

2. Datastoreに書き込まれたスプレッドデータをGoogle Driveから読み取り表示する

ここからは、今回連載2番目のテーマである、Drive上スプレッドデータをApp Engineから読み取ってDatastoreに書き込む処理の自動化について見ていきます。

2. 1 Pull Taskを使用した処理の概要

App Engineには、負荷の大きな処理を分割して並行実行する機能としてTask Queueサービスがあります。Task QueueにはPush QueueとPull Queueの2種類の処理方式がありますが、ここではPull Queueを使用します。Pull Queueでは、ユーザーが指定した周期や時間で処理を実行することができます。また、処理の負荷に応じてタスクの数を指定して処理を行うことができ、処理の終了によってタスクは自動的に削除されます。

図4：Pull Taskを使用した処理内容（クリックで拡大）

図4は連載の全体図でPull Taskの処理部分をモジュール単位で書き直した内容になっています。図でcron.xmlにはタスクの実行スケジュールが記述されており、このスケジュールにしたがってtaskcontrolServletが呼び出され、呼び出されたtaskcontrolServletが処理を実行するsptodsServletをキューに投入します。

sptodsServletはDrive上のスプレッドシートからデータを読み込んでDatastoreに書き込む処理と、書き込み完了時にその情報を関係者にメール送信する処理を行います。タスクの処理頻度やバケットサイズなどの処理能力は、queue.xmlに記述されている制約内で実行されます。

2. 2 サンプルへのPull Task機能追加

[1]　web.xmlの定義とPull　Task

ディプロイメントディスクリプタ（web.xml）の定義では、先頭に定義されているサーブレットからアクセスされるビーンズに、これまでの連載で見てきた処理がすべて記述されており、リスト4の(1)から後の定義はすべてPull Taskに関係するものになっています。

下記リスト4で

(1)はタスクのキュース投入を行うサーブレットの定義でcron.xmlから参照されます。
(2)はスプレッドデータのDatastore書き込み用サーブレットの定義でqueue.xmlから参照されます。
(3)はPull　Queueに対するアクセス制限の指定になっています。

リスト4 war/WEB-INF/web.xml

01<?xml version="1.0" encoding="utf-8" standalone="no"?>
02<web-app xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.5" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
03  <servlet>
04    <servlet-name>spread</servlet-name>
05    <servlet-class>com.google.gdata.SpreadServlet</servlet-class>
06  </servlet>
07  <servlet-mapping>
08    <servlet-name>spread</servlet-name>
09    <url-pattern>/spread</url-pattern>
10  </servlet-mapping>
11   
12  <!-- 　(1) タスクのキュース投入を行うサーブレットの定義  -->
13  <servlet>
14    <servlet-name>taskcontrol</servlet-name>
15    <servlet-class>com.google.gdata.taskcontrolServlet</servlet-class>
16  </servlet>
17  <servlet-mapping>
18    <servlet-name>taskcontrol</servlet-name>
19    <url-pattern>/cron/taskcontrol</url-pattern>
20  </servlet-mapping>
21  
22  <!-- (2) スプレッドデータのDatastore書き込み用サーブレット定義   -->
23  <servlet>
24    <servlet-name>sptods</servlet-name>
25    <servlet-class>com.google.gdata.sptodsServlet</servlet-class>
26  </servlet>
27  <servlet-mapping>
28    <servlet-name>sptods</servlet-name>
29    <url-pattern>/task/sptods</url-pattern>
30  </servlet-mapping>
31   
32  <!-- 　(3) Pull　Queueのアクセス制限  -->
33  <security-constraint>
34    <web-resource-collection>
35      <url-pattern>/task/*</url-pattern>
36      <url-pattern>/cron/*</url-pattern>
37    </web-resource-collection>
38    <auth-constraint>
39      <role-name>admin</role-name>
40    </auth-constraint>
41  </security-constraint>
42   
43  <welcome-file-list>
44    <welcome-file>index.htm</welcome-file>
45  </welcome-file-list>
46</web-app>

[2] cron.xmlでタスクスケジュール指定

タスクはcron.xmlファイルに記述されたスケジュールに従って実行されます。リスト5はサンプルで使用されているcron.xmlで、 WEB-INF ディレクトリ下に配置されます。

リスト5　war/WEB-INF/cron.xml

1<?xml version="1.0" encoding="UTF-8"?>
2<cronentries>
3  <cron>
4    <url>/cron/taskcontrol</url>
5    <description>Write Spread data to Datastore</description>
6    <schedule> every monday of month 09:00</schedule>
7    <timezone>Asia/Tokyo</timezone>
8  </cron> 
9</cronentries>

リスト5で定義されている各タグは、それぞれ次のような意味を持ちます。

cron.xmlでスケジューリングされる制御サーブレットを指定します。サンプルのでは、web.xmlの(2)で定義されているを指しています。

scheduleタグではタスクの実行スケジュールを指定し、リスト4では毎週月曜の午前９時にタスクが実行されるように指定しています。下記のようにさまざまなパターンでタスクの実行スケジュール指定が可能です。

every 5 minutes //5分毎にタスクジョブを実行
2nd,third mon,wed of march 17:00 //３月の第2、第3月曜、水曜の17時にタスクジョブを実行
every monday of month 09:00 //月のすべての月曜９時にタスクジョブを実行
1st monday of sep,oct,nov 17:00 //８月、１０月、１１月の第1月曜17時にタスクジョブを実行

タスクジョブの実行を同じ間隔で繰り返し実行する場合は、
every N (hours | mins | minutes)
を使用します。ここで、N（整数）は、hours または mins(minutes) で時間の単位を指定します。
タスクを実行する最小の間隔は1分です。

以上のとタグは必須で、他にオプションとしてのコメント記述とタグを指定できます。には、タイムゾーンの名前を指定します。タイムゾーンを指定しない場合は UTC（GMT）で実行されます。

[3] タスクの実行制御

Pull Taskで実行されるサーブレットは指定されたスケジュールに従って実行されるプログラムで、Webブラウザからのアクセスでは実行できないようにすべきです。　これを行っているのがリスト4の(3)にある　タグ下ので、ここではWebリソースにアクセスできるロールをadminに制限しています。

[4] task制御サーブレット

リスト6　　task制御サーブレット（taskcontrolServlet.java）

1package com.google.gdata;
2import javax.servlet.http.*;
3import java.io.IOException;
4import java.util.logging.Logger;
5import com.google.appengine.api.taskqueue.Queue;
6import com.google.appengine.api.taskqueue.QueueFactory;
7import com.google.appengine.api.taskqueue.TaskOptions;
8import com.google.appengine.api.taskqueue.TransientFailureException;
9import com.google.apphosting.api.ApiProxy.ApiDeadlineExceededException;

@SuppressWarnings("serial") public class taskcontrolServlet extends HttpServlet { private static final Logger log = Logger.getLogger(sptodsServlet.class.getName()); public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException { try { Queue queue = QueueFactory.getQueue("sptods"); //（1） queue.add(TaskOptions.Builder.withUrl("/task/sptods").param("sheet", "201304")); //（2） } catch (TransientFailureException e) { log.severe("{\"flag\": \"" + "TransientFailureException :" + e + "\"}"); } catch (ApiDeadlineExceededException e) { log.severe("{\"flag\": \"" + "ApiDeadlineExceededException :" + e + "\"}"); } } }

リスト6のtaskcontrolServlet.javaではPull queue にタスクを追加します。処理はシンプルで次の2つの処理を行っています。

（1）でqueue.xmlに定義されているキューの名前を指定してキューオブジェクト（queue）を取得する。
（2）次にwithUrlで指定されたサーブレットをタスクの実行プログラムとしてaddメソッドでキューに追加する。

ここでサーブレットに渡すパラメータはparamで指定することができます。

この処理でタスクはキューに追加され、次のqueue.xmlに指定された環境で実行されます。

[5] queue.xml キューの定義

タスクキューに登録するQueueは、queue.xmlで実行間隔を制御でき、サンプルではリスト7ように定義されています。

リスト7　war/WEB-INF/queue.xml

1<?xml version="1.0" encoding="UTF-8"?>
2<queue-entries>
3  <queue>
4    <name>sptods</name>
5    <rate>2/m</rate>
6    <bucket-size>5</bucket-size>
7  </queue> 
8</queue-entries>

queue.xmlで、、およびはそれぞれ次の意味を持ちます。

キューの名前。リスト6　task制御サーブレットのQueueFactory.getQueue() で queue.xml を参照する時に指定される名前です。キューの名前には、文字、数字、ハイフンを使用できます。

このキューでタスクを処理する頻度で、[2/m] で指定した場合1分間に2つのキューが処理できます。値は「数字/時間の単位」の形で表します。単位は、sが秒、mが分、hが時間、dが日を表します。例えば、5/m という値は、タスクが1分あたり 5回の頻度で処理されることを意味しています。なお、数字が 0（0/s など）の場合、キューは「保留」とみなされ、タスクの処理は行われません。

バケットサイズは、数多くのタスクがキューに投入された時に、キューの処理rateを上げる上限を指定する値で、指定されていない場合のデフォルト値は5です。例えば、サンプルのようにrateが2/sでbucket-sizeが5の場合、5/sまでrateを上げることができますが、これ以上にすることはできません。

[3] タスク実行サーブレット

キューに追加された実行サーブレットはデフォルトではPOSTメソッドで呼び出され、スプレッドデータのDatastore書き込を行います・サーブレットの指定はリスト4 web.xmlの(2)で行っています。

リスト8　sptodsServlet.java

001package com.google.gdata;
002 
003import javax.servlet.http.*;
004import java.io.IOException;
005import java.sql.Connection;
006import java.text.DateFormat;
007import java.text.SimpleDateFormat;
008import java.util.Date;
009import java.util.logging.Logger;
010import com.google.appengine.api.datastore.DatastoreService;
011import com.google.appengine.api.datastore.DatastoreServiceFactory;
012import com.google.appengine.api.datastore.Entity;
013import com.google.appengine.api.datastore.Key;
014import com.google.appengine.api.datastore.KeyFactory;
015import com.google.gdata.client.spreadsheet.FeedURLFactory;
016import com.google.gdata.client.spreadsheet.ListQuery;
017import com.google.gdata.client.spreadsheet.SpreadsheetQuery;
018import com.google.gdata.client.spreadsheet.SpreadsheetService;
019import com.google.gdata.data.spreadsheet.CustomElementCollection;
020import com.google.gdata.data.spreadsheet.ListEntry;
021import com.google.gdata.data.spreadsheet.ListFeed;
022import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
023import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
024import com.google.gdata.data.spreadsheet.WorksheetEntry;
025import com.google.gdata.util.AuthenticationException;
026import java.util.Properties;
027 
028import javax.mail.internet.AddressException;
029import javax.mail.internet.InternetAddress;
030import javax.mail.MessagingException;
031import javax.mail.internet.MimeMessage;
032import javax.mail.Message;
033import javax.mail.Session;
034import javax.mail.Transport;
035 
036@SuppressWarnings("serial")
037public class sptodsServlet extends HttpServlet {
038  private static final Logger log = Logger.getLogger(sptodsServlet.class.getName());        //（1）
039  Connection con  = null;
040  @Override 
041  public void doPost(HttpServletRequest req, HttpServletResponse resp)
042                                                      throws IOException {
043    req.setCharacterEncoding("utf-8");
044    resp.setContentType("text/plain");
045    resp.setCharacterEncoding("utf-8");
046    String sheet = req.getParameter("sheet");
047    String applicationName = "cyberspace-SpreadsheetSearch-1";
048    String username = "xxxxxxxxxxxxxxxxxxxxxx";
049    String password = "xxxxxxxxxxxxxx";
050    try {
051      SpreadsheetService spreadsheetservice = new SpreadsheetService(applicationName);
052      spreadsheetservice.setUserCredentials(username, password);
053      // 検索対象のスプレッドシートを取得
054      FeedURLFactory feedurlfactory = FeedURLFactory.getDefault();
055      SpreadsheetQuery spreadsheetquery = 
056      new SpreadsheetQuery(feedurlfactory.getSpreadsheetsFeedUrl());
057      spreadsheetquery.setTitleQuery(sheet); // 検索対象のスプレッドシート名を指定している
058      SpreadsheetFeed spreadsheetfeed = 
059      spreadsheetservice.query(spreadsheetquery, SpreadsheetFeed.class);
060      SpreadsheetEntry spreadsheetentry = spreadsheetfeed.getEntries().get(0);
061      // 検索対象のワークシートを取得
062      WorksheetEntry worksheetentry = spreadsheetentry.getDefaultWorksheet();
063      //  ワークシート内を検索
064      ListQuery listQuery = new ListQuery(worksheetentry.getListFeedUrl());
065      ListFeed listFeed = spreadsheetservice.query(listQuery, ListFeed.class); 
066      DatastoreService ds = DatastoreServiceFactory.getDatastoreService(); 
067      int i = 0;
068      for (ListEntry listentry : listFeed.getEntries()) {
069        CustomElementCollection customelementcollection = listentry.getCustomElements(); 
070        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
071        String moddate = df.format(new Date()); 
072        Entity sales = null;
073        if (!customelementcollection.getValue("店舗名").equals("売上合計")) { 
074          Key saleskey = KeyFactory.createKey
075          ("salesym", sheet + customelementcollection.getValue("店舗名")); 
076          sales = new Entity(saleskey); 
077          sales.setProperty("seq", i++);
078        } else {
079          Key saleskey = KeyFactory.createKey
080          ("salesymtotal", sheet + customelementcollection.getValue("店舗名")); 
081          sales = new Entity(saleskey);
082        } 
083        sales.setProperty("shop", customelementcollection.getValue("店舗名"));
084        sales.setProperty("yyyymm", sheet);
085        sales.setProperty("food", customelementcollection.getValue("食品"));
086        sales.setProperty("electric", customelementcollection.getValue("家電"));
087        sales.setProperty("bedding", customelementcollection.getValue("寝具"));
088        sales.setProperty("other", customelementcollection.getValue("その他"));
089        sales.setProperty("moddate", moddate); 
090        ds.put(sales);
091        log.severe("{\"flag\": \"" + "Success " + "\"}");
092      } 
093    } catch (AuthenticationException e) {
094      log.severe("{\"flag\": \"" + "認証エラー  : " + e + "\"}");
095    } catch (IOException e) {
096      log.severe("{\"flag\": \"" + "IOエラー  : " + e + "\"}");
097    } catch (Exception e) {
098      log.severe("{\"flag\": \"" + "エラー  : " + e + "\"}");
099    }
100    //////////////////////////////////////////////////////////
101    //        メール送信処理l 
102    Properties props = new Properties();
103    Session session = Session.getDefaultInstance(props, null);
104    String msgBody = "Drive上スプレッドシートの" + sheet.substring(0, 4) + "年" + sheet.substring(4, 6) +"月度データの、Datastore書き込みが完了しました。\n";
105    try {
106      Message msg = new MimeMessage(session);
107      msg.setFrom(new InternetAddress("sws3gaej4pgm3@gmail.com", "Spread Admin"));
108      msg.addRecipient(Message.RecipientType.TO, 
109      new InternetAddress("superelmer21@gmail.com", "Spread user"));
110      msg.addRecipient(Message.RecipientType.CC, 
111      new InternetAddress("sws3gaej4pgm3@gmail.com", "Admin user"));
112      msg.setSubject("Spread to Datastore");
113      msg.setText(msgBody);
114      Transport.send(msg);
115      log.severe("{\"flag\": \"" + "Success " + "\"}");
116    } catch (AddressException e) {
117      log.severe("{\"flag\": \"" + "アドレスエラー  : " + e + "\"}");
118    } catch (MessagingException e) {
119      log.severe("{\"flag\": \"" + "メッセージングエラー :" + e + "\"}");
120    }
121  }
122}

リスト8はDriveのスプレッドデータを読み取ってDatastoreに書き込むサーブレットです。サーブレットで行われている処理内容は、連載第4回のリスト2にあるビーンズメソッド（addSpreadAllShopToDsYm）と殆ど同じですが、最後にメール送信の処理が追加されています。

[メール送信処理]

Datastoreへの書き込み完了後、メールでの通知を行っています。App Engineのメール送信ではMimeMessageオブジェクトを使用します。リスト8ではインスタンスmsgのメソッドを使用してFROM、TO、CCのアドレスおよび送信メッセージ情報を組み立て、Transportクラスのsend()メソッドでメール送信します。送信されたメールは、メールクライアントがGmailの場合下記のように受信表示されます。

図5：Gmailでのメール受信表示（クリックで拡大）

[ログへの出力]

リスト8のサーブレットはユーザーインタラクションがないため、出力はログ・ファイルに行います。ログ出力ではLog4jを使用していますが、（1）でクラスの名称を Logger インスタンスに設定してLoggerインスタンス（log）を生成、そのあと下記メソッドを使用してログ出力を行っています。

log.info("メッセージ1"); //ログメッセージ1の出力
log.warning("メッセージ2"); //警告（warning）ログメッセージ2の出力
log.severe("メッセージ3"); //エラーログメッセージ3の出力

2. 3 Pull Taskの実行

[1] デフォルトアプリケーションに設定

タスクはデフォルトアプリケーションでなければ実行されません。タスク実行にはプロジェクトをApp EngineのApplicationとしてアップロード後、アップロードしたApplicationのバージョンを、App Engine管理者画面のVersionsから「Make Default」ボタンクリックでデフォルトバージョンに設定します。

[2] 実行確認用HTML

リスト９　pulltask.htm

01<!DOCTYPE html>
02<html>
03<head>
04<meta charset="utf-8"/>
05<title>タスクジョブ</title>
06</head>
07<body><h3>タスクジョブテスト</h3>
08<a href="/cron/taskcontrol">pullタスクを実行</a>
09</body>
10</html>

また、タスクをデフォルトアプリケーションにしても、スケジュールされた時間が来るまで実行されません。しかし、これは開発時にタスク処理の動作確認を行う場合は不便です。リスト９のような簡単なプログラムを作れば、リスト6のtask制御サーブレットをWebクライアントから直接実行することができ、このプログラムを使用してプログラムの動作確認とデバッグを行います。ただしこのプログラムはweb.xmlの下ので /task/*　が指定されたままでは実行できないので、この行をコメントアウトして実行します。

スプレッドシートを使用したGoogle DriveとApp Engineのデータ交換を中心とした本連載も今回で終了です。ここまで見てきた内容は数あるGoogleのクラウドを連携する内容でしたが、連載第2回の図18にあるように、App Engineを中心としたクラウド連携は他にもあり、その中でも有力な機能としてApp EngineのDatastoreにあるデータをCloud Storageを介してBigQueryでのビッグデータ解析を行えるようにする連携があります。そしてこの連携を連載で見てきたDriveスプレッドデータとApp Engineの連携と組み合わせれば、大量のスプレッド（およびExcel）データをビッグデータソースとして使用するシステムを構築することもできるようになります。

図6：Googleのクラウド連携で作るビッグデータ処理システム（クリックで拡大）

著者

清野克行

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

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

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