G-Sheets as a Database

Feasibilities of Google Sheets that make it a good basic Database 

# Features that make it a good database
Google Sheets
  • Store Data in Rows and columns
  • Security
  • Consistency
  • Easy to Setup
  • Less in Cost
# Google API's
Google Api's for Sheets

  • Get
  • Update Single/Batch Cells
  • Delete Single/Batch Cell
Functionalities
Obstacles 

  • Lack of params for different CRUD Operations
  • Proper Documentations not Available

Idea and source of Motivation

# Idea
Idea: Build a ORM for Gsheets support using google API's as a dependency

Source of Motivations:

- Lack of a updated ORM with proper documentation.
  The only one that exists (https://github.com/mickhansen/google-sheets-orm) doesn't have a documentation so very difficult to use.

 

- Easy and fast setup

  : Setting up Google sheets thorugh Google Console Portal is so easy and fast to setup for quick running of a database service.

 

- Best Platform for non-tech people

  : Google Sheets can be a best platform non-technical people and the sheet can also be integrated to SQL databases like Postgres using tools like https://www.wax.run/

  • # PRESENTING CODE
Implementation in Javascript

const {google} = require("googleapis");

const keys = require("/keys.json");

const cl = new google.auth.JWT(keys.client_email, null, keys.private_key, [
    "https://www.googleapis.com/auth/spreadsheets",
]);

class Sheet {
    constructor(sheetId, sheetName) {
        this.sheetId = sheetId;
        this.sheetName = sheetName;
    }
    // find function to GET all Cells
  	async find() {
        try {
            const gsapi = google.sheets({version: "v4", auth: cl});
            const opt = {
                spreadsheetId: this.sheetId,
                range: this.sheetName,
            };
          
          	// Get all rows in the sheet. This response is a array of arrays
            let rows = await gsapi.spreadsheets.values.get(opt).data.rows;
            const response = [];
          	
            // converting array format to json with first row as key. 
            for (let row_id = 1; row_id < rows.length; row_id++) {
                let row = rows[row_id];
                let obj = {};
                for (const heading in rows[0]) {
                    obj[rows[0][heading]] = row[heading];
                }
                response.push(obj);
            }
            return response;
        } catch (err) {
            throw new Error("No such Sheet found");
        }
    }
}

const data_instance=new Sheet(env.SheetID,env.Sheetname)
const all_data=data_instance.find();

  • 1

  • Start with the basic CRUD requests that returns all the data.

  • 2

  • Add features like filtering by different query params

  • 3

  • Release a basic version on npm

  • 4

Add batch Update for all features 

Timeline

  • 5

  • Add advanced options such as virtuals and Eager Loading

thank you

- Rajiv Harlalka

- github.com/rajivharlalka

- rajivharlalaka.me

Â