GoogleスプレッドシートのAPIを使ってみた

ruby

GoogleスプレッドシートをWebスクレイピングしてみた - NAT’s Programming Champloo
以前、上記のエントリで、GoogleスプレッドシートをWebスクレイピングしたデータを表示するプログラムを作った。しかしWebスクレイピングには、HTMLの内容が変わるなど、データ取得元サイトの仕様が変わるとデータが取れなくなる弱点がある。つい先日も、GoogleスプレッドシートのWebサイトがhttpではなく、httpsで表示するように仕様が変わり、データが取れなくなっていた。この問題は、とりあえずデータ取得URLをhttpからhttpsに変える事で対処できた。
今後も仕様が変わるたびに問題が発生するのが嫌なので、GoogleスプレッドシートAPIを使って、プログラムを作り直すことにした。以下、そのためにやった事の覚え書き。

GoogleスプレッドシートAPIを使う上での考慮点

WebスクレイピングからGoogleスプレッドシートAPIに変える上での一番の問題点は、データ取得の所要時間。データ量は5カラムくらいの行が700行程度。Webスクレイピングは1秒前後でデータを取れたのだが、APIを使うと5秒以上かかった。
画面表示のたびにデータを取ると、表示のたびに5秒以上待たされることになるので、バックグラウンドで定期的にAPIでデータを取得してデータベースに格納し、表示する時はデータベースのデータを使うことにした。

プログラム構成の変更

rubyとsinatraで作っていたプログラムを、rubyとruby on railsとsqlite3の構成に変更した。
バックグラウンドで定期的にAPIを取得するプログラムは、rubyとActiveRecordで書いた。

ActiceRecordを単体で使う

バックグラウンドで定期的にAPIを取得するプログラムは、Ruby on Railsアプリではなく、ActiveRecordを単体で使った。そのためのコードは、下記を参考にした。
http://blog.livedoor.jp/takaaki_bb/archives/50602246.html
SQLite3を使うときの初期設定のコードは、下記のようになる。

require 'rubygems'
require 'active_record'

RAILS_ENV = "development"

ActiveRecord::Base.establish_connection(
  :adapter  => "sqlite3",
  :database => "db/#{RAILS_ENV}.sqlite3",
  :timeout  => 5000
)

GoogleスプレッドシートAPIを使うrubyコード

以前、APIを調べるために書いたrubyコードを書き直して利用。あまり汎用的なコードになってないけど、再利用できるようにクラスを作成。

require 'net/http'
require 'rexml/document'
require 'time'

Net::HTTP.version_1_2

class FeedLoader
  def load(path)
    host = 'spreadsheets.google.com'
    Net::HTTP.start(host) do |http|
      headers = { 'GData-Version' => '3.0'}
      # puts "get #{path}"
      res = http.get(path, headers)
      # puts "res #{res.code},#{res.message}"
      if res.code != '200'
        raise "get feed(http://#{host}#{path}) faied: #{res.message}"
      end
      res.body
    end
  end
end

class WorkSheet < FeedLoader
  attr_reader :title, :listfeed

  def initialize(key, worksheetId)
    @key = key
    @worksheetId = worksheetId
  end

  def make_feed_uri(feed_type)
    "/feeds/#{feed_type}/#{@key}/#{@worksheetId}/public/basic"
  end

  def lists
    lists_feed = load_lists
    l = []
    doc = REXML::Document.new lists_feed
    doc.each_element('//entry') do |entry|
      title = entry.elements['title'].text
      line = {'title' => title}
      content = entry.elements['content'].text
      content.scan(/([^:]*): ([^,]*),? ?/) do |pair|
        line.store(pair[0], pair[1])
      end
      l << line
    end
    @updated = Time.parse(REXML::XPath.first(doc, "/feed/updated").text)
    return l
  end

  def updated
    unless @updated
      lists
    end
    @updated
  end

  def load_lists
    load(make_feed_uri('list'))
  end

  def cells(range = nil)
    uri = make_feed_uri('cells')
    uri = uri + "?range=#{range}" if range
    cells_feed = load(uri)
    rows = []
    cells = []
    pre_row = 0

    doc = REXML::Document.new cells_feed
    doc.each_element('//entry') do |entry|
      title = entry.elements['title'].text
      /([A-Z])([0-9]+)/ =~ title
      cell = $1
      row = $2
      if pre_row != row
        rows << cells
        cells = []
      end
      cells << entry.elements['content'].text
      pre_row = row
    end
    rows << cells unless cells.empty?
    return rows    
  end
end

class GoogleSpreadSheets < FeedLoader
  
  def initialize(key)
    @key = key
  end

  def worksheets()
    worksheets_feed = load "/feeds/worksheets/#{@key}/public/basic"
    ws = []
    doc = REXML::Document.new worksheets_feed
    doc.each_element('//entry') do |entry|
      title = entry.elements['title'].text
      listfeed = entry.elements['content'].attributes['src']
      ws << WorkSheet.new(title, listfeed)
    end
    return ws
  end
end

stations = WorkSheet.new('pS_Iu-LTUb202uc2p8lQoOw', 'od7')
stations.lists.each do |line|
  if line['駅名']
    puts "#{line['title']}:#{line['駅名かな']}(#{line['駅名']})@#{line['都道府県名']}"
  else
    puts "●#{line['駅名かな']}"
  end
end
puts "更新日時：#{stations.updated}"