Using the Google API to Add Rows in a Google Sheet with a Rails App

January 18, 2023

The Right Rudder Marketing website is practically complete, but there was a feature I wanted to program into the custom CMS that I had built out.  I have forms on the site where visitors would enter in their name and email address to sign up for a newsletter or download content and I could access that information by logging in or checking my email (Rails Action Mailer integration).  But I wanted to take it a step further.  I wanted to get this information added into a Google Sheet every time someone completed the form.

To download a pdf visitors need to enter their email address


On the website’s footer visitors can enter their name and email address to get subscribed to a newsletter


The Google Sheet I want automatically populated

To complete this, it boils down to the following steps.

  1. Create a project in the Google Developers Console and then enable the Google Drive API
  2. Create credentials to access the API, then download the credentials JSON file.
  3. In the Rails app, use the google-drive-ruby gem to interact with the Google Sheets API.
  4. In your model (mine are called newletter_emails and checklist_emails), use a callback method such as after_create to trigger the Google Drive API call when a new instance is created.

Creating a Project in Google Developers Console


The first step is to tell Google we want to talk to it via the Google Sheets API.  We need to first create a project.  We need to make a project because that allows us to access the available APIs in the Google Developers Console.

In terms of APIs and Google, I have only used Recaptcha which didn’t require me to go through Google Developers Console.  Thus, I have never used Google Developers Console so I had to accept the TOS.

I most certainly read through the Google Cloud Platform Terms of Service 100% before I clicked accept! 😂

The next step is pretty self explanatory.  Click “CREATE PROJECT” to create the project.


Make a project.  I called mine “Right Rudder Marketing Leads”


From here, you’ll want to enable the Google Drive API.  To do that simply click the “ENABLE APIS AND SERVICES” button.  You’ll be taken to a search screen.  Look for the Google Drive API



Click the enable APIs and Services button


Search for Google Drive


Hit enable and let’s get the party started.



Create Credentials




Click the blue button that says CREATE CREDENTIALS

If you’ve been following along, we’re now at the point where we need to create credentials.  I had initially used the Google Sheets API when I tried to do this and took the screenshot, but I was running into issues.  But regardless, the credentials I made still works when I used the Google Drive API instead.


Ahh.. The hard questions of life.


The first question asks if we are getting user data or application data.  Technically, the visitors on our site may or may not be a Google user so therefore, it wouldn’t be User data and we will select Application data as the data belongs to our Ruby on Rails application.  As a best practice, if you are collecting any type of visitor data, you should have some type of terms of service documentation or notice which infers the visitor’s consent.


Second question is about cloud computing.  Although technically my app is hosted on the cloud, I am not using a compute engine or anything like that so I will mark “No, I’m not using them”


Next create a service account that accesses the API.  Then go to keys and add a new key.  We want a JSON file for our credentials.


Click DONE to create the service account



Click ADD KEY



Choose JSON


JSON file should download



Doing the above steps has in essence created a door to our Google Account that can be accessed by our Ruby on Rails app.  As our final goal is to create a Google Sheet that automatically updates, you can see how following these steps are important.


Use the Google Gem



The first issue I ran into when getting started with using the Google Gem was properly pushing my credentials.  I had the JSON file downloaded, but the website is on a GitHub repository so it wasn’t as simple as just placing the JSON file in the root or config directory and start playing. 

The approach I took to this was to create a blank JSON file called googleconfig.json in the config directory, add the blank JSON file to my .gitignore file, and store the data in environment variables.  Then, I would use a helper function that could access the environment variables which would populate the JSON file which could then be read the google_drive gem.

In my newsletter_emails_helper.rb file I wrote the following code:

 def create_json_credentials(path)

   credentials = {

     "type": ENV['TYPE'],

     "project_id": ENV['PROJECT_ID'],

     "private_key_id": ENV['PRIVATE_KEY_ID'],

     "private_key": ENV['PRIVATE_KEY'],

     "client_email": ENV['CLIENT_EMAIL'],

     "client_id": ENV['CLIENT_ID'],

     "auth_uri": ENV['AUTH_URI'],

     "token_uri": ENV['TOKEN_URI'],

     "auth_provider_x509_cert_url": ENV['AUTH_PROVIDER_X509_CERT_URL'],

     "client_x509_cert_url": ENV['CLIENT_X509_CERT_URL']

   }


    File.open(path, 'w') do |f|

     f.write(credentials.to_json)

   end

    path

 end


Whenever the helper function create_json_credentials is called, it updates the JSON file.  In retrospect, I think this might be inefficient as the JSON file doesn’t need to be updated every single time a new sign up occurs, but it works for now.

Next, I used a callback function in my newsletter_email.rb model file which would start the process of updating the google sheet.

require 'google_drive'

class NewsletterEmail < ApplicationRecord

after_create :update_google_sheet



 private



 def update_google_sheet

   path = create_json_credentials("config/googleconfig.json")

   session = GoogleDrive::Session.from_service_account_key(path)

   spreadsheet = session.spreadsheet_by_key("MYSPREADSHEETKEY").worksheets[0]

  

   # Write the data from the new instance of the newsletter_emails model

   spreadsheet.insert_rows(spreadsheet.num_rows + 1, [[self.name, self.email, "Newsletter Sign Up", self.created_at]])



   # Save the changes to the Google Sheet

   spreadsheet.save

 end


end

A few things to point out here..  require ‘google_drive’ activates the google drive ruby gem.  I installed the gem by simply adding 

gem 'google_drive'

And then running bundle install.  Next, my variable “path” calls upon the create_json_credentials helper function and passes a path which is the empty json file we created earlier.  Lastly, the spreadsheet key needs to match the google sheet key.  This is found in the URL of the google sheet.  I thought I was finished but I was getting the following error:

Google::Apis::ClientError (notFound: File not found: MYSPREADSHEETKEY.):

I pondered this for a while.  Everything was right.  The key was correct, the JSON file had the credentials.. What was missing?  


The last part was going the the sheet itself and then sharing the sheet with the email that was provided in the json file.


You would think the Google API would be able to talk to its own file.. Guess not.



FINALLY.  It works.  Below is how it looks now.



Visitor enters information



They get a thank you message and an email (previously coded)



My Google Sheet automatically updates



Wow!  What an adventure.  I hope you found this helpful if you ever wanted to do something like this.  Happy coding! And if you have any questions, or if you need any consulting, software development help, or website design needs please let me know!


↩ Back to Articles