Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Json format for 2019-nCoV from Google Spread Sheets
# 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')}")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.