Google Sheets DB

Table of Contents

• Google Sheets DB
  • Table of Contents
  • Description
  • Getting Started
    • Prerequisites
    • Initialization
  • Usage example
    • Instantiation
    • Access tables
    • .search(criteria)
    • .create(data)
    • .read(id)
    • .update(newData, id)
    • .delete(id)
  • Flaws
  • License

Description

Simple database Abstraction using a Google SpreadSheet as the underlying storage.
Provides a SCRUD implementation to handle your data.

You can access the project here as reader.

Getting Started

Prerequisites

• A google account;
• A GAS script;
• A google spreadsheets;

Initialization

You can ether make a copy of the source file and starting building your project over
OR
You can add it as a library : scriptID => 1aimWaOqHCGgHHFkCv18-LjLXKCRZd_YrQRrrSbNYceH1--BPr2uCwaYu

Usage example

Instantiation

    const ss = SpreadsheetApp.openById("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");

    // As part of the script
    const myDB = new GSDB(ss);

    // As Library
    const myDB = new LEM_GSDB.connect(ss)

Access tables

    // let's assume your spreadsheet has sheets "users", "user_roles","customers","logs", ...

    const users = myDB.users;
    const userRoles = myDB.user_roles;
    const userCustomers = myDB.customers;
    const userLogs = myDB.logs;
    ...

.search(criteria)

    myDB.users.search({ lastName: "doe" });
    /* returns 
        [
            { 
                id: 1,
                name: "john",
                lastName: "doe",
                email: "john@doe.com" 
            },
            {
                id: 2, 
                name: "jane", 
                lastName: "doe",
                email: "jane@doe.com" 
            }
        ];
    */

    myDB.users.search();
    // returns an array of all the records in the table.

.create(data)

    myDB.users.create(
        { 
            name: "julien",
            lastName: "lemarchand" 
        }
    );
    /* returns 
        {
            id: n+1,
            name: "julien",
            lastName: "lemarchand",
            email: "" 
        },
    */

    myDB.users.create(
        {
            name: "julien",
            lastName: "Lemarchand",
            eyesColor: "green",
        }
    );
    /* returns
        {
            id: n+1,
            name: "julien",
            lastName: "lemarchand",
            email: "" 
        }
        
    "eyeColor" is ignored here because it is not part of the table fields
    */

.read(id)

    myDB.users.read(2);
    /* returns
        { 
            id: 2,
            name: "jane",
            lastName: "Doe",
            email: "john@doe.com"
        }
    */

.update(newData, id)

    const idToUpdate = 1;

    //the following two result the same:
    myDB.users.update(
        { 
            id: idToUpdate, 
            lastName: "bonham",
            email: "bonzo@drummer.rip" 
        }
    );

    myDB.users.update(
        { 
            id: 4,
            lastName: "bonham",
            email: "bonzo@drummer.rip" 
        },
        idToUpdate
    );


    // returns { id: 1, name: "john", lastName: "bonham", email: "bonzo@drummer.rip" }

.delete(id)

myDB.users.delete(3);

Flaws

• GAS only provides auto-completion of top-level objects and functions for libraries. Due to the object approach of this project, there will therefore be no autocompletion available once the DB instance has been created. Adding it as a library to your project could be a bit frustrating to use, although the methods are quite simple.

License

This project is licensed under the MIT License - see the LICENSE file for details.