I love the fintech space. So much that I recently completed a program through Oxford University on the subject! This also means that I find myself trying to look up all the fintech-related conferences around the world on a regular basis to try and keep track of them as well as where they are located. Eventually, I decided that this was getting to be a bit repetitive and did what any self-respecting techie would do: built a spreadsheet. For a while, that was it. I had this wonderful master spreadsheet of just about every fintech conference in the world neatly cataloged by location and month of the year and my life was once again simple and all was in order.
Then not too long ago, I came across a tweet talking about how a fun and often useful coding project can be had by turning spreadsheets into websites or services that make use of all that data. Ding ding ding. We have a winner! I recently launched my first web project, WhereDoMyTaxesGo.co, which tells a user just where all their tax money goes at the federal and state level, and was looking for another quick project that I could dig into.
As a professional marketer, I find it useful to take on small coding challenges here and there to 1) Keep my own technical skills sharp, and 2) Make sure that I know what’s happening on the technical side of the house so that I can effectively communicate with developers using their own language. I definitely take pride in my abilities as a technical marketer and building small projects like this is good for keeping my skills sharp!
On the surface this seemed pretty simple: Build website, style it, then connect spreadsheet in some way to serve as the database that it could pull that information from. Initially, there was some quick momentum and I had a simple outline mocked up in the card-based style that has been so popular recently. This is why I love sinatra as a DSL, it really takes no time at all to get a functioning website up and running. In this case, the site itself was done in just a few hours over two days. I’m not going to dig into the specifics of that, but feel free to take a look through this post about how I built WhereDoMyTaxesGo in all the gory detail.
The specific part that I will go into, though, is just how I figured out how actually connect the front end website with the backend google sheet. This one took a bit of digging around. I found some high-level stuff from other developers that were a good signpost, but nothing on the Sinatra or Rails way to implement it.
First up was just figuring out how it was going to work. Obviously I had to find a way for the front end and Google Sheet to talk to one another, and then I would have to build some dynamic content on the front end that could take that data and display it without hard coding it in. This actually turned out to be WAY easier than I was expecting.
It turns out that Google provides an API for Google Sheets that takes care of basically all the heavy lifting. Following the quickstart guide, you’ll first enable the API and download a credentials file which basically tells Google that you’re cool and it should respond to your requests for information. Next up, you’ll do some quick command line installation of the proper gem. Don’t forget to also add this to your gemfile while you’re at it. Here’s mine for this project:
Short and sweet.
Go ahead and follow the rest of the Quickstart guide on the API page just to make sure that you’ve got everything else set up and correctly authenticated.
Assuming that this was set up correctly, you should have a cool little script that can grab information from the designated google sheet and print it to the command line. Pretty cool! But not quite a dynamic website, eh?
At this stage, I chopped up that whole quickstart file and started putting it all in my main sinatra app – starting with this snippet right at the top which configures and then initializes the API:
#### Begin Google API Config #### OOB_URI = 'urn:ietf:wg:oauth:2.0:oob'.freeze APPLICATION_NAME = 'Google Sheets API Ruby Quickstart'.freeze CREDENTIALS_PATH = 'credentials.json'.freeze TOKEN_PATH = 'token.yaml'.freeze SCOPE = Google::Apis::SheetsV4::AUTH_SPREADSHEETS_READONLY # Initialize the API service = Google::Apis::SheetsV4::SheetsService.new service.client_options.application_name = APPLICATION_NAME service.authorization = authorize #### End Google API ####
I like to leave comments for myself to denote sections of code in an otherwise large file.
Heading over to the routes section of my project (depending on how you set up your sinatra projects, this could be either in the main app.rb file, or in a separate ‘routes’ file), I broke out this little bit of the quickstart code:
get "/" do
spreadsheet_id = '**Enter your unique google sheet ID here**'
range = 'conferences!A2:H'
response = service.get_spreadsheet_values(spreadsheet_id, range)
erb :index, :locals => {:response => response}
end
For me, the specific range of cells that I am looking for are on the ‘conferences’ tab of the sheet specified in ‘spreadsheet_id’, and I am looking for every cell from A2 through the end of H. I had headers in the first row so I didn’t want to include that, but if you want to include the first row then just start with ‘A1’. The last letter after the colon will be the final column that you want the API to read. So if you want to capture all data from a sheet called ‘WordPress’ and column B and row 3 through the end of the D column, you’d put: ‘wordpress!B3:D’ for your ‘range’ value. In my case, i’m also using a view called ‘index’ so that is specified in the ‘erb’ line. Make sure to set ‘response’ as a local variable as well so that you can access the data that the API returns on your view.
Next up, I created a file called gauth.rb that would handle the actual authorization method that was created in the quickstart file:
@return [Google::Auth::UserRefreshCredentials] OAuth2 credentials
def authorize
client_id = Google::Auth::ClientId.from_file(CREDENTIALS_PATH)
token_store = Google::Auth::Stores::FileTokenStore.new(file: TOKEN_PATH)
authorizer = Google::Auth::UserAuthorizer.new(client_id, SCOPE, token_store)
user_id = 'default'
credentials = authorizer.get_credentials(user_id)
if credentials.nil?
url = authorizer.get_authorization_url(base_url: OOB_URI)
puts 'Open the following URL in the browser and enter the ' \
"resulting code after authorization:\n" + url
code = gets
credentials = authorizer.get_and_store_credentials_from_code(
user_id: user_id, code: code, base_url: OOB_URI
)
end
credentials
end
This basically tells the application where to look for the credentials, and if they aren’t there, how to create the credential file. Then returns the credentials for whatever code calls the method to use. In this case, it gets called by that ‘initialize the API’ section of code that we put in the main app.rb file.
Finally, I got to the fun part of actually inserting the data from the sheet into my views. In just 12 lines of code I created a simple little ruby block that creates a card of information for every single conference in the spreadsheet, regardless of how many conferences I add:
This turned out to be WAY easier than I was expecting. Responses.values.each will return a row (in the form of an array) each time that it is called and then you just specify which number the item is in that array that you want. So in this case row[3] would return the fourth item in the row of data that the block returns.
The final results looks like this:
And that’s it for connecting the google sheet! I can add and remove rows which will be reflected in real time on the website. Even better, I used Typeform and Zapier to collect information submitted by readers which gets added to the sheet automatically. (To ensure quality, I actually made a second sheet identical to the first which is where user-submitted events are queued for moderation until I am able to review them. But even then, it’s just a matter of copy/paste to update the sheet!)
So that’s it! Obviously a lot more went into building the full functionality of the site, but connecting sinatra with the Google Sheets API ended up being way simpler than I thought. A little trial and error and it was done in one coding session. I’m sure that I still missed a step or two, so feel free to ask questions in the comments and I’ll get to them asap.