CREATOR BLOG

GoogleスプレッドシートのデータをJSONで取得してみた

2014/11/28
atsuko.a
  • このエントリーをはてなブックマークに追加

こんにちは、フロントエンドエンジニアのあつこです(ΦωΦ)
最近一段と寒くなってきましたが皆さんいかがお過しでしょうか。
私は寒さに弱いので、早速コートを出してきました。冬本番になったら何を着たらいいのだろう……

先日GoogleスプレッドシートのデータをJSONで扱う機会があったのでここで、ちょっとまとめておきたいと思います。

もくじ

  • 事の発端
  • Google Sheets APIとは
  • 4ステップでデータを取得する
  • JSON形式のデータをjQueryで整形する
  • まとめ

事の発端

社内で運用しているチェックシートをもっと手軽にできないか、という要望があったのでチェックフォームを作りました。
チェック項目のデータはその他のものにも転用したい為、Googleスプレッドシートで管理しています。
イメージとしてはこんな感じ。
1128_1
Googleスプレッドシートを選んだ具体的な理由はこちら。

  • wikiにWYSIWYGから埋め込める
  • 閲覧権限がついている
  • 項目の追加・削除が非エンジニアにも簡単
  • JSで扱える形式でデータを引っこ抜ける

今回はGoogleが提供しているAPIを利用して、スプレッドシートのデータを引っこ抜いてもごもごしてみます。

Google Sheets APIとは

Googleがスプレッドシートシートの操作の為に提供している、Google Sheets APIというAPIがあります。
コレを利用して、スプレッドシートの取得・編集・削除などが出来るアプリケーションを作ったりできますヽ(=´▽`=)ノ
JAVAと.NETのライブラリーが用意されているようですが、そこまで大掛かりなことをするわけではないのでjQueryを使ってデータを引っこ抜いてきます。

4ステップでデータを取得する

実際にAPIを使ってデータを取得してみましょう。

1.スプレッドシートを用意する

ダミーでこんなシートを用意してみました
1128_2
※権限の関係でスクショを載せています
ねこの名前と原産地のセットを、地域別に分類してあります。

2.スプレッドシートをWEBに公開する

APIを使う際には、スプレッドシート自体がWEBに公開されていないといけません。
スプレッドシートの公開方法はこんな感じ。

  1. ツールバーの「ファイル」を選択
  2. メニュー内の「ウェブに公開」を選択
  3. 範囲などを指定して公開する

1128_3
これでOK!簡単!
ちなみに、iframeで埋め込む際のタグもここから取得できます(ΦωΦ)

3.スプレッドシートのID・ワークシートのIDを取得する

スプレッドシート自体のIDは、URLの[key]の部分になります。

1枚目のワークシートのIDは「od6」に設定されているようです。
2枚目以降のシートや正確に知りたい場合は下のアドレスから参照できます。

xml形式で、このスプレッドシートに関する情報が取得できます。
XML整形サービスなどを使用すると上手いこと整形してくれます。

データを取得したいシートの部分にある、[worksheetId]部分がワークシートのIDになります

4.実際にデータを取得する

3で取得した[key]と[worksheetId]を使って、データを取得します。

こうするとxml形式の、データが取れます。

JSではちょっと扱いにくいので、末尾に「?alt=json」を付けてみます。

これでJSONデータが取得出来ました٩(๑❛ᴗ❛๑)۶

JSON形式のデータをjQueryで整形する

スプレッドシートの内容は更新されるので、フォームにアクセスする度にデータを取得しHTMLに描画するようにします。
jQueryでJSONデータを扱うには、$.ajax()やら$.getJSON()やらを使います。今回は$.ajax()で。

エラー処理は省略
APIで取得したJSONデータには、ワークシート自体の情報(エンコード設定や、シートの製作者等)が含まれているので、実データ部分のみを抜き出して変数に入れておきます(7行目)
data.feed.entryにはセル毎のデータが入っているのですが、どんな順番になっているかというとこんな感じ。
1128_4
データが入っているセルを左→右、上→下と進んでいきます。
また、セルの結合情報などは保持されず、一番左上のセルにデータが入っているとみなされるようです。

全てのセルが同列に扱われるので、例のように見出しと実データとセルの使い方が違う場合はちょっと注意が必要です。
セル毎にこれらのデータを持っているのですが、引っこ抜いたり整形するのにはgs$cell辺りのデータがあればよさそう。

今回はこんな感じの処理を行って、下のようなフォームを生成しました。

forとforEachが混在しているのは、気分で書き換えただけなので特に意味は無いです。
1128_5
突貫なので、シートの形式に依存しまくりですが何とか動きます。
実際に使用する際には

  • シートの形式をしっかり決める
  • 例外処理をする
  • 閲覧権限の無いアカウントからのアクセスの対応

などをきちんとした方がよさそうです。

まとめ

ざざっとスプレッドシートからデータを引っこ抜くことができました!
ちょっと触ってみて思ったのは、なんて整形しにくいデータ構造なんだ……と:(;゙゚'ω゚'):
スプレッドシートという特性上仕方ないのですが、ちょっと触りにくかったです。

ですが、メリットとして

  • 非エンジニアにもデータの追加削除が簡単
  • iframeで埋め込める

などが挙げられるので、データ形式を上手いことしてあげられればちょっとしたデータベース代わりも使えそうです(`・ω・´)

ちなみに、今回はスプレッドシートを指定してデータの取得を行いましたが、
Googleのアカウント情報を元に紐付いているスプレッドシートの一覧や、操作なんかもできたりします。
すごいぞ、Google Sheets API!

それでは今日はこのへんで!

この記事を書いた人

atsuko.aフロントエンドエンジニア

猫派の甘党、胃が弱い系WEB屋。 コーディングしたり、たまにデザインしたりします。

Recent Entry

CSSの単位まとめ pxからvm/vh/vmin/vmaxまで

レスポンシブHTMLメールを作ってみての感想

CSSのリファクタリングでした5つのこと

大人が読んでも頭を使う絵本の紹介

サイト内にうまくInstagramを取り入れてるサイト11選

Category

Archive