Skip to content

Instantly share code, notes, and snippets.

@pdxmph
Created September 29, 2011 16:14
Show Gist options
  • Save pdxmph/1251131 to your computer and use it in GitHub Desktop.
Save pdxmph/1251131 to your computer and use it in GitHub Desktop.
Update Google spreadsheet with bitly urls
#!/usr/bin/env ruby
require "rubygems"
require "roo"
require "ostruct"
require "bitly"
require "yaml"
config = YAML.load(File.open('smt_config.yml'))
sites = config["sites"]
bitly_user = config['bitly']['user']
bitly_apikey = config['bitly']['api_key']
sites.each do |site|
bitly = Bitly.new(bitly_user, bitly_apikey)
spreadsheet = site[1]['campaign_spread']
next if spreadsheet == nil
ss = Google.new(spreadsheet)
ss.default_sheet = ss.sheets.first
2.upto(ss.last_row) do |line|
row = ss.row(line)
break if row[0] == "Archive"
next if row[1] == nil || row[2] == nil || row[3] == nil || row[4] == nil || row[6] != nil
record = OpenStruct.new
record.desc = row[0]
record.utm_source = row[1]
record.utm_medium = row[2]
record.utm_campaign = row[3]
record.reference = row[4]
record.url = row[5].gsub(/\/$/,"")
new_url = "#{record.url}?utm_source=#{record.utm_source}&utm_medium=#{record.utm_medium}&utm_campaign=#{record.utm_campaign}&reference=#{record.reference}"
bitly_url = bitly.shorten(new_url).short_url
sleep 1
puts "#{new_url} => #{bitly_url}"
ss.set_value(line,7,bitly_url)
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment