Google Sheets as a Database

by Burak Kanber

Today I'm launching Scrumsheet, a Scrum workflow app. Unlike other apps, Scrumsheet keeps all your data in a single Google Sheet.

Does this count as a "serverless" app? Probably; if you consider apps built on Firebase to be serverless, then Scrumsheet is too.

What excites me most about this project is the fact that I don't need your data to give you a good Scrum app. Asana, Trello, Pivotal – why do they need to know about your tasks and todos? Why do they need to store them in some centralized server?

By using Google Sheets as a Database, the user gets to control their data fully. If the app ever stops working, you can continue to use the Google Sheet. If one person on your Scrum team hates the app, they can use the Google Sheet. If you ever decide to export all your data, great – it's right at your fingertips! You can also import a ton of data with ease; it's just a spreadsheet.

How it Works

Scrumsheet is an Electron app. I quickly discovered that the Google Javascript Client Library does not work in an Electron environment, so all API requests must be made via REST/HTTP directly. Fortunately, the Google APIs are easy to work with.

Authentication

The first challenge is setting up Google Authentication. Normally, the client library would help you with this, but instead we need to use HTTP requests directly.

Using Google's OAuth guide works, but with one trick: in an Electron app, the "redirect URI" is meaningless. Therefore, when managing the authentication window, you must add some event listeners to your new Electron BrowserWindow to capture redirects and trap the authorization code.

SheetDB

I wrote a lightweight Javascript library that keeps the Google Sheet in sync with the application state. This helper class needs to be able to manage a handful of tasks:

  • Read worksheets in a spreadsheet, parse their data, and determine if the worksheet contains "People", "Stories", "Standups", or "Retros".
  • Ensure worksheets with a given schema (eg, "ensure the People sheet with the columns: id, name, email, role).
  • Append a row/record to a worksheet
  • Edit an individual row in a worksheet, by predicate
  • Reorder rows in worksheets

Reading worksheets and parsing them is a simple task; appending rows is simple too. We simply use the Google Sheets API for these tasks. The more interesting item here is what happens when you edit a row or reorder it.

Google Sheets' API provides a 'batchUpdate' endpoint, which works similarly to database transactions. This is very useful when running the combo action of updating a story's status (ie, moving between board columns), while also changing its ordering (ie, moving up or down, placing between items). When the user takes this action, the app builds a batchUpdate that first edits the row's cell values, and then moves the row to the proper position. Just like database transactions, this will rollback the entire editing process if there's a failure anywhere in the batch.

SheetDB also interacts with the Google Drive Comments API to power the "discussion" features of Scrumsheet. When you add a comment to a scrum project, that simply adds a Google Drive comment to your spreadsheet!

UI Around Data

It's a common adage that most apps are simply CRUD UI wrappers around databases. Scrumsheet, to me, is the purest expression of that adage. We start with a database – doesn't matter what, but in this case it's a Google Sheet – and then wrap its functionality with a beautiful UI.

The difference here is that, unlike with a SQL database, using a Google Sheet allows the user to interact "safely" with the data both through the UI and through the database directly. Sure, some developers do maintenance through a SQL prompt, but it's rare to be able to do your daily work interacting with data directly – no longer!

Get Scrumsheet

Visit Scrumsheet's homepage to learn more and download Scrumsheet.