Dashing Graph from Google Spreadsheet

Creating a nice dashboard is just the visual part. Much of the work occurs behind the scenes where you have to retreive and store data.

Google Spreadsheet

One possible data storage are google spreadsheets. It’s easy to use and has a good API for task automations, so I use it as a datasource for a disk usage widget for a server.

My raw data looks like this:

One Column contains the date, another one shows the value for our graph.

If you open your data file in your browser, you should copy the id from the address bar e.g. XXXXXXXXXXXXXXXXXXXX_XXXXXXXXXXXX

Install required components

gem install roo
gem install google_drive
gem install google-spreadsheet-ruby

You also have to add them to your Gemfile

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

Get the data

The following piece of code is based on this roo example.

require 'roo'
require 'date'


def fetch_spreadsheet_data()
 google_user = "you@gmail.com"
 google_password = "YOUR-PASSWORD"

  workbook = Roo::Google.new("XXXXXXX YOUR DOCUMENT ID XXXX_XXXXXXXXXXX",user: google_user, password: google_password)
  workbook.default_sheet = workbook.sheets[0]
 
   # Create a hash of the headers so we can access columns by name (assuming row
   # 1 contains the column headings). This will also grab any data in hidden
   # columns.
  headers = Hash.new
  workbook.row(1).each_with_index {|header,i|
 headers[header] = i
 }

   graph_data = []
 # Iterate over the rows using the `first_row` and `last_row` methods. Skip
  # the header row in the range.
  ((workbook.first_row + 1)..workbook.last_row).each do |row|
     value = workbook.row(row)[headers['Value']]
   date = workbook.row(row)[headers['Date']]     
    timestamp = DateTime.parse(date).to_time.to_i
   graph_data << { x: timestamp, y: value }
  end
  send_event('YOUR GRAPH WIDGET NAME', points: graph_data.last(50))
end

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

• The script converts the date to a timestamp and
• The data is limited to the latest 50 entries, to keep the graph clearly represented
• At the moment I still need username and password and configure the gmail account to allow “less secure apps”, so you shouldn’t use this with your main google account

More about Dashing

Statuscake Widget for Dashing