Free real-time hassle-free database using Google Sheets

Andrew Yip
Jul 14, 2018 · 3 min read
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:

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!

DevCJeddah

Bite-size technical hands-on from the Developer Circles…

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store