@webfaster.com/sheetsdb NPM

SHEETSDB

Sheetsdb is a Javascript client to use Google sheets as your primary database.

Why

We saw an increase in use-case where people wanted to have a very simple interface to manage their data. They also wanted to create, read, update and delete their data easily within the tools they already knew. We could't find a better solution than connecting one of the most used tools and use it as the primary database.

Advantages

No need of a Hosting provider (Thank you Google)
Super easy to setup!
A very clean/user friendly interface (Google Sheets)
All the import modules you can think of
Very lightweighted, no indexes/no advances queries etc.
Create your custom formulas (awesome) in Google sheets (easy data manipulation)
Very easy to update & manage the data
Create all charts & insights you want

Disadvantages

Not scalable (I guess a limit of +-1000 rows)
No advanced queries possible (yet)
Not the best performance (slower than existing databases eg. startup time)
Easy to have a bad Security (everyone with access to the sheet can edit it)

Google sheets Limitations

I don't hope/think that you'll reach them. Otherwise you should definitely look for a better database solution (eg. MongoDB), but here are the limits of Google sheets.

Up to 5 million cells for spreadsheets that are created in or converted to Google Sheets
Maximum number of columns of 18,278 columns
Number of Tabs: 200 sheets per workbook
GoogleFinance formulas: 1,000 GoogleFinance formulas
ImportRange formulas: 50 cross-workbook reference formulas
ImportData, ImportHtml, ImportFeed, or ImportXml formulas: 50 functions for external data
Maximum string length is 50,000 characters

From our experience we saw lacking/bugging/slowing down Google sheets from 40.000 rows. But again, if you have this amount of data, you should definitely look into better solutions.

When to use this database type?

Using Google sheets as your primary database is ideal if:

You need a clean interface and you have knowledge about Google sheets / excel.
You have a small dataset (< 5000 rows)

Documentation

This page is the official documentation page. The current version of SheetsDB is 1.0.0 and was released on July 15, 2020. Feel free to open issues on Github and/or solve them yourself.

Contributors

Pull requests are always welcome! Please base pull request against the master branch.

Installation

First install Node.JS and make sure you have a Google drive account. Make sure you enable the Google Sheets API here: https://console.developers.google.com/apis/library/sheets.googleapis.com.

npm install @webfaster.com/sheetsdb --save

Importing

To create a new connection with the database, you should import the library. Next you should generate new API-keys from the Google developer console (see link above). Here you can create a private_key, client_email. The url parameter is the full url of your Google Sheet.

// Using Node.JS `require`
const db = require('@webfaster.com/sheetsdb');

// Connect to your database
db.connect({
  url: env.url,
  clientEmail: env.client_email,
  privateKey: env.private_key
});

Once you're connected with the Google sheet, the library will parse all sheets and cache all the data. No further action is required. From this moment on, you can access a CRUD (create, read, update, delete) from gdb.collections['tab-id'].

To make an example call to the database (eg. createOne), it should work as follows:

await gdb.collections.persons.createOne({
  email: 'timothy@example.com',
  firstname: 'Timothy',
  lastname: 'Verhaeghe'
});

A good practice that we do is to "map" the collections string to a cleaner variable (eg. gdb.collections.persons is stored in a Persons variable). This will give us the following code snippet.

await Persons.createOne({
  email: 'timothy@example.com',
  firstname: 'Timothy',
  lastname: 'Verhaeghe'
});

Overview

1. Create new item

To create a new item in the database, you should use createOne:

// Persons.createOne(newObj)
await Persons.createOne({
  email: 'timothy@example.com',
  firstname: 'Timothy',
  lastname: 'Verhaeghe'
});

2. Find an item

To find ONE specific item in the database, you should use findOne:

// Persons.findOne(query)
await Persons.findOne({
  email: 'timothy@example.com',
});

3. Find all items

To find ALL items that match a specific criteria in the database, you should use find:

// Persons.find(query)
await Persons.find({
  email: 'timothy@example.com',
});

4. Update an item

To update an item in the database, you should use updateOne:

// Persons.updateOne(query, newObj)
await Persons.updateOne({
  email: 'timothy@example.com',
}, {
  firstname: 'Morgan',
  lastname: 'Freeman'
});

5. Remove an item

To remove a given item from the database, you should use removeOne:

// Persons.removeOne(query)
await Persons.removeOne({
  email: 'timothy@example.com',
});

Important notes

There is a short delay (5 min) between update in the library & update in Google sheets (this is required due to Google sheets quota limits)
Advanced queries like joins, innerjoins etc. are not yet supported due to the nature of Google Sheets API.
Collection/Sheet names should always be lowercase & without spaces! (naming convention)
Each collection should always have a column called id, createdAt, updatedAt (just add the fields, we'll do the rest)
Header rows should be there! If no header row, it would not work.
New lines are always added at the bottom of the sheet. If you add some fields with auto calculation and you apply it to the entire page, this will be seen as a cell with a value and thus not be overwritten.
raw cannot be used as header value
You should set id / createdAt / updatedAt as a field (otherwhise you can't use these fields)

Known issues

Feel free to open new issues on Github.

Auto converting strings like 8510 MARKE (zipcode in Belgium) will be parsed as timestamp

Todo

if you edit something in Google sheets => Get alert message
Write a syncing function with MongoDB (so you have a Google sheets interface, but a MongoDB backend).
updateAll, deleteAll
Make it possible to create "advanced"
Search for the "limits" (max 100 rows ofzo)
Create an CLI tool (so that you as a developer can list everything etc.)
Write better documentation
Use Axios instead of Requests (depreciated)