A project that enables developers to take their Google Sheets and draw data from them with a RESTful API.
It uses a combination of Python scripts, Flask, Firebase, Google Apps Engine, and the Google Sheets API to accomplish this. The following parts of the readme will describe how to set the app up from scratch.
Setting up the Google Sheet is a very specific step. The sheet must be tailored to the spec. exactly, or unexpected behavior may occur. Indeed, there are still some safety measures in place, but they should not be relied on all the time.
The sheet must be one single Google Sheet, with any name. The sheet must have public view access, or, at the very least, view access for the account running the script.py sync script.
Sub-sheets of this spread sheet must be named after the thing they represent. They can have any name, but try to keep extraneous/unusual characters to a minimum.
The first row of each sub-sheet must be the human readable comment header. This is an English phrase representing the type of data below it. One example could be for the game date: Date; or for the game's visiting team: Visiting Team
The second row of each sub-sheet must be the machine readable comment header. This is what is called a "slug": a name that can be used to represent another name that programs cannot parse correctly. This includes stripping capitals, whitespace, quotes, etc. An example could be for the game date: date; or for the game's visiting team: visiting_team
There can be as many columns as needed if and only if they are consistent amongst all sub-sheets.
The next part of the application flow is the Sync Script. This is script.py in the source code. The purpose of this file is to pull info from the Google Sheet and put it in a Firebase database. This could be swapped for another DB backend, but it would require some custom editing of the source.
There are some general configuration steps to take before running this script, and they are separated into three sections:
There are three variables, SHEET_ID, DATA_RANGE, and SHEET_NAMES.
SHEET_IDis the string ID token from your sheet's URL. It is documented further inscript.pyDATA_RANGEis a string representing the location of the data on the sheet, in Google Sheets range syntax.SHEET_NAMESis a Pythondict(key/value collection) of sheet names to sheet slugs. All of the sheet names must be listed, along with their respective slug names. More details inscript.py
There is only one dictionary variable, config that needs some information from Firebase. To get this information, you need to start a Firebase app.
Once you have, go to the Firebase console (generally top right of the Firebase website). On the sidebar, click on the Settings gear and you should see the Settings dashboard, on the General tab. Copy the Web API Key, and paste it in the apiKey field of config.
For the next three fields, authDomain, databaseURL, and storageBucket, replace the <YOUR_PROJECT_ID_HERE> with your project ID (found just above the Web API Key on the Settings page for your Firebase app).
This next step is optional, however, it is recommended for local environments, since it gives your script full access to your Firebase app, meaning no configuration pain for you.
NOTE: With great power comes great responsibility; don't expose this sensitive information, and understand the risks it entails. For the most part, you should be fine, as this API is read only
Click on the "Service Accounts" tab of the Settings menu, and click the blue button marked "Generate New Private Key", and click "Generate Key" in the dialog that pops up. This should download a JSON file to your computer. Rename that file service_auth.json and put it on the local app's root directory.
For this part of the project, you will need to make a project in the Google Developer's Console.
(copied from https://developers.google.com/sheets/api/quickstart/python; go there if you have any problems)
- Use this wizard to create or select a project in the Google Developers Console and automatically turn on the API. Click Continue, then Go to credentials.
- On the Add credentials to your project page, click the Cancel button.
- At the top of the page, select the OAuth consent screen tab. Select an Email address, enter a Product name if not already set, and click the Save button.
- Select the Credentials tab, click the Create credentials button and select OAuth client ID.
- Select the application type Other, enter the name "Google Sheets API Quickstart", and click the Create button.
- Click OK to dismiss the resulting dialog.
- Click the download button to the right of the client ID.
- Move this file to your working directory and rename it
client_secret.json.
There are several package dependencies this project has. For the server side Flask app, they are pre-installed, but for this script they have to be manually installed. Do this by running this command from the project root:
pip install -r requirements.txt
If you wish, make a Virtual Environment for this project to stop dependencies from mixing.
This should be all for the sync script configuration. To actually run it, invoke it like any other Python program: python script.py. You can also run this on an interval automatically with tools like cron or celery, but those are unfortunately not included from the get-go at this time.
The Flask app is a Python web app that serves the Firebase data in an API format.
To get it going, you will need to deploy a Google Compute Engine enabled app in the Developer's Console.
Note down your Google Cloud project ID from before (in the script.py step). This is your "project id" concerning this part of the guide.
The first step is to install the Google Cloud SDK and command line tools. At the end of the installer, make sure to check the options for Start Cloud SDK Shell and Run gcloud init. Follow the instructions from there. After the init command is complete, leave the shell window open, and navigate to your project directory on your local machine. Leave this for now; we will come back to it later.
Then, go back to your Google Developer Console, and open your project.
Navigate to the APIs & Services tab, and click the button labeled "Enable APIs and Services", and search and enable the "Google Compute Engine API".
If all went well, that should be it, and you can now go back to the shell window, and type
gcloud app deploy
to deploy your app to the cloud, and be able to use it in your projects.
The API is documented in the docs folder.
A sample HTML/JS implementation of the API can be found in the examples folder. It can be run locally or pasted into any website as a widget. Feel free to trim the library imports, like JQuery/Bootstrap if they are redundant.
To add a python package to your project, you should put it in requirements.txt, and run this from your project root: pip install -t lib -r requirments.txt