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:

google-docs-disc-usage

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

4 Responses to “Dashing Graph from Google Spreadsheet”

  1. Muhammad

    This is Great! Thank you!

    I get the warning below. Please could you provide and update to use oauth instead of login
    WARNING: GoogleDrive.login is deprecated and will be removed in the next version. Use GoogleDrive.login_with_oauth instead.

    Thanks

    Reply
    • hannes

      Hi Muhammad,
      As soon as I need to update my own dashboard, I’ll publish the new code here, but at the moment I don’t have time to change it.
      regards, Hannes

      Reply
  2. Scott

    Hi, I find this post very helpful, however I am trying to modify it to read the excel data into a table, So I can display the data table on a dashboard. But I am struggling to do so. Do you have any suggestions on how I could possibly do this.
    Any help would be greatly appreciated.

    Reply

Leave a Reply