Pulling Data from Google spreadsheet

Question

I am having difficulty pulling data from Google spreadsheet

I have added following gem files

gem 'roo'
gem 'google_drive'
gem 'google-spreadsheet-ruby'

My jobs file is require 'roo' require 'rubygems'

def fetch_spreadsheet_data()
    google_user = "MY EMAIL ADDRESS"
    google_password = "MY PASSWORD"

    workbook = Roo::Google.new("https://docs.google.com/spreadsheet/ccc?key=1hdwnrDsuJId1FLE0yWICYP1HGqYNu2NHH2IcoPyAzOQ#gid=0",user: google_user, password: google_password)
    send_event('catchup_data', {current: s.cell('B',2) })
    send_event('Bounced_back', {current: s.cell('B',3) )
end


SCHEDULER.every '5m' do
    fetch_spreadsheet_data()
end

My dashboard.erb file has following html

  <li data-row="2" data-col="3" data-sizex="1" data-sizey="1">
    <div data-id="bounce_back" data-view="Number" data-title="Triage - Work in Progress" style="background-color:#5AC352;"></div>
  </li>

 <li data-row="2" data-col="4" data-sizex="1" data-sizey="1">
    <div data-id="catchup_data" data-view="Number" data-title="Squad catchup sessions last month" style="background-color:#DBA901;"></div>
  </li>

Not sure what am I missing that the data is not coming through. Can anyone please help me?

score 0 · Answer 1 · answered Jul 01 '15 at 15:44

There are a few things wrong that I can see:

You're sending 'Bounced_back' but binding to the id of 'bounce_back'
You are trying to get the cell data from 's' but 's' is undefined

Looking at the Roo docs, I believe you have copied 's' from there. Just above that, they use sheet instead so I believe you have to grab the sheet from the workbook before using it.

I googled a bit and found this: http://etzelstorfer.com/en/dashing-graph-from-google-spreadsheet/

In summary, this should work for you:

def fetch_spreadsheet_data()
    google_user = "MY EMAIL ADDRESS"
    google_password = "MY PASSWORD"

    workbook = Roo::Google.new("https://docs.google.com/spreadsheet/ccc?key=1hdwnrDsuJId1FLE0yWICYP1HGqYNu2NHH2IcoPyAzOQ#gid=0",user: google_user, password: google_password)
    s = workbook.sheets[0] # assuming first sheet in workbook
    send_event('catchup_data', {current: s.cell('B',2) })
    send_event('bounce_back', {current: s.cell('B',3) )
end


SCHEDULER.every '5m' do
    fetch_spreadsheet_data()
end

Pulling Data from Google spreadsheet

1 Answers1