Package Exports

@sheetbase/sheets-server

This package does not declare an exports field, so the exports above have been automatically detected and optimized by JSPM instead. If any package subpath is missing, it is recommended to post an issue to the original package (@sheetbase/sheets-server) to support the "exports" field. If that is not possible, create a JSPM override to customize the exports field for this package.

Readme

Sheetbase Module: @sheetbase/sheets-server

Using Google Sheets as a database.

Install

Using npm: npm install --save @sheetbase/sheets-server

import * as Sheets from "@sheetbase/sheets-server";

As a library: 1pbQpXAA98ruKtYTtKwBDtdgGTL_Nc_ayGzdRR2ULosG6GcKQJUF5Qyjy

Set the Indentifier to SheetsModule and select the lastest version, view code.

declare const SheetsModule: { Sheets: any };
const Sheets = SheetsModule.Sheets;

Scopes

https://www.googleapis.com/auth/spreadsheets

Usage

Docs homepage: https://sheetbase.github.io/sheets-server
API reference: https://sheetbase.github.io/sheets-server/api

Getting started

Install: npm install --save @sheetbase/sheets-server

Usage:

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

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

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

Configs

databaseId

Type: string

The spreadsheet id works as the database.

keyFields

Type: { [sheetName: string]: string }
Default: key field

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

Type: boolean | Object
Default: {}

Security rules for checking against the request:

true or {} = private, no read/write access anywhere
false = public, read/write to any sheet/table
Object = rule based access

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
    }
  }
}

securityHelpers

Type: Object
Default: {}

Additional helpers attached to data snapshot for security rule. Built-in:

only(props: string[]): if snapshot is an object abnd has only these properties

securityHelpers: {
  foo: snapshot => {
    return snapshot.val().foo === 'bar';
  },
}

// use in rule
{
  '.write': 'data.foo()' // equal: 'data.val().foo === "bar"'
}

AuthToken

Type: Class
Default: null

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 with orther modules (Auth, ...).
extend: create new Sheets instance from this instance.
toAdmin: create an admin instance from this instance.
registerRoutes: expose database routes.
ref: create a data service for a location.
key: generate an unique key.
all: get all items of a sheet/table.
query: query a sheet/table.
item: get an item of a sheet/table.
add: add an item.
update: update a item of a sheet/table.
remove: delete an item.
increase: increase/decrease a number field.

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-server/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:

where: (required) an item property to perform query on
equal: (optional) must exists and equal to (===)
exists: (optional) exists = true, not exists = false
contains: (optional) must be a string and contains the phrase (for array, user childExists)
lt|lte|gt|gte: (optional) less/greater than or equal
childExists: (optional) exists = key name or a value, not exists = add ! before key name.
childEqual: (optional) object only, exists = key=value, not exists = key!=value, child must be exists and equal to (===)

// 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");

increase

Increase/decrease a number field.

// increase likeCount by 1
Sheets.increase("foo", "foo-1", "likeCount");

// increase likeCount by 1 and counter by 1
Sheets.increase("foo", "foo-1", ["likeCount", "counter"]);

// increase counter by 3
Sheets.increase("foo", "foo-1", { counter: 3 });

// increase rating.count by 1
Sheets.increase("foo", "foo-1", { "rating/count": 1 });

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:

path: sheet/table name and item key
sheet or table: sheet/table name
key or id: item key
where, equal, ...: query condition

Get all item from 'foo':

sheet=foo
table=foo
path=/foo

Get an item from 'foo':

sheet=foo&key=foo-1
table=foo&id=foo-1
path=/foo/foo-1

Query from 'foo':

table=foo&where=abc&equal=xyz (same for other query)

POST `/database`

Add/update/delete/increase. Route body:

path: sheet/table name and item key
sheet or table: sheet/table name
key or id: item key
data: item data
increasing: increasing data

Add an item (PUT):

{ sheet: 'foo', key: 'foo-x', data: { ... } }
{ table: 'foo', data: { ... } } // auto generated key

Update an item (PATCH):

{ sheet: 'foo', key: 'foo-x', data: { ... } }

Remove an item (DELETE):

{ sheet: 'foo', key: 'foo-x' }

Increase a value (POST):

{ sheet: 'foo', key: 'foo-x', increasing: 'likeCount' }

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

now: current time
req: Sheetbase Router req object
auth: auth object
root: root data snapshot
data: data snapshot for current location
newData: data snapshot to be updated
inputData: data snapshot of input update data
$dynamic: any dynamic data

License

@sheetbase/sheets-server is released under the MIT license.

@sheetbase/sheets-server