Generate scattered plot diagram with Cycle Time (Y axis) and Story Point (X axis) from Zenhub Cycle time report

transform_zenhub_csv.rb

# Generate scattered plot diagram with Cycle Time (Y axis) and Story Point (X axis) from Zenhub Cycle time report

require 'bundler/inline'

gemfile(true) do # true => gems that aren't already installed on the user's system should be installed.
  source 'https://rubygems.org'
  
  gem 'csv'
  gem 'activesupport'
  gem 'google_drive'

  gem 'amazing_print'
  gem 'pry-byebug', require: false
end

# SCRIPT PARAMETERS:

# path to Zenhub Cycle time report CSV export
file = ARGV[0]

# ignore items above that threshold in days
outlier_threshold = ENV['OUTLIER']&.to_i || 50

# path to transformed CSV output
output_file = 'google_spreadsheet_cycle_time_per_sp.csv'

# Google spreadsheet Google document key
document_key = ARGV[1]

# COMPOSABLE PROCS:

ParseCsv = proc { |file, _options| CSV.read(file, liberal_parsing: true, headers: true) }

# CSV headers :
# "Repo","Issue Title","Estimate","Started Sprint en cours","Completed Closed","Completion Time (Days)"
Extract_GithubId_Estimate_CycleTime = proc do |csv_table| 
  csv_table.inject([]) do |table, row|
    matchdata = 
      row['Issue Title'].
      tr('"', ''). # to make regexp parsing easier when title contains double quotes
      match(/^#(\d+) - (.*)/)
    
    table << { github_id: matchdata[1], title: matchdata[2], estimate: row['Estimate'], cycle_time: row['Completion Time (Days)']}
  end
end

require 'active_support/core_ext/object/blank'
IgnoreTicketsWithoutEstimate = proc do |table|
  table.reject do |row|
    estimate = row[:estimate]
     estimate.blank? || estimate.to_i == 0
  end
end

IgnoreOutliers = proc do |table|
  table.reject { |row| row[:cycle_time].to_i > outlier_threshold }
end

SaveOutputToCsvFile = proc do |table|
  CSV.open(output_file, 'w') do |csv_output|
    table.each do |row|
      csv_output << row.values
    end
  end

  table
end

SaveToGoogleSpreadsheet = proc do |table|
  session = GoogleDrive::Session.from_config("config.json")

  worksheet = session.spreadsheet_by_key(document_key).worksheets[0]
  
  table.each_with_index do |row, index|
    row_index = index + 2
    worksheet[row_index, 1] = row[:title]
    worksheet[row_index, 2] = row[:github_id]
    worksheet[row_index, 3] = row[:estimate]
    worksheet[row_index, 4] = row[:cycle_time]
  end

  worksheet.save

  table
end

(
  ParseCsv >> Extract_GithubId_Estimate_CycleTime >> IgnoreTicketsWithoutEstimate >> IgnoreOutliers >> SaveOutputToCsvFile >> SaveToGoogleSpreadsheet
).call(file)