Json format for 2019-nCoV from Google Spread Sheets

2019_ncov_json.rb

# 2019_ncov_json.rb create json format for 2019-nCoV from Google Spread Sheets
# Thanks
# https://docs.google.com/spreadsheets/d/1wQVypefm946ch4XDp37uZ-wartW4V7ILdg-qYiDXUHM/htmlview?usp=sharing&sle=true
# https://gisanddata.maps.arcgis.com/apps/opsdashboard/index.html#/bda7594740fd40299423467b48e9ecf6

require 'open-uri'
require 'json'
require 'pp'

module GoogleSpreadSheet
  def google_spread_sheet_json(gss_uid)
    gss_url = 'https://spreadsheets.google.com/tq?key='
    gss_opt = '&output=html&usp=sharing'
    gss_url = [gss_url, gss_uid, gss_opt].join

    result = URI.open(gss_url).read
    result = result.gsub("/*O_o*/\ngoogle.visualization.Query.setResponse(", '')
    result = result.gsub(');', '')
    JSON.parse(result, symbolize_names: true)
  end

  def to_symbles
    map { |c| c.gsub(%r{\/.*$}, '').gsub(/\s/, '_').downcase.to_sym }
  end

  def to_time
    year, mon, day, hour, min, sec = split(',').map(&:to_i)
    t = year, mon + 1, day, hour, min, sec
    Time.utc(*t)
  end
end

include GoogleSpreadSheet

def total(ncov_hash)
  c = ncov_hash.inject(0) { |rslt, e| rslt + e[:confirmed] }
  d = ncov_hash.inject(0) { |rslt, e| rslt + e[:deaths] }
  r = ncov_hash.inject(0) { |rslt, e| rslt + e[:recovered] }
  l = ncov_hash.map { |e| e[:last_update] }.max
  { planet: 'Earth', last_update: l, confirmed: c, deaths: d, recovered: r }
end

gss_hash = google_spread_sheet_json('1wQVypefm946ch4XDp37uZ-wartW4V7ILdg-qYiDXUHM')

cols = gss_hash[:table][:cols].map { |e| e[:label] }
rows = gss_hash[:table][:rows].map { |e| e[:c] }

rows = rows.map do |row|
  row.map { |e| e ? e[:v] : nil }
end

cols_sym = cols.to_symbles
ncov_hash = rows.map do |row|
  row_arry = row.map.with_index do |e, i|
    e = e.to_i if e.is_a?(Float)
    if e.is_a?(String) && (m = e.match(/^Date\((.*?)\)/))
      e = m[1].to_time
    end
    { cols_sym[i] => e }
  end
  row_arry.reject { |e| e.value?(nil) } .inject { |r, e| r.merge(e) }
end

ncov_hash = ncov_hash.push(total(ncov_hash))

puts('Earth')
earth = ncov_hash.map { |e| e if e[:planet] == 'Earth' }.compact.first
puts("confirmed: #{earth[:confirmed]}")
puts("deaths: #{earth[:deaths]}")
puts("recovered: #{earth[:recovered]}")
puts("lastst update: #{earth[:last_update].localtime('+09:00')}")

puts('Japan')
japan = ncov_hash.map { |e| e if e[:country] == 'Japan' }.compact.first
puts("confirmed: #{japan[:confirmed]}")
puts("deaths: #{japan[:deaths]}")
puts("recovered: #{japan[:recovered]}")
puts("last update: #{japan[:last_update].localtime('+09:00')}")