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:

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:

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:

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

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.

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

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.

12. March 2015 by ChrisJ
Categories: Coding | 2 comments

Comments (2)

  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

Required fields are marked *