Bart Broere

Freelance Developer

Using a Google Spreadsheet as an app's backend. What could possibly go wrong?

02 Aug 2018

TLDR: You could use a Google Spreadsheet as the backend of an app, but you really shouldn’t. Using Python to programmatically update parts of a spreadsheet makes way more sense than using a spreadsheet to get data in Python.

Imagine making an app for someone that would like to distribute a schedule for an event, that can change often during the event. You can’t include the schedule into the app itself, because users might end up with outdated data. Probably, you’ll choose to set up a REST API with some endpoints.

Now you need a way for the event organiser to update data. And what administrative interface would be more familiar to an event organiser than a spreadsheet?

What if I told you, you could use a Google Spreadsheet as a database backend for your app? With the Python packages gsheets and an HTTP server like Flask, you can have your API endpoint online in a matter of minutes. You are not even limited to reading data: gsheets can write to the sheet as well.

In the example code below, we want to get a list of sessions, with each session hosting multiple pitches. We do this join by letting columns in the spreadsheet refer to id columns of rows on a different sheet.

from flask import Flask, Response
from gsheets import Sheets
import json
from datetime import datetime

sheets = Sheets.from_files('client_secret.json', 'storage.json')
spreadsheet = sheets['identifier-of-your-spreadsheet-here']
app = Flask(__name__)


def process_sheet(spreadsheet):
    sessions = spreadsheet.find('Sessions')
    pitches = spreadsheet.find('Pitches')
    sessions_merged = []
    for session in sessions.values()[1:]:
        included_pitches = []
        for pitch in pitches.values():
            if pitch[4] == session[0]:  # nested for loops are a very sad and 
                                        # expensive way to join data
                included_pitches.append({"title": pitch[3],
                                         "speaker": pitch[1],
                                         "summary": pitch[2]})
        sessions_merged.append(
            {"name": session[1],
             "start": datetime.strptime(session[3], 
                                        '%d-%m-%Y %H:%M').isoformat(),
             "end": datetime.strptime(session[4], 
                                      '%d-%m-%Y %H:%M').isoformat(),
             "location": session[5],
             "host": session[2],
             "summaries": included_pitches
             }
        )
    return sessions_merged


@app.route('/sessions')
def sessions():
    response = Response(json.dumps(process_sheet(spreadsheet)))
    response.headers['Access-Control-Allow-Origin'] = '*'
    return response
    
    
if __name__ == "__main__":
    app.run()

Obviously, this code is far too brittle to be anywhere near a production system. Any wrong input, data type, change of column order, empty id column, empty row, or other change could crash the complete API endpoint. The sweet spot for using a script like this is when making a rapid prototype (i.e. hackathons). The admin backend is just a Google Spreadsheet, saving you valuable time developing a boring backend.

Please consider that spreadsheets:

Fair warning: Never YOLO a script like this into production. If you really want to use Google Spreadsheets as your production backend, you should add more checks and safeties (and maybe look into some of Google’s actual database services).

Nevertheless, it’s cool Google Spreadsheets has an API. In my opinion, using Python to programmatically update parts of a spreadsheet makes way more sense, than using a spreadsheet to get data in Python.

Edit: After posting this code snippet, I found an article describing the perfectly named npm module get-sheet-done. With this, you can remove the Flask API from the equation entirely, and interact with the Google Sheets API directly from JavaScript. In this case, your spreadsheet needs to be publicly visible though.

Related Posts