Create a landing page with Sinatra, Google Spreadsheets, and Ruby

In my career I've been tasked with building landing pages to capture user interest in a product or application. We'd want details from potential users, such as their name, and email address and phone number to contact them when the app was ready.

You could choose to use a Google Form but you're left with a page that looks like a Google Form then. As I showed in my last post, using the Google Spreadsheet API with Ruby is nice and straightforward. So here's how you can build your own landing page using the Google Spreadsheet API and Ruby.

Getting started

First up you're going to need a new spreadsheet to save your data in. Create one in your Google Drive. You'll also need a service account that you can authorise to edit your sheet on your application's behalf. Follow the instructions from the previous post to setup your service account. Hold on to the JSON file with your credentials for now.

If you followed the previous post, do not forget to give edit access to the service account for your new spreadsheet. Open the spreadsheet, click share in the top right and paste the email address from your client_secret.json file.

I've created a template project with some view files to get us going for this post. Clone or download the template now.

git clone https://github.com/philnash/ruby-google-sheets-sinatra.git
cd ruby-google-sheets-sinatra

Grab your Google credentials and add them to the application folder, make sure the file is called client_secret.json.

In the template I've included a Gemfile with the two dependencies we need for this project; sinatra and google_drive. Install the gems with:

bundle install

Now we can start to build our app.

Building with Sinatra

Create a file called app.rb and open it in your favourite text editor. First we need to require our dependencies. Bundler can handle that for us:

# app.rb
require("bundler")
Bundler.require

Now we need an index page to render when we hit the root. I have included the layout and view in the views folder. To render the page just add:

# app.rb
require("bundler")
Bundler.require

get "/" do
  erb :index
end

Save the file and start the app to make sure you're on the right track.

bundle exec ruby app.rb

Your app will start on http://localhost:4567/. Check it out and see if you have a screen that looks like the below.

If you can see the landing page for a fancy new app, then everything is working as expected.

As you can see, there is a form on the page. This is the form we'll use to collect user details and populate our spreadsheet with them. In this case we're asking for the name, email and phone number. We can keep our users up to date with emails as the app is completed and SMS them a link to install the new app once it's ready.

The form is setup to POST data to the root path. Let's build that endpoint now.

Saving data to Google Spreadsheets from Sinatra

We need to build a route that will receive the parameters from the POST request made by the form and send them on to the Google Spreadsheets API. Let's start by writing a helper method that will give us access to the worksheet we want to save the data in.

The method should start a new session with the Spreadsheet API, open up the spreadsheet we want to save to and select the first worksheet. We'll build it so that we save the objects as instance variables so that we don't have to go through the process every time.

# app.rb
def worksheet
  @session ||= GoogleDrive::Session.from_service_account_key("client_secret.json")
  @spreadsheet ||= @session.spreadsheet_by_title("Fancy new app spreadsheet")
  @worksheet ||= @spreadsheet.worksheets.first
end

I called my spreadsheet the "Fancy new app spreadsheet". Make sure you include the spreadsheet name that you chose.

Now to write the endpoint. We'll add the data in the last row of the worksheet plus one row so that it's always adding to the spreadsheet.

# app.rb
post "/" do
  new_row = [params["name"], params["email"], params["phone_number"]]
  begin
    worksheet.insert_rows(worksheet.num_rows 1, [new_row])
    worksheet.save
    erb :thanks
  rescue
    erb :index, locals: {
      error_message: "Your details could not be saved, please try again."
    }
  end
end

We take the incoming params and turn them into an array that will represent the row. The names come from the name attribute of the respective <input> fields in the view. Note how we're using the relevant input types for each field; text for name, email for the email and tel for the phone number. Dominik showed recently how useful these fields are for validation as well as popping up the right keyboard on mobile devices.

We use worksheet.insert_rows to insert the new row into the spreadsheet, save the worksheet and render a page that says thank you.

We do the work with the API within a begin … rescue … end block so that if there are any issues with the API we catch them and return the user to the home page.

Save the file and restart the server. Visit http://localhost:4567/ and enter your details. Click submit and see them appear in your spreadsheet.

Real application, spreadsheet data store

You've now seen how to use Sinatra to take parameters from an HTML form and store them in a Google Spreadsheet. You can create your own design for the landing page and make the data available to non-technical users with ease. Check out all the code from this post in the save-data branch of the GitHub repo.

Now you have your data in a Spreadsheet, you can manipulate the data with Apps Script. Greg has already written about how to send SMS messages from a Google Spreadsheet using Apps Script.

Let me know if you've built anything interesting with Google Spreadsheets and Ruby. You can reach me at philnash@twilio.com or on Twitter at @philnash.

Create a landing page with Sinatra, Google Spreadsheets, and Ruby was originally published on the Twilio Blog on March 22, 2017.