Free real-time hassle-free database using Google Sheets

Photo by Sanwal Deen on Unsplash

Most CRUD app development work boils down to effective data visualization and affording users an intuitive user interface (UI) to curate their data. The same can be said about database management, especially for your non-tech savvy client, who does everything on a spreadsheet. This got me thinking:

What if we can build a database for our user-facing app using a data management tool that is familiar to our clients?

The answer is a cloud-based spreadsheet solution that has an API, in other words, Google Sheets.

GSAD makes everyone happy

Using Google Sheets as a Database (GSAD), you’ll get the following for free:

1. App owner manages the database like a spreadsheet
2. Users get to contribute data through a familiar Google Form submission
3. The database is on Google Drive, so it’s basically free hosting, and zero-hassle to you, the developer.

In the following, I will walk you through:

1. How to setup a google sheet to serve as a database
2. How to get your app talking to the database
3. How to brief your users in submitting and editing their data entries
4. How to educate the app owner in managing the database

Setup Google Sheets as a database

Create a Google Sheet in your Google Drive, treat it like a relational database and setup the fields using the first row as header. Note that the column names can only be of lower-case letters with no special characters or spaces.

For your app to communicate with the Google Sheet in a read-only, anonymous way, publish your sheet to the web (File > Publish to the Web…). Don’t forget to toggle the auto-republish feature so that every edit would be live, right-away.

To make the spreadsheet UI more intuitive, it’s often best to freeze the first row and/or the first column that serves as the record identifier to prevent your client from killing your app accidentally.

Get your app ready to talk to the database

There are libraries in various languages that make it easy to communicate with Google Sheets API. Here I’ll show an example in node.js using the node-google-spreadsheets package with promise support in ES6 syntax with the yarn package manager.

Install the necessary packages to your project: yarn add google-spreadsheets bluebird

A minimal example that retrieves all rows as an array in a promise-ready fashion:

import { rows } from 'google-spreadsheets'
import { promisify } from 'bluebird'
const getRows = promisify(rows)
const sheetId = [SHEET_ID]
getRows({
        key: sheetId,
        worksheet: 1 // start counting with 1
      }).then(res => console.log(res))

This code snippet will print the rows as an array to the console. Note that sheetId is the string that follows the url https://docs.google.com/spreadsheets/d/ as you open the Google Sheet in the browser, and the arrangement of the worksheets inside the Sheet matter, with the left-most one being 1.

Brief the user

For users to submit new records (rows in the spreadsheet), you can simply link a Google Form to a worksheet where users can submit and edit their responses. This works great for information that doesn’t change too often, for example, in a restaurant review or job posting scenario.

Educate the client

For the client, you need to provide edit permission for the Google account that would peruse/manage the Sheet. Be sure to remind them of the following:

1. Do not alter the header (first row) and the record identifier (if there is one)
2. Do not reorder the worksheets (as they are accessed by their position in the app)
3. It’s just like any other spreadsheet: if they screw up, just ctrl + z

Now you have a scalable, time-machined, real-time database that is easy to use and ready to integrate.

Go forth and make great user experiences!