GoogleApps‎ > ‎

Google Docs & Spreadsheet から GoogleAppEngine にデータを転送する

みなさんこんにちわ!GoogleAppEngineでWebアプリを動かす…よくあることですね!
弊社でも SengokuDroid というAndroid用のゲームを動かしています!
さて、Webアプリを作成しているとパラメータの設定などをしなければいけない場合が多々ありますね!
SengokuDroidでも様々なパラメータ(動作を許可する端末側アプリバージョンなど)が存在していますが、その設定をどうやるか?なかなか悩ましい問題です。

ハードコーディングする。
イケてないですね。ちょっとパラメータを変えたいのに、いちいちプログラムを書き換えてdeployしなおし?それはめんどくさすぎます。おまけに、それってappnengineを理解しているエンジニア以外の人が好きな時に変更できないじゃないか!

パラメータ用Kindを用意して自由に設定できるようにする。
WebUIが必要ですね。悪くない選択肢ですが、コピペビリティがあまり高くなさそうです。大量データの流しこみとかをしたい需要などにも対応しにくそうに思われます。

表計算ソフトで編集。
今までのSengokuDroidはこの方式でした。表計算ソフトでデータを作成し、データ(.xlsとかですね)をJSONに変換してから専用のアップローダプログラムでサーバに投入します。うーん、だいぶ敷居が下がってきましたが、リポジトリからツールを拾ってきたり、ツールの使い方を学習したりと若干のコストがかかります。SengokuDroidでは現在のデータ投入担当以外はイマイチ手順を把握していなかったり…。

Spreadsheetから直接操作で更新!!
これです!これが求めているものにかなり近い!! というわけで今回はこの方法を推し進めてみます。

Google Docs & Spreadsheet + GoogleAppEngine

まずは簡単な動作イメージから

こういうSpreadsheetを用意して

ツールバーの見慣れぬメニューを選択すると

アップロード先やKind、Keyをどれにするかの指定をして"アップロード"ボタンを押すと

appengine側に取り込まれます!!

とっても簡単だ!

コード

最初に書いておきますが、全ては自己責任でお願いします。バグがあっても、サーバのデータが吹っ飛んでも、何があっても自分でなんとか頑張ってください。コードの内容をひと通り理解したうえで実行しましょう。

AppsScript
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [
    {name: "start", functionName: "showUI"}
  ];
  ss.addMenu("uploader", menuEntries);
}

function showUI(){
  var app = UiApp.createApplication();
  
  setUpUI_(app);
  
  SpreadsheetApp.getActiveSpreadsheet().show(app);
  return app;
}

function setUpUI_(app){
  app.setTitle('データアップローダ');
  
  var uploadUrlLabel = app.createLabel().setText('アップロード用URL');
  var uploadUrlText = app.createTextBox().setId('uploadUrlText').setName('uploadUrlText');
  var uploadUrl = getProperty_('uploadUrl');
  uploadUrl &&  uploadUrlText.setText(uploadUrl);
  
  var kindLabel = app.createLabel().setText('Kind');
  var kindText = app.createTextBox().setId('kindText').setName('kindText');
  var kind = getProperty_('kind');
  kind && kindText.setText(kind);
  
  var keyLabel = app.createLabel().setText('Keyとするプロパティ');
  var keyText = app.createTextBox().setId('keyText').setName('keyText');
  var key = getProperty_('key');
  key && keyText.setText(key);
  
  var uploadButton = app.createButton('アップロード');
  var clickHandler = app.createServerClickHandler('uploadHandler_');
  clickHandler.addCallbackElement(uploadUrlText);
  clickHandler.addCallbackElement(kindText);
  clickHandler.addCallbackElement(keyText);
  uploadButton.addClickHandler(clickHandler);
  
  var previewButton = app.createButton('プレビュー');
  var clickHandler = app.createServerClickHandler('previewHandler_');
  previewButton.addClickHandler(clickHandler);
  
  var closeButton = app.createButton('閉じる');
  var clickHandler = app.createServerClickHandler('closeHandler_');
  closeButton.addClickHandler(clickHandler);
  
  // レイアウト
  var grid = app.createGrid(3,2);
  grid.setWidget(0, 0, uploadUrlLabel);
  grid.setWidget(0, 1, uploadUrlText);
  
  grid.setWidget(1, 0, kindLabel);
  grid.setWidget(1, 1, kindText);
  
  grid.setWidget(2, 0, keyLabel);
  grid.setWidget(2, 1, keyText);
  
  app.add(grid);
  app.add(uploadButton);
  app.add(previewButton);
  app.add(closeButton);
}

function setProperty_(name, value){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sht = ss.getActiveSheet();
  var sheetIdentifyStr = ss.getName() + "#" + sht.getName() + "#";
  
  ScriptProperties.setProperty(sheetIdentifyStr + name, value);
}

function getProperty_(name){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sht = ss.getActiveSheet();
  var sheetIdentifyStr = ss.getName() + "#" + sht.getName() + "#";
  
  return ScriptProperties.getProperty(sheetIdentifyStr + name);
}

function uploadHandler_(params){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sht = ss.getActiveSheet();
  var sheetIdentifyStr = ss.getName() + "#" + sht.getName() + "#";
  var app = UiApp.getActiveApplication();
  
  // 入力値の取得
  var uploadUrl = params.parameter['uploadUrlText'];
  var kind = params.parameter['kindText'];
  var key = params.parameter['keyText'];
  
  // 入力値の保存(次回のデフォルト)
  setProperty_('uploadUrl', uploadUrl);
  setProperty_('kind', kind);
  setProperty_('key', key);
  
  var params = {
    json : sheet2json_(),
    kind : kind,
    key : key
  };
  
  try {
    var resp = postWithOAuth_(uploadUrl, params);
    Browser.msgBox(resp.getResponseCode());
  } catch(e) {
    var msg = "";
    for (var i in e) {
      msg += e[i] + "\n";
    }
    Browser.msgBox(msg);
  }
  
  return app;
}

function previewHandler_(){
  var app = UiApp.getActiveApplication();
  Browser.msgBox("Do you know?\nhttp://jsoneditor.appspot.com/\n\n" + sheet2json_());
  return app;
}

function closeHandler_(){
  var app = UiApp.getActiveApplication();
  app.close();
  return app;
}

function sheet2json_(){
  var sht = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var index = {
    columnStart:0,
    columnEnd:0
  };
  var titles = [];
  var i = 1;
  while(sht.getRange(1, i).getValue() != ""){
    titles.push(sht.getRange(1, i).getValue());
    i++;
  }
  
  i = 2;
  var j;
  var datas = [];
  while(sht.getRange(i, 1).getValue() != ""){
    var data = {};
    for(j = 0;j < titles.length; j++){
      var title = titles[j];
      var val = sht.getRange(i, j + 1).getValue();
      data[title] = val;
    }
    datas.push(data);
    i++;
  }
  var json = JSON.stringify(datas, null, true);
  Logger.log("sheet2json:" + json);
  return json;
}

function postWithOAuth_(url, params){
  initApps_();
  var options = option_();
  options['method'] = 'post';
  options['payload'] = makePostPayload_(params);
  var response = UrlFetchApp.fetch(url, options);
  return response;
}

function makePostPayload_(params){
  var payload = "";
  for(var i in params){
    payload += encodeURI(i) + "=" + encodeURI(params[i]) + "&"; 
  }
  return payload;
}

function signupOAuth() {
  var url = "http://???.appspot.com/";
  UrlFetchApp.fetch(url, option_());
}

function initApps_() {
  var setDefault = function(name, value){
    var now = ScriptProperties.getProperty(name);
    !now && ScriptProperties.setProperty(name, value);
  };
  setDefault("provider", "appengine");
  setDefault("schema", "http://");
  setDefault("appId", "???");
  setDefault("domainName", "appspot.com");
  setDefault("consumerKey", "anonymous");
  setDefault("consumerSecret", "anonymous");
}

function initOAuthUrl_() {
  var schema = ScriptProperties.getProperty("schema");
  var appid = ScriptProperties.getProperty("appId");
  var domain = ScriptProperties.getProperty("domainName");
  
  var ah = schema + appid + "." + domain + "/_ah/";
  ScriptProperties.setProperty("accessTokenUrl", ah + "OAuthGetAccessToken");
  ScriptProperties.setProperty("requestTokenUrl", ah + "OAuthGetRequestToken");
  ScriptProperties.setProperty("authUrl", ah + "OAuthAuthorizeToken");
}

function option_() {
  initApps_();
  initOAuthUrl_();
  var oAuthServiceName = ScriptProperties.getProperty("provider");
  var accessTokenUrl = ScriptProperties.getProperty("accessTokenUrl");
  var requestTokenUrl = ScriptProperties.getProperty("requestTokenUrl");
  var authUrl = ScriptProperties.getProperty("authUrl");
  var key = ScriptProperties.getProperty("consumerKey");
  var secret = ScriptProperties.getProperty("consumerSecret");
  
  var oAuthConfig = UrlFetchApp.addOAuthService(oAuthServiceName);
  oAuthConfig.setAccessTokenUrl(accessTokenUrl);
  oAuthConfig.setRequestTokenUrl(requestTokenUrl);
  oAuthConfig.setAuthorizationUrl(authUrl);
  oAuthConfig.setConsumerKey(key);
  oAuthConfig.setConsumerSecret(secret);
  
  var options = {
    "oAuthServiceName" : oAuthServiceName,
    "oAuthUseToken" : "always"
  };
  
  return options;
}

AppEngine
package jp.co.topgate.controller;

import java.util.List;
import java.util.logging.Logger;

import net.vvakame.util.jsonpullparser.util.JsonArray;
import net.vvakame.util.jsonpullparser.util.JsonHash;

import org.slim3.controller.Controller;
import org.slim3.controller.Navigation;
import org.slim3.datastore.Datastore;

import com.google.appengine.api.datastore.Entity;
import com.google.appengine.api.oauth.OAuthService;
import com.google.appengine.api.oauth.OAuthServiceFactory;
import com.google.appengine.api.users.User;
import com.google.common.collect.Lists;

/**
 * AppsScriptからのデータ受取用コントローラ
 * @author vvakame
 */
public class AppsScriptController extends Controller {

	static final Logger logger = Logger.getLogger(AppsScriptController.class.getName());


	@Override
	protected Navigation run() throws Exception {

		OAuthService oAuthService = OAuthServiceFactory.getOAuthService();
		User user = oAuthService.getCurrentUser();
		logger.info("userId=" + user.getUserId());
		logger.info("email=" + user.getEmail());
		logger.info("nickName=" + user.getNickname());

		// アクセスチェックしたほうがいいよ!

		String json = asString("json");
		JsonArray ary = JsonArray.fromString(json);

		String kind = asString("kind");
		String keyEntry = asString("key");

		List entites = Lists.newArrayList();
		for (int i = 0; i < ary.size(); i++) {
			JsonHash hash = ary.getJsonHashOrNull(i);
			if (hash == null) {
				throw new IllegalArgumentException("idx=" + i + " is invalid. " + json);
			}
			Entity tmpEntity = new Entity(kind);
			Entity entity = null;
			for (String hashKey : hash.keySet()) {
				Object obj = hash.get(hashKey);
				if (keyEntry.equals(hashKey)) {
					if (obj instanceof String) {
						entity = new Entity(kind, (String) obj);
					} else if (obj instanceof Long) {
						entity = new Entity(kind, ((Long) obj).longValue());
					}
				} else {
					tmpEntity.setProperty(hashKey, obj);
				}
			}
			entity.setPropertiesFrom(tmpEntity);
			entites.add(entity);
		}
		Datastore.put(entites);

		return null;
	}
}

だいたいこんな感じ。めんどいので解説はしません。拙作の JsonPullParser というJSON処理用のライブラリを使ってますが、まぁだいたいJSON.orgと同じなので読めるかと思います。

使い方

一応使い方をざっくりと解説しておきます。

  1. 新規Spreadsheet作成
  2. メニューのTools→Script editor
  3. AppsScriptをがっつりコピペして保存
  4. signupOAuth を1回実行(エラーになる
  5. ファイル→プロパティ→スクリプトのプロパティ の内容を確認し以下の項目を編集
    1. appId
    2. authUrl
    3. requestTokenUrl
    4. accessTokenUrl
    5. (consumerKey) 基本はanonymousでいいはず
    6. (consumerSecret) 基本はanonymousでいいはず
  6. もう一回signupOAuth を実行 appengineにアクセスする場合のOAuthの承認などを行う  これで準備は完了です
  7. Spreadsheetのほうのメニューから uploader→start を選択
  8. 適切な値を入力してアップロードを押す

メモ

  • UI Builder 避けた
    • 現在はコード生成してくれるわけではないので、gitなどのSCMでリソース管理が出来なくなる
    • レイアウトがぐちょったりしてdirty hackが必要になる
  • UIからOAuthの認証する方法がわからん
    • まぁAppsScript作成者が認証すれば後はみんながその人の権限でアクセスできるらしい
  • OAuthをrevokeしたり再認証したりする方法わからん
    • どーしよーもないので oAuthServiceName 変えてしのぐしかない

参考資料

何か疑問や質問があれば @vvakame まで。手探りでなんとかできた!って感じなので詳しい質問には答えられない可能性大です。
Comments