Create a landing page with Sinatra, Google Spreadsheets, and Ruby
Tags: ruby, database, googlespreadsheets, sinatra
Published: 2017-03-23 09:24:56

---
title: Create a landing page with Sinatra, Google Spreadsheets, and Ruby
published: true
description: Build a landing page for a new application using Google Spreadsheets as a data store and the Sinatra web framework.
cover_image: https://www.twilio.com/blogwp-content/uploads/2017/03/devto_header.png
tags: ruby, database, google-spreadsheets, sinatra
canonical_url: https://www.twilio.com/blog/2017/03/create-a-landing-page-with-sinatra-google-spreadsheets-and-ruby.html
---

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 <a href="https://www.google.co.uk/forms/about/">Google Form</a> but you're left with a page that looks like a Google Form then. As I showed in my last post, <a href="https://www.twilio.com/blog/2017/03/google-spreadsheets-ruby.html">using the Google Spreadsheet API with Ruby</a> 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 <a href="http://drive.google.com">Google Drive</a>. You'll also need a service account that you can authorise to edit your sheet on your application's behalf. Follow the <a href="https://www.twilio.com/blog/2017/03/google-spreadsheets-ruby.html#h.6k35lx9u310k">instructions from the previous post to setup your service account</a>. Hold on to the JSON file with your credentials for now.

<em>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 </em>`client_secret.json`<em> file.</em>

I've created a template project with some view files to get us going for this post. Clone or <a href="https://github.com/philnash/ruby-google-sheets-sinatra/archive/master.zip">download</a> the template now.

```bash
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; <a href="http://www.sinatrarb.com/">sinatra</a> and <a href="https://github.com/gimite/google-drive-ruby">google_drive</a>. Install the gems with:

```bash
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:

```ruby
# 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:

```ruby
# 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.

```bash
bundle exec ruby app.rb
```

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

<img class="aligncenter size-large wp-image-19878" src="https://twilioinc.wpengine.com/wp-content/uploads/2017/03/landingpage-1024x880.png" alt="The Fancy New App landing page. It shows a form and some details about the (fictional) app." width="640" height="550" />

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.

```ruby
# 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.

```ruby
# 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 `&lt;input&gt;` 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 <a href="https://www.twilio.com/blog/2017/03/phone-number-verification-without-regular-expression.html">how useful these fields are for validation as well as popping up the right keyboard on mobile devices</a>.

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 <a href="http://localhost:4567/">http://localhost:4567/</a> and enter your details. Click submit and see them appear in your spreadsheet.

<img class="aligncenter size-full wp-image-19879" src="https://twilioinc.wpengine.com/wp-content/uploads/2017/03/spreadsheet_success.gif" alt="When you fill in your details in the application and submit the form they appear in the spreadsheet." width="872" height="540" />

## 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 <a href="https://github.com/philnash/ruby-google-sheets-sinatra/tree/save-data">save-data branch of the GitHub repo</a>.

Now you have your data in a Spreadsheet, you can manipulate the data with Apps Script. Greg has already written about <a href="https://www.twilio.com/blog/2016/02/send-sms-from-a-google-spreadsheet.html">how to send SMS messages from a Google Spreadsheet using Apps Script</a>.

Let me know if you've built anything interesting with Google Spreadsheets and Ruby. You can reach me at <a href="mailto:philnash@twilio.com">philnash@twilio.com</a> or on Twitter at <a href="https://twitter.com/philnash">@philnash</a>.

___

_[Create a landing page with Sinatra, Google Spreadsheets, and Ruby](https://www.twilio.com/blog/2017/03/create-a-landing-page-with-sinatra-google-spreadsheets-and-ruby.html) was originally published on the [Twilio Blog](https://www.twilio.com/blog/) on March 22, 2017._