How to Use Google Sheets as a Database (Responsibly)
Tags: googlesheets, database, api, autocode
Published: 2020-10-07 07:00:16

---
title: How to Use Google Sheets as a Database (Responsibly)
published: true
description: Turn your spreadsheet into a database with examples for querying, inserting, updating, and deleting via API
tags: googlesheets, database, api, Autocode
cover_image: https://du2ha4sdmc25q.cloudfront.net/googlesheets/gsheets-database-example/src_ZRXDowU7gZg4aUcmXfdXD8DByy7K8RkFdPGc/readme/gallery/1-spreadsheet-template.png
---

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](https://autocode.com), 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.

<img src="https://du2ha4sdmc25q.cloudfront.net/googlesheets/gsheets-database-example/src_ZRXDowU7gZg4aUcmXfdXD8DByy7K8RkFdPGc/readme/gallery/1-spreadsheet-template.png">

```shell
# 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'
```
```json
[
  {
    "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](https://docs.google.com/spreadsheets/u/1/d/1Lhj0s2X9YD_4WIQ3DpubXLKevok4PqFP3EwDzVLDqyQ/template/preview). 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](https://autocode.com/app/googlesheets/gsheets-database-example/). 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](https://support.google.com/drive/answer/37603) (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:

![](https://stdlib-static.s3.us-west-2.amazonaws.com/files/autocode/google-sheets-unresponsive.png)

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: 

![](https://stdlib-static.s3.us-west-2.amazonaws.com/files/autocode/google-sheets-api-benchmarks.png)

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](https://autocode.com/lib/googlesheets/query). These APIs take a parameter called `range` formatted in [A1 notation](https://developers.google.com/sheets/api/guides/concepts) that corresponds to the part of the spreadsheet the API call should consider as part of the database.

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

A `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](https://github.com/FunctionScript/KeyQL). 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:

<img src="https://du2ha4sdmc25q.cloudfront.net/googlesheets/gsheets-database-example/src_ZRXDowU7gZg4aUcmXfdXD8DByy7K8RkFdPGc/readme/gallery/2-browser-access.png">

And you can even use the same [lib-node](https://github.com/stdlib/lib-node/) Node package that the endpoints use to call the Google Sheets APIs:

<img src="https://du2ha4sdmc25q.cloudfront.net/googlesheets/gsheets-database-example/src_ZRXDowU7gZg4aUcmXfdXD8DByy7K8RkFdPGc/readme/gallery/3-lib-access.png">

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:

```shell
$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/job/contains/?query=ist'
```
```json
[
  {
    "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:

```shell
$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/born_on/date_gt/?query=2000/01/01'
```
```json
[
  {
    "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:

```shell
$ curl --request GET --url \
  'https://YOUR_USERNAME.api.stdlib.com/gsheets-database-example/select/name/istartswith/?query=bil'
```
```json
[
  {
    "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](https://autocode.com/lib/googlesheets/query/#insert)
API. For example, to add `Bill Gates` to your spreadsheet, you could make the following request (all parameters are lower-case):

```shell
$ 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](https://autocode.com/lib/googlesheets/query/#update).
For example, to update the `Job` field of `Bilbo Baggins` to `Ring Bearer` in your spreadsheet, you could make the following request (all parameters are lower-case):

```shell
$ 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](https://autocode.com/lib/googlesheets/query/#delete). For example, to remove `Bilbo Baggins` from your spreadsheet, you could make the following request:

```shell
$ 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](https://autocode.com). You can also reach out to me directly on Twitter [@Hacubu](https://twitter.com/hacubu).

If you want to stay up to date on the latest from Autocode, you can follow [@AutocodeHQ](https://twitter.com/AutocodeHQ). Happy hacking!