Export a Tiller transaction spreadsheet as a ledger file while checking for duplicates in ledger-web database.

export_tiller.rb

require 'rubygems'
require 'bundler/setup'

require 'pg'
require 'sequel'
require 'ledger_gen'
require 'google_drive'

SPREADSHEET_ID = 'your-google-sheet-id'
DATABASE_URL = 'postgres://username:password@host/database'
DB = Sequel.connect(DATABASE_URL)

ACCOUNT_RULES = [
  [ /(Meijer|Kroger)/i,       'Expenses:Food:Groceries' ],
  [ /(Hulu|Netflix|Kindle)/i, 'Expenses:Entertainment'  ],
]

session = GoogleDrive::Session.from_config('.config.json')

def for_each_row(session, spreadsheet, worksheet)
  worksheets = session.spreadsheet_by_key(spreadsheet).worksheets

  ws = worksheets.detect { |w| w.title == worksheet }

  titles = []
  (1..ws.num_cols).each do |col|
    titles << ws[1,col]
  end

  (2..ws.num_rows).each do |row|
    data = {}
    (1..ws.num_cols).each do |col|
      data[titles[col -1]] = ws[row, col]
    end

    yield data
  end  
end

def transaction_exists?(txn_id)
  DB.fetch("select 1 from ledger where '#{txn_id}' in (select btrim(x) from unnest(string_to_array(jtags->>'tiller_id', ',')) x) limit 1").all.length > 0
end

def account_for_row(row)
  account = row['Account']
  description = row['Description']

  ACCOUNT_RULES.each do |rule|
    if description =~ rule.first
      return rule.last
    end
  end

  'Expenses:Misc'
end

journal = LedgerGen::Journal.new

for_each_row session, SPREADSHEET_ID, "Transactions" do |row|

  txn_id = row['Transaction ID']
  txn_date = Date.strptime(row["Date"], "%m/%d/%Y")

  next if txn_date < Date.new(2018,2,1)
  next if transaction_exists? txn_id

  amount = row["Amount"].gsub('$', '').gsub(',', '').to_f

  journal.transaction do |txn|
    txn.cleared!
    txn.date txn_date
    txn.payee row["Description"]
    txn.comment "tiller_id: #{txn_id}"

    txn.posting account_for_row(row), amount * -1
    txn.posting row["Account"]
  end
end

puts journal.pretty_print('-y "%m/%d" --sort=date')