google-spreadsheet-checker.rb

#!/usr/bin/ruby
#
# google-spreadsheet-checker.rb
#
# how to use
#
#   1. Add Google Account in ~/.netrc
#
#     machine spreadsheet-checker
#     login ******@gmail.com
#     password **********
#
#   2. edit @key, @gid and @gtaik_to
#
#   3. run this script
#
require 'rubygems'
require 'google_spreadsheet'
require 'net/netrc'
require 'yaml/store'
require 'xmpp4r'
require 'pp'

#
# https://spreadsheets.google.com/ccc?key=@key#gid=@gid
#
@key = "?????????????????"
@gid = 0
@gtalk_to = ["yoggy0@gmail.com"]

@db = YAML::Store.new('.google-spreadsheet-checker-#{key}')

def get_rows
  n = 0
  @db.transaction do
    n = @db['rows'] if @db.root? 'rows'
  end
  n
end

def set_rows(n)
  @db.transaction do
    @db['rows'] = n
  end
end

def gtail_sendmsg(to, msg)
  netrc = Net::Netrc.locate "spreadsheet-checker"
  begin
    gtalk = Jabber::Client.new(Jabber::JID.new("#{netrc.login}/bot"))
    gtalk.connect('talk.google.com', 5222)
    gtalk.auth(netrc.password)
    gtalk.send(Jabber::Presence.new.set_show(:chat))

    to.each {|u|
      m = Jabber::Message.new(u)
      m.body = msg
      gtalk.send(m)
    }
  rescue Exception => e
    pp e
  end

end

loop do
  begin
    # open google spreadsheet
    netrc = Net::Netrc.locate "spreadsheet-checker"
    session = GoogleSpreadsheet.login(netrc.login, netrc.password)
    ss = session.spreadsheet_by_key(@key)
    ws = ss.worksheets[@gid]
    ws.reload

    r = ws.num_rows

    if r != get_rows
      msg = ""
      msg += "please check Google Spreadsheets\n"
      msg += "https://spreadsheets.google.com/ccc?key=#{@key}#gid=#{@gid}\n"
      msg += "\n"
      ws.num_cols.times {|j|
        msg += ws[1, j+1] + " : " + ws[r, j+1] + "\n"
      }
      puts msg
      gtail_sendmsg @gtalk_to, msg

      set_rows r
    end
  rescue Exception => e
    pp e
  end

  sleep 10

  GC.start
end