Google Spreadsheetのデータを自由自在に取るためにはテクニックが必要です。Google Script Appsではこの辺りは割りと簡単というか、お決まりのメソッドとルーチンを書くだけで柔軟に取得することができます。しかし、JavaScriptで取る場合には、結構面倒くさいです。逆にいえば、スプレッドシートでの取り扱いができれば、あとはいかようにもなる点ではJavaScriptのほうが柔軟です。そこで、このあたりをまとめてみたいと考えています。
新しいスプレッドシートが公開されてから、これまでのようなURLでのスプレッドシートへのデータアクセスが出来なくなってしまいました。URLの仕様そのものが旧スプレッドシートから変更されてしまった為で、このままではVisualization APIへアクセスさせて使うといった事ができません。この辺りの情報が例の如く、まったくまとめられておらず、公開もされている場所も不明な為、ウェブにあった情報をまとめてみたところ、アクセスできそうな情報があったため、それをまとめてみようと思っています。
今回参考にする新スプレッドシートは、旧スプレッドシートを以降させたこのスプレッドシートを使用します。普通に開くと以下のようなURLが表示されます。
このURLを読み解きたいと考えます。大分というか、かなりURLに変更が掛かっています。シートのIDがURLの一部になっている上に、gidがシート１枚目が0といった旧式とは異なり、本当の意味でのランダム生成されるGUID的なものに変更されています。また、現時点では特定のシートのみを表示させるといった旧スプレッドシートにあった機能が搭載されていないため、個別シートをPDFでエクスポートするような形で表示させることが出来ます。
このままだと、ベタのデータ（json形式）が出るだけでプログラム上のデータとしては取得が出来ます。HTML成形させた状態で表示させるならば下記のようになります。
また、ウェブに公開している場合には、以下のURLで公開ドキュメントとして見ることが出来ます。但し、single=trueは使用することが出来ません。
旧バージョンと同じく、オプション指定をするかのようにコンストラクタの引数をしていしていくことで、データの取得が用意になります。但し、旧バージョンより大分シンプルになってしまっています。様々な形式でのエクスポートを考えているのであれば、個別シートをPDFででエクスポートするを参照してみてください。また、この値をBlobで引き受けて処理をさせるなんてことも可能です。
keyの指定
必須の項目です。Google Spreadsheet個別に割り当てられているkeyを指定します。上のURLにある1wJzSxUdvSZS7woAuAgEIIdZ_fN87QNCA90Ckr9ijJ-4の部分がそれです。ファイルによってkeyが異なります。
gidの指定
これまで通り指定はできますが、ランダムなGUIDが付けられています。その為、これまでは１桁程度だったのが、10桁程度の数値の列となっています。別に指定しなくても良いですが、通常、データの取得などで使う場合には、必須の項目です。
outputの指定
必須ではありませんし、正直あまり使いません。ただ、output文が旧スプレッドシートとは異なっています。tqx=out:htmlといった形での指定となり、htmlやcsvなどしかありません。また、旧スプレッドシートで対応していたfeed出力ですが、出来るっぽいのですが、od6と書かれた部分がどうもいろいろ違うようです。worksheetidと呼ばれるものになっているようなのですが、このworksheetidが何者なのかがわかりません。gidではないようですが・・・その為、出力といっても結構面倒なものになっています。このworksheetidは一覧で出してみないとわかりません。固定値ではないようです。feedでのアクセス方法は以下の通りです。
※一覧の出し方は以下のようになっています。
※ATOM形式の場合にはURLは以下のようになっています。
※RSS形式の場合にはURLは以下のようになっています。
また、以下のURLのほうがわかりやすく出力することができます。こちらの場合のURLはURLの中の特定の数値が○枚目のシートの意味になっているので、上記の謎のworksheetidとやらを考える必要がありません。
※ATOM形式の場合にはURLは以下のようになっています。
※RSS形式の場合にはURLは以下のようになっています。
※JSON形式の場合にはURLは以下のようになっています。
rangeの指定
rangeの指定は、特定のファイルの特定のシートに於ける、特定の範囲だけを指定するものです。指定方法はURLエンコードされている形での指定でなくとも、表示することが可能になっています。A1:C6の範囲を指定して表示する場合には、そのまんま&に続けて、range=A1:C6をくっつければOKです。もちろん、URLエンコードされたこれまでの文字列でも問題ありません。
Queryの指定
コンストラクタの引数に置いて、QUERY文を作って投げることでSQL文のような取り方が可能になっています。但し、ちょっとややこしいので、ノートパッドなどで文をつくってから、何度か貼り付けて抽出できるようにしてみると良いと思います。作り方は、tq=に続けてSQL文っぽいのを入れてあげるわけです。下記にA列とB列のみを表示させ、尚且つB列の値が1000より上の値のレコードだけ抽出してみました。スペース部分はURLエンコードが必要です。QUERY文の作り方は、Query Language Referenceを参照してくだっさい。
新しいスプレッドシートが出てからしばらくの間、Google Visualization APIを使ったスプレッドシートからのグラフの作成等がしばらくの間出来ませんでした。しかし、大分情報が集まってきて、このセクションにある新しいスプレッドシートのデータの取り方（URL）で、XMLガジェットを作成し、グラフを表示してみました。今回このグラフを作成するにあたっては、特に特別なことはしていません。ソースコードもVisualization APIのQueryへ投げるURLが違うだけです。今回は
参照URL
※ちなみに、このグラフを表示するための旧スプレッドシートの場合のURLは以下の通りです。
ソースコード
<?xml version="1.0" encoding="UTF-8" ?><Module><ModulePrefs title="sample1" /><Content type="html"><![CDATA[<html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="content-type" content="text/html; charset=utf-8"/> <title> Google Visualization API Sample </title> <script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript"> //corechartのパッケージをロード google.load("visualization", "1", {packages:["corechart"]}); google.setOnLoadCallback(initialize); //初期化 function initialize() { // The URL of the spreadsheet to source data from. var query = new google.visualization.Query( 'https://docs.google.com/spreadsheets/d/1wJzSxUdvSZS7woAuAgEIIdZ_fN87QNCA90Ckr9ijJ-4/gviz/tq?gid=1978850084'); query.send(draw); } //返り値を受けてグラフを描画するルーチン function draw(response) { if (response.isError()) { alert('Error in query'); } //データテーブルに格納する var data = response.getDataTable(); var chart = new google.visualization.ColumnChart(document.getElementById('chart_div')); //グラフオプションの指定 var options = { title: '売上分析', legend: {textStyle: {fontSize: 11}},dataOpacity: '0.4',hAxis: {title: '支店名'},vAxis: {title: '金額（百万円）'},height: 500,width: 800 }; //グラフの描画 chart.draw(data, options); } </script> </head> <body style="font-family: Arial;border: 0 none;"> //チャート描画場所 <div id="chart_div"></div> </body></html>]]></Content></Module>※支店別売上高データを基に、Google Visualization APIの棒グラフを作成しています。
サンプル表示
サンプル表示してみました。すんなり表示してくれてありがたかった。これならば、新しいスプレッドシート形式であっても、心置きなくVisualization APIを用いてグラフの表示ができます。
今回参考にするスプレッドシートはこのスプレッドシートを使用します。普通に開くと以下の様なURLが表示されます。
このURLを読み解きたいと考えます。実はこのURLですと、ちょっと具合が悪いので、以下のようなURLで指定します。そのシートだけ表示する場合には&single=trueを追加すると、目的のシートのみの表示に変化します。
このURLにコンストラクタの引数をつなげて実行すると、目的のシートの値を手に入れることができるのです。通常開かれているURLでは、引数をつなげても、うまく表示されず、またデータも取れません。また、下記のようなURLでも可能です。
それぞれ、表示方法がだいぶ違います。
１つ目のURLの場合、通常いつも見ているシートの画面です。しかし、２つ目は編集ができないビューの状態です。３つ目に至ってはもはやスプレッドシートではありません。完全にデータ取得を目的としたJSON形式での出力となります。
コンストラクタの引数というとなんだか面倒くさく考えてしまいますが、要するにURLに加える文字列です。文字列を加えてつなげて投げてやることで、それらをオプションとして扱って、表示の方法を変えてくれるわけです。例えば、上にあるURLでは、gid=0なんてのがそれに該当します。&でつなげて、ルールに従った引数を渡してやれば、結構複雑な表示や柔軟なデータの取得が可能です。
keyの指定
必須の項目です。Google Spreadsheet個別に割り当てられているkeyを指定します。上のURLにある0AjN10lCzkUqkdHJqaUxkaUdyOUR3VGQwM3VHWTdWcGcの部分がそれです。ファイルによってkeyが異なります。
gidの指定
gidはkeyで指定したファイルに含まれている各シートに割り当てられているシート番号で、１枚しかない場合には指定は不要です。しかし、通常２枚３枚とある場合、省略すると１枚目が呼び出されてしまうので、やや必須の項目です。gidはシートが増えると新しいシートには次のgidが当てられますが、gid=1なら1枚目という意味ではないので注意。シートを移動しても変わらず、また、シートを削除して新たに増やしていくとgidは飛び飛びになり歯抜けのIDが付与されていく仕様になっています。
outputの指定
outputの指定はどういった形式で出力するか？を指定するものです。結構幅広く出力できるようで、以下のような種類があります。
※1： ATOM形式の場合にはURLは以下のようになります。
※２： RSS形式の場合にはURLは以下のようになります。
※３ JSON形式の場合にはURLは以下のようになります。
rangeの指定
rangeの指定は、特定のファイルの特定のシートに於ける、特定の範囲だけを指定するものです。指定方法はURLエンコードされている形での指定が必要なので、例えば、A2:B10の指定をしたい場合には、range=A2%3AB10といった形で、コロンを%3Aという文字列にしてつなげます。実際に表示してみるとこんな感じになります。
singleの指定
singleは表示する場合に指定した特定のシートだけを表示するかどうかを指定するものです。single=trueでgidなどで指定した特定のシートのみを表示することが可能です。
JavaScriptで取得する場合には、Visualization APIを用いてデータを取得します。そのため、以下のようなコードで取得します。その際に上記で指定したURLを用いるわけです。通常表示されているURLでは常に１シート目が取得されてしまいますので、ご注意ください。取得をする際にqueryを投げていますが、その時に簡易SQL言語的なもので指定をすると、さらにその中から特定の条件に合うデータの取得が可能になります。
//Visualization APIを使用するための外部ライブラリの指定<script type="text/javascript" src="https://www.google.com/jsapi"></script><script type="text/javascript"> google.load("visualization", "1");//読み込みが完了したらinitializeに飛び初期化を開始google.setOnLoadCallback(initialize);//初期化。chartman関数に送る function initialize() { // The URL of the spreadsheet to source data from. var query = new google.visualization.Query( 'https://docs.google.com/spreadsheet/pub?key=スプレッドシートのID&gid=0'); query.send(chartman); }//返り値を受けてグラフを描画するルーチン function chartman(response) { if (response.isError()) { alert('Error in query'); } //データテーブルに格納する var data = response.getDataTable(); //値を取得して格納する var varValues = data.getValue();・・・ここに処理を記述していくことになる・・・</script>
}Visualization APIを使用するので、必ずjsapiライブラリの指定は必須です。
その後、初期化の関数に飛ばすルーチン。これも記述します。
その後は、Visualization APIの仕様に則り、queryにて組み立てておいたURLを投げる。それを受け取る関数は必ず引数がresponseとなります。受け取ったデータからgetDataTable()にてデータテーブルとして受け取り、それを配列に格納するために、data.getValue()で値の塊を取得しています。
正直な所、この辺りはGoogle Apps Scriptのほうが簡単です。また、この面倒くさいやり取りを簡単にしてくれるspreadsheetrenderer.jsというライブラリが存在します。ダウンロードしておき、特定の場所に配置し、使用します。
Google Apps Scriptの場合、GASのほうで取得のためのメソッドが用意されているので、コンストラクタの引数などを気にせずに自在にデータを取得することが可能です。主に以下の様なコードで取得します。
//スプレッドシートのIDとシート名を指定var sheet = SpreadsheetApp.openById("0AjN10lCzkUqkdHJqaUxkaUdyOUR3VGQwM3VHWTdWcGc").getSheetByName("sheet1");//特定のレンジを配列として取得var varValues = sheet.getRange("A2:D10").getValues();openByIdにてkeyを指定、getSheetByNameでシートの名前を指定、getRangeで範囲を指定し、getValueで値を取得という流れになります。単体のセルの場合には、getValueを使用します。
この時、getRangeをgetDataRangeとして指定すると、そのシートで表示されている内容全部をデータの塊とみなし、getValuesで取得の流れになります。range指定で取得したデータは配列として格納されますので、上記の例で言えば、取り出す際には、varValues[1][0]などとして指定することで、値を取り出すことが可能です。この場合、2行目の1セル目となりますので（関数内では１行目１セル目は0からスタートするため）、A2を指定していることになります。ルーチンを書く際には、この0から始まっていることに注意して書かなければなりません。