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

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