Morioh
Login
Elvis Miranda

Elvis Miranda

4 years ago

Build a site quickly Using Google Sheets Python and Aws

A bit of background before we get started:

A few days ago, I was looking for courses that were free or discounted due to the COVID-19. I thought that others might be doing the same, so I decided to compile some resources and publish them online.

I started compiling the courses using a Google Sheet and was planning on sharing it after I had enough resources. However, something was bothering me. Opening sheets on a mobile suck and most people use their phones for browsing the internet. I thought I could do better.

The thing is that I have little experience in web development. Also, I didn’t want to dedicate more than a few hours on developing and launching the site. So I decided to build something quickly.

Here are the requirements I set for building the site quickly and the approach I took to meet them:

Requirements

  1. The site should load fast and look good on mobiles (or at least better than a Google Sheet)
  2. I need to be able to add or remove resources fast and without much hassle. Keep in mind that I may want to add bit more interactivity from the client side in the future.
  3. Use Python as much as possible (and as little as possible of HTML/CSS/JS)
  4. It should take me only a few hours to develop and launch
  5. Maintenance costs should be very very close to zero

Approach

  1. Used of Bootstrap (only CSS, excluded JS components)
  2. Managed content using Google Sheets. I used the Google Sheets API for getting content programmatically into the site. It’s easy to make a web API from a Google Sheet if needed.
  3. Generated the site using from a basic HTML/CSS template using Jinja2
  4. Hosted the site on an AWS S3 bucket + Cloudfront (for SSL certificate). Bought the domain using Route 53
  5. The use that I’m doing of the AWS services fall under the Free Tier. I don’t foresee that this will change anytime soon. So far, I’ve only invested ~15€ on the site

After a couple of hours, I launched stayhomeandlearn.org.

The rest of this article is a tutorial on how to build a static site using Google Sheets, AWS, and Python.

For this tutorial, we will set up a script that reads data from Google Sheets, generates a static site using a predefined template, and deploys it to an S3 bucket. This article is meant for programmers with little knowledge of web development that want to get something running quickly.

There are five sections in the tutorial: Requirements, Review Code and Jinja Template, Using the Google Sheets API, and Build and Deploy Your Site.

Requirements

These you’ll need to set up or review on your own. I added some links for that purpose.

Code and Jinja Template

First, create a directory called my_pokemon_stats and open a terminal from there. Then, create a virtual environment and install the required packages as follows:

Next, download and save these two files there: template.html and site_builder.py. These are the building blocks for generating the site.

template.html is the Jinja template we will use for building the site. It’s an HTML-like file where you can add logic that will be processed in Python and generate the definitive site. This file looks as follows:

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <link href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" rel="stylesheet"
          integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
    <title>My Pokemon Stats</title>
</head>
<body>
<header id="header">
    <div class="container text-center">
        <h1 class="pt-5 pb-1 font-weight-bold">
            My Pokemon Stats
        </h1>
        <hr>
        <p class="pt-2">
            This is a site I use to store the stats of all my Pokemon.
        </p>
    </div>
</header>
<section id="pokemon_table">
    <div class="container py-4">
        <div class="table-responsive">
            <table class="table table-hover">
                <thead class="thead-dark">
                <tr>
                    <th scope="col">Name</th>
                    <th scope="col">Type 1</th>
                    <th scope="col">Type 2</th>
                    <th scope="col">Total</th>
                    <th scope="col">HP</th>
                    <th scope="col">Attack</th>
                    <th scope="col">Defense</th>
                </tr>
                </thead>
                <tbody>
                {% for row in data %}
                <tr>
                    <td>{{ row["Name"] }}</td>
                    <td>{{ row["Type 1"] }}</td>
                    <td>{{ row["Type 2"] }}</td>
                    <td>{{ row["Total"] }}</td>
                    <td>{{ row["HP"] }}</td>
                    <td>{{ row["Attack"] }}</td>
                    <td>{{ row["Defense"] }}</td>
                </tr>
                {% endfor %}
                </tbody>
            </table>
        </div>
    </div>
</section>
</body>
</html>

Let’s break it down:

  • You can safely ignore most of what’s inside the <head> tag. It’s standard HTML5 code that you’ll see in most pages. However, there are just two interesting tags that we’ll take a closer look at: <link> and <title>.
  • In this case, the <link> tag is used to import the Bootstrap component library. We will use it to define simple styles for the different sections of the page and make it look good without much effort. <title> defines the title of the page (what you see in the browser’s tab) and it is useful for SEO and social media sharing.
  • Next, there’s the <header> section inside the <body> tag. This is where we define the text that will appear on the page. It will look like the image below. I used standard styling from Bootstrap to center the text and added a bit of padding.

This is image title

  • Finally, we have the <section id="pokemon_table">. The <div> and <table> tags provide some basic styling for building a table. Next, we define the header of the table inside the <thead> tags. Inside the <tbody> tag is where Jinja does its magic
  • The {% for row in data %} is a loop that goes through each row of the Pokemon’s data. In each of the <td>{{ row["..."] }}</td> we get the information per row from field (e.g. Name, Type 1, Type 2). This generates something that will look as follows:

This is image title

Next, we have the site_builder.py file. This script downloads the Pokemon’s data from Google Sheets, processes the data and the template.html file, and then uploads the resulting file to an S3 bucket.

import csv

import boto3
import gspread
import jinja2
from oauth2client.service_account import ServiceAccountCredentials

AWS_PROFILE = "INSERT-AWS-PROFILE-HERE"
BUCKET = "INSERT-BUCKET-NAME-HERE"
WORKBOOK = "INSERT-WORKBOOK-NAME-HERE"


def download_data():
    """Download data using the Google Sheets API"""
    scope = [
        "https://spreadsheets.google.com/feeds",
        "https://www.googleapis.com/auth/drive",
    ]
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        "credentials.json", scope
    )
    client = gspread.authorize(credentials)

    worksheet = client.open(WORKBOOK).get_worksheet(0)
    sheet_values = worksheet.get_all_values()

    print(f"Downloading: {worksheet.title}")
    with open("my_pokemon_stats.csv", "w") as f:
        writer = csv.writer(f)
        writer.writerows(sheet_values)


def generate_site():
    """Generate site in local directory"""
    print("Process data and build site")

    template_loader = jinja2.FileSystemLoader(searchpath="./")
    template_env = jinja2.Environment(loader=template_loader)
    template = template_env.get_template("template.html")

    with open("my_pokemon_stats.csv") as csv_file:
        csv_reader = csv.DictReader(csv_file)
        data = [row for row in csv_reader]

    output = template.render(data=data)

    with open("index.html", "w") as f:
        f.write(output)


def deploy_site():
    """Deploy site S3 bucket"""
    print("Upload data to S3")
    session = boto3.Session(profile_name=AWS_PROFILE)
    s3 = session.resource("s3")
    s3.Bucket(BUCKET).upload_file(
        Filename="index.html", Key="index.html", ExtraArgs={"ContentType": "text/html"}
    )


if __name__ == "__main__":
    download_data()
    generate_site()
    deploy_site()

The code is structured in 3 functions: download_sheets, generate_site, and deploy_site. We will fill the details for accessing AWS and the Google Sheets API in the next sections.

Using the Google Sheets API

Follow these steps to download the Pokemon’s data using the Google Sheets API:

  1. Create a Workbook in Google Sheets (you can copy mine: My Pokemon Stats)
  2. Go to the Google APIs Console
  3. Create a new project called MyPokemonStats.
  4. Click on Enable API and Services. Search for and enable the Google Sheets API.
  5. Go back to the Google APIs Console and click on Enable API and Services again. Now search for and enable the Google Drive API.
  6. Click on _Create credentials._For the next 4 questions select:Google Drive API, Web Server (e.g. node.js, Tomcat), Application data, and No, I’m not using them.
  7. Click on What credentials do I need? Select a name for the service account (e.g. get-data) grant it a Project Role of Editor. Select the JSON option for Key type
  8. A dialog box will open. Save the JSON file, copy it to the my_pokemon_stats directory, and rename it to credentials.json.
  9. Open the credentials.json file. Find a key called client_email, copy its value (e.g., get-data@iam…). Go back to your Workbook in Google Sheets, click the Share button in the top right, and paste the client email into the People field to give it edit rights. Hit Send.
  10. Go to the site_builder.py script, and set the WORKBOOK variable to whatever name you gave to your workbook on step 1.

Setting up an S3 bucket and AWS-related configurations

Now, let’s create the S3 bucket and configure our code to access AWS programmatically:

  1. Go to the Amazon S3 Console
  2. Create an S3 bucket
  3. Once in the bucket, click on Properties and then on Static website hosting
  4. Select the option Use this bucket to host a website
  5. Under Index document and Error document put index.html
  6. Save the URL from Endpoint. You’ll use that URL for connecting to your site.
  7. Go to Permissions and click Edit
  8. Clear Block all public access, choose Save, and confirm. When you change this anyone on the internet will have access to the contents of this bucket. That’s what you want when you are publishing a site, however, don’t put anything private there!
  9. Now go to Bucket Policy, replace the bucket name in the following policy, paste it there, and click Save.
{
  "Version":"2012-10-17",
  "Statement":[{
 "Sid":"PublicReadGetObject",
        "Effect":"Allow",
   "Principal": "*",
      "Action":["s3:GetObject"],
      "Resource":["arn:aws:s3:::BUCKET-NAME-HERE/*"
      ]
    }
  ]
}

10. Go to the site_builder.py script. Set the variable AWS_PROFILE variable to the profile name you use for accessing AWS (in a UNIX sytem it should be one of the profiles in ~/.aws/credentials).

Build and Deploy Your Site

Finally, you should be able to run python site_builder.py from the root folder of the project to generate the site. This will download the data from Google Sheets, process the template.html file using Jinja and upload the site to the S3 bucket.

If you want to check the site, go to the endpoint URL (step 6 from the previous section).

Closing Words

This method is by no means perfect but will help you ship something quickly. I used this strategy to build stayhomeandlearn.org and it worked quite well. From April 1st until April 16th, the site got over fifteen thousand visitors, which exceeded my most optimistic expectations.

This is image title

The site is slowly walking towards its death now. However, this process taught me how important it is to focus on shipping instead of wasting time looking for the perfect tools. I built the site quickly, people liked it, and after the first day it already had more traffic than any of the side projects I’ve done so far. That thing about perfect being the enemy of good is real.

In my case, I had to add more functionality to the script for styling and deploying purposes. If you are interested, you can take a look at the code in my GitHub repository.

#python #programming

Build a site quickly Using Google Sheets Python and Aws
18.70 GEEK