docs

Sheets Server

Using Google Sheets as a database.

Git repo: https://github.com/sheetbase/sheets/

Getting started

Install: npm install --save @sheetbase/sheets

Usage:

import { sheets } from "@sheetbase/sheets";

const Sheets = sheets(
  /* configs */ {
    databaseId: "Abc...xyz"
  }
);

const foo = Sheets.all("foo"); // => [{}, {}, {}, ...]

Configs

databaseId

The spreadsheet id works as the database.

keyFields

Key fields of tables.

keyFields: {
  foo: 'slug', // use the value of the 'slug' field as the key
  bar: 'xxx' // use the value of the 'xxx' field as the key
}

security

Security rules for checking against the request:

security: {
  foo: { '.read': true, '.write': true }, // read/write
  bar: { '.read': true } // read only
  baz: { '.write': true } // write only
  bax: {
    $uid: {
      '.read': '!!auth && auth.uid == $uid' // only authorize user can read
    }
  }
}

AuthToken

User management token class to decode auth token.

// import and create user instance (Auth)

Sheets.setIntegration("AuthToken", Auth.Token);

Sheets

CRUD interface for Sheetbase backend accessing Google Sheets.

setIntegration

Integrate Sheets module with orther modules (Auth, …)

// import and create user instance (Auth)
// const Auth = auth({ ... });

// integrate Token class to the Sheets instacce
Sheets.setIntegration("AuthToken", Auth.Token);

// then we may use `auth ` object in security rule
// { '.read': '!!auth && auth.uid == $uid' }

extend

Create new Sheets instance from this instance.

const SheetsAdmin = Sheets.extend({
  security: false // turn off security for this instance
});

toAdmin

Create an admin instance from this instance.

const SheetsAdmin = Sheets.toAdmin(); // will pass all security, security = false

registerRoutes

Expose database routes.

Sheets.registerRoutes({
  router: Sheetbase.Router, // Sheetbase router
  middlewares: [], // list of middlewares, [] = no middlewares
  disabledRoutes: [] // list of disabled routes, [] = no disabled
});

ref

Create a data service for a location. Data service interface: https://github.com/sheetbase/sheets/blob/master/src/lib/data.ts

const fooRef = Sheets.ref("/foo");

const foo1Ref = fooRef.child("foo-1"); // create a ref to '/foo/foo-1'
foo1Ref.parent(); // create a ref to '/foo'
const rootRef = fooRef.root(); // create a ref to '/'

fooRef.key(); // generate an unique id: -Abc...xyz

fooRef.toObject(); // retrieve data as an object
fooRef.toArray(); // retrieve data as an array

foo1Ref.update({ title: "Foo 1" }); // create foo-1 if not exists
foo1Ref.update({ title: "Foo 1 new title" }); // update foo-1 title if exists
foo1Ref.update(null); // delete foo-1

key

Generate a Firebase-liked unique key.

const key = Sheets.key(); // -Abc...xyz

all

Get all items of a sheet/table.

const foo = Sheets.all("foo"); // [{}, {}, {}, ...]
const bar = Sheets.ref("/bar").toObject(); // { item-1: {}, item-2: {}, item-3: {}, ... }

query

Query a sheet/table.

// simple query, all item from 'foo' has field1 === 'xxx'
const foo = Sheets.query("foo", { where: "field1", equal: "xxx" });
// shorthand for where/equal, pass in an object, format: { where: equal }
const foo2 = Sheets.query("foo", { field1: "xxx" });

// advanced query, all item from 'bar' has content field include 'hello'
const bar = Sheets.query("bar", item => {
  return !!item.content && item.content.indexOf("hello") > -1;
});

List of simple query:

// equal
// (title === 'Foo me')
Sheets.query("foo", { where: "title", equal: "Foo me" });

// exists
// (!!content)
Sheets.query("foo", { where: "content", exists: true });
// (!content)
Sheets.query("foo", { where: "content", exists: false });

// contains
// (title.indexOf('me') > -1)
Sheets.query("foo", { where: "title", contains: "me" });

// lt, lte, gt, gte
// (age < 18)
Sheets.query("foo", { where: "age", lt: 18 });
// (age >= 18)
Sheets.query("foo", { where: "age", gte: 18 });

// childExists
// (object, !!categories['cat-1'])
Sheets.query("foo", { where: "categories", childExists: "cat-1" });
// (object, !categories['cat-1'])
Sheets.query("foo", { where: "categories", childExists: "!cat-1" });
// (array, list.indexOf('abc') > -1)
Sheets.query("foo", { where: "list", childExists: "abc" });
// (array, list.indexOf('abc') < 0)
Sheets.query("foo", { where: "list", childExists: "!abc" });

// childEqual
// (categories['cat-1'] === 'Cat 1')
Sheets.query("foo", { where: "categories", childEqual: "cat-1=Cat 1" });
// (categories['cat-1'] !== 'Cat 1')
Sheets.query("foo", { where: "categories", childEqual: "cat-1!=Cat 1" });

item

Get an item of a sheet/table.

// get item by its key
const foo1 = Sheets.item("foo", "foo-1"); // { ... }

// second argument also accept the query arg (like query above)
// if only one item returned then it the item we need
// but if there is no item or more than 1 item, then it returns NULL
// so choose another unique field for query arg
const foo2 = Sheets.item("foo", { field1: "xxx" });

add

Add an item.

// add 'foo-x'
// { key: 'foo-x', title: 'Foo x' }
Sheets.add("foo", "foo-x", { title: "Foo x" });

// add a 'foo' with auto key
// { key: '-Abc...xyz', title: 'A foo' }
Sheets.add("foo", null, { title: "A foo" });

update

Update a item of a sheet/table.

// update foo-x title
Sheets.update("foo", "foo-x", { title: "Foo x new title" });

remove

Delete an item.

// delete 'foo-x'
Sheets.remove("foo", "foo-x");

Routes

To add routes to your app, see options AddonRoutesOptions:

Sheets.registerRoutes(options?: AddonRoutesOptions);

Default disabled

Disabled routes by default, to enable set { disabledRoutes: [] } in registerRoutes():

[
  "post:/database", // add/update/remove an item
  "put:/database" // add an item
  "patch:/database" // update an item
  "delete:/database" // remove an item
];

Endpoints

GET /database

Get all, query or item. Route query string:

Get all item from ‘foo’:

Get an item from ‘foo’:

Query from ‘foo’:

POST /database

Add/update/delete item. Route body:

Add an item (PUT):

Update an item (PATCH):

Remove an item (DELETE):

PUT /database

Add an item. Route body same as POST.

PATCH /database

Update an item. Route body same as POST.

DELETE /database

Remove an item. Route body same as POST, omit data field.

Security

Sheets Server comes with a rule based security.

To by pass security, add { security: false } in configs.

Rule-based

Allow all read and write (public).

{
  '.read': true,
  '.write': true
}

The module borrow idea from Firebase Realtime Database, see https://firebase.google.com/docs/database/security/quickstart#sample-rules

Rule objects