This March, I had a sexy task to import all employees and departments contacts information which were stored in Google Spreadsheet into Google contact.
A dummy people will think to pay data entry for entering them through google contact form. Whoa.. if i were a data entry, i would have had broken fingers after entering all data. Any way, I found a shortcut to import all data in only seconds or minutes, that is; make a robot to enter all data when we have a lunch.
Now, let's prepare the tools :
1. Installed Ruby on Rails Application
2. gem install gdata
3. gem install google-spreadsheet-ruby
4. open any text editor
Create Basic Rake Frame
namespace :db do
desc "GS2C: Google spreadsheet to Google contact"
task :gs2c => :environment do
require 'gdata'
require 'google_spreadsheet'
#Here will be some sexy scripts
end
end
Here it is, The Robot
namespace :db do
desc "GS2C: Google spreadsheet to Google contact"
task :gs2c => :environment do
require 'fastercsv'
require 'gdata'
require 'google_spreadsheet'
#sample spreadsheet URL
#https://spreadsheets.google.com/a/kiranatama.com/ccc?key=0AlQZq7IIjE6UdF92SkhRazlYaVFUWFNYZGxxLWpoT0E&hl=en
#settings
@spreadsheet_key = "0AlQZq7IIjE6UdF92SkhRazlYaVFUWFNYZGxxLWpoT0E"
login_config = YAML.load_file("#{RAILS_ROOT}/config/google_login.yml")[RAILS_ENV]
login_config["google"].each { |key, value| instance_variable_set("@#{key}", value) }
def create_spreadsheet_connector
@google_spreadsheet = GoogleSpreadsheet.login(@email_contact, @password_contact)
create_contact_connector
end
def create_contact_connector
@google_contact = GData::Client::Contacts.new({
:authsub_scope => 'http://www.google.com/m8/feeds/',
:source => 'google-DocListManager-v1.1',
:version => '3.0'})
@google_contact.clientlogin(@email_contact, @password_contact)
create_contact_entry
end
def create_contact_entry
@contact_entry = <<-EOF
<atom:entry xmlns:atom='http://www.w3.org/2005/Atom'
xmlns:gd='http://schemas.google.com/g/2005'>
<atom:category scheme='http://schemas.google.com/g/2005#kind'
term='http://schemas.google.com/contact/2008#contact' />
EOF
start_load_csv
end
def start_load_csv
@spreadsheet = @google_spreadsheet.spreadsheet_by_key(@spreadsheet_key)
#Sample Row Header / First Row
#First Name | Last Name | Email | Work Phone | Home Phone | Address | City | State | Country | ZIP
#You have to skip first row and blank row
i = 0
@spreadsheet.worksheets do |row|
if i < 1
i++
return
end
@google_contact.post( 'http://www.google.com/m8/feeds/' + 'contacts/default/base?v=3', create_google_contact(row,i))
i++
end
end
def create_address(address,city,state,country,zip)
address = <<-EOF
<gd:postalAddress rel='http://schemas.google.com/g/2005#home' primary='true'>
#{address}
#{city}
#{state}, #{zip}
#{country}
</gd:postalAddress>
EOF
return address
end
def create_google_contact(row,i)
first_name = row[i, 1].blank? ? "---" : row[i, 1]
last_name = row[i, 1].blank? ? "---" : row[i, 2]
email = row[i, 3].blank? ? "---" : row[i, 3]
work_phone = row[i, 4].blank? ? "---" : row[i, 4]
home_phone = row[i, 5].blank? ? "---" : row[i, 5]
address = row[i, 6].blank? ? "---" : row[i, 6]
city = row[i, 7].blank? ? "---" : row[i, 7]
state = row[i, 8].blank? ? "---" : row[i, 8]
country = row[i, 9].blank? ? "---" : row[i, 9]
zip = row[i, 10].blank? ? "---" : row[i, 10]
full_name = first_name + last_name
data = <<-EOF
#{@contact_entry}
<title>#{full_name}</title>
<atom:content type='text'>#{contact.note}</atom:content>
<gd:name>
<gd:fullName>#{full_name}</gd:fullName>
<gd:givenName>#{first_name}</gd:givenName>
<gd:familyName>#{last_name}</gd:familyName>
</gd:name>
<gd:email primary='true' rel='http://schemas.google.com/g/2005#home' address='#{email}'/>
<gd:phoneNumber rel='http://schemas.google.com/g/2005#home'>#{home_phone}</gd:phoneNumber>
<gd:phoneNumber rel='http://schemas.google.com/g/2005#work'>#{work_phone}</gd:phoneNumber>
#{create_address(address,city,state,country,zip)}
</atom:entry>
EOF
return data
end
create_spreadsheet_connector
end
end
Command Line :
rake db:gs2c or
rake -T
2:42 AM
Reinhart
Posted in 
