285 reads

Use Google Sheets as a Database, Responsibly: A Step-by-Step Guide

by Jacob LeeOctober 17th, 2020

Too Long; Didn't Read

Use Google Sheets as a database, Responsibly: A Step-by-Step Guide. We'll use Autocode, a Node.js API development platform and editor with built-in autocomplete, to deploy a simple app and handle Google's authentication process. I'll explore the limitations of the spreadsheet, including scalability, and where it makes sense to start looking at more complex alternatives. You don't need anything other than a Google account and a free Autocodes account to get started.

Companies Mentioned

featured image - Use Google Sheets as a Database, Responsibly: A Step-by-Step Guide

While database technology and other tools have come a long way, it's still tough to beat the humble spreadsheet's versatility and intuitiveness. While using them for sensitive, critical applications like storing COVID-19 patient data is ill-advised, the fact that everyone knows how to use a spreadsheet means they're great for smaller cross-functional projects where a non-developer might need to examine or edit data.

In this guide, I'll show you how to use Google Sheets as a database, complete with an API interface accessible over HTTP. We'll use Autocode, a Node.js API development platform and editor with built-in autocomplete, to deploy a simple app and handle Google's authentication process. I'll also explore the limitations of Google Sheets, including scalability, and where it makes sense to start looking at more complex alternatives.

# Returns all people in the database whose names start 
# with "bil", case-insensitive
$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/name/istartswith/?query=bil'

[
  {
    "Name": "Bilbo Baggins",
    "Job": "Burglar",
    "Fictional": "TRUE",
    "Born On": "9/21/1937",
    "Updated At": ""
  },
  {
    "Name": "Bill Nye",
    "Job": "Scientist",
    "Fictional": "FALSE",
    "Born On": "11/27/1955",
    "Updated At": ""
  },
  {
    "Name": "billie eilish",
    "Job": "Artist",
    "Fictional": "FALSE",
    "Born On": "12/18/2001",
    "Updated At": ""
  }
]

You don't need anything other than a Google account and a free Autocode account to get started. Let's go!

TL;DR (30s)

First, you'll need to clone your own copy of the template Google Sheet by clicking here, then pressing "Use Template" in the top right. This will add the sample spreadsheet to your personal Google account.

Once you've done that, click here to open the starter app in Autocode. Poke around the source code if you'd like, then install the app to your Autocode account by pressing the green button. When prompted to link a Google Sheet, follow the instructions in the flow to link your Google account, then select the spreadsheet you just cloned.

After that, your app should be ready to go! Try accessing a few of the endpoints via their URLs and see what is returned/what happens to your new spreadsheet database. You can check out the Endpoints section below for example calls.

Limitations

That tl;dr was easy, right? So why isn't everything built on Google Sheets?

While a backend that takes 30 seconds to set up and that everyone can interact with is extremely appealing, there are some obvious limitations. A spreadsheet as a database as described above doesn't naturally support multiple tables or relationships between rows. There's also no concept of enforcing types for a given column, transactions, built-in backups, or encryption, so sensitive/critical data (like COVID-19 patient data) is probably best stored elsewhere.

In terms of scalability, Google Sheets has a hard limit of 5,000,000 cells (including blank cells). When I tried to verify this by creating a spreadsheet with that many values, however, I encountered significant issues in performance before that threshold:

Mass-operations like pasting a large number of cells slowed, then began to fail at around 1m cells. Navigation was generally sluggish.

My experiments around making API calls yielded similar results. Query speed seemed to scale linearly with the number of cells:

Queries became impractically slow around the 500,000 cell mark, but were still below 2 seconds for a 100,000 cell query. Therefore, if you anticipate a dataset larger than a few hundred thousand cells, it would probably be smart to choose a more scalable option.

How It Works

When you link your cloned Google Sheet to your app and install it to your account, Autocode automatically handles authentication between your app and your Google account using your app's token (see the

const lib = require('lib')({token: process.env.STDLIB_SECRET_TOKEN})

line at the top of all the endpoints).

For the actual queries, each endpoint contains Node.js code that calls a method from the googlesheets.query API. These APIs take a parameter called

range

formatted in A1 notation that corresponds to the part of the spreadsheet the API call should consider as part of the database.

let queryResult = await lib.googlesheets.query['@0.3.0'].select({
  range: `A:E`,
  bounds: 'FULL_RANGE',
  where: [{
    'Name__istartswith': query
  }]
});

range

value of

A:E

is essentially shorthand for "use all rows in columns A through E in the spreadsheet as my database". The query interprets the first row of each column in that range as the field name of the values in that column. Given the template you cloned, the above query will check all values in the A column (named

Name

) for rows with a value matching the query.

These API calls use the KeyQL query language. If you're interested in a deep dive, you can check it out for more examples.

Calling Endpoints

As previously mentioned, these endpoints are accessible via HTTP, so you can make calls to them via

fetch

cURL

, or whatever other HTTP client you prefer. You can use your web browser directly:

And you can even use the same lib-node Node package that the endpoints use to call the Google Sheets APIs:

Your endpoints will respond to either GET or POST requests. Parameters are parsed from the querystring for GET requests and the request body for POST requests. Each endpoint has default parameters set for the sake of clarity. You can find examples for each endpoint below.

Endpoints

functions/select/job/contains.js

This endpoint is an example of a

contains

KeyQL query. It looks for rows in the linked Google Sheet where the

Job

field contains a substring (case-sensitive) matching the parameter

query

. From the sample sheet, it returns:

$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/job/contains/?query=ist'

[
  {
    "Job": "Mistborn",
    "Born On": "2006-07-17",
    "Fictional": "TRUE",
    "Name": "Vin Venture",
    "Updated At": ""
  },
  {
    "Job": "Scientist",
    "Born On": "1955-11-27",
    "Name": "Bill Nye",
    "Fictional": "FALSE",
    "Updated At": ""
  },
  {
    "Job": "Artist",
    "Born On": "2001-12-18",
    "Name": "billie eilish",
    "Fictional": "FALSE",
    "Updated At": ""
  }
]

functions/select/born_on/date_gt.js

This endpoint is an example of a

date_gt

KeyQL query. It looks for rows in the linked Google Sheet where the

Born On

field is after the

query

parameter, formatted as

2000/01/01

. From the sample sheet, it returns:

$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/born_on/date_gt/?query=2000/01/01'

[
  {
    "Job": "Mistborn",
    "Born On": "2006/07/17",
    "Fictional": "TRUE",
    "Name": "Vin Venture",
    "Updated At": ""
  },
  {
    "Job": "Artist",
    "Born On": "2001/12/18",
    "Name": "billie eilish",
    "Fictional": "FALSE",
    "Updated At": ""
  }
]

functions/select/name/istartswith.js

This endpoint is an example of a

istartswith

KeyQL query. It looks for rows in the linked Google Sheet where the

Name

field starts with the

query

parameter (case-insensitive). From the sample sheet, it returns:

$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/name/istartswith/?query=bil'

[
  {
    "Job": "Scientist",
    "Born On": "1955-11-27",
    "Name": "Bill Nye",
    "Fictional": "FALSE",
    "Updated At": ""
  },
  {
    "Job": "Artist",
    "Born On": "2001-12-18",
    "Name": "billie eilish",
    "Fictional": "FALSE",
    "Updated At": ""
  },
  {
    "Job": "Burglar",
    "Born On": "1937-09-21",
    "Fictional": "TRUE",
    "Name": "Bilbo Baggins",
    "Updated At": ""
  }
]

functions/insert.js

This endpoint is an example of an insert query. It passes the input parameters into the

fieldsets

parameter of the googlesheets.query.insert API. For example, to add Bill Gates to your spreadsheet, you could make the following request (all parameters are lower-case):

$ curl --request POST \
  --header "Content-Type: application/json" \
  --data '{"name":"Bill Gates","job":"CEO","fictional":false,"bornOn":"10/28/1955"}' \
  --url 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/insert/'

Note: Autocode APIs do not respond exclusively one HTTP method over another, and instead rely on descriptive naming to avoid confusion over functionality.

functions/update.js

This endpoint is an example of an update query. It sets the

Updated At

field of people whose names exactly match the

name

parameter, and updates other fields based on input parameters. It uses the googlesheets.query.update API.

For example, to update the

Job

field of

Bilbo Baggins

Ring Bearer

in your spreadsheet, you could make the following request (all parameters are lower-case):

$ curl --request POST \
  --header "Content-Type: application/json" \
  --data '{"name":"Bilbo Baggins","job":"Ring Bearer"}' \
  --url 'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/update/'

Note: This can affect multiple rows if more than one row matches the query conditions.

functions/delete.js

This endpoint is an example of a delete query. It removes rows of people whose names exactly match the

name

parameter. It uses the googlesheets.query.delete API. For example, to remove

Bilbo Baggins

from your spreadsheet, you could make the following request:

$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/delete/?name=Bilbo%20Baggins'

Note: This can affect multiple rows if more than one row matches the query conditions.

Thank You!

If you have any questions or feedback, the best thing to do is to join the Autocode community Slack channel. You can get an invite from the Community tab in the top bar on the website. You can also reach out to me directly on Twitter, @Hacubu.

If you want to stay up to date on the latest from Autocode, you can follow @AutocodeHQ. Happy hacking!