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

本連載も今回が最終回となりますが、今回は次の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回で解説していますので、そちらを参照して下さい。

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++) {																	 //（９）
    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）を指定してクリア処理を実行します。