Visualize your Outlook meetings using Ruby and NVD3

Visualize your Outlook meetings using Ruby and NVD3

Software Engineering 2044 views

The other day my co-worker lamented that he has so many meetings he couldn't get any actual work done. His frustration lead me to think of how I could visualize how much time meetings take up during the workday.

We use Microsoft Outlook, and I knew I could programmatically access the calendar information. This could be a small, fun project to visualize.

Goals & Criteria

After ruminating a bit, I came up with these goals:

Requirements

This ruby script only runs on Windows where Outlook is installed. It has been tested using Ruby 1.9.3-p545 on Windows 7 x64.

Retrieving source

From the Windows command-line, clone the project from GitLab:

git clone https://gitlab.com/ejstembler/outlook-meetings-visualizer.git

Installing dependencies

Install bundler 1.6.3 if you don't already have it installed:

gem install bundler

Run bundler before attempting to run the script:

bundle install

Running the script

ruby outlook_meetings_visualizer.rb

The Output

After it has successfully completed, it will automatically open the index.html file in the output directory in the default web browser.

Here's what it looks like for my calendar:

screenshot

How it Works

The script extracts Outlook calendar items using Microsoft OLE Automation via the win32ole module. The data is saved in a temporary SQLite3 database at db/calendar.sqlite3.

Appointments are selected, and summed, from the database with the following criteria:

The data is normalized, and converted to JSON for use in a NVD3 MultiBarChart. An index.html file is generated in output/index.html which renders the NVD3 MultiBarChart.

The main run method performs the following steps:

  1. open_database
  2. create_calendar_table
  3. insert_appointments
  4. select_data
  5. close_database
  6. save_index_html
  7. open_index_html

Here's what outlook_meetings_visualizer.rb looks like:

require 'sqlite3'
require 'win32ole'
require 'json'
require 'launchy'
require 'fileutils'

class OutlookMeetingsVisualizer

  DB_DATE     = '%Y-%m-%d'
  DB_DATETIME = DB_DATE + ' %H:%M:%S.%L'
  INSERT_SQL  = 'INSERT INTO appointments (subject, location, start_datetime, duration, end_datetime, body) VALUES (?, ?, ?, ?, ?, ?)'

  def open_database
    db_path = File.join(File.dirname(__FILE__), 'db/calendar.sqlite3')
    FileUtils.rm(db_path) if File.exist?(db_path)
    @db = SQLite3::Database.new(db_path)
  end

  def close_database
    @db.close
  end

  def create_calendar_table
    @db.execute <<-SQL
CREATE TABLE appointments (
    subject VARCHAR(255),
    location VARCHAR(255),
    start_datetime VARCHAR(23),
    duration INT,
    end_datetime VARCHAR(23),
    body BLOB
);
    SQL
  end

  def insert_appointments
    # http://rubyonwindows.blogspot.com/2007/07/automating-outlook-with-ruby-calendar.html
    outlook  = WIN32OLE.new('Outlook.Application')
    mapi     = outlook.GetNameSpace('MAPI')
    calendar = mapi.GetDefaultFolder(9)

    calendar.Items.each do |appointment|
      subject        = appointment.Subject
      location       = appointment.Location
      start_datetime = appointment.Start.strftime(DB_DATETIME)
      duration       = appointment.Duration
      end_datetime   = appointment.End.strftime(DB_DATETIME)
      body           = appointment.Body
      @db.execute(INSERT_SQL, [subject, location, start_datetime, duration, end_datetime, body])
    end
  end

  def select_data
    # http://www.sqlite.org/lang_datefunc.html
    sql = <<-SQL
SELECT
    start_date,
    SUM(duration)
FROM
    (
        SELECT
            duration,
            strftime('#{DB_DATE}', start_datetime) start_date
        FROM
            appointments
        WHERE
            duration < 1440                                                               -- Only if less than 1 day
        AND subject <> 'Lunch'                                                            -- Ignore lunch block
        AND strftime('%w', start_datetime) in ('1', '2', '3' ,'4', '5')                   -- Only workdays (Mon-Fri)
        AND strftime('%w', end_datetime) in ('1', '2', '3' ,'4', '5')                     -- Only workdays (Mon-Fri)
        AND strftime('#{DB_DATE}', start_datetime) = strftime('#{DB_DATE}', end_datetime) -- start/end on same day
    )
GROUP BY
    start_date
    SQL
    @data = @db.execute(sql)
  end

  def save_index_html
    File.open('output/index.html', 'wt') {|f| f.write(index_html)}
  end

  def open_index_html
    Launchy.open 'output/index.html'
  end

  def run
    open_database
    create_calendar_table
    insert_appointments
    select_data
    close_database
    save_index_html
    open_index_html
  end

  def data_for_nvd3
    meetings = { key: 'Meetings', values: @data.map {|row| { x: row[0], y: row[1] }} }
    workday  = { key: 'Workday', values: @data.map {|row| { x: row[0], y: 480 - row[1] }} }
    "#{meetings.to_json},\n#{workday.to_json}"
  end

  def nvd3_chart_javascript
    <<-JAVASCRIPT
    var data = [
#{data_for_nvd3}
    ];

nv.addGraph(function() {
    var chart = nv.models.multiBarChart()
      .transitionDuration(350)
      .reduceXTicks(true)   // If 'false', every single x-axis tick label will be rendered.
      .rotateLabels(0)      // Angle to rotate x-axis labels.
      .showControls(false)  // Allow user to switch between 'Grouped' and 'Stacked' mode.
      .groupSpacing(0.1)    // Distance between each group of bars.
      .stacked(true);

    chart.yAxis
        .tickFormat(d3.format(',d'));

    d3.select('#chart svg')
        .datum(data)
        .call(chart);

    nv.utils.windowResize(chart.update);

    return chart;
});
    JAVASCRIPT
  end

  def index_html
    <<-HTML



    Meetings Visualization
    
    
    
    




  





    HTML
  end

end

if __FILE__ == $PROGRAM_NAME
  OutlookMeetingsVisualizer.new.run
end

Full Source Code

ejstembler/outlook-meetings-visualizer

More Ruby Articles