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

クラウド

技術解説

連載 :

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

2013年11月13日(水)

清野克行

2．Drive上のSpreadSheetをApp Engineから読み取り画面表示する

ここまでで、ExcelシートとSpreadsheetのアップロードおよびファイル変換についての説明は終わり、次に本連載メインテーマのApp EngineからのSpreadsheet読み取りについて入っていきます。

ただし、ここからはJavaやJavascriptを使用したプログラミングの内容になっていきますので、ある程度プログラミング経験があることを前提とした内容になっていきます。

図9：Drive上のスプレッドデータをApp Engineから読み取る（クリックで拡大）

ここで紹介するApp　EngineからのSpreadsheet読み取りでは、参照画面のボタンクリックから起動されるサーブレットによってビーンズをインスタンス化し、その中のメソッドによって実行されます（図9）。読み取られたSpreadデータはブラウザに画面表示されますが、今回は簡易形式での画面表示を紹介し、次回の連載でスプレッドシート風のグリッド表示について紹介します。

2.1 デプロイメントディスクリプタ（web.xml）を記述する

リスト1　デプロイメントディスクリプタ（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>
        :
  <welcome-file-list>
    <welcome-file>index.htm</welcome-file>
  </welcome-file-list>
</web-app>

ブラウザからの表示リクエストはデプロイメントディスクリプタ（web.xml）の記述にしたがってサーブレットを呼び出します。この辺りは問題ないでしょう。

2.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.ServiceException;

@SuppressWarnings("serial")
public class SpreadServlet extends HttpServlet {
  public void doGet(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();			//スプレッドシート処理のビーンズをインスタンス化
    if (mode.equals("getspreadallshopym")) {	  	//modeパラメータでビーンズメソッド指定
      String rv = ss.getSpreadAllShopYm(sheet); 
      //ワークシート一覧表示のビーンズメソッド呼び出し
      out.println(rv);
  } else if (mode.equals("getspreadshopym")) {
      String shop = req.getParameter("shop");
      String rv = ss.getSpreadShopYm(sheet, shop);
      //ワークシート店舗別表示のビーンズメソッド呼び出し
      out.println(rv);
    }
  }
}

呼び出されたサーブレットはmodeパラメータで指定された値によってビーンズメソッドを呼び出します。

2.3 ワークシート一覧表示のビーンズメソッドを記述する

ここで見ていくビーンズメソッドでは、ワークシートに書き込まれたデータを一覧表示するもので、Spreadsheetの検索表示では一般的なものです。

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

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.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 getSpreadAllShopYm(String sheet) { 
    String appliName = "cyberspace-SpreadsheetSearch-1";
    String user = "xxxxxxxxxxxxxxxxxxx";
    String pass = "xxxxxxxxxxxxxxxxx";
    try {
      ///////////////////////////////////////////////////////
      //		[I] 認証処理
      SpreadsheetService spreadsheetservice = new SpreadsheetService(appliName);
      spreadsheetservice.setUserCredentials(user, pass);
      ///////////////////////////////////////////////////////
      //		[II] 検索対象のスプレッドシートを特定
      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);
      ///////////////////////////////////////////////////////
      //		[III] 検索対象のワークシートを特定
      WorksheetEntry worksheetentry = spreadsheetentry.getDefaultWorksheet(); 
      ///////////////////////////////////////////////////////
      //		[IV] クエリでワークシート内の検索
      ListQuery listquery = new ListQuery(worksheetentry.getListFeedUrl());
      ListFeed listfeed = spreadsheetservice.query(listquery, ListFeed.class);
      List<ListEntry> datalist = lisfFeed.getEntries(); 
      ///////////////////////////////////////////////////////
      //		[V] ワークシートデータをJSONフォーマットにする
      String rv = "{ \"wsdat\":[";
      for (Iterator<ListEntry> it = datalist.iterator(); it.hasNext();) {
        ListEntry listentry = (ListEntry) it.next();
        rv +=  "{\"cdat\": \"" + listentry.getPlainTextContent() + "\"},";
      }
      rv = rv.substring(0, rv.length() - 1) + "]}";
      return rv; 
    } catch (AuthenticationException e) {
      e.printStackTrace();
      return "参照不成功　：" + e;
    } catch (IOException e) {
      e.printStackTrace();
      return "参照不成功　：" + e;
  } catch (ServiceException e) {
  e.printStackTrace();
  return "参照不成功　：" + e;
  } 
 }
  ：
}

[I] 認証処理

  String appliName = "cyberspace-SpreadsheetSearch-1";
  String user = "xxxxxxxxxxxxxxxxxxx";
  String pass = "xxxxxxxxxxxxxxxxx";
  
  SpreadsheetService spreadsheetservice = new SpreadsheetService(appliName);		//（1）
  spreadsheetservice.setUserCredentials(user, pass);								//（2）

処理の最初はアプリケーション名の指定と、ユーザ名とパスワードによる認証処理です。

（1）アプリケーション名はSpreadsheetと接続する場合の識別用に使用されます。

（2）ユーザ名とパスワードによる認証はsetUserCredentialsメソッドで行いますが、Googleアカウントのメールアドレスとパスワードを使用します。　実システムでは画面からGoogleアカウントとパスワードを入力して、ユーザの認証を行うようにするのが良いでしょう。

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

FeedURLFactory feedurlfactory = FeedURLFactory.getDefault();					（1）
SpreadsheetQuery spreadsheetquery 
  = new SpreadsheetQuery(feedurlfactory.getSpreadsheetsFeedUrl());			（2）
spreadsheetquery.setTitleQuery(sheet); 										（3） 
SpreadsheetFeed spreadsheetfeed 
  = spreadsheetservice.query(spreadsheetquery, SpreadsheetFeed.class);		（4）
SpreadsheetEntry spreadsheetentry = spreadsheetfeed.getEntries().get(0);		（5）

（1）では、Spreadsheetsサーバで使用されるフィードURLのデフォルトインスタンスを生成します。
フィード（Feed）には幾つかの意味がありますが、ここでは、Webサイトから受信可能なデータと考えれば良いでしょう。

（2）feedurlfactory.getSpreadsheetsFeedUrl()　では認証によってアクセス可能なすべてのSpreadsheetに対するフィードを受け取ることができるURLを取得し、SpreadsheetQueryでは取得したフィードURLを引数にして、Spreadsheetを検索するためのqueryを生成します。

（3）生成されたqueryにスプレッドシート名（sheet）を引数にしたフルテキスト検索を構成します。
ここで注意が必要なのは、Drive上には同じ名前のSpreadsheetファイルを複数配置することができるということです。また、同じ名前のspreadsheetはDrive上のフォルダが同じでも違っていても、すべて（3）のフル検索の対象になります。

例えば、図10にはSalesフォルダ内にファイル名201304のシートが1つあり、図11にはtestフォルダ内に同じ201304のシートが2つありますが、これらはすべて検索の対象になります。

図10：Salesフォルダ内の201304シート（クリックで拡大）

図11：Testフォルダ内の201304シート（クリックで拡大）

（4）では（3）で生成したspreadsheetqueryを引数にクエリを実行し、クエリ内容に一致するエントリを含んだフィードを取得します。

（5）spreadsheetfeed.getEntries()　でフィード内のスプレッドシートエントリのリストを返しますが、上で見たように同じ名前のスプレッドシートを複数作成することもできるので、get(0)　で１つのスプレッドシートを特定します。ただここで、複数の同じ名前のシートの内、どのシートがget(0)で選択されるのかが問題ですが、図10、図11の右端に表示されているLAST　MODIFIED(最終更新日)が最も新しいシートが選択されます。つまりgetメソッドの引数は更新日の新しい方から、0,1,2…となっている訳です。
ただし、更新の度に選択されるシートが変わってしまうのでは正確な処理を行うことはできません。したがって、Drive上でのスプレッドシート名はすべて一意（unique）にしておくべきです。

[III] 検索対象のワークシートを特定する

WorksheetEntry worksheetentry = spreadsheetentry.getDefaultWorksheet();			（1）

ここまでの処理で、検索対象は1枚のスプレッドシートに特定されましたが、最後にワークシートの特定を行います。

図12：スプレッドシート内のワークシート（クリックで拡大）

Excelでは１つのシートファイルに、タブ設定によって複数の入力シートを保持できますが、Drive上のSpreadsheet　でも同様に複数のワークシートを保持することができます。したがって、複数ワークシートから１つを特定することが必要になってきますが、これを行っているのが（1）です。getDefaultWorksheet()　は複数のワークシートから一番上に表示されているシート（図12のSheet1）を選択して返します。

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

ここまでで、複数のSpreadsheetから、幾つかの処理を経て検索対象のワークシートを特定する処理を見てきましたが、これでようやくワークシートデータの検索ができるようになります。

ListQuery listQuery = new ListQuery(worksheetentry.getListFeedUrl());			（1）
ListFeed listFeed = spreadsheetservice.query(listQuery, ListFeed.class);			（2）
List<ListEntry> dataList = listFeed.getEntries();        						（3）

（1）worksheetentry.getListFeedUrl()　でワークシートのリストフィードのURLを取得し、それを引数にした ListQuery　の生成でワークシートに対する行ベースのクエリを構成します。

（2）ターゲットサービスへのクエリを実行し、クエリ結果に一致するリストフィード（listFeed）を返します。

（3）リストフィード内のエントリを行単位のリストで返します。

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

String rv = "{ \"wsdat\":[";
for (Iterator<ListEntry> it = dataList.iterator(); it.hasNext();) {
  ListEntry listEntry = (ListEntry) it.next();
  rv +=  "{\"cdat\": \"" + listEntry.getPlainTextContent() + "\"},";
}
rv = rv.substring(0, rv.length() - 1) + "]}";
return rv;

最後に、行単位のワークシートデータをJSONフォーマットに組み立てでクライアントに送りますが、この部分は一般的な繰り返し（iterate）処理ですので、問題ないでしょう。

著者

清野克行

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

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

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