GoogleスプレッドシートのデータをJSONで取得してみた
- 2014/11/28
- atsuko.a
こんにちは、フロントエンドエンジニアのあつこです(ΦωΦ)
最近一段と寒くなってきましたが皆さんいかがお過しでしょうか。
私は寒さに弱いので、早速コートを出してきました。冬本番になったら何を着たらいいのだろう……
先日GoogleスプレッドシートのデータをJSONで扱う機会があったのでここで、ちょっとまとめておきたいと思います。
もくじ
- 事の発端
- Google Sheets APIとは
- 4ステップでデータを取得する
- JSON形式のデータをjQueryで整形する
- まとめ
事の発端
社内で運用しているチェックシートをもっと手軽にできないか、という要望があったのでチェックフォームを作りました。
チェック項目のデータはその他のものにも転用したい為、Googleスプレッドシートで管理しています。
イメージとしてはこんな感じ。
Googleスプレッドシートを選んだ具体的な理由はこちら。
- wikiにWYSIWYGから埋め込める
- 閲覧権限がついている
- 項目の追加・削除が非エンジニアにも簡単
- JSで扱える形式でデータを引っこ抜ける
今回はGoogleが提供しているAPIを利用して、スプレッドシートのデータを引っこ抜いてもごもごしてみます。
Google Sheets APIとは
Googleがスプレッドシートシートの操作の為に提供している、Google Sheets APIというAPIがあります。
コレを利用して、スプレッドシートの取得・編集・削除などが出来るアプリケーションを作ったりできますヽ(=´▽`=)ノ
JAVAと.NETのライブラリーが用意されているようですが、そこまで大掛かりなことをするわけではないのでjQueryを使ってデータを引っこ抜いてきます。
4ステップでデータを取得する
実際にAPIを使ってデータを取得してみましょう。
1.スプレッドシートを用意する
ダミーでこんなシートを用意してみました
※権限の関係でスクショを載せています
ねこの名前と原産地のセットを、地域別に分類してあります。
2.スプレッドシートをWEBに公開する
APIを使う際には、スプレッドシート自体がWEBに公開されていないといけません。
スプレッドシートの公開方法はこんな感じ。
- ツールバーの「ファイル」を選択
- メニュー内の「ウェブに公開」を選択
- 範囲などを指定して公開する
これでOK!簡単!
ちなみに、iframeで埋め込む際のタグもここから取得できます(ΦωΦ)
3.スプレッドシートのID・ワークシートのIDを取得する
スプレッドシート自体のIDは、URLの[key]の部分になります。
1 |
https://docs.google.com/spreadsheets/d/[key]/edit#gid=0 |
1枚目のワークシートのIDは「od6」に設定されているようです。
2枚目以降のシートや正確に知りたい場合は下のアドレスから参照できます。
1 |
https://spreadsheets.google.com/feeds/worksheets/[key]/public/basic |
xml形式で、このスプレッドシートに関する情報が取得できます。
XML整形サービスなどを使用すると上手いこと整形してくれます。
データを取得したいシートの部分にある、[worksheetId]部分がワークシートのIDになります
1 |
https://spreadsheets.google.com/feeds/list/[key]/[worksheetId]/public/basic |
4.実際にデータを取得する
3で取得した[key]と[worksheetId]を使って、データを取得します。
1 |
https://spreadsheets.google.com/feeds/cells/[key]/[worksheetId]/public/values |
こうするとxml形式の、データが取れます。
JSではちょっと扱いにくいので、末尾に「?alt=json」を付けてみます。
1 |
https://spreadsheets.google.com/feeds/cells/[key]/[worksheetId]/public/values?alt=json |
これでJSONデータが取得出来ました٩(๑❛ᴗ❛๑)۶
JSON形式のデータをjQueryで整形する
スプレッドシートの内容は更新されるので、フォームにアクセスする度にデータを取得しHTMLに描画するようにします。
jQueryでJSONデータを扱うには、$.ajax()やら$.getJSON()やらを使います。今回は$.ajax()で。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$.ajax({ type: 'GET', url: 'https://spreadsheets.google.com/feeds/cells/[key]/[worksheetId]/public/values?alt=json', dataType: 'jsonp', cache: false, success: function(data){ // 通信が成功した時 var sheetsEntry = data.feed.entry; // 実データ部分を取得 categorized = catedorizeData(sheetsEntry); // データを整形して配列で返す renderForm(categorized); // レンダリング用の関数を呼ぶ }, error: function(){ // 通信が失敗した時 console.log('error'); } }); |
エラー処理は省略
APIで取得したJSONデータには、ワークシート自体の情報(エンコード設定や、シートの製作者等)が含まれているので、実データ部分のみを抜き出して変数に入れておきます(7行目)
data.feed.entryにはセル毎のデータが入っているのですが、どんな順番になっているかというとこんな感じ。
データが入っているセルを左→右、上→下と進んでいきます。
また、セルの結合情報などは保持されず、一番左上のセルにデータが入っているとみなされるようです。
全てのセルが同列に扱われるので、例のように見出しと実データとセルの使い方が違う場合はちょっと注意が必要です。
セル毎にこれらのデータを持っているのですが、引っこ抜いたり整形するのにはgs$cell辺りのデータがあればよさそう。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
{ "category": [ { "scheme": "http://schemas.google.com/spreadsheets/2006", "term": "http://schemas.google.com/spreadsheets/2006#cell" } ], "content": { "$t": "ジャパニーズボブテイル", "type": "text" }, "gs$cell": { // セルの位置 "$t": "ジャパニーズボブテイル", // 実際のデータ "col": "1", // 列 "row": "2" // 行 }, "id": { "$t": "https://spreadsheets.google.com/feeds/cells/[key]/[worksheetId]/public/values/R2C1" }, "link": [ { "href": "https://spreadsheets.google.com/feeds/cells/[key]/[worksheetId]/public/values/R2C1", "rel": "self", "type": "application/atom+xml" } ], "title": { "$t": "A2", "type": "text" }, "updated": { "$t": "2014-11-27T02:16:32.949Z" } } |
今回はこんな感じの処理を行って、下のようなフォームを生成しました。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
function catedorizeData(sheetsEntry){ // データを整形して配列で返す var categorized = []; for(var i = 0; i < sheetsEntry.length; i++) { var dataCol = sheetsEntry[i].gs$cell.col; var dataRow = sheetsEntry[i].gs$cell.row; if(dataCol == 1 && dataRow != sheetsEntry[i+1].gs$cell.row){ categorized[categorized.length] = []; } categorized[categorized.length-1].push(sheetsEntry[i]); } return categorized; } function renderForm(categorized){ // レンダリング用の関数 var target = $('.formSubmit'); categorized.forEach(function(areaCats){ target.before('<h2>'+areaCats[0].gs$cell.$t+'</h2>'); target.before('<dl>'); for(var i = 1; i < areaCats.length; i+=2){ target.before('<dt><label><input name="cats" type="checkbox" />'+areaCats[i].gs$cell.$t+'</label></dt>'); target.before('<dd>原産地:'+areaCats[i+1].gs$cell.$t+'</dd>'); } target.before('</dl>'); }); } |
forとforEachが混在しているのは、気分で書き換えただけなので特に意味は無いです。
突貫なので、シートの形式に依存しまくりですが何とか動きます。
実際に使用する際には
- シートの形式をしっかり決める
- 例外処理をする
- 閲覧権限の無いアカウントからのアクセスの対応
などをきちんとした方がよさそうです。
まとめ
ざざっとスプレッドシートからデータを引っこ抜くことができました!
ちょっと触ってみて思ったのは、なんて整形しにくいデータ構造なんだ……と:(;゙゚'ω゚'):
スプレッドシートという特性上仕方ないのですが、ちょっと触りにくかったです。
ですが、メリットとして
- 非エンジニアにもデータの追加削除が簡単
- iframeで埋め込める
などが挙げられるので、データ形式を上手いことしてあげられればちょっとしたデータベース代わりも使えそうです(`・ω・´)
ちなみに、今回はスプレッドシートを指定してデータの取得を行いましたが、
Googleのアカウント情報を元に紐付いているスプレッドシートの一覧や、操作なんかもできたりします。
すごいぞ、Google Sheets API!
それでは今日はこのへんで!