Starter Web App (Flask w/ Google Sheets)

An example web application, built in Python with the Flask package and a Google Sheets datastore, for educational purposes.

Installation

Clone or download from GitHub source. And navigate there from the command-line:

cd starter-web-app-flask-sheets

Create and activate an Anaconda virtual environment:

conda create -n web-starter-sheets-env python=3.7 # first time only
conda activate web-starter-sheets-env

NOTE: Subsequent commands assume you're running them from within the virtual environment, in the root directory of the repository.

Install package dependencies (first time only):

pip install -r requirements.txt

Setup

Google Sheets

adapted from, see also: this excellent blog post

Downloading API Credentials

Visit the Google Developer Console. Create a new project, or select an existing one. Click on your project, then from the project page, search for the "Google Sheets API" and enable it. Also search for the "Google Drive API" and enable it.

From either API page, or from the API Credentials page, follow a process to create and download credentials to use the APIs. Fill in the form to find out what kind of credentials:

• API: "Google Sheets API"
• Calling From: "Web Server"
• Accessing: "Application Data"
• Using Engines: "No"

The suggested credentials will be for a service account. Follow the prompt to create a new service account with a role of: "Project" > "Editor", and create credentials for that service account. Download the resulting .json file and store it in this repo as "auth/google_api_credentials.json".

There is a way for us to configure the spreadsheet service to read the contents of that file, but to make the development environment compatible with the way the application needs to operate on the the Heroku server, we'll store the credentials information in an environment variable instead:

export GOOGLE_API_CREDENTIALS="$(< auth/google_api_credentials.json)"
echo $GOOGLE_API_CREDENTIALS #> { "type": "service_account" ... }

Configuring a Google Sheet Datastore

Create your own Google Sheet document, or use this example public document.

If you create your own, make sure it contains a sheet called "Products" with column headers id, name, department, price, and availability_date. And modify the document's sharing settings to grant "edit" privileges to the "client email" address located in the credentials file.

Note the document's unique identifier from its URL, and store the identifier in an environment variable called GOOGLE_SHEET_ID.

Usage

Read and write example data to and from your Google Sheets document:

python web_app/spreadsheet_service.py

Run a local web server, then view your app in a browser at http://localhost:5000/:

FLASK_APP=web_app flask run

NOTE: you can quit the server by pressing ctrl+c at any time. If you change a file, you'll likely need to restart the server for the changes to take effect.

Deploying

First, install the Heroku CLI, and make sure you can login and list your applications.

heroku login

heroku apps:list

Create an application server (e.g. "products-flask-app-2020"):

heroku apps:create products-flask-app-2020

Find the application's "heroku git url" from the application's settings tab in the heroku online dashboard, then associate this repository with that remote address:

git remote add heroku-2020 REMOTE_ADDRESS # like https://git.heroku.com/products-flask-app-2020.git

Set environment variables on the server:

heroku config:set GOOGLE_SHEET_ID="1_hisQ9kNjmc-cafIasMue6IQG-ql_6TcqFGpVNOkUSE" -a products-flask-app-2020
heroku config:set GOOGLE_SHEET_NAME="Products-2020" -a products-flask-app-2020

Set the entire contents of the credentials.json file into an environment variable (approach allows service to function on Heroku server without uploading the .json file there):

heroku config:set GOOGLE_API_CREDENTIALS="$(< auth/google_api_credentials.json)" -a products-flask-app-2020

Deploy to the "heroku-2020" remote address:

git push heroku-2020 crud:master

Licence