ピボットテーブル表示

Google Visualization APIにはまだ、ピボットテーブルを実現するような機能は存在していない為、せっかくのSpreadsheetの機能を十分にウェブで実現することができません。そこで、今回は、Visualization APIではなく、別のJavaScriptライブラリで実現をしてみようと考えました。まだまだ自分に実力がない点もあり、また普段、Safariで見ていることもあって、問題が発生していてなかなか実現できなかったのですが、とりあえずChromeならば見ることが出来るようになったので、これでとりあえず良しとしました。どうも、ガジェット内でgetJSONを発行している時にブラウザ側がレスポンスデータを破棄するAccess-Control-Allow-Originの問題なのか？Safariでは表示がされませんでした。ということで、現在はSafariやIE8では見ることができません。ローカル環境だとSafariでも表示されるのに、なぜだろう・・・・（ローカル用HTML）。

しかし、今回のライブラリのお陰で、Google Visualization API以外の手段でもGoogle Spreadsheetにアクセスして、リアルタイムでピボットテーブルを作れるようになりました。対象のシートはこちらです。

Google Apps for Businessで動かす場合には、クロスドメインの問題もあって制限されている関係で、データ類はjsファイルに記述し、Google sites内のどこかに保存して参照できるようにするしか方法がありません。

とりあえず表示（Google Spreadsheetより抽出）

<?xml version="1.0" encoding="UTF-8" ?>
<Module>
<ModulePrefs title="sample1" scaling="true" scrolling="true" />
<Content type="html">
<![CDATA[
<html lang="ja">
<head>
  <meta charset="UTF-8">
  <title>pivotテスト</title>
  <link rel="stylesheet" type="text/css" ">
  <script src=""></script>
  <script src=""></script>
  <script src=""></script>
  <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">

google.load('visualization', '1', {packages: ['table']});

      google.setOnLoadCallback(initialize);
      
      function initialize() {
        // The URL of the spreadsheet to source data from.

var spreadsheeturl = "https://docs.google.com/a/hmw.gr.jp/spreadsheet/pub?key=0AlsoCjrriU5rdHRFVFhvM0JlLVR1QUx2Q0xhTFBlZ0E&gid=4&single=true";

        var query = new google.visualization.Query(spreadsheeturl);
        query.send(draw);
      }
      function draw(response) {

if (response.isError()) {

alert('Error in query');

}


var dataman = response.getDataTable();

//行の数を取得する

var numRows = response.getDataTable().getNumberOfRows();

total = Number(numRows) + 1;


//二次元配列を作成する

var msgcnt =7;

var dataArray = new Array(total);

for(i = 0; i < total; i++){

dataArray[i] = new Array(msgcnt);

}

var cnt =1;

//データを挿入する

for(i=0; i<numRows; i++){

if(i==0){

//カラムの行だけ作成する

dataArray[i][0] = "分類ID";

dataArray[i][1] = "成分名";

dataArray[i][2] = "規格";

dataArray[i][3] = "薬品名";

dataArray[i][4] = "メーカー";

dataArray[i][5] = "薬価";

dataArray[i][6] = "卸名";

dataArray[i][7] = "数量合計";

}

//レコードデータを入れる

dataArray[cnt][0] = dataman.getValue(i, 0);

dataArray[cnt][1] = dataman.getValue(i, 1);

dataArray[cnt][2] = dataman.getValue(i, 2);

dataArray[cnt][3] = dataman.getValue(i, 3);

dataArray[cnt][4] = dataman.getValue(i, 4);

dataArray[cnt][5] = dataman.getValue(i, 5);

dataArray[cnt][6] = dataman.getValue(i, 6);

dataArray[cnt][7] = dataman.getValue(i, 7);

cnt = cnt + 1;

}


$(function(){

$("#output").pivotUI(dataArray,

{

rows: ["成分名"],

cols: ["卸名"],

vals: ['数量合計'],

aggregatorName: 'intSum',

rendererName: 'table'

},

true

);

});

   }
    </script>
</head>
<body>
<div id="output"></div>
</body></html>
]]>
</Content>
</Module>

ポイント

  1. 今回はPivotTable.jsという外部ライブラリを使っています。そのため、jQuery関係のライブラリも必要です。
  2. Google SpreadsheetはJSON形式で出力可能なのですが、そのままではPivotTable.jsがデータを処理してくれないので加工が必要です。そこでSheetAsJSONというGoogle Apps Scriptをスプレッドシートに仕込み、ウェブアプリケーションとして公開。そのGASに投げるときのURLをuriにいれてgetJSONとして受け取っています。
  3. しかし、SheetAsJSONもそのままだと、まだPivotTable.jsが受け付けてくれないので、一部コードを削ります。doGet()内にあるdata[sheet] = readData_(ss, sheet);の部分の[sheet]を削除します。これで大丈夫。
  4. Google Apps Script で spreadsheet のデータを JSON として読み込む のサイトで紹介されているGASのコードで出された結果では、なぜか受信ができなかったので今回は採用せず。
  5. Safariで表示ができなかったので、何らかの追加の対策が必要。SheetAsJSON側なのか、それとも間にPHPでも経由させて受信するか？いずれにせよガジェット側ではどうにもならないので、発信側に対策が必要である。どうも、他にもSafariに関してのみこんな問題があるようで・・・

参考リンク