スプレッドシートのデータを自由に取りたい

Google Spreadsheetのデータを自由自在に取るためにはテクニックが必要です。Google Script Appsではこの辺りは割りと簡単というか、お決まりのメソッドとルーチンを書くだけで柔軟に取得することができます。しかし、JavaScriptで取る場合には、結構面倒くさいです。逆にいえば、スプレッドシートでの取り扱いができれば、あとはいかようにもなる点ではJavaScriptのほうが柔軟です。そこで、このあたりをまとめてみたいと考えています。

スプレッドシートURLを読み解く（新しいスプレッドシート）

新しいスプレッドシートが公開されてから、これまでのようなURLでのスプレッドシートへのデータアクセスが出来なくなってしまいました。URLの仕様そのものが旧スプレッドシートから変更されてしまった為で、このままではVisualization APIへアクセスさせて使うといった事ができません。この辺りの情報が例の如く、まったくまとめられておらず、公開もされている場所も不明な為、ウェブにあった情報をまとめてみたところ、アクセスできそうな情報があったため、それをまとめてみようと思っています。

概要

今回参考にする新スプレッドシートは、旧スプレッドシートを以降させたこのスプレッドシートを使用します。普通に開くと以下のようなURLが表示されます。

https://docs.google.com/spreadsheets/d/1wJzSxUdvSZS7woAuAgEIIdZ_fN87QNCA90Ckr9ijJ-4/edit#gid=1370144118

このURLを読み解きたいと考えます。大分というか、かなりURLに変更が掛かっています。シートのIDがURLの一部になっている上に、gidがシート１枚目が0といった旧式とは異なり、本当の意味でのランダム生成されるGUID的なものに変更されています。また、現時点では特定のシートのみを表示させるといった旧スプレッドシートにあった機能が搭載されていないため、個別シートをPDFでエクスポートするような形で表示させることが出来ます。

https://docs.google.com/spreadsheets/d/1wJzSxUdvSZS7woAuAgEIIdZ_fN87QNCA90Ckr9ijJ-4/gviz/tq?gid=1978850084

このままだと、ベタのデータ（json形式）が出るだけでプログラム上のデータとしては取得が出来ます。HTML成形させた状態で表示させるならば下記のようになります。

https://docs.google.com/spreadsheets/d/1wJzSxUdvSZS7woAuAgEIIdZ_fN87QNCA90Ckr9ijJ-4/gviz/tq?gid=1978850084&tqx=out:html

また、ウェブに公開している場合には、以下のURLで公開ドキュメントとして見ることが出来ます。但し、single=trueは使用することが出来ません。

https://docs.google.com/spreadsheets/d/1wJzSxUdvSZS7woAuAgEIIdZ_fN87QNCA90Ckr9ijJ-4/pubhtml?gid=1978850084

コンストラクタの引数

旧バージョンと同じく、オプション指定をするかのようにコンストラクタの引数をしていしていくことで、データの取得が用意になります。但し、旧バージョンより大分シンプルになってしまっています。様々な形式でのエクスポートを考えているのであれば、個別シートを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でのアクセス方法は以下の通りです。

※一覧の出し方は以下のようになっています。

• https://spreadsheets.google.com/feeds/worksheets/スプレッドシートのID/private/full

※ATOM形式の場合にはURLは以下のようになっています。

1. リストの場合：https://spreadsheets.google.com/feeds/list/スプレッドシートのID/omnqxt8/private/full
2. セルの場合：https://spreadsheets.google.com/feeds/cells/スプレッドシートのID/omnqxt8/private/full

※RSS形式の場合にはURLは以下のようになっています。

1. リストの場合：https://spreadsheets.google.com/feeds/list/スプレッドシートのID/omnqxt8/private/full?alt=rss
2. セルの場合：https://spreadsheets.google.com/feeds/cells/スプレッドシートのID/omnqxt8/private/full?alt=rss

また、以下のURLのほうがわかりやすく出力することができます。こちらの場合のURLはURLの中の特定の数値が○枚目のシートの意味になっているので、上記の謎のworksheetidとやらを考える必要がありません。

※ATOM形式の場合にはURLは以下のようになっています。

1. リストの場合：https://spreadsheets.google.com/feeds/list/スプレッドシートのID/1/public/full
2. セルの場合：https://spreadsheets.google.com/feeds/cells/スプレッドシートのID/1/public/full

※RSS形式の場合にはURLは以下のようになっています。

1. リストの場合：https://spreadsheets.google.com/feeds/list/スプレッドシートのID/1/public/full?alt=rss
2. セルの場合：https://spreadsheets.google.com/feeds/cells/スプレッドシートのID/1/public/full?alt=rss

※JSON形式の場合にはURLは以下のようになっています。

1. リストの場合：https://spreadsheets.google.com/feeds/list/スプレッドシートのID/1/public/full?alt=json-in-script
2. セルの場合：https://spreadsheets.google.com/feeds/cells/スプレッドシートのID/1/public/full?alt=json-in-script

rangeの指定

rangeの指定は、特定のファイルの特定のシートに於ける、特定の範囲だけを指定するものです。指定方法はURLエンコードされている形での指定でなくとも、表示することが可能になっています。A1:C6の範囲を指定して表示する場合には、そのまんま&に続けて、range=A1:C6をくっつければOKです。もちろん、URLエンコードされたこれまでの文字列でも問題ありません。

Queryの指定

コンストラクタの引数に置いて、QUERY文を作って投げることでSQL文のような取り方が可能になっています。但し、ちょっとややこしいので、ノートパッドなどで文をつくってから、何度か貼り付けて抽出できるようにしてみると良いと思います。作り方は、tq=に続けてSQL文っぽいのを入れてあげるわけです。下記にA列とB列のみを表示させ、尚且つB列の値が1000より上の値のレコードだけ抽出してみました。スペース部分はURLエンコードが必要です。QUERY文の作り方は、Query Language Referenceを参照してくだっさい。

https://docs.google.com/spreadsheets/d/1wJzSxUdvSZS7woAuAgEIIdZ_fN87QNCA90Ckr9ijJ-4/gviz/tq?tqx=out:html&tq=SELECT A,B WHERE B > 1000

新スプレッドシートのデータでグラフを作成

新しいスプレッドシートが出てからしばらくの間、Google Visualization APIを使ったスプレッドシートからのグラフの作成等がしばらくの間出来ませんでした。しかし、大分情報が集まってきて、このセクションにある新しいスプレッドシートのデータの取り方（URL）で、XMLガジェットを作成し、グラフを表示してみました。今回このグラフを作成するにあたっては、特に特別なことはしていません。ソースコードもVisualization APIのQueryへ投げるURLが違うだけです。今回は

1. 新しいスプレッドシートで作成したデータを使用
2. シートは２シート目（sheet2）の支店別売上高っぽいデータを参照している
3. Queryに投げているURLは、上記にもあるが、以下のURLである
4. スプレッドシートは公開しています。

参照URL

https://docs.google.com/spreadsheets/d/1wJzSxUdvSZS7woAuAgEIIdZ_fN87QNCA90Ckr9ijJ-4/gviz/tq?gid=1978850084

※ちなみに、このグラフを表示するための旧スプレッドシートの場合のURLは以下の通りです。

https://docs.google.com/spreadsheet/pub?key=0AjN10lCzkUqkdHJqaUxkaUdyOUR3VGQwM3VHWTdWcGc&gid=1

ソースコード

<?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を用いてグラフの表示ができます。