Verse of the Day, Part 2: using Google Spreadsheet


Last time, I described what I thought needed to happen in order to send out a verse of the day message via SMS and email. This makes use of Google Drive, ESV Bible, Twillio, and MailChimp APIs and interfaces.

The first thing is to create a place to store our information. This includes people’s signup information such as names, emails, and SMS numbers. Also we need a place to store the content that will be sent out.

Since most of our staff uses Google Docs, a Google spreadsheet seemed like the best choice to support the hand entry process. Some of the signup data was expected to come through a MailChimp form so keeping the spreadsheet and mailchimp in sync might become an issue, but for this year, we can sync the signups by hand. MailChimp has an easy import/export from Google Docs process which makes it easier.

So, first, we need to log into Google Apps. This was easy last year when I did my first crud scripts, all I needed was a username and password and the ID of the sheet, but this year, Google depreciated that simple auth method and is pushed everything to OAuth2. The good part is there are plenty of code examples, the bad part is the amount of setup required.

Assuming you have a developers account, go to Google Developers Console and create a project, then create a Client ID for a native application. From our config.rb:

GDRIVE = {
              :ss_title => 'Verse of the Day',
              :schedule_ws => 'Schedule',
              :recipients_ws => 'Recipients',
              :oauth_client_id => "xxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.apps.googleusercontent.com",
              :oauth_client_secret => "XXXXXXXXXXXXXXXXXXXXXXXX",
              :oauth_refresh_token => 'x/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
              :scope => "https://www.googleapis.com/auth/drive https://spreadsheets.google.com/feeds/",
            }

We need to fill in the scope, Client ID and Client secret to generate the refresh token, which we will use to get an new auth token that we will use to log in each day. Auth tokens time out in about an hour, so we can’t just store an auth token to use day after day.

A note about security, if this machine was to be exposed to any risk that someone could see my config file, I would need to find a safer way to store these keys.

To get the refresh token, we need to use a web browser to pop up the Google login we are all so familiar with. To do this we use the following script on the command line:

require './config'
require 'oauth2'

raise "Missing client_id variable" if Config::GDRIVE[:client_id].to_s.empty?
raise "Missing client_secret variable" if Config::GDRIVE[:client_secret].to_s.empty?
raise "Missing scope variable" if Config::GDRIVE[:scope].to_s.empty?

redirect_uri = 'https://localhost/usethistoken'

auth = OAuth2::Client.new(Config::GDRIVE[:client_id], Config::GDRIVE[:client_secret], {:site => 'https://accounts.google.com', :authorize_url => "/o/oauth2/auth", :token_url => "/o/oauth2/token"})

puts "1) Login to your Google Account then paste this URL into your browser\n\n"
puts auth.auth_code.authorize_url(:scope => Config::GDRIVE[:scope], :access_type => "offline", :redirect_uri => redirect_uri, :approval_prompt => 'force')

puts "\n\n\n2) Accept the authorization request from Google in your browser:"

puts "\n\n\n3) Google will redirect your browser, copy the code parameter out of the URL in your browser's location bar, then paste it here and hit enter:\n"
code = gets.chomp.strip

access_token_obj = auth.auth_code.get_token(code, { :redirect_uri => redirect_uri, :token_method => :post })

puts "Auth token: #{access_token_obj.token}"
puts "Refresh token: #{access_token_obj.refresh_token}"

Now the refresh token goes into our config file.

Now we have what we need, along this the name of the spreadsheet file and spreadsheet page, to get a handle for our spreadsheet:

def get_worksheet_from_gdrive(ss_title, ws_title)
  client = Google::APIClient.new(:application_name => '40DaysSMS',
  :application_version => '0.0.1')
  auth = client.authorization
  auth.client_id = Config::GDRIVE[:oauth_client_id]
  auth.client_secret = Config::GDRIVE[:oauth_client_secret]
  scope =  Config::GDRIVE[:scope]
  
  auth.redirect_uri = "http://localhost/redirect"
  auth.refresh_token = Config::GDRIVE[:oauth_refresh_token]
  auth.fetch_access_token!
  session = GoogleDrive.login_with_oauth(auth.access_token)
  
  session.file_by_title(ss_title).worksheet_by_title(ws_title)
end

Then we can grab the data we need, in this case, grab the row that has today’s date:

def get_scheduled_messages 
  ws = get_worksheet_from_gdrive(Config::GDRIVE[:ss_title], Config::GDRIVE[:schedule_ws]) 
  keys = ws.rows[0] 
  today = Time.now.strftime('%-m/%-d/%Y') 
  messages_list = ws.rows.select {|r| r[0] == today} # get today's row(s) 
  messages = [] 
  messages_list.each { |message| messages << Hash[*keys.zip(message).flatten] } 
  messages.map! do |message| 
    message.each do |k,v| #find {ESV:.*}, {ESVF:.*}, and {.*} templates in message content and replace them
        v.gsub!(/\{ESV:(.*?)\}/) { |s| s = get_esv_text($1) } 
        v.gsub!(/\{ESVF:(.*?)\}/) { |s| s = get_esv_text($1, true) } 
        v.gsub!(/\{(.*?)\}/) { |s| s = message[$1] } 
        message[k] = v 
    end 
    message 
  end 
  messages 
end

The code above gets the sheet then grabs row #1 as the keys. Next it looks through the rows for ones with today’s date in column #1. Those rows it turns into hashes with the keys from row #1.

We wanted to be able to do some substitutions in the text found in the cells so I created a syntax where {ESV:John 3:16} will get John 3:16 as plain text from the ESV Bible webservice. {ESVF:John 3:16} will return an HTML formatted version of the text. Using {row_name} will substitute the content of another row.

def get_esv_text(verse, formated = false)
  output_format = ( formated ? "html" : "plain-text" )
  include_subheaders = ( formated ? true : false )
  include_passage_horizontal_lines = ( formated ? true : false )
  include_heading_horizontal_lines = ( formated ? true : false )
  include_heading_horizontal_lines = ( formated ? true : false )
  include_headings = ( formated ? true : false )

  # puts "ESV: getting #{verse}"
  bible = ESV::Client.new(:api_key => Config::ESV[:api_key])
  passage = bible.passage_query(verse, {'output-format' =>output_format,
                              'include-subheadings' => include_subheaders,
                              'include-passage-horizontal-lines' => include_passage_horizontal_lines,
                              'include-heading-horizontal-lines' => include_heading_horizontal_lines,
                              'include-headings' => include_headings,
                              'include-short-copyright' => false,
                              'include-verse-numbers' => false,
                              'include-footnotes' => false,
                              'line-length' => 0,
                              'include-passage-references' => false}).parsed_response.squeeze(" ").strip
   # puts "ESV: returned #{passage}"
   return passage unless passage.start_with?("ERROR")
   return "ESV API ERROR"
end

We also need to grab the phone numbers for our SMS messages. The numbers are in row #3:

def get_sms_recipients
  ws = get_worksheet_from_gdrive(Config::GDRIVE[:ss_title], Config::GDRIVE[:recipients_ws])
  ws.rows.collect{|r| r[2].to_i}.reject{|r| r == 0}
end

Once we have all the bits we can look at sending SMS messages and setting up and send a MailChimp campaign.

I have created a GitHub project for the current code if you are interested.


2 responses to “Verse of the Day, Part 2: using Google Spreadsheet”

  1. Hi! Found your posts on your integration of Google sheets with Mailchimp via Google when searching for such a service. It seems though that you had to build this from scratch and run the job on your own machine – have you seen any other working solutions to automate Mailchimp content? There seems to be an abundance of tools to integrate with various contact lists but none that I have found for content. Strange… Many thanks for any and all advice you might have! Cheers!

  2. One of the reasons I ended up scripting this out was that where didn’t seem to be any easy way to do simple content automation like what I needed. I have low-tech users generating content, so having them generate whole HTML5 email wasn’t going to work. The search-n-replace code I came up with works for me but is pretty primitive and can’t cope with templates that are too complex, but it is a start.

Leave a Reply

Your email address will not be published. Required fields are marked *