Google」カテゴリーアーカイブ

Google

JSONP形式で新バージョンのGoogleスプレッドシートの内容を取得

< 注意 >
このページの投稿内容に従ってWEBサイトを公開すると、Gmailアドレスが一般公開のJSONPファイルに記載されてしまうため、公開専用のGoogleアカウントを新たに取得することをお勧めします。

< Googleスプレッドシート >
旧バージョンではXMLやJSONなどさまざまな形式で公開する機能が提供されていましたが、新しいバージョンになってから取得できなくなっていました。 …… と思っていたのですが、StackOverflowに興味深い投稿を見つけました。
Unable to publish specific cells in new Google Spreadsheets – StackOverflow
JSONPで取得できる …… えJSONP!?クロスドメインで取得可能なようですww
StackOverflowの回答によるとAPI自体は新しいものではないようです。私は気にしませんが、気に入らない方は使用しない方が良いかもしれません。

< URLフォーマット >
↓こんな感じです。

https://spreadsheets.google.com/feeds/cells/[ スプレッドシートのキー ]/od6/public/values?min-row=[ 開始行番号 ]&max-row=[ 終了行番号 ]&min-col=[ 開始列番号 ]&max-col=[ 終了列番号 ]&alt=json-in-script&callback=[ コールバック関数名 ]

< どうやって使う? >
JSONPフォーマットなので特にパーサなども必要とせずJavascriptから使用可能です。個人的にはもう少し便利に使用したいので、ページ指定の引数をGETリクエストパラメータに変換するスクリプトを作ろうかと思います。

< jQueryプラグイン >
jQueryプラグインを作ってみました。ネーミングは適当ですが “generateGoogleJsonpUri” というものです。
generateGoogleJsonpUri – github
↓こちらがブログフレームワーク的に使用してみたサンプルです。
https://mecrazy.net/sample/generateGoogleJsonpUri/
このプラグインを使用すればシートの内容を取得し2次元配列に変換可能ですので、お好きなHTMLと組み合わせて使用できるかと思います。

< 使い方 – 定義 >
定義は

var test = $.genJsonpGDU("スプレッドシートのキー");

もしくは

var test = $.genJsonpGDU({key:"スプレッドシートのキー"});

のような感じです。

スプレッドシートのキーは以下のキャプチャにもあるようにURLに含まれているので簡単に特定できます。
spreadsheet_sample_001
このキャプチャではURLは “https://docs.google.com/spreadsheets/d/1IG2QEOXehU01b6DoMQi19CvFpm2EZJOUM2y4lqXKduw/edit#gid=0” となっており、スプレッドシートのキーは “1IG2QEOXehU01b6DoMQi19CvFpm2EZJOUM2y4lqXKduw” です。ご自身の使用したいシートのキーを取得してみてください。

< 使い方 – ページング設定 >

test.pager({
  startRow:2,//開始行番号 ( 必須 )
  minCol:2,//開始列番号 ( 必須 )
  maxCol:7,//終了列番号 ( 必須 )
  recsPerPage:5//1ページで読み込む行数 ( 任意 )
});

上の設定と下のテーブルを比較してみてください。1ページ目として読み込むのがピンクの範囲で、2ページ目として読み込まれるのは緑の範囲です。”B2:G2″ が最初の行で、 “B6:G6” が5番目の行といった具合です。.

A1 B1 C1 D1 E1 F1 G1 H1
A2 B2 C2 D2 E2 F2 G2 H2
A3 B3 C3 D3 E3 F3 G3 H3
A4 B4 C4 D4 E4 F4 G4 H4
A5 B5 C5 D5 E5 F5 G5 H5
A6 B6 C6 D6 E6 F6 G6 H6
A7 B7 C7 D7 E7 F7 G7 H7
A8 B8 C8 D8 E8 F8 G8 H8
A9 B9 C9 D9 E9 F9 G9 H9
A10 B10 C10 D10 E10 F10 G10 H10
A11 B11 C11 D11 E11 F11 G11 H11
A12 B12 C12 D12 E12 F12 G12 H12
A13 B13 C13 D13 E13 F13 G13 H13

< 使い方 – ページURLの取得 >
1ページ目のURLはこんな感じで取得します。

var pageUrl = test.page(1);

< 使い方 – Ajax通信 >
“test.ajax” の引数は jQuery.ajax の引数と全く同じですので、 “http://api.jquery.com/jQuery.ajax/” を参照ください。

test.ajax({
  url:test.page(1),
  success:function(json){
    console.log(json);
  }
});

前のページの有無や次のページの有無をコールバックのsuccess関数内で判定したい場合は “test.get()” を使用してください。

test.ajax({
  url:test.page(1),
  success:function(json){
    console.log(json);
    var param = test.get();
    if(param.pager.prev){
      console.log('前のページあり');
    }else{
      console.log('前のページなし');
    }
    if(param.pager.next){
      console.log('次のページあり');
    }else{
      console.log('次のページなし');
    }
  }
});

< 使い方 – JSONから2次元配列への変換 >
GoogleのJSONレイアウトは結構階層が深いのでセルを2次元配列に変換する関数 “test.jsonToArr” を実装してみました。

test.ajax({
  url:test.page(1),
  success:function(json){
    var arr2d = test.jsonToArr(json);
    console.log(arr2d);
  }
});

< 使い方 – 変数キャッシュ >

test.set({cache:true});

ページング機能を使用した際にキャッシング機能を有効にするかどうかの設定です。デフォルトでは20分おきにJSONPファイルをチェックし更新日が変更されていたらキャッシュをクリアします。チェックする間隔を指定したい場合は以下のようにしてください。

test.set({
  cache:true,
  cacheInterval:{
    sec:0,min:50,hour:0
  }
});

Google Driveだけで動的WEBページを生成してみます

< 注意 >
このページの投稿内容に従ってWEBサイトを公開すると、Gmailアドレスが一般公開のXMLファイルに記載されてしまうため、公開専用のGoogleアカウントを新たに取得することをお勧めします。

< 2014/07/08 新バージョン対応 >
JSONP形式で新バージョンのGoogleスプレッドシートの内容を取得を投稿しました。新バージョンのGoogleスプレッドシートを動的WEBページの生成に使用したい方は参照ください。

< 2014/04/26 追記 >
現在Drive NotepadはGoogle Web Storeからは削除されているようです。代わりにAnyfile Notepadやその他のGoogle Drive用テキストエディタをご使用ください。

< 2014/05/12 追記 >
Drive NotepadがGoogle Web Storeに復活しているようです。

< オススメ >
“Drive Notepad” というGoogle Drive用WEBアプリをインストールすることをオススメします。インストールにはChromeが必要です。Google DriveとDrive Notepadを接続した後はChromeは必須ではありませんので、一時的なインストールでもかまいません。Drive Notepad自体は一般的なブラウザで使用可能です。
https://chrome.google.com/webstore/detail/drive-notepad/gpgjomejfimnbmobcocilppikhncegaj?hl=en-GB
drive_notepad_en_us

< スプレッドシートによるコンテンツの更新 >
今のところ “Googleスプレッドシートversion 1” が必須となります。これは現状(2014/04/19時点)の最新バージョンである “version 2” がCSVやXMLの公開に対応していないためです。通常の手順で新しいスプレッドシートを作成するとversion 2になってしまいます。

Googleは新しいスプレッドシートについて “新しい Google スプレッドシートについて” にて説明しています。

個人的にはversion 2でCSVやXMLの公開がサポートされないのではないかと危惧していたので “新しい Google スプレッドシートでサポートされていない機能” もチェックしてみました。このページにはCSVやXMLの公開については書かれていませんでした。でもそうですね、Googleがいつ仕様変更するかなんて誰にもわかりませんよねw
現状についてもろもろ同意できたでしょうか?w

https://g.co/oldsheets へアクセスして version 1 のスプレッドシートを作成しましょう。

こちらが私のサンプルです。

シートは1枚でヘッダがあり、3カラムでデータが2行です。
( imgur の画像表示用サイトを作ろうとしています )

< 公開しましょう >
下の画像に従ってください。
publish_to_the_web_ja

“ウェブに公開” を選んだあと “公開開始” を押したら、”公開データへのリンクを取得” のところを見て下さい。
publish_to_the_web_2_ja
スプレッドシートは “ウェブページ / ページに埋め込むHTML / CSV / TXT / PDF / ATOM / RSS / XLS / ODS” 全ての形式で公開されます。WEBサイトと連動するためにはCSVやATOMやRSSあたりから選ぶのが良いのではないでしょうか。どれか一つを選択すると、セル範囲の選択機能が下に出てくると思います。範囲指定することで受信するファイルサイズを小さくすることが可能です。

CSVを選択した場合、範囲選択の前にシートを選択します。以下がサンプルです。
select_sheet_ja
私のサンプルでは “A1” がヘッダで最大5行を取得する想定なので、範囲指定は “A2:A6” となります。
select_cell_ja
表示されているURL内に “range=A2%3AA6” という記述が確認できるのではないでしょうか。自分のサイトにページング機能をつけたいときなどはこの “range” の値をJavascriptでダイナミックに変化させます。

< ムズい? >
心配無用です。自分用に書いたプログラムをここに公開しておきました。
https://github.com/mecrazy/generateGoogleDriveUri
このスクリプトではCSVとATOMとRSSフォーマットに対応しています。これはただのURL生成プログラムですのでパーサはご自身で用意してください。ATOMとRSSについてはjQueryをパーサとして使用可能です。

“generateGoogleDriveUri.min.js” はこんな感じで使います。

<!DOCTYPE html>
<html>
<head>
<title>サンプルページ</title<
<script src="js/generateGoogleDriveUri.min.js"></script>
<script type="text/javascript">
window.onload = function(){

//オブジェクトの定義
var gduObj = new genGDU('公開したファイルへのリンクをここに記述');

//URI生成
var atomUri = gduObj.genUri(
  'atom', // ATOM形式を選択。
  {
    startRow:2, //2行目から開始
    startCol:1, //1カラム目から開始
    cols:1, //1カラム取得
    rows:5, //5行取得
    page:1 //1ページ目を取得
  }
);

var html = '<a href="' + atomUri + '" target="_blank">' + atomUri + '</a>';
document.getElementById('sample').innerHTML = html;

}
</script>
</head>
<body id="sample"></body>
</html>

< index.html の追加とDrive Notepadの設定 >
ChromeでDrive Notepadを追加した後であればGoogle Driveの作成メニューに “Text Document” が確認できるかと思います。
add_index_ja
初めてDrive Notepadを使用したときは以下のような表示が出ると思います。
permission_01_en_us
“Login and/or grant app permissions…” を押したあと下の質問に同意します。
permission_02_ja

ファイル名を “index.html” に変更して保存したらタブを閉じます。
change_file_name_en_us

“アプリケーションを管理” を開きます。
manage_apps_ja
Drive Notepadのところの “デフォルトで使用” にチェックをします。
use_by_default_ja
これでDrive Notepadは他のGoogle Driveアプリと同じように使えるようになりました。

< 共有設定の変更 >
WEBサイトとして公開したいフォルダを選択したら以下のように共有設定を開いてください。
set_sharing_enabled_01_ja
“ウェブ上で一般公開” にチェックしてください。
set_sharing_enabled_02_ja

< HTML・CSS・Javascriptを準備します >
Drive Notepadを使って準備するか、普段お使いのテキストエディタで作成したものをアップロードしてください。
私の階層ツリーはこんな感じです。
my_public_tree_ja

以下のリンク先のページは私がGoogleスプレッドシートで作成したページです。
https://googledrive.com/host/0B2hsIY7iHEnLS290OHlmRkFmbU0/
ソースはサンプルと思って参考にしていただいてかまいません。

< その他スクリプト >
imgurのURLコンバータを作成しましたので使いたい方はどうぞ。
https://github.com/mecrazy/imgurUrlConverter