GSheetDB

A simple way to use Google Sheet as your Data Base with own authentication system.

No google workspace project is needed.

Install

pip install gsheetdb

Setup

1. Create a google sheet
2. Add as many sheets (tables) as you want
3. First line is table header, first column must be id
4. Table relationship: set a column header with the name of a table, its content is the id (or ids) of the other table
5. Go to "Extensions > App Script"
6. Copy the content of the file gsheet.js to the current file.
7. Create a new deploy: "Deploy > New deployment"
   • Select type: "Web app"
   • Description: Anything you wanted
   • Execute as: "Me (your_email@gmail.com)"
   • Who has access: "Anyone"
8. Copy Deployment ID

Usage

from gsheetdb import Sheet

const sheet = Sheet({ 'deploymentId': '123456789abcdef' })

Get Tables Schemas

sheet.get()

Get Table Item

Return all items

const data = sheet.get('Sheet1')

Add Item

Ids are generated automaticaly

sheet.set('Sheet1', [
    {'col1': 'val1', 'col2': 2, 'col3': datetime.datetime()},
    {'col1': 'val2', 'col2': 3, 'col3': datetime.datetime()},
])

Modify Item

Same API as set but with id. If id doesn't exist, it fails.

sheet.set('Sheet1', [
    {id: 1234, 'col1': 'val2'}
])

Delete Item

Remove by item ids

sheet.rm('Sheet1', [1234])

New Table

sheet.new('MyNewSheetName', ['field1', 'field2', 'field3'])

Query Items

Add the query to get function.

Query can be object or array.

General rules:

• =: field: value
• !=: field: {ne: value}. ne stands for "not equal"
• >: field: {gt: value}. gt stands for "greater than"
• <: field: {lt: value}. lt stands for "lower than"
• >=: field: {ge: value}. ge stands for "greater or equals to"
• <=: field: {le: value}. le stands for "greater or equals to"
• AND: curly brace {A, B, C} read as "A and B and C"
• OR: square brace [A, B, C] read as "A or B or C"

sheet.get('Sheet1', {'col1': 123})

sheet.get('Sheet1', {'col1': 123, 'col2': 456})

sheet.get('Sheet1', [{'col1': [123, 456]}])
// OR
sheet.get('Sheet1', [{'col1': 123}, {'col1': 456}])

sheet.get('Sheet1', {'col1': {'gt': 123}})

sheet.get('Sheet1', {'col1': {'lt': 123}})

sheet.get('Sheet1', {'col1': {'ge': 123}})

sheet.get('Sheet1', {'col1': {'ge': 123}})

sheet.get('Sheet1', {'col1': {'gt': 123, le: 456}})

sheet.get('Sheet1', {'col1': [{'gt': 1, le: 3}, {'ge': 14, 'lt': 16}]})

sheet.get('Sheet1', {'col1': [30, {'gt': 1, 'le': 3}, {'ge': 14, 'lt': 16}]})

Auth (Optional)

Create a table _user with the following columns:

1. id to identify each user.

2. token or username and password depending on how you want to do the login.

3. permission should contain either admin, user or blocked (default).

   • admin can read (r), write (w) and delete (x) access to all tables.
   • user can only read recursively tables that reference its user's id.
   • blocked can not do rwx.

4. read allow or disable read to tables, give their names splited by ",".

5. write allow or disable write to tables, give their names splited by ",".

6. delete allow or disable delete to tables, give their names splited by ",".

You can change the name of Auth table here.

Get auth info

sheet.me()