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

2013年12月24日(火)
清野 克行

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

  1. 1つはこれまで見てきた処理と反対方向のデータの流れで、Datastoreに書き込まれたスプレッドデータをGoogle Drive側から読み取りDrive上のスプレッドシートに書き込み再現する処理です(図1内の1)。
  2. もう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つだけ置いておけばよいので、ファイル管理も容易で効率的になります。

1. 2 Drive側

[1] JavaScriptのコード記述

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

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

function getalldata() {
  var sheet = SpreadsheetApp.getActiveSheet();					//(1)
  var ymdat = sheet.getRange(17, 1).getValue();						//(2)
  var payload =																		//(3)
  {
    "mode" : "sfetchall",
    "sheet" : "" + ymdat 
  }; 
  var options = 																		//(4)
  {
    "method" : "post",
    "payload" : payload
  };
  var res = UrlFetchApp.fetch("http://swsgaejpgm4.appspot.com/spread", options); 
                                                                      //(5)
  var r = JSON.parse(res);																					 //(6)
  var htext = [ "店舗名", "食品", "家電", "寝具", "その他", "合計"];		 //(7)
  var shops = ["水戸", "浦和", "千葉","川崎", "小田原", "大阪", "神戸", "京都", "滋賀", "和歌山", 
  "鳥取", "岡山", "島根", "広島", "山口"];													 //(8)
  for (var i = 0; i < r.ds.length; i++) {																	 //(9)
    for (var j = 0; j < r.ds.length; j++) {
      if (shops[i] == r.ds[j].shop) {
        var total = 0;
        sheet.getRange(i + 2, 2).setValue(r.ds[j].shop);
        sheet.getRange(i + 2, 3).setValue(r.ds[j].food);
        total += parseInt(r.ds[j].food);
        sheet.getRange(i + 2, 4).setValue(r.ds[j].electric);
        total += parseInt(r.ds[j].electric);
        sheet.getRange(i + 2, 5).setValue(r.ds[j].bedding);
        total += parseInt(r.ds[j].bedding);
        sheet.getRange(i + 2, 6).setValue(r.ds[j].other);
        total += parseInt(r.ds[j].other);
        sheet.getRange(i + 2, 7).setValue(total);
      } else {
        continue;
      } 
    }
  }
}

function clearcell() {
  var sheet = SpreadsheetApp.getActiveSheet();				//(10)
  for (var i = 2; i < 17 ; i++) {
    for (var j = 3; j < 8; j++) {
      sheet.getRange(i, j).setValue(0);								//(11)
    } 
  } 
} 

[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)

package com.google.gdata;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.http.*;
import com.google.gdata.util.AuthenticationException;
import com.google.gdata.util.ServiceException;
import com.google.appengine.api.datastore.EntityNotFoundException;

@SuppressWarnings("serial")
public class SpreadServlet extends HttpServlet {
    :
  public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
    resp.setContentType("text/plain");
    resp.setContentType("text/html; charset=utf-8");
    PrintWriter out = resp.getWriter();
    String mode = req.getParameter("mode"); 
    String sheet = req.getParameter("sheet");
    SpreadBean ss = new SpreadBean();					//(1)
    if (mode.equals("addspreadbyshoptodsym")) {
      String shop = req.getParameter("shop");
      String rv = ss.addSpreadByShopToDsYm(sheet, shop);
      out.println(rv);
        :
    } else if (mode.equals("sfetchall")) { 
      String rv = ss.sfetchall(sheet);						//(2)
      out.println(rv);													//(3)
    }
  }
} 

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

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

リスト3 ビーンズ(SpreadBean.java)

package com.google.gdata;
package com.google.gdata;
  
import java.io.IOException;
import java.net.URL;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import com.google.appengine.api.datastore.DatastoreService;
import com.google.appengine.api.datastore.DatastoreServiceFactory;
import com.google.appengine.api.datastore.Entity;
import com.google.appengine.api.datastore.EntityNotFoundException;
import com.google.appengine.api.datastore.Key;
import com.google.appengine.api.datastore.KeyFactory;
import com.google.appengine.api.datastore.Query;
import com.google.appengine.api.datastore.Query.Filter;
import com.google.appengine.api.datastore.Query.FilterOperator;
import com.google.appengine.api.datastore.Query.SortDirection;
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.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;
import com.google.appengine.api.datastore.Query.FilterPredicate;
import com.google.appengine.api.datastore.PreparedQuery;
  
public class SpreadBean {
    :
  public String sfetchall(String yyyymm){													//(1)
    try {
      String rv = "{ \"ds\":[";
      DatastoreService ds = DatastoreServiceFactory.getDatastoreService();
      Filter ymFilter = new FilterPredicate("yyyymm", FilterOperator.EQUAL, yyyymm);
      Query q = new Query("salesym").setFilter(ymFilter);
      PreparedQuery pq = ds.prepare(q); 
      for (Entity res : pq.asIterable()) {
        String shop = (String) res.getProperty("shop");
        String food = (String) res.getProperty("food");
        String electric = (String) res.getProperty("electric");
        String bedding = (String) res.getProperty("bedding");
        String other = (String) res.getProperty("other");
        String moddate = (String) res.getProperty("moddate");
        rv += "{\"shop\": \"" + shop 											//(2)
          + "\", \"food\": \"" + food 
          + "\", \"electric\": \"" + electric 
          + "\", \"bedding\": \"" + bedding 
          + "\", \"other\": \"" + other 
          + "\", \"moddate\": \"" + moddate 
          + "\"},"; 
       } 
      rv = rv.substring(0, rv.length() - 1) + "]}";
      return rv; 
    } catch (Exception e) {
      e.printStackTrace();
      return "参照不成功 :" + e;
    } 
  } 
} 

SpreadBeanには連載で見てきたメソッドがすべて記述されています。URLFetchではリスト2のSpreadServletから(1)のsfetchallメソッドが呼び出され、ここでDatastoreをアクセスして得られたデータがDrive上のスプレッドシートに表示されることになります。
処理内容は第3回、リスト3のgetDsAllShopYmメソッドと殆ど同じですが、Datastoreをアクセスして得られた値を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

<?xml version="1.0" encoding="utf-8" standalone="no"?>
<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">
  <servlet>
    <servlet-name>spread</servlet-name>
    <servlet-class>com.google.gdata.SpreadServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>spread</servlet-name>
    <url-pattern>/spread</url-pattern>
  </servlet-mapping>
  
  <!--  (1) タスクのキュース投入を行うサーブレットの定義  -->
  <servlet>
    <servlet-name>taskcontrol</servlet-name>
    <servlet-class>com.google.gdata.taskcontrolServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>taskcontrol</servlet-name>
    <url-pattern>/cron/taskcontrol</url-pattern>
  </servlet-mapping>
 
  <!-- (2) スプレッドデータのDatastore書き込み用サーブレット定義   -->
  <servlet>
    <servlet-name>sptods</servlet-name>
    <servlet-class>com.google.gdata.sptodsServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>sptods</servlet-name>
    <url-pattern>/task/sptods</url-pattern>
  </servlet-mapping>
  
  <!--  (3) Pull Queueのアクセス制限  -->
  <security-constraint>
    <web-resource-collection>
      <url-pattern>/task/*</url-pattern>
      <url-pattern>/cron/*</url-pattern>
    </web-resource-collection>
    <auth-constraint>
      <role-name>admin</role-name>
    </auth-constraint>
  </security-constraint>
  
  <welcome-file-list>
    <welcome-file>index.htm</welcome-file>
  </welcome-file-list>
</web-app>

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

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

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

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

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

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

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

every 5 minutes //5分毎にタスクジョブを実行
2nd,third mon,wed of march 17:00 //3月の第2、第3月曜、水曜の17時にタスクジョブを実行
every monday of month 09:00 //月のすべての月曜9時にタスクジョブを実行
1st monday of sep,oct,nov 17:00 //8月、10月、11月の第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)

package com.google.gdata;
import javax.servlet.http.*;
import java.io.IOException;
import java.util.logging.Logger;
import com.google.appengine.api.taskqueue.Queue;
import com.google.appengine.api.taskqueue.QueueFactory;
import com.google.appengine.api.taskqueue.TaskOptions;
import com.google.appengine.api.taskqueue.TransientFailureException;
import 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

<?xml version="1.0" encoding="UTF-8"?>
<queue-entries>
  <queue>
    <name>sptods</name>
    <rate>2/m</rate>
    <bucket-size>5</bucket-size>
  </queue> 
</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

package com.google.gdata;

import javax.servlet.http.*;
import java.io.IOException;
import java.sql.Connection;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.Logger;
import com.google.appengine.api.datastore.DatastoreService;
import com.google.appengine.api.datastore.DatastoreServiceFactory;
import com.google.appengine.api.datastore.Entity;
import com.google.appengine.api.datastore.Key;
import com.google.appengine.api.datastore.KeyFactory;
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.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.util.AuthenticationException;
import java.util.Properties;

import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.MessagingException;
import javax.mail.internet.MimeMessage;
import javax.mail.Message;
import javax.mail.Session;
import javax.mail.Transport;

@SuppressWarnings("serial")
public class sptodsServlet extends HttpServlet {
  private static final Logger log = Logger.getLogger(sptodsServlet.class.getName());		//(1)
  Connection con  = null;
  @Override 
  public void doPost(HttpServletRequest req, HttpServletResponse resp)
                                                      throws IOException {
    req.setCharacterEncoding("utf-8");
    resp.setContentType("text/plain");
    resp.setCharacterEncoding("utf-8");
    String sheet = req.getParameter("sheet");
    String applicationName = "cyberspace-SpreadsheetSearch-1";
    String username = "xxxxxxxxxxxxxxxxxxxxxx";
    String password = "xxxxxxxxxxxxxx";
    try {
      SpreadsheetService spreadsheetservice = new SpreadsheetService(applicationName);
      spreadsheetservice.setUserCredentials(username, password);
      // 検索対象のスプレッドシートを取得
      FeedURLFactory feedurlfactory = FeedURLFactory.getDefault();
      SpreadsheetQuery spreadsheetquery = 
      new SpreadsheetQuery(feedurlfactory.getSpreadsheetsFeedUrl());
      spreadsheetquery.setTitleQuery(sheet); // 検索対象のスプレッドシート名を指定している
      SpreadsheetFeed spreadsheetfeed = 
      spreadsheetservice.query(spreadsheetquery, SpreadsheetFeed.class);
      SpreadsheetEntry spreadsheetentry = spreadsheetfeed.getEntries().get(0);
      // 検索対象のワークシートを取得
      WorksheetEntry worksheetentry = spreadsheetentry.getDefaultWorksheet();
      //  ワークシート内を検索
      ListQuery listQuery = new ListQuery(worksheetentry.getListFeedUrl());
      ListFeed listFeed = spreadsheetservice.query(listQuery, ListFeed.class); 
      DatastoreService ds = DatastoreServiceFactory.getDatastoreService(); 
      int i = 0;
      for (ListEntry listentry : listFeed.getEntries()) {
        CustomElementCollection customelementcollection = listentry.getCustomElements(); 
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String moddate = df.format(new Date()); 
        Entity sales = null;
        if (!customelementcollection.getValue("店舗名").equals("売上合計")) { 
          Key saleskey = KeyFactory.createKey
          ("salesym", sheet + customelementcollection.getValue("店舗名")); 
          sales = new Entity(saleskey); 
          sales.setProperty("seq", i++);
        } else {
          Key saleskey = KeyFactory.createKey
          ("salesymtotal", sheet + customelementcollection.getValue("店舗名")); 
          sales = new Entity(saleskey);
        } 
        sales.setProperty("shop", customelementcollection.getValue("店舗名"));
        sales.setProperty("yyyymm", sheet);
        sales.setProperty("food", customelementcollection.getValue("食品"));
        sales.setProperty("electric", customelementcollection.getValue("家電"));
        sales.setProperty("bedding", customelementcollection.getValue("寝具"));
        sales.setProperty("other", customelementcollection.getValue("その他"));
        sales.setProperty("moddate", moddate); 
        ds.put(sales);
        log.severe("{\"flag\": \"" + "Success " + "\"}");
      } 
    } catch (AuthenticationException e) {
      log.severe("{\"flag\": \"" + "認証エラー  : " + e + "\"}");
    } catch (IOException e) {
      log.severe("{\"flag\": \"" + "IOエラー  : " + e + "\"}");
    } catch (Exception e) {
      log.severe("{\"flag\": \"" + "エラー  : " + e + "\"}");
    }
    //////////////////////////////////////////////////////////
    //        メール送信処理l 
    Properties props = new Properties();
    Session session = Session.getDefaultInstance(props, null);
    String msgBody = "Drive上スプレッドシートの" + sheet.substring(0, 4) + "年" + sheet.substring(4, 6) +"月度データの、Datastore書き込みが完了しました。\n";
    try {
      Message msg = new MimeMessage(session);
      msg.setFrom(new InternetAddress("sws3gaej4pgm3@gmail.com", "Spread Admin"));
      msg.addRecipient(Message.RecipientType.TO, 
      new InternetAddress("superelmer21@gmail.com", "Spread user"));
      msg.addRecipient(Message.RecipientType.CC, 
      new InternetAddress("sws3gaej4pgm3@gmail.com", "Admin user"));
      msg.setSubject("Spread to Datastore");
      msg.setText(msgBody);
      Transport.send(msg);
      log.severe("{\"flag\": \"" + "Success " + "\"}");
    } catch (AddressException e) {
      log.severe("{\"flag\": \"" + "アドレスエラー  : " + e + "\"}");
    } catch (MessagingException e) {
      log.severe("{\"flag\": \"" + "メッセージングエラー :" + e + "\"}");
    }
  }
}

リスト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

リスト9 pulltask.htm

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8"/>
<title>タスクジョブ</title>
</head>
<body><h3>タスクジョブテスト</h3>
<a href="/cron/taskcontrol">pullタスクを実行</a>
</body>
</html>

また、タスクをデフォルトアプリケーションにしても、スケジュールされた時間が来るまで実行されません。しかし、これは開発時にタスク処理の動作確認を行う場合は不便です。リスト9のような簡単なプログラムを作れば、リスト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/

連載バックナンバー

Think ITメルマガ会員登録受付中

Think ITでは、技術情報が詰まったメールマガジン「Think IT Weekly」の配信サービスを提供しています。メルマガ会員登録を済ませれば、メルマガだけでなく、さまざまな限定特典を入手できるようになります。

Think ITメルマガ会員のサービス内容を見る

他にもこの記事が読まれています